Being a DBA we must have knowledge on all kind of situation come across database’s life maintenance. We all are aware that CDR (Controlfile, Datafile, Redologfile) plays a most important role in Oracle database. With the help of backup, we can restore the database .
In this article, we are going to learn about the recovery from controlfile in noarchivelog mode. As per my interviews, the experienced interviewer wants to dig out your knowledge of CDR (Controlfile, Datafile, Redologfile) in the Backup and Recovery section. I hope this content will help you to solve database issues.
This approach, using the controlfile coming from the autobackup, is different compared with the copy of an available multiplexed copy of a current controlfile because there you have to perform an incomplete recovery of your database while using the auto backup controlfile you have already a controlfile with SCN consistent with all your datafiles
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
I shut down my database…
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.
and then removed all my datafiles, redolog and current controlfile
[oracle@localhost orcl]$ ls APEX_1930613455248703.dbf example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf [oracle@localhost orcl]$ rm *
From rman console I typed the following commands:
[oracle@localhost orcl]$ rman target / RMAN> startup nomount; RMAN> restore controlfile from autobackup; Starting restore at 17-JUL-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK recovery area destination: /home/oracle/app/oracle/flash_recovery_area database name (or database unique name) used for search: ORCL channel ORA_DISK_1: AUTOBACKUP /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_17/o1_mf_s_788864449_80c39jlo_.bkp found in the recovery area AUTOBACKUP search with format "%F" not attempted because DBID was not set channel ORA_DISK_1: restoring control file from AUTOBACKUP /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_17/o1_mf_s_788864449_80c39jlo_.bkp channel ORA_DISK_1: control file restore from AUTOBACKUP complete output file name=/home/oracle/app/oracle/oradata/orcl/control01.ctl output file name=/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl Finished restore at 17-JUL-12
And now I’m able to at least mount my database
RMAN> alter database mount; database mounted released channel: ORA_DISK_1
and check that my database is using a backup controlfile
[oracle@localhost orcl]$ sqlplus / as sysdba SQL> select controlfile_type from V$DATABASE; CONTROL ------- BACKUP
From the rman console issue the restore command.
RMAN> restore database; Starting restore at 17-JUL-12 Starting implicit crosscheck backup at 17-JUL-12 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK Crosschecked 4 objects Finished implicit crosscheck backup at 17-JUL-12 Starting implicit crosscheck copy at 17-JUL-12 using channel ORA_DISK_1 Finished implicit crosscheck copy at 17-JUL-12 searching for all files in the recovery area cataloging files... cataloging done List of Cataloged Files ======================= File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_17/o1_mf_s_788864449_80c39jlo_.bkp using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /home/oracle/app/oracle/oradata/orcl/system01.dbf channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/app/oracle/oradata/orcl/users01.dbf channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/app/oracle/oradata/orcl/example01.dbf channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_07_17/o1_mf_nnndf_TAG20120717T090114_80c32cxk_.bkp channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_07_17/o1_mf_nnndf_TAG20120717T090114_80c32cxk_.bkp tag=TAG20120717T090114 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:04:07 Finished restore at 17-JUL-12
Finally open your database with the resetlogs option
RMAN> alter database open resetlogs; database opened
Your database is now available again.
