4.7. Performing a Recovery after a RESETLOGS Operation
The RESETLOGS clause is required in most incomplete recovery situations to open the database. This clause resets the redo log sequence for the Oracle database. In versions prior to Oracle 10g, this was a critical point because this invalidated your ability to use the backups prior to the use of RESETLOGS to recover again past the point of issuing the RESETLOGS. This is one of the reasons Oracle support always advised customers to take backup immediately following incomplete recovery.
In Oracle 10g, this problem has been remedied. Oracle has made revisions to the process of recovering through the RESETLOGS point. This new feature is internal to the recovery process; you don't need to do anything.
In order to test this new feature, here's an overview of the steps you need to take:
Make a new backup or have a good whole database backup available.
Force the redo log activity to archive logs.
Validate the log sequence number to perform your incomplete recovery, for which you use the RESETLOGS clause.
Shut down the database and delete the USERS01.DBF file to cause a recovery situation.
Start up the database in MOUNT mode and use RMAN to perform an incomplete recovery using the SET UNTIL SEQUENCE command.
When the incomplete recovery is complete, use the RESETLOGS clause to open the database.
To recover through the RESETLOGS, you need to simulate more database activity and force this activity to the archive logs. We will be using the USERS tablespace.
Shut down the database and delete the USERS01.DBF to cause another recovery situation.
Start up the database in MOUNT mode and use RMAN to perform a complete recovery.
When the recovery is complete, just use an ALTER DATABASE OPEN command.
Finally, view the V$LOG_HISTORY table and validate that the database activity is available in the database.
Let's go walk through this lengthy but straightforward procedure step by step:
Perform a backup if you do not have a good whole database backup:
RMAN> connect target connected to target database: ORA101T (DBID=2615281366) RMAN> run 2> {
3> allocate channel c1 type disk; 4> backup database; 5> backup (archivelog all); 6> } using target database controlfile instead of recovery catalog allocated channel: c1 channel c1: sid=36 devtype=DISK Starting backup at 11-SEP-04 channel c1: starting compressed full datafile backupset channel c1: specifying datafile(s) in backupset input datafile fno=00001 name=C:ORACLEORADATAORA101TSYSTEM01.DBF input datafile fno=00003 name=C:ORACLEORADATAORA101TSYSAUX01.DBF input datafile fno=00005 name=C:ORACLEORADATAORA101TEXAMPLE01.DBF input datafile fno=00002 name=C:ORACLEORADATAORA101TUNDOTBS01.DBF input datafile fno=00006 name=C:ORACLEORADATAORA101TINDEX01.DBF input datafile fno=00004 name=C:ORACLEORADATAORA101TUSERS01.DBF channel c1: starting piece 1 at 11-SEP-04 channel c1: finished piece 1 at 11-SEP-04 piece handle=
C:ORACLEFLASH_RECOVERY_AREAORA101TORA101TBACKUPSET
2004_09_11O1_MF_NNNDF_TAG20040911T120548_0N6M4JBO_.BKP comment=NONE channel c1: backup set complete, elapsed time: 00:01:36 Finished backup at 11-SEP-04 Starting backup at 11-SEP-04 current log archived channel c1: starting compressed archive log backupset channel c1: specifying archive log(s) in backup set input archive log thread=1 sequence=1 recid=1 stamp=536194825 input archive log thread=1 sequence=2 recid=2 stamp=536194914 input archive log thread=1 sequence=3 recid=3 stamp=536209216 input archive log thread=1 sequence=4 recid=4 stamp=536227827 input archive log thread=1 sequence=5 recid=5 stamp=536587661 channel c1: starting piece 1 at 11-SEP-04 channel c1: finished piece 1 at 11-SEP-04 piece handle=
C:ORACLEFLASH_RECOVERY_AREAORA101TORA101TBACKUPSET
2004_09_11O1_MF_ANNNN_TAG20040911T120745_0N6M8BSK_.BKP comment=NONE
channel c1: backup set complete, elapsed time: 00:00:18 Finished backup at 11-SEP-04 Starting Control File Autobackup at 11-SEP-04 piece handle=
C:ORACLEFLASH_RECOVERY_AREAORA101TORA101TAUTOBACKUP
2004_09_11O1_MF_N_536587693_0N6M936N_.BKP comment=NONE Finished Control File Autobackup at 11-SEP-04 released channel: c1 RMAN>
Verify the redo log sequence and thread number in the V$LOG_HISTORY table so that you can perform incomplete recovery to a redo log sequence:
SQL> select * from v$log_history; RECID STAMP THRD# SEQ# FIR_CHNG FIRST_TIM N_CHNG S_CHNG RESETLOG ----- --------- ----- ---- -------- --------- ------ ------ -------- 8 536588583 1 6 609492 11-SEP-04 610333 567536 06-SEP-04 9 536588584 1 7 610333 11-SEP-04 610335 567536 06-SEP-04 10 536588589 1 8 610335 11-SEP-04 610338 567536 06-SEP-04 SQL>
Simulate a failure by shutting down the database and deleting the USERS01.DBF:
C:Documents and Settingsdstuns> sqlplus /nolog SQL*Plus: Release 10.1.0.2.0 - Production on Sat Sep 11 12:25:08 2004 Copyright (c) 1982, 2004, Oracle. All rights reserved. SQL> connect / as sysdba Connected. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> host C:oracleoradataora101t> del USERS01.DBF
Begin the recovery process by starting the database in MOUNT mode:
SQL> startup mount ORACLE instance started. Total System Global Area 88080384 bytes Fixed Size 787588 bytes Variable Size 78642044 bytes Database Buffers 8388608 bytes Redo Buffers 262144 bytes Database mounted. SQL>
Perform an incomplete recovery using the SET UNTIL SEQUENCE clause:
RMAN> connect target connected to target database: ORA101T (DBID=2615281366) RMAN> run 2> { 3> set until sequence 6 thread 1; 4> restore database; 5> recover database; 6> }
executing command: SET until clause using target database controlfile instead of recovery catalog Starting restore at 11-SEP-04 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=49 devtype=DISK channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to C:ORACLEORADATAORA101TSYSTEM01.DBF restoring datafile 00002 to C:ORACLEORADATAORA101TUNDOTBS01.DBF restoring datafile 00003 to C:ORACLEORADATAORA101TSYSAUX01.DBF restoring datafile 00004 to C:ORACLEORADATAORA101TUSERS01.DBF restoring datafile 00005 to C:ORACLEORADATAORA101TEXAMPLE01.DBF restoring datafile 00006 to C:ORACLEORADATAORA101TINDEX01.DBF channel ORA_DISK_1: restored backup piece 1 piece handle=
C:ORACLEFLASH_RECOVERY_AREAORA101TORA101TBACKUPSET
2004_09_11O1_MF_NNNDF_TAG20040911T120548_0N6M4JBO_.BKP
tag=TAG20040911T120548 channel ORA_DISK_1: restore complete Finished restore at 11-SEP-04 Starting recover at 11-SEP-04 using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 5 is already on disk as file
C:ORACLEORAARCHORA101TARC00005_0536179727.001 archive log filename=C:ORACLEORAARCHORA101TARC00005_0536179727.001 thread=1 sequence=5 media recovery complete Finished recover at 11-SEP-04 RMAN> alter database open resetlogs;
database opened RMAN>
Simulate database activity by creating a table T1 and forcing this activity to the archived redo logs:
SQL> connect test/test Connected. SQL> create table t1(c1 char(20)); Table created. SQL> connect / as sysdba Connected. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered.
Shut down the database and simulate a database failure by deleting the USERS01.DBF file:
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> C:oracleoradataora101t> del USERS01.DBF
Start the database in MOUNT mode and then perform a complete recovery in RMAN:
SQL> startup mount ORACLE instance started. Total System Global Area 88080384 bytes Fixed Size 787588 bytes Variable Size 78642044 bytes Database Buffers 8388608 bytes Redo Buffers 262144 bytes Database mounted. SQL> C:Documents and Settingsdstuns> rman Recovery Manager: Release 10.1.0.2.0 - Production Copyright (c) 1995, 2004, Oracle. All rights reserved. RMAN> connect target connected to target database: ORA101T (DBID=2615281366) RMAN> run 2> { 3> restore database; 4> recover database; 5> } Starting restore at 11-SEP-04 using target database controlfile instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=49 devtype=DISK channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to C:ORACLEORADATAORA101TSYSTEM01.DBF restoring datafile 00002 to C:ORACLEORADATAORA101TUNDOTBS01.DBF restoring datafile 00003 to C:ORACLEORADATAORA101TSYSAUX01.DBF restoring datafile 00004 to C:ORACLEORADATAORA101TUSERS01.DBF restoring datafile 00005 to C:ORACLEORADATAORA101TEXAMPLE01.DBF
restoring datafile 00006 to C:ORACLEORADATAORA101TINDEX01.DBF channel ORA_DISK_1: restored backup piece 1 piece handle=
C:ORACLEFLASH_RECOVERY_AREAORA101TORA101TBACKUPSET
2004_09_11O1_MF_NNNDF_TAG20040911T120548_0N6M4JBO_.BKP
tag=TAG20040911T120548 channel ORA_DISK_1: restore complete Finished restore at 11-SEP-04 Starting recover at 11-SEP-04 using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 1 is already on disk as file
C:ORACLEORAARCHORA101TARC00001_0536589683.001 archive log thread 1 sequence 2 is already on disk as file
C:ORACLEORAARCHORA101TARC00002_0536589683.001 archive log thread 1 sequence 3 is already on disk as file
C:ORACLEORAARCHORA101TARC00003_0536589683.001 archive log thread 1 sequence 4 is already on disk as file
C:ORACLEORAARCHORA101TARC00004_0536589683.001 archive log filename=
C:ORACLEORAARCHORA101TARC00005_0536179727.001 thread=1 sequence=5 archive log filename=
C:ORACLEORAARCHORA101TARC00001_0536589683.001 thread=1 sequence=1 archive log filename=
C:ORACLEORAARCHORA101TARC00002_0536589683.001 thread=1 sequence=2 media recovery complete Finished recover at 11-SEP-04 RMAN> alter database open; database opened RMAN>
Validate that you have recovered through the last RESETLOGS by verifying that the current V$LOG_HISTORY table shows the log sequence 6 and thread 1 followed by new redo logs files:
SQL> select * from v$log_history; RECID STAMP THRD# SEQ# FIR_CHNG FIRST_TIM N_CHNG S_CHNG RESETLOG ----- --------- ----- ---- -------- --------- ------ ------ -------- 8 536588583 1 6 609492 11-SEP-04 610333 567536 06-SEP-04 9 536588584 1 7 610333 11-SEP-04 610335 567536 06-SEP-04 10 536588589 1 8 610335 11-SEP-04 610338 567536 06-SEP-04 11 536590054 1 1 609493 11-SEP-04 609880 609493 11-SEP-04 12 536590057 1 2 609880 11-SEP-04 609882 609493 11-SEP-04 13 536590062 1 3 609882 11-SEP-04 609885 609493 11-SEP-04 14 536590102 1 4 609885 11-SEP-04 609904 609493 11-SEP-04 SQL>
As you can see, this process requires a few steps to simulate. This is because you are simulating two failures and then performing two recoveries from the same backup. The first recovery is opening the databases with RESETLOGS option. The second recovery is using the same backup to recover past the first recovery with RESETLOGS to the complete recovery without opening the database with RESETLOGS.