Recovery from a backup taken before a RESETLOGS
Posted by Gavin Soorma on June 28, 2009
Simulate loss of redo log files
[oracle@itlinuxdevblade08 opsdba]$ rm redo*.log
Check alert log
Errors in file /u02/ORACLE/opsdba/bdump/opsdba_arc1_29898.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u02/ORACLE/opsdba/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Wed Jun 28 12:50:59 2006
ARC1: Failed to archive thread 1 sequence 14 (0)
RECOVER DATABASE UNTIL LAST LOG SEQUENCE (13)
RMAN> run {
2> set until logseq=14 thread=1; >>>> Note add one to the max applied log sequence
3> restore database;
4> recover database;
5> }
executing command: SET until clause
Starting restore at 28-JUN-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 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 /u02/ORACLE/opsdba/system01.dbf
restoring datafile 00002 to /u02/ORACLE/opsdba/undotbs01.dbf
restoring datafile 00003 to /u02/ORACLE/opsdba/sysaux01.dbf
restoring datafile 00004 to /u02/ORACLE/opsdba/users01.dbf
restoring datafile 00006 to /u02/ORACLE/opsdba/datatbs01.dbf
restoring datafile 00007 to /u02/ORACLE/opsdba/indxtbs01.dbf
restoring datafile 00008 to /u02/ORACLE/opsdba/datatbs02.dbf
restoring datafile 00009 to /u02/ORACLE/opsdba/users02.dbf
channel ORA_DISK_1: reading from backup piece /u02/ORACLE/opsdba_bkp/bkp_03hmonj9
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/ORACLE/opsdba_bkp/bkp_03hmonj9 tag=TAG20060628T123657
channel ORA_DISK_1: restore complete, elapsed time: 00:00:46
Finished restore at 28-JUN-06
Starting recover at 28-JUN-06
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 8 is already on disk as file /u02/ORACLE/opsdba/arch/arch.8.1.594244455.log
archive log thread 1 sequence 9 is already on disk as file /u02/ORACLE/opsdba/arch/arch.9.1.594244455.log
archive log thread 1 sequence 10 is already on disk as file /u02/ORACLE/opsdba/arch/arch.10.1.594244455.log
archive log thread 1 sequence 11 is already on disk as file /u02/ORACLE/opsdba/arch/arch.11.1.594244455.log
archive log thread 1 sequence 12 is already on disk as file /u02/ORACLE/opsdba/arch/arch.12.1.594244455.log
archive log thread 1 sequence 13 is already on disk as file /u02/ORACLE/opsdba/arch/arch.13.1.594244455.log
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=7
channel ORA_DISK_1: reading from backup piece /u02/ORACLE/opsdba_bkp/bkp_05hmonkt
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/ORACLE/opsdba_bkp/bkp_05hmonkt tag=TAG20060628T123748
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/u02/ORACLE/opsdba/arch/arch.7.1.594244455.log thread=1 sequence=7
archive log filename=/u02/ORACLE/opsdba/arch/arch.8.1.594244455.log thread=1 sequence=8
archive log filename=/u02/ORACLE/opsdba/arch/arch.9.1.594244455.log thread=1 sequence=9
archive log filename=/u02/ORACLE/opsdba/arch/arch.10.1.594244455.log thread=1 sequence=10
archive log filename=/u02/ORACLE/opsdba/arch/arch.11.1.594244455.log thread=1 sequence=11
archive log filename=/u02/ORACLE/opsdba/arch/arch.12.1.594244455.log thread=1 sequence=12
archive log filename=/u02/ORACLE/opsdba/arch/arch.13.1.594244455.log thread=1 sequence=13
media recovery complete, elapsed time: 00:00:05
Finished recover at 28-JUN-06
RMAN> exit
Recovery Manager complete.
[oracle@itlinuxdevblade08 opsdba]$ sql
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 28 12:57:34 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP
and Data Mining options
SQL> alter database open resetlogs;
Database altered.
SQL> conn scott/tigerConnected.
SQL> select count(*) from myobjects;
COUNT(*)
----------
1271227
RECOVERY COMPLETE !
GENERATE SOME MORE ARCHIVE LOGS
SQL> exec load_data;
PL/SQL procedure successfully completed.
SQL> select count(*) from myobjects;
COUNT(*)
----------
1678207
NOTE: NO BACKUP HAS BEEN TAKEN AFTER RESETLOGS - AGAIN SIMULATE FAILURE
[oracle@itlinuxdevblade08 opsdba]$ rm redo*.log
[oracle@itlinuxdevblade08 opsdba]$ sql
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 28 13:11:35 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP
and Data Mining options
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 327155712 bytes
Fixed Size 2020512 bytes
Variable Size 171969376 bytes
Database Buffers 146800640 bytes
Redo Buffers 6365184 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u02/ORACLE/opsdba/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
RMAN> list incarnation of database; ;
NOTE - incarnation has not changed after resetlogs
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
185729689 185729700 OPSDBA 1480400868 PARENT 525876 23-JUN-06
185729689 185729690 OPSDBA 1480400868 PARENT 654443 27-JUN-06
185729689 185817798 OPSDBA 1480400868 CURRENT 685999 28-JUN-06
RECOVER AGAIN - NOTICE HOW ALL ARCHIVE LOG FILES ARE APPLIED –
BOTH BEFORE RESETLOGS AS WELL AS AFTER RESETLOGS
Note:After RESETLOGS log sequence 8 is the last log sequence applied -
so we recover until sequence 9
RMAN> run {
2> set until sequence=9 thread =1;
3> restore database;
4> recover database;
5> }
executing command: SET until clause
Starting restore at 28-JUN-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 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 /u02/ORACLE/opsdba/system01.dbf
restoring datafile 00002 to /u02/ORACLE/opsdba/undotbs01.dbf
restoring datafile 00003 to /u02/ORACLE/opsdba/sysaux01.dbf
restoring datafile 00004 to /u02/ORACLE/opsdba/users01.dbf
restoring datafile 00006 to /u02/ORACLE/opsdba/datatbs01.dbf
restoring datafile 00007 to /u02/ORACLE/opsdba/indxtbs01.dbf
restoring datafile 00008 to /u02/ORACLE/opsdba/datatbs02.dbf
restoring datafile 00009 to /u02/ORACLE/opsdba/users02.dbf
channel ORA_DISK_1: reading from backup piece /u02/ORACLE/opsdba_bkp/bkp_03hmonj9
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/ORACLE/opsdba_bkp/bkp_03hmonj9 tag=TAG20060628T123657
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
Finished restore at 28-JUN-06
Starting recover at 28-JUN-06
using channel ORA_DISK_1
starting media recovery
NOTE: Applying archived log files BEFORE resetlogs
archive log thread 1 sequence 7 is already on disk as file /u02/ORACLE/opsdba/arch/arch.7.1.594244455.log
archive log thread 1 sequence 8 is already on disk as file /u02/ORACLE/opsdba/arch/arch.8.1.594244455.log
archive log thread 1 sequence 9 is already on disk as file /u02/ORACLE/opsdba/arch/arch.9.1.594244455.log
archive log thread 1 sequence 10 is already on disk as file /u02/ORACLE/opsdba/arch/arch.10.1.594244455.log
archive log thread 1 sequence 11 is already on disk as file /u02/ORACLE/opsdba/arch/arch.11.1.594244455.log
archive log thread 1 sequence 12 is already on disk as file /u02/ORACLE/opsdba/arch/arch.12.1.594244455.log
archive log thread 1 sequence 13 is already on disk as file /u02/ORACLE/opsdba/arch/arch.13.1.594244455.log
NOTE: Applying archived log files AFTER resetlogs
archive log thread 1 sequence 1 is already on disk as file /u02/ORACLE/opsdba/arch/arch.1.1.594305864.log
archive log thread 1 sequence 2 is already on disk as file /u02/ORACLE/opsdba/arch/arch.2.1.594305864.log
archive log thread 1 sequence 3 is already on disk as file /u02/ORACLE/opsdba/arch/arch.3.1.594305864.log
archive log thread 1 sequence 4 is already on disk as file /u02/ORACLE/opsdba/arch/arch.4.1.594305864.log
archive log thread 1 sequence 5 is already on disk as file /u02/ORACLE/opsdba/arch/arch.5.1.594305864.log
archive log thread 1 sequence 6 is already on disk as file /u02/ORACLE/opsdba/arch/arch.6.1.594305864.log
archive log thread 1 sequence 7 is already on disk as file /u02/ORACLE/opsdba/arch/arch.7.1.594305864.log
archive log thread 1 sequence 8 is already on disk as file /u02/ORACLE/opsdba/arch/arch.8.1.594305864.log
archive log filename=/u02/ORACLE/opsdba/arch/arch.7.1.594244455.log thread=1 sequence=7
archive log filename=/u02/ORACLE/opsdba/arch/arch.8.1.594244455.log thread=1 sequence=8
archive log filename=/u02/ORACLE/opsdba/arch/arch.9.1.594244455.log thread=1 sequence=9
archive log filename=/u02/ORACLE/opsdba/arch/arch.10.1.594244455.log thread=1 sequence=10
archive log filename=/u02/ORACLE/opsdba/arch/arch.11.1.594244455.log thread=1 sequence=11
archive log filename=/u02/ORACLE/opsdba/arch/arch.12.1.594244455.log thread=1 sequence=12
archive log filename=/u02/ORACLE/opsdba/arch/arch.13.1.594244455.log thread=1 sequence=13
archive log filename=/u02/ORACLE/opsdba/arch/arch.1.1.594305864.log thread=1 sequence=1
archive log filename=/u02/ORACLE/opsdba/arch/arch.2.1.594305864.log thread=1 sequence=2
archive log filename=/u02/ORACLE/opsdba/arch/arch.3.1.594305864.log thread=1 sequence=3
archive log filename=/u02/ORACLE/opsdba/arch/arch.4.1.594305864.log thread=1 sequence=4
archive log filename=/u02/ORACLE/opsdba/arch/arch.5.1.594305864.log thread=1 sequence=5
archive log filename=/u02/ORACLE/opsdba/arch/arch.6.1.594305864.log thread=1 sequence=6
archive log filename=/u02/ORACLE/opsdba/arch/arch.7.1.594305864.log thread=1 sequence=7
archive log filename=/u02/ORACLE/opsdba/arch/arch.8.1.594305864.log thread=1 sequence=8
media recovery complete, elapsed time: 00:00:09
Finished recover at 28-JUN-06
RMAN> alter database open resetlogs;
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN> quit
ALL DATA RECOVERED UNTIL POINT OF FAILURE
[oracle@itlinuxdevblade08 opsdba]$ sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 28 13:34:51 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP
and Data Mining options
SQL> select count(*) from myobjects;
COUNT(*)
----------
1678207
