fbpx

How to Create an Oracle Guaranteed Restore Point on Data Guard

When you are doing an important change to your Oracle database, you need to be able to go back to the point you were before the change in case something goes wrong.

There is no better way to go back in time than with a guaranteed restore point.

1. Create a 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';

=====>>>>> 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 <primary> set state = 'TRANSPORT-OFF';
DGMGRL> edit database <standby> 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'; 

=====>>>>> 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 <primary> set state = 'TRANSPORT-ON';
DGMGRL> edit database <standby> set state = 'APPLY-ON';

2. 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’;

=====>>>>> 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 <primary> set state = 'TRANSPORT-OFF';
DGMGRL> edit database <standby> 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’;

=====>>>>> 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 <primary> set state = 'TRANSPORT-ON';
DGMGRL> edit database <standby> set state = 'APPLY-ON';

7. If Active Data Guard licence is used, open read only the standby database

3. 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’;

=====>>>>> 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 <primary> set state = 'TRANSPORT-OFF';
DGMGRL> edit database <standby> 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’;

=====>>>>>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 <primary> set state = 'TRANSPORT-ON';
DGMGRL> edit database <standby> set state = 'APPLY-ON';

Leave a Reply

Your email address will not be published. Required fields are marked *