RECOVER LOSS OF ALL CONTROLFILES (NO CATALOG)

In this session will be discussing scenario of recovering database from loss of all controlfile using rman.

STEP1: CREATE A SCENARIO FOR TEST PURPOSE.

[oracle@NVMBD1BZY150D00 clonedb]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 19 15:50:38 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn oracle/oracle

Connected.

SQL>CREATE TABLE myobjects AS SELECT * FROM DBA_OBJECTS;

SQL> select table_name from user_tables;

TABLE_NAME

------------------------------

MYOBJECTS

SQL> insert into myobjects select * from myobjects;

919664 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from myobjects;

  COUNT(*)

----------

   1839328   >>>> need to check this record count after recovery

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     5

Next log sequence to archive   7

Current log sequence           7

Note - current log sequence is 7 - not archived but contains the last committed changes that we made

Note - archive logs will not be found in $ARCV area, but in the flashback location

STEP2: STIMULATE A FAILURE

SQL> select name from v$controlfile;

NAME

-------------------------------------------------------------------------------

/u01/ORACLE/testdb/control01.ctl

/u01/ORACLE/testdb/control02.ctl

/u01/ORACLE/testdb/control03.ctl

SQL> !rm /u01/ORACLE/testdb/*.ctl

SQL> alter tablespace users online;

alter tablespace users online

*

ERROR at line 1:

ORA-00603: ORACLE server session terminated by fatal error.

STEP3: SHUT DONW THE DATABASE AND STARTUP IN NOMOUNT STAGE TO RECOVER CONTROL FILE.

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  893386752 bytes

Fixed Size                  2076816 bytes

Variable Size             432017264 bytes

Database Buffers          452984832 bytes

Redo Buffers                6307840 bytes

Since we are not using a RMAN catalog we need to set the DBID

RMAN> set dbid=2415549446;

executing command: SET DBID

STEP4: RESTORE THE CONTROLFILE.

RMAN> run {

2> restore controlfile from autobackup;

3> }

Starting restore at 18-SEP-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

recovery area destination: /u01/ORACLE/flash_recovery_area

database name (or database unique name) used for search: TESTDB

channel ORA_DISK_1: autobackup found in the recovery area

channel ORA_DISK_1: autobackup found: /u01/ORACLE/flash_recovery_area/TESTDB/autobackup/2007_09_18/o1_mf_s_633601094_3gynd74g_.bkp

channel ORA_DISK_1: control file restore from autobackup complete

output filename=/u01/ORACLE/testdb/control01.ctl

output filename=/u01/ORACLE/testdb/control02.ctl

output filename=/u01/ORACLE/testdb/control03.ctl

Finished restore at 18-SEP-07

STEP5: MOUNT AND RECOVER THE DATABASE;

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> recover database;

Starting recover at 18-SEP-07

Starting implicit crosscheck backup at 18-SEP-07

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

Crosschecked 1 objects

Finished implicit crosscheck backup at 18-SEP-07

Starting implicit crosscheck copy at 18-SEP-07

using channel ORA_DISK_1

Finished implicit crosscheck copy at 18-SEP-07

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /u01/ORACLE/flash_recovery_area/TESTDB/autobackup/2007_09_18/o1_mf_s_633601094_3gynd74g_.bkp

using channel ORA_DISK_1

allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: sid=155 devtype=SBT_TAPE

channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.2.4.0

datafile 4 not processed because file is offline

starting media recovery

archive log thread 1 sequence 6 is already on disk as file /u01/ORACLE/flash_recovery_area/TESTDB/archivelog/2007_09_18/o1_mf_1_6_3gyn7vnk_.arc

archive log thread 1 sequence 7 is already on disk as file /u01/ORACLE/testdb/redo03.log

archive log filename=/u01/ORACLE/flash_recovery_area/TESTDB/archivelog/2007_09_18/o1_mf_1_6_3gyn7vnk_.arc thread=1 sequence=6

archive log filename=/u01/ORACLE/testdb/redo03.log thread=1 sequence=7  >>>> current redo log with committed but unarchived changes applied

media recovery complete, elapsed time: 00:00:09

Finished recover at 18-SEP-07

STEP6: OPEN DATABASE WITH RESETLOGS OPTION.

SQL> alter database open resetlogs;

Database altered.

SQL> conn ORACLE/ORACLE

Connected.

SQL> select count(*) from myobjects;

  COUNT(*)

----------

   1839328

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!