Steps to use Guaranteed Restore Points for backouts of database changes in a dataguard broker environment
Failover
Create the restore point on the failover databaase first, if there is addtional standby’s do the same on each standby
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL>create restore point AT_test GUARANTEE FLASHBACK DATABASE;
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE;
Primary
Create the restore point on the primary database
SQL>alter system switch logfile;
SQL>alter system switch logfile;
SQL>alter system switch logfile;
SQL>create restore point AT_test GUARANTEE FLASHBACK DATABASE;
Back out steps – Flashback
In the event of having to backout us the following steps to flashback to the restore points
Disabling the configuration in dataguard broker
Disable the configuration in dataguard broker
DGMGRL> show configuration – check no errors before starting this
DGMGRL> disable FAST_START FAILOVER; – Only needed if fast start failover is enabled
DGMGRL> DISABLE CONFIGURATION;
Primary
Flashback the primary database to the restore point
SQL>shu immediate
SQL>startup mount
SQL>FLASHBACK DATABASE TO RESTORE POINT AT_test;
SQL>alter database open resetlogs;
Standby
Flashback the primary database to the restore point
SQL>shu immediate
SQL>startup mount
SQL>FLASHBACK DATABASE TO RESTORE POINT AT_test;
Enabling the configuration in dataguard broker
Enable the configuration in dataguard broker
DGMGRL> show configuration – check no errors before starting this
DGMGRL> enable CONFIGURATION;
DGMGRL> enable FAST_START FAILOVER; – Only needed if fast start failover is enabled
Post Flashback Checks
Check the standby alert log to make sure the logs are being applied, you should see output like below
Media Recovery Waiting for thread 1 sequence 8 (in transit)
Recovery of Online Redo Log: Thread 1 Group 13 Seq 8 Reading mem 0
Mem# 0: /wload/test2/app/oracle/u02/oradata/testdb1/srl13a.rdo
Mem# 1: /wload/test2/app/oracle/u03/oradata/testdb1/srl13b.rdo
RFS[3]: Selected log 14 for thread 1 sequence 9 dbid 528024012 branch 780852273
Tue Apr 17 15:27:39 2012
Archived Log entry 997 added for thread 1 sequence 8 ID 0x1fc46837 dest 1:
Media Recovery Waiting for thread 1 sequence 9 (in transit)
Recovery of Online Redo Log: Thread 1 Group 14 Seq 9 Reading mem 0
Mem# 0: /wload/test2/app/oracle/u02/oradata/testdb1/srl14a.rdo
Mem# 1: /wload/test2/app/oracle/u03/oradata/testdb1/srl14b.rdo
Hello, Thanks for the great document on flashback on Primary and standby database.
We currently don’t use database broker for primary and failover.Would the steps be any different for enabling flashback and guaranteed restore points on primary and standby database.
Thank you.