Recovery if all controlfiles are missing

Problem Generation



i. Shutdown Database.
SQL>  SHUTDOWN immediate;




ii. Delete all  Control files from operating system
[oracle@odb]$ rm -i  /u01/app/oracle/oradata/disk1/ocmdb/control01.ctl
[oracle@odb]$ rm -i /u01/app/oracle/admin/ocmdb/control/control02.ctl


iii. Connect as sysdba and startup Database.
[oracle@odb]$ sqlplus / as sysdba
SQL> STARTUP;
ORACLE instance started.
Total System Global Area  536870912 bytes
Fixed Size               1220460 bytes
Variable Size          222298260 bytes
Database Buffers       310378496 bytes
Redo Buffers             2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info




Solution


i. Using RMAN restore contolfile from Autobackup and Recover Database
RMAN> run
 {
 restore controlfile from autobackup;
 sql ‘ alter database mount’;
 recover database;
 sql ‘ alter database open resetlogs’;
}




ii. List Database Incarnation
RMAN> list incarnation;
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID         STATUS  Reset SCN  Reset Time
——- ——- ——– —————- — ———- ———-
1    2      OCMDB 4083722939    PARENT  1        15-JAN-11
1    1415 OCMDB 4083722939    PARENT  503961 29-JAN-11
1    1867 OCMDB 4083722939    CURRENT 519078 29-JAN-11




iii. After database is open switch logfile few times




SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;




v. Always backup controlfile after open resetlogs even if CONTROLFILE AUTOBACKUP ON. Let us NOT BACKUP controlfile and see what happens in next scenario.
RMAN> backup current controlfile;  — For now do not execute this command