Steps to Create,Drop and Flashback Guaranteed restore point in Dataguard

CREATE GUARANTEED RESTORE POINT:

1. Stop redo transport and redo apply
   a)If broker is not configured:
   On primary database:
   SQL> alter system set log_archive_dest_state_2=’defer’;
   On standby database:
   SQL> alter database recover managed standby database cancel;
   b)If broker is in place:
   DGMGRL> edit database db set state = ‘TRANSPORT-OFF’;
   DGMGRL> edit database db01 set state = ‘APPLY-OFF’;
2. Set Restore Point in standby database
   On standby database:
   SQL> CREATE RESTORE POINT restore_dg GUARANTEE FLASHBACK DATABASE;
3. Set Restore Point in primary database
   On primary database:
   SQL> CREATE RESTORE POINT restore_dg GUARANTEE FLASHBACK DATABASE;
4. Enable redo transport and redo apply
   a)If broker is not configured:
   On primary database:
   SQL> alter system set log_archive_dest_state_2=’enable’;
   On standby database:
   SQL> alter database recover managed standby database disconnect from session;
   b)If broker is in place:
   DGMGRL> edit database db set state = ‘TRANSPORT-ON’;
   DGMGRL> edit database db01 set state = ‘APPLY-ON’;

FLASHBACK DATABASE TO GUARANTEED RESTORE POINT

1. Stop redo transport and redo apply
   a)If broker is not configured:
   On primary database:
   SQL> alter system set log_archive_dest_state_2=’defer’;
   On standby database:
   SQL> alter database recover managed standby database cancel;
   b)If broker is in place:
   DGMGRL> edit database db set state = ‘TRANSPORT-OFF’;
   DGMGRL> edit database db01 set state = ‘APPLY-OFF’;
2. Shutdown Primary Database and start in mount state
3. Flashback primary database to restore point
   On primary database:
   SQL> flashback database to RESTORE POINT restore_dg;
   SQL> alter database open resetlogs;
4. Shutdown Standby database and start in mount state
5. Flashback standby database
   On standby database:
   SQL> flashback database to RESTORE POINT restore_dg;
6. Enable redo transport and redo apply
   a)If broker is not configured:
   On primary database:
   SQL> alter system set log_archive_dest_state_2=’enable’;
   On standby database:
   SQL> alter database recover managed standby database disconnect from session;
   b)If broker is in place:
   DGMGRL> edit database db set state = ‘TRANSPORT-ON’;
   DGMGRL> edit database db01 set state = ‘APPLY-ON’;

DROP GUARANTEED RESTORE POINT

1. Stop redo transport and redo apply
   a)If broker is not configured:
   On primary database:
   SQL> alter system set log_archive_dest_state_2=’defer’;
   On standby database:
   SQL> alter database recover managed standby database cancel;
   b)If broker is in place:
   DGMGRL> edit database db set state = ‘TRANSPORT-OFF’;
   DGMGRL> edit database db01 set state = ‘APPLY-OFF’;
2. Drop Restore Point in primary database
   On primary database:
   SQL> drop RESTORE POINT restore_dg;
3. Drop Restore Point in standby database
   Ensure the standby database is in mount state and drop
   GUARANTEED RESTORE POINT :
   SQL> drop restore point restore_dg;
   If Active Data Guard is used, open read only the standby database after dropping the Guaranteed restore point.
4. Enable redo transport and redo apply
   a)If broker is not configured:
   On primary database:
   SQL> alter system set log_archive_dest_state_2=’enable’;
   On standby database:
   SQL> alter database recover managed standby database disconnect from session;
   b)If broker is in place:
   DGMGRL> edit database db set state = ‘TRANSPORT-ON’;
   DGMGRL> edit database db01 set state = ‘APPLY-ON’;