This post describes the steps necessary to create, use and drop a Guaranteed restore point in a Data Guard configuration. For the purposes of this post, the following fictitious environment is used as an example to describe the procedure:
Primary database: chicago Standby database: boston
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_n='defer';
Here, replace n with the corresponding number for remote destinations.
On standby database:
SQL> alter database recover managed standby database cancel;
b) If broker is in place:
DGMGRL> edit database chicago set state = 'TRANSPORT-OFF'; DGMGRL> edit database boston set state = 'APPLY-OFF';
2. Set GRP in standby database.
On standby database:
SQL> CREATE RESTORE POINT grp_dg GUARANTEE FLASHBACK DATABASE;
3. Set GRP in primary database.
On primary database:
SQL> CREATE RESTORE POINT grp_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_n='enable';
Here, replace n with the corresponding number for remote destinations.
On standby database:
SQL> alter database recover managed standby database using current logfile disconnect;
b) If broker is in place:
DGMGRL> edit database chicago set state = 'TRANSPORT-ON'; DGMGRL> edit database boston 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_n='defer';
Here, replace n with the corresponding number for remote destinations.
On standby database:
SQL> alter database recover managed standby database cancel;
b) If broker is in place:
DGMGRL> edit database chicago set state = 'TRANSPORT-OFF'; DGMGRL> edit database boston set state = 'APPLY-OFF';
2. Shutdown Primary Database and start one instance in mount stage.
3. Flashback primary database to restore point.
On primary database:
SQL> flashback database to RESTORE POINT grp_dg; SQL> alter database open resetlogs;
4. Shutdown Standby database and start one instance in mount stage.
5. Flashback standby database.
On standby database:
SQL> flashback database to RESTORE POINT grp_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_n='enable';
Here, replace n with the corresponding number for remote destinations.
On standby database:
SQL> alter database recover managed standby database using current logfile disconnect;
b) If broker is in place:
DGMGRL> edit database chicago set state = 'TRANSPORT-ON'; DGMGRL> edit database boston set state = 'APPLY-ON';
7. If Active Data Guard licence is used, open read only the standby database.
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_n='defer';
Here, replace n with the corresponding number for remote destinations.
On standby database:
SQL> alter database recover managed standby database cancel;
b) If broker is in place:
DGMGRL> edit database chicago set state = 'TRANSPORT-OFF'; DGMGRL> edit database boston set state = 'APPLY-OFF';
2. Drop GRP in primary database.
On primary database:
SQL> drop RESTORE POINT grp_dg;
3. Drop GRP in standby database.
Ensure the standby database is in mount stage and drop GRP:
SQL> drop restore point grp_dg;
If Active Data Guard licence is used, open read only the standby database after dropping the GRP.
4. Enable redo transport and redo apply
a) If broker is not configured:
On primary database:
SQL> alter system set log_archive_dest_state_n='enable';
Here, replace n with the corresponding number for remote destinations.
On standby database:
SQL> alter database recover managed standby database using current logfile disconnect;
b) If broker is in place:
DGMGRL> edit database chicago set state = 'TRANSPORT-ON'; DGMGRL> edit database boston set state = 'APPLY-ON';