SCENARIO – LOSS OF ALL CONTROLFILES (NO CATALOG)

 

 

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

 

Simulate 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

 

 

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

 

 

Restore the controlfile

 

RMAN> run {

2> restore controlfile from autobackup;

3> }

 

Starting restore at 18-SEP-07

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

 

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

 

 

SQL> alter database open resetlogs;

 

Database altered.

 

 conn scott/tiger

Connected.

SQL> select count(*) from myobjects;

 

  COUNT(*)

---------- 

   1839328