Controlfiles saved at least in 2 locations. If one of them is corrupted then you can copy from other one after shutdown database. In this case I will show you how to recover from autobackup via RMAN. So you need CONFIGURE CONTROLFILE AUTOBACKUP ON; in RMAN and you should have full backup database. It can recover even if all controlfiles are unavailable.
SQL> SQL> select * from v$controlfile; STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS ------- ------------------------------------------------------------ --- ---------- -------------- /u01/app/oracle/oradata/DB11G/control01.ctl NO 16384 594 /u01/app/oracle/flash_recovery_area/DB11G/control02.ctl NO 16384 594 SQL>
Two controfiles are there.
# rman target / RMAN> startup nomount; connected to target database (not started) Oracle instance started Total System Global Area 217157632 bytes Fixed Size 2211928 bytes Variable Size 159387560 bytes Database Buffers 50331648 bytes Redo Buffers 5226496 bytes RMAN> restore controlfile from autobackup; Starting restore at 02-FEB-17 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=19 device type=DISK recovery area destination: /u01/app/oracle/flash_recovery_area database name (or database unique name) used for search: DB11G channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/flash_recovery_area/DB11G/autobackup/2017_02_02/o1_mf_s_934853109_d946txy0_.bkp found in the recovery area channel ORA_DISK_1: looking for AUTOBACKUP on day: 20170202 channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/flash_recovery_area/DB11G/autobackup/2017_02_02/o1_mf_s_934853109_d946txy0_.bkp channel ORA_DISK_1: control file restore from AUTOBACKUP complete output file name=/u01/app/oracle/oradata/DB11G/control01.ctl output file name=/u01/app/oracle/flash_recovery_area/DB11G/control02.ctl Finished restore at 02-FEB-17 RMAN> sql 'alter database mount'; sql statement: alter database mount released channel: ORA_DISK_1 RMAN> recover database; Starting recover at 02-FEB-17 Starting implicit crosscheck backup at 02-FEB-17 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=19 device type=DISK Crosschecked 3 objects Finished implicit crosscheck backup at 02-FEB-17 Starting implicit crosscheck copy at 02-FEB-17 using channel ORA_DISK_1 Finished implicit crosscheck copy at 02-FEB-17 searching for all files in the recovery area cataloging files... cataloging done List of Cataloged Files ======================= File Name: /u01/app/oracle/flash_recovery_area/DB11G/autobackup/2017_02_02/o1_mf_s_934853109_d946txy0_.bkp using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 42 is already on disk as file /stripe/d1/redo03.log archived log file name=/stripe/d1/redo03.log thread=1 sequence=42 media recovery complete, elapsed time: 00:00:05 Finished recover at 02-FEB-17 RMAN> sql 'alter database open resetlogs'; sql statement: alter database open resetlogs RMAN>
Note: Database should be open with RESETLOGS options after recovering controlfiles.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
Anuradha’s LinkedIn: Anuradha’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
