Recover database without control files and redo log files

Again there has been some gap in my writing of articles. Today I will share one of my experience faced recently. We came to know that one of the databases crashed. After some investigation we found that we only had data files and initialization file intact. All other files i.e. control files and redo log files were lost. This was a development database and the control files and log files were not multiplexed (First mistake). With only data files, how would you recover a database with minimal loss? We even did not have consistent backup for the database (Second mistake). After doing some research, we finally decided to give a go to recover the database with available data files and initialization file.

The steps followed to recover the database:

Startup the database with the initialization file. As we do not have the control files, start the database in no mount state.

SQL> startup nomount 
ORACLE instance started. 

Total System Global Area  209715200 bytes 
Fixed Size                  1248140 bytes 
Variable Size              75498612 bytes 
Database Buffers          130023424 bytes 
Redo Buffers                2945024 bytes 
SQL>

Check the path of control files.

SQL> show parameter control 

NAME                                 TYPE        VALUE 
------------------------------------ ----------- ------------------------------ 
control_file_record_keep_time        integer     7 
control_files                        string      K:\ORCL10G\CONTROL\CONTROL01.C 
                                                 TL, K:\ORCL10G\CONTROL\CONTROL 
                                                 02.CTL, K:\ORCL10G\CONTROL\CON 
                                                 TROL03.CTL

Having the details of all the data files at hand recreate the control files.

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL10G" RESETLOGS NOARCHIVELOG 
    MAXLOGFILES 16 
    MAXLOGMEMBERS 3 
    MAXDATAFILES 100 
    MAXINSTANCES 8 
    MAXLOGHISTORY 292 
LOGFILE 
  GROUP 1 'K:\ORCL10G\LOG\REDO01.LOG'  SIZE 50M, 
  GROUP 2 'K:\ORCL10G\LOG\REDO02.LOG'  SIZE 50M, 
  GROUP 3 'K:\ORCL10G\LOG\REDO03.LOG'  SIZE 50M 
DATAFILE 
  'K:\ORCL10G\DATA\SYSTEM01.DBF', 
  'K:\ORCL10G\DATA\UNDOTBS01.DBF', 
  'K:\ORCL10G\DATA\SYSAUX01.DBF', 
  'K:\ORCL10G\DATA\USERS01.DBF', 
  'K:\ORCL10G\DATA\EXAMPLE01.DBF', 
  'K:\ORCL10G\DATA\UNDOTBS02.DBF' 
CHARACTER SET WE8MSWIN1252; 

Control file created.

Note the RESETLOGS option in the create control file script. This will reset the logs and synchronizes the SCN between the database files, control files and redo log file. Oracle will re-create the redo log files when the database is opened with resetlogs options.

Once the control file is created, try mounting the database. If the database mounts well then everything seems to be fine.

SQL> alter database mount; 

Database altered.

The database mounted successfully. Open the database with resetlogs option.

SQL> alter database open resetlogs; 

Database altered.

Here we go. The database opened successfully. The only point to consider is that of the data loss. The data in the redo log files, as it existed before the loss, will be lost. Hence the data loss here could be minimum.

Conclusion :

1. Always multiplex the Control files and Redo log files.
2. Have a consistent backup of the database.