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 10-JAN-10

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/2010-01-10/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 10-JAN-10

Mount and recover the database

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> recover database;

Starting recover at 10-JAN-10

Starting implicit crosscheck backup at 10-JAN-10

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

Crosschecked 1 objects

Finished implicit crosscheck backup at 10-JAN-10

Starting implicit crosscheck copy at 10-JAN-10

using channel ORA_DISK_1

Finished implicit crosscheck copy at 10-JAN-10

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/2010-01-10/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/2010-01-10/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/2010-01-10/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 10-JAN-10

SQL> alter database open resetlogs;

Database altered.

conn scott/tiger

Connected.

SQL> select count(*) from myobjects;

COUNT(*)

———-

1839328

Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: