How to recover the Control file in RMAN Oracle

  • Find the location of your Control Files and drop them.
  •  SQL> select name from v$controlfile;
    
    NAME
    --------------------------------------------------------------------------------
    /opt/oracle/oradata/oracle/control01.ctl
    /opt/oracle/oradata/oracle/control02.ctl
    
    bash:> rm -rf /opt/oracle/oradata/oracle/control01.ctl /opt/oracle/oradata/oracle/control02.ctl
    
     
  • Next simulate the error using a command that will need to alter the control file.
  •  SQL> alter tablespace system online;
    alter tablespace system online
    *
    ERROR at line 1:
    ORA-00210: cannot open the specified control file
    ORA-00202: control file: '/opt/oracle/oradata/oracle/control01.ctl'
    ORA-27041: unable to open file
    Linux-x86_64 Error: 2: No such file or directory
    Additional information: 3
  • Shutdown the database and start it in nomount mode.
  •  SQL> shutdown abort;
    ORACLE instance shut down.
    
    
    SQL> startup nomount;
    ORACLE instance started.
    Total System Global Area  417546240 bytes
    Fixed Size                  2213936 bytes
    Variable Size             314574800 bytes
    Database Buffers           96468992 bytes
    Redo Buffers                4288512 bytes
    Database must be in no mount mode as it will not reach the stage where the control file is needed.
  • Now let's go to our RMAN tool and restore our control file.
  • A very important step here is that you need to provide your database DBID. If you don't have in your database config documentation or any old RMAN logs or you don't use Recovery catalog then you can try to use the "strings" tool using the syntax : "strings undotbs01.dbf | grep MAXVALUE" this will return the value of your DBID.
    RMAN> set DBID=1423547369
    executing command: SET DBID
    
    RMAN> restore controlfile from autobackup;
    Starting restore at 25-OCT-13
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=19 device type=DISK
    recovery area destination: /opt/oracle/flash_recovery_area
    database name (or database unique name) used for search: DB1
    channel ORA_DISK_1: AUTOBACKUP /opt/oracle/flash_recovery_area/DB1/autobackup/2013_10_24/o1_mf_s_829684173_96m5hgd9_.bkp found in the recovery area
    channel ORA_DISK_1: looking for AUTOBACKUP on day: 20131025
    channel ORA_DISK_1: looking for AUTOBACKUP on day: 20131024
    channel ORA_DISK_1: restoring control file from AUTOBACKUP /opt/oracle/flash_recovery_area/DB1/autobackup/2013_10_24/o1_mf_s_829684173_96m5hgd9_.bkp
    channel ORA_DISK_1: control file restore from AUTOBACKUP complete
    output file name=/opt/oracle/oradata/db1/control01.ctl
    output file name=/opt/oracle/flash_recovery_area/db1/control02.ctl
    Finished restore at 25-OCT-13
    This is only possible if you have your RMAN settings controlfile autobackup on. If you haven't done so follow this tutorial
  • Next mount the database(which means that controlfile will be validated)
  •  RMAN> alter database mount;
    
    database mounted
    released channel: ORA_DISK_1
  • Next we need to recover and open the database.
  •  RMAN> recover database;
    
    Starting recover at 25-OCT-13
    Starting implicit crosscheck backup at 25-OCT-13
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=19 device type=DISK
    Crosschecked 3 objects
    Finished implicit crosscheck backup at 25-OCT-13
    Starting implicit crosscheck copy at 25-OCT-13
    using channel ORA_DISK_1
    Finished implicit crosscheck copy at 25-OCT-13
    searching for all files in the recovery area
    cataloging files...
    cataloging done
    List of Cataloged Files
    =======================
    File Name: /opt/oracle/flash_recovery_area/DB1/autobackup/2013_10_24/o1_mf_s_829684173_96m5hgd9_.bkp
    using channel ORA_DISK_1
    starting media recovery
    archived log for thread 1 with sequence 3 is already on disk as file /opt/oracle/flash_recovery_area/DB1/archivelog/2013_10_24/o1_mf_1_3_96m5hc6s_.arc
    archived log for thread 1 with sequence 4 is already on disk as file /opt/oracle/oradata/db1/redo01.log
    archived log file name=/opt/oracle/flash_recovery_area/DB1/archivelog/2013_10_24/o1_mf_1_3_96m5hc6s_.arc thread=1 sequence=3
    archived log file name=/opt/oracle/oradata/db1/redo01.log thread=1 sequence=4
    media recovery complete, elapsed time: 00:00:01
    Finished recover at 25-OCT-13
    
    
    
    RMAN> alter database open resetlogs;
    
    database opened
    Is good to remember that we can run sql commands in RMAN.