Warning: Creating default object from empty value in /home/customer/www/gavinsoorma.com/public_html/wp-content/themes/specular/admin/inc/class.redux_filesystem.php on line 29
Recovery from a backup taken before a RESETLOGS | Oracle DBA – Tips and Techniques
News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Recovery from a backup taken before a RESETLOGS

  • Posted by Gavin Soorma
  • On June 28, 2009
  • 0 Comments

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
 

0 Comments

Leave Reply

Your email address will not be published. Required fields are marked *