Oracle 11g Recovery from Backup Control File

Recovery from Backup Control File due to loss of all Control Files
------------------------------------------------------------------

1.
SQL> Select name
From v$controlfile;

2.
SQL> connect sys as sysdba
SQL> shutdown immediate;

3.
To simulate loss of control file rename the control files listed in step 1 above.

4.
SQL> startup

O/P -> ORA-00205: error in identifying control file, check alert log for more info

5.
Copy the control file from backup location to the location shown in step 1 above
(Where the control file should be)

$cp /u01/oracle/orabackup/orcl1/hot/control1.bak /u01/app/oracle/oradata/orcl1/controlfile/control01.ctl
$cp /u01/oracle/orabackup/orcl1/hot/control1.bak /u01/app/oracle/oradata/orcl1/controlfile/control02.ctl
$cp /u01/oracle/orabackup/orcl1/hot/control1.bak /u01/app/oracle/oradata/orcl1/controlfile/control03.ctl

or

SQL> host cp /u01/oracle/orabackup/orcl1/hot/control1.bak /u01/app/oracle/oradata/orcl1/controlfile/control01.ctl

SQL> host cp /u01/oracle/orabackup/orcl1/hot/control1.bak /u01/app/oracle/oradata/orcl1/controlfile/control02.ctl

SQL> host cp /u01/oracle/orabackup/orcl1/hot/control1.bak /u01/app/oracle/oradata/orcl1/controlfile/control03.ctl

6.
SQL> alter database mount;

7.
SQL> recover database using backup controlfile;

O/P -> may have ORA-00279
followed by ORA-00289
followed by ORA-00280...
Specify log: filename Auto Cancel

8.
Type
auto

O/P -> may have ORA-00308
followed by ORA-27037...

9.
Needed if step 8 did not complete with 'Media recovery complete'.

SQL> Select lf.group#, lf.member, l.sequence#
From v$logfile lf, v$log l
Where lf.group# = l.group#;

Note the member and Sequence#

10.
Start recovery again

SQL> recover database using backup controlfile;

O/P -> ORA-00279
followed by ORA-00289
followed by ORA-00280...
Specify log: filename Auto Cancel

11.
Enter the member value of sequence# for which error was given in step 10 above

e.g./u01/app/oracle/oradata/orcl1/onlinelog/redo01.log

O/p -> Media recovery complete.

----------------------------------------------------------

- Suresh