Loss of Control File

Scenario: Database is running in archive log mode and control file is lost when database is running, we need either old control file backup or control file creation script ready to recover the database without any data loss

1. Shut down the database and take the backup of Control Files, redo log files and data files

2. Though not required, an ASCII backup of control file, we can use this if control file backup is not available or not working

SQL> startup;

SQL> alter database backup controlfile to trace as '/home/oracle/control_CHARAN.sql';

3. Edit the control file creation script and remove all comments and unnecessary lines and keep only the control file creation script

4. Insert some rows in a test table

5. Delete the control file

6. You can do commits/log switches even if control file is not available, shut abort the database

7. Restore the backup control file and mount the database

SQL> startup mount;

Perform recovery

SQL> alter database recover automatic using backup controlfile until cancel;

cancel recovery when Oracle prompts for un generated archive log

SQL> recover cancel;

Since some transactions were performed after the control file was deleted, Oracle asks to recover those transactions, which are in online redo log files, we need to initiate another recovery session and pass each online redo log file as input until the recovery was completed/data files are consistent.

SQL> recover database using backup controlfile until cancel;

SQL> alter database open resetlogs;

Now lets do the same exercise using control file creation script

1. Perform some transactions and delete control file and shut abort the database

2. Start the instance in no mount state and run the control file creation script

Database will be automatically mounted when the control file is created.

3. Perform the recovery as listed above

SQL> alter database recover automatic using backup controlfile until cancel;

SQL> recover cancel;

SQL> recover automatic using backup controlfile until cancel;

SQL> alter database open resetlogs;

That's it!!!

Regards

Charan