Oracle 11g: Recover the loss of a control file

Using RMAN

• Shutdown the database
• Statup the database in NOMOUNT
• Use rman to recover the control file :

  $>rman target /
  rman>startup nomount
  rman>restore controlfile from '+DATA/<oracle_sid>/controlfile/current.xxx.yyyy';
  rman>alter database mount;
  rman>alter database open;
  

You can also restore the control file from an autobackup :

...
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
...

Using files from file system

• Check the alert log file to retrieve the lost controlfile
• Shutdown the database
• Copy a multiplexe control file (remember control file must be multiplexed and Oracle recommends having at least three copies of the control file) into the missing one.
• Startup the database in the NOMOUNT state
• Execute the following command under SQLplus:
  

  SQL> RECOVER CONTROLFILE USING BACKUP CONTROLFILE UNTIL CANCEL;

• Open the database with RESETLOGS option:
  

  SQL> ALTER DATABASE MOUNT;
  SQL> ALTER DATABASE OPEN RESETLOGS;