28 | 03 | 2024
Latest Articles
Popular Articles

RMAN

Cancel based recovery with RMAN, a piece of cake

User Rating:  / 0
PoorBest 

Cancel based recovery with RMAN, a piece of cake.



You take a take nightly full backup on disk
Your database operates in archive log mode
You have defined a recovery window of 3 days
You thus have 3 database backups on disk and archivelog (backups) on disk

You receive a email
"Guy the vendor' s consultant ran some scripts, the database is stuck, can you please reset the database to its state of 12:00 this noon ?"

I first wonder whether it is the right database I am connected with
I then wonder what the current scn number is, and I use the timestamp_to_scn functionality to list the scn number to which the customer would like to reset me the database to.
See also Time and SCN functions in the Oracle Database.




SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
PLATINUM


SQL> select systimestamp,current_scn from v$database;

SYSTIMESTAMP
---------------------------------------------------------------------------
CURRENT_SCN
----------------
06-SEP-11 02.43.25.676134 PM +02:00
2654083


SQL>  select scn_to_timestamp(2646000)  from v$database;

SCN_TO_TIMESTAMP(2646000)
---------------------------------------------------------------------------
06-SEP-11 11.46.12.000000000 AM


SQL>  select timestamp_to_scn('06-SEP-11 12.01.00.000000') from v$database;

TIMESTAMP_TO_SCN('06-SEP-1112.01.00.000000')
--------------------------------------------
2646538

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

Total System Global Area       1068937216 bytes
Fixed Size                        2233336 bytes
Variable Size                   557845512 bytes
Database Buffers                503316480 bytes
Redo Buffers                      5541888 bytes
Database mounted.
SQL> exit


RMAN> run {
2> set until scn 2646535;
3> restore database;
4> recover database;
5> }

executing command: SET until clause

Starting restore at 06-SEP-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=98 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/PLATINUM/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/oradata/PLATINUM/T_PLATGLP_IX_01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/oradata/PLATINUM/T_PLAT_AUDITTRAIL_01.dbf
channel ORA_DISK_1: reading from backup piece /u01/rman/PLATINUM/DB_PLATINUM_2dmlqij4_1_1
channel ORA_DISK_1: piece handle=/u01/rman/PLATINUM/DB_PLATINUM_2dmlqij4_1_1 tag=TAG20110905T213010
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/oradata/PLATINUM/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/oradata/PLATINUM/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oradata/PLATINUM/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/oradata/PLATINUM/T_PLATGLP_01.dbf
channel ORA_DISK_1: reading from backup piece /u01/rman/PLATINUM/DB_PLATINUM_2cmlqij3_1_1
channel ORA_DISK_1: piece handle=/u01/rman/PLATINUM/DB_PLATINUM_2cmlqij3_1_1 tag=TAG20110905T213010
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:25
Finished restore at 06-SEP-11

Starting recover at 06-SEP-11
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 171 is already on disk as file /u01/app/oracle/fast_recovery_area/PLATINUM/archivelog/2011_09_06/o1_mf_1_171_76bkxw6d_.arc
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=169
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=170
channel ORA_DISK_1: reading from backup piece /u01/rman/PLATINUM/AL_PLATINUM_2gmlqipj
channel ORA_DISK_1: piece handle=/u01/rman/PLATINUM/AL_PLATINUM_2gmlqipj tag=TAG20110905T213338
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
archived log file name=/u01/app/oracle/fast_recovery_area/PLATINUM/archivelog/2011_09_06/o1_mf_1_169_76d9h9h7_.arc thread=1 sequence=169
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/PLATINUM/archivelog/2011_09_06/o1_mf_1_169_76d9h9h7_.arc RECID=107 STAMP=761154829
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/PLATINUM/archivelog/2011_09_06/o1_mf_1_170_76d9h9q6_.arc RECID=106 STAMP=761154825
media recovery complete, elapsed time: 00:00:05
Finished recover at 06-SEP-11


RMAN> sql "alter database open resetlogs";

sql statement: alter database open resetlogs
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete.





Note that we needed to open the database with the resetlogs option. Prior to Oracle 10G R1 it was mandatory to take a full backup immediatly after the database was opened with resetlogs option.
From 10G R1 onwards one can recover through resetlogs. See also Can we recover through resetlogs ? Yes we can