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