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


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
control01.ctl redo01.log redo03.log system01.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
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
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/home/oracle/app/oracle/oradata/orcl/control01.ctl
output file
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;


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:

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
channel ORA_DISK_1: restoring datafile 00002 to
channel ORA_DISK_1: restoring datafile 00003 to
channel ORA_DISK_1: restoring datafile 00004 to
channel ORA_DISK_1: restoring datafile 00005 to
channel ORA_DISK_1: restoring datafile 00006 to
channel ORA_DISK_1: reading from backup piece
channel ORA_DISK_1: piece
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.


About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.