Thursday, November 20, 2014

How to flash the database back to a guaranteed restore point

After a week of testing their new release, my customer wanted their database reset to the state it was previously in.
Earlier this week, I created a restore point of type GUARANTEED FLASHBACK.

Here is how I restored a database using flashback database technology. It was, as expected, very quick. Only a few seconds to flash back 14 GB of changes.

Connect to Recovery Manager:
oracle@myserver:[TESTDB01]# rman target / catalog uid/pwd@rmancat

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Nov 20 16:06:44 2014

connected to target database: TESTDB01 (DBID=411134280, not open)
connected to recovery catalog database

RMAN> list restore point all;

SCN              RSP Time  Type          Time         Name
---------------- --------- ----------   ---------     ----
153050263689                GUARANTEED  17-NOV-14     AKSEPT_TEST_START

Start the flashback operation:
RMAN> FLASHBACK DATABASE TO RESTORE POINT AKSEPT_TEST_START;
Starting flashback at 20-NOV-14
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=585 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 6.3.0.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=683 device type=DISK

starting media recovery

archived log for thread 1 with sequence 4651 is already on disk as file /u04/fast_recovery_area/TESTDB01/archivelog/2014_11_17/o1_mf_1_4651_b6m91zog_.arc
media recovery complete, elapsed time: 00:00:01
Finished flashback at 20-NOV-14 

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> exit


-- all done --

From alert.log:
Completed: ALTER DATABASE   MOUNT
Thu Nov 20 16:10:37 2014
alter database recover datafile list clear
Completed: alter database recover datafile list clear
RMAN flashback database to before scn 153050263690 in incarnation 2
Flashback Restore Start
Thu Nov 20 16:12:11 2014
Flashback Restore Complete
Flashback Media Recovery Start
started logmerger process
Parallel Media Recovery started with 8 slaves
Thu Nov 20 16:12:22 2014
Flashback Media Recovery Log /u04/fast_recovery_area/TESTDB01/archivelog/2014_11_17/o1_mf_1_4651_b6m91zog_.arc
Thu Nov 20 16:12:22 2014
Incomplete Recovery applied until change 153050263690 time 11/17/2014 07:53:33
Flashback Media Recovery Complete
Completed: RMAN flashback database to before scn 153050263690 in incarnation 2
Thu Nov 20 16:13:29 2014
alter database open resetlogs
RESETLOGS after incomplete recovery UNTIL CHANGE 153050263690
Archived Log entry 4827 added for thread 1 sequence 4826 ID 0x18811648 dest 1:
Archived Log entry 4828 added for thread 1 sequence 4823 ID 0x18811648 dest 1:
Archived Log entry 4829 added for thread 1 sequence 4822 ID 0x18811648 dest 1:
Archived Log entry 4830 added for thread 1 sequence 4827 ID 0x18811648 dest 1:
Archived Log entry 4831 added for thread 1 sequence 4824 ID 0x18811648 dest 1:
Archived Log entry 4832 added for thread 1 sequence 4825 ID 0x18811648 dest 1:
Clearing online redo logfile 1 /u03/oradata/TESTDB01/redo01.log
Clearing online log 1 of thread 1 sequence number 4826
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /u03/oradata/TESTDB01/redo02.log
Clearing online log 2 of thread 1 sequence number 4823
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /u03/oradata/TESTDB01/redo03.log
Clearing online log 3 of thread 1 sequence number 4822
Thu Nov 20 16:13:41 2014
Clearing online redo logfile 3 complete
Clearing online redo logfile 4 /u03/oradata/TESTDB01/redo04.log
Clearing online log 4 of thread 1 sequence number 4827
Clearing online redo logfile 4 complete
Clearing online redo logfile 5 /u03/oradata/TESTDB01/redo05.log
Clearing online log 5 of thread 1 sequence number 4824
Clearing online redo logfile 5 complete
Clearing online redo logfile 6 /u03/oradata/TESTDB01/redo06.log
Clearing online log 6 of thread 1 sequence number 4825
Thu Nov 20 16:13:52 2014
Clearing online redo logfile 6 complete
Resetting resetlogs activation ID 411113032 (0x18811648)
Online log /u03/oradata/TESTDB01/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u03/oradata/TESTDB01/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u03/oradata/TESTDB01/redo03.log: Thread 1 Group 3 was previously cleared
Online log /u03/oradata/TESTDB01/redo04.log: Thread 1 Group 4 was previously cleared
Online log /u03/oradata/TESTDB01/redo05.log: Thread 1 Group 5 was previously cleared
Online log /u03/oradata/TESTDB01/redo06.log: Thread 1 Group 6 was previously cleared
Thu Nov 20 16:13:52 2014
Setting recovery target incarnation to 3

No comments:

Post a Comment