Recover database if it is in between crashed

Create New database using old datafiles.

For this we will use oradim approach which is generally used while manual database creation.

prerequisite:
- New window.
- No previous instance.
- Cold backup or copied datafile ( while database running , it doesn't matter).
- Oracle should be installed with option ( software only ).
- Chose new instance name ( here we are using PROD).
- Directory structure should be same as in pfile.


Start 1,2,3 go :-)

1. Open cmd

set oracle_sid=prod
oradim -new -sid prod -intpwd prod -startmode m -pfile D:\app\pfile.ora

You might got this error
DIM-00014: Cannot open the Windows NT Service Control Manager.
solution open cmd with "run as administrator"

2. Below is the content of my pfile.ora file

db_name='PROD'
memory_target=1G
processes = 150
audit_file_dest='D:\app\username\admin\adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest=D:\app\username\admin\prod\flash_recovery_area
db_recovery_file_dest_size=2G
diagnostic_dest=D:\app\username\admin\prod\diagnostic_dest
dispatchers='(PROTOCOL=TCP) (SERVICE=PRODXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = ("D:\app\username\oradata\prod\CONTROL1.ctl")
compatible ='11.2.0'

3. At this stage we need to create control file after startup database at nomount state
    connect with sysdba user
   (before creation of control file and reuse of datafile , copy all the datafiles and redo log file)

C:\Windows\system32> sqlplus sys/prod as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 9 15:35:48 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile='D:\app\pfile.ora'
ORACLE instance started.

Total System Global Area  644468736 bytes
Fixed Size                  1376520 bytes
Variable Size             192941816 bytes
Database Buffers          444596224 bytes
Redo Buffers                5554176 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS ARCHIVELOG
  2  MAXLOGFILES 16
  3  MAXLOGMEMBERS 3
  4  MAXDATAFILES 100
  5  MAXINSTANCES 8
  6  MAXLOGHISTORY 18688
  7  LOGFILE
  8  GROUP 1 'D:\app\username\oradata\prod\REDO01.LOG' SIZE 50M,
  9  GROUP 2 'D:\app\username\oradata\prod\REDO02.LOG' SIZE 50M,
 10  GROUP 3 'D:\app\username\oradata\prod\REDO03.LOG' SIZE 50M
 11  DATAFILE
 12  'D:\app\username\oradata\prod\SYSTEM01.DBF',
 13  'D:\app\username\oradata\prod\SYSAUX01.DBF',
 14  'D:\app\username\oradata\prod\UNDOTBS01.DBF',
 15  'D:\app\username\oradata\prod\USERS01.DBF',
 16  'D:\app\username\oradata\prod\TRY01.DBF',
 17  'D:\app\username\oradata\prod\LOB_INDEX.DBF'
 18  CHARACTER SET AL32UTF8
 19  ;

Control file created.

after creation of control file the system will automatically move to MOUNT State.

 (media recovery needed) 

SQL>  RECOVER DATABASE;
Media recovery complete.

SQL> ALTER SYSTEM ARCHIVE LOG ALL;

System altered.

SQL> alter database open;

Database altered.

SQL> conn scott/tiger
Connected.

Thats it.. 

Now we need to create spfile from pfile, so that database can startup without any problem after shutdown

 create spfile from pfile='D:\app\pfile.ora';

You may also like this post
Oracle de install / clear all previous directory