Recovery using RMAN backup when control file is lost (control file recreation method)

How to recover database using control file recreation method?

There are two ways we can recover the database when control file is lost:
1. Restore the control file and recover the database
Note:Please follow  http://orcl11gdba.blogspot.in/2014/09/recovery-using-rman-backup-when-control.html  for this method
2. Recreate the control file using backup control file and recover

DB Name: TESTDB

Here we shall perform second way to recover the database. To do this we have to make sure that controlfile is backed up to some location.

SQL> alter database backup controlfile to trace as '/tmp/control.sql';
[oracle@avalance_testserv /tmp]$ ls
control.sql 

Note:This file will contain two SQL queries with Resetlogs and noresetlogs option. We have to consider the query resetlogs only.

[oracle@avalance_testserv /tmp]$ cat ctrl.sql
CREATE CONTROLFILE REUSE DATABASE "TESTDB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 5
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/TESTDB/oradata/dbf/redo01.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/TESTDB/oradata/dbf/redo02.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/TESTDB/oradata/dbf/redo03.log'  SIZE 100M BLOCKSIZE 512
DATAFILE
  '/u01/app/oracle/TESTDB/oradata/dbf/system01.dbf',
  '/u01/app/oracle/TESTDB/oradata/dbf/sysaux01.dbf',
  '/u01/app/oracle/TESTDB/oradata/dbf/undotbs01.dbf',
  '/u01/app/oracle/TESTDB/oradata/dbf/users01.dbf',
  '/u01/app/oracle/TESTDB/oradata/dbf/app_data01.dbf'
CHARACTER SET US7ASCII
;

Observation: This is nothing but structure of database .Now I am going to remove the control file so that we can perform recreation method

[oracle@avalance_testserv oradata]$ ls
control.ctl  dbf  redo
[oracle@avalance_testserv oradata]$
[oracle@avalance_testserv oradata]$ rm control.ctl
[oracle@avalance_testserv oradata]$ ls
dbf  redo

[oracle@avalance_testserv oradata]$ sqlplus / as sysdba
SQL> shut abort
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  609681408 bytes
Fixed Size                  2215664 bytes
Variable Size             549454096 bytes
Database Buffers           50331648 bytes
Redo Buffers                7680000 bytes
SQL> exit


Observation: we are putting the database in nomount state because we can’t directly mount it without control file.to do that we have to restore it from backup as below.
 


SQL>@control.sql
Control file created.
SQL> alter database mount;
Alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted

Observation:Here database will be automatically mounted.

SQL> recover database using backup controlfile;

ORA-00279: change 865927 generated at 09/18/2014 16:14:41 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/archivelog/2014_09_18/o1_mf_1_1_
%u_.arc
ORA-00280: change 865927 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/TESTDB/oradata/dbf/redo01.log
Log applied.
Media recovery complete.
       
Note: Here we can give auto option to apply archived logs and then current redo log file redo1.log

SQL> alter database open resetlogs;
Database altered.


It is suggested to take RMAN backup immediately after the activity is over. please follow the link to take RMAN backup: http://orcl11gdba.blogspot.in/2014/05/taking-database-backup-using-rman-method.html
Cheers…!!!