Point in Time Recovery Using RMAN

Point in time recovery using RMAN – untill a log sequence number

1.Recovery Objective

SQL> conn scott/tiger
 Connected.
 SQL> select count(*) from myobjects;
COUNT(*)
 ----------
 249410

2.Switch a logfile

SQL> conn / as sysdba
 Connected.
 SQL> alter system switch logfile;
System altered.

3.Note the current log sequence number (13)

SQL> archive log list
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination /u02/ORACLE/opsdba/arch
 Oldest online log sequence 12
 Next log sequence to archive 14
 Current log sequence 14

4.Simulate an application failure – WRONG Delete!!

SQL> conn scott/tiger
 Connected.
 SQL> delete myobjects;
249410 rows deleted.
SQL> commit;
Commit complete.

The developer states that the wrong DML statement was made AFTER 8.15 AM and is positive about the same.

We need to determine the log sequence we need to recover until

select sequence#,first_change#, to_char(first_time,'HH24:MI:SS')
from v$log order by 3
 SQL> /
SEQUENCE# FIRST_CHANGE# TO_CHAR(
 ---------- ------------- --------
 13 2760463 07:49:36
 14 2761178 08:12:47
 15 2766622 08:18:49

Log sequence 14 was first written to at 8:12 AM so we should recover to a log sequence before this – i.e sequence# 13

5. Shutdown and mount the database

SQL> shutdown immediate;
 Database closed.
 Database dismounted.
 ORACLE instance shut down.

 SQL> startup mount;
 ORACLE instance started.
Total System Global Area 264241152 bytes
 Fixed Size 2070416 bytes
 Variable Size 163580016 bytes
 Database Buffers 92274688 bytes
 Redo Buffers 6316032 bytes
 Database mounted.

 RMAN> run {
 2> set until sequence=14; >>> add one to the sequence number we have to recover until
 3> restore database;
 4> recover database;
 5> }
executing command: SET until clause
Starting restore at 29-JAN-07
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: sid=154 devtype=DISK
 allocated channel: ORA_SBT_TAPE_1
 channel ORA_SBT_TAPE_1: sid=158 devtype=SBT_TAPE
 channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.2.4.0
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 00005 to /u02/ORACLE/opsdba/users02.dbf
 restoring datafile 00006 to /u02/ORACLE/opsdba/users03.dbf
 restoring datafile 00007 to /u02/ORACLE/opsdba/users05.dbf
 restoring datafile 00008 to /u02/ORACLE/opsdba/users06.dbf
 restoring datafile 00009 to /u02/ORACLE/opsdba/users07.dbf
 restoring datafile 00010 to /u02/ORACLE/opsdba/users04.dbf
 restoring datafile 00011 to /u02/ORACLE/opsdba/drtbs1.dbf
 restoring datafile 00012 to /u02/ORACLE/opsdba/drtbs2.dbf

 channel ORA_DISK_1: reading from backup piece
 /opt/oracle/backup/opsdba/OPSDBA.20070129.161.1.1.613122551
 channel ORA_DISK_1: 
 restored backup piece 1
 piece handle=/opt/oracle/backup/opsdba/OPSDBA.20070129.161.1.1.613122551 
 tag=TAG20070129T074911
 channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
 Finished restore at 29-JAN-07
Starting recover at 29-JAN-07
 using channel ORA_DISK_1
 using channel ORA_SBT_TAPE_1
starting media recovery
archive log thread 1 sequence 13 is already on disk as file 
 /u02/ORACLE/opsdba/arch/arch_1_13_613052894.dbf
 channel ORA_DISK_1: starting archive log restore to default destination
 channel ORA_DISK_1: restoring archive log
 archive log thread=1 sequence=12
 channel ORA_DISK_1: reading from backup piece 
 /opt/oracle/backup/opsdba/OPSDBA.20070129.162.1.1.613122577
 channel ORA_DISK_1: restored backup piece 1
 piece handle=/opt/oracle/backup/opsdba/OPSDBA.20070129.162.1.1.613122577 
 tag=TAG20070129T074937
 channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
 archive log filename=/u02/ORACLE/opsdba/arch/arch_1_12_613052894.dbf thread=1 sequence=12
 archive log filename=/u02/ORACLE/opsdba/arch/arch_1_13_613052894.dbf thread=1 sequence=13
 media recovery complete, elapsed time: 00:00:01
 Finished recover at 29-JAN-07
RMAN> sql 'alter database open resetlogs';
sql statement: alter database open resetlogs

6.Confirm that the recovery has worked

 opsdba:/opt/tivoli/tsm/client/oracle/bin64>sqlplus scott/tiger
SQL*Plus: Release 10.2.0.2.0 - Production on Mon Jan 29 09:43:14 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
 Connected to:
 Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
 With the Partitioning, Oracle Label Security, OLAP and Data Mining options
SQL> select count(*) from myobjects;
COUNT(*)
 ----------
 249410

Source

Reference links:

Leave a Reply

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