Suppose an error has occurred on the primary database in a Data Guard configuration in which the standby database is using real-time apply. In this situation, the same error will be applied on the standby database.

However, if Flashback Database is enabled, you can revert the primary and standby databases back to their pre-error condition by issuing the FLASHBACK DATABASE and OPEN RESETLOGS statements on the primary database, and then issuing a similar FLASHBACK STANDBY DATABASE statement on the standby database before restarting log apply services. (If Flashback Database is not enabled, you need to re-create the standby database, as described in Chapter 3 and Chapter 4, after the point-in-time recovery was performed on the primary database.)

12.5.1 Flashing Back a Physical Standby Database to a Specific Point-in-Time

The following steps describe how to avoid re-creating a physical standby database after you issued the OPEN RESETLOGS statement on the primary database.

Step 1 Determine the SCN before the RESETLOGS operation occurred.

On the primary database, use the following query to obtain the value of the system change number (SCN) that is 2 SCNs before the RESETLOGS operation occurred on the primary database:

SQL> SELECT TO_CHAR(RESETLOGS_CHANGE# - 2) FROM V$DATABASE;

Step 2 Obtain the current SCN on the standby database.

On the standby database, obtain the current SCN with the following query:

SQL> SELECT TO_CHAR(CURRENT_SCN) FROM V$DATABASE;

Step 3 Determine if it is necessary to flash back the database.

If the value of CURRENT_SCN is larger than the value of resetlogs_change# – 2, issue the following statement to flash back the standby database.

SQL> FLASHBACK STANDBY DATABASE TO SCN resetlogs_change# -2; 

If the value of CURRENT_SCN is less than the value of the resetlogs_change# – 2, skip to Step 4.

If the standby database’s SCN is far enough behind the primary database’s SCN, log apply services will be able to continue through the OPEN RESETLOGS statement without stopping. In this case, flashing back the database is unnecessary because log apply services do not stop upon reaching the OPEN RESETLOGS statement in the redo data.

Step 4 Restart Redo Apply.

To start Redo Apply on the physical standby database, issue the following statement:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; 

The standby database is now ready to receive and apply redo from the primary database.

LOGICAL STANDBY
===============
1) On primary determine an SCN at least 2 SCN’s prior to the OPEN RESETLOGS
SELECT TO_CHAR(resetlogs_change# – 2) FROM v$DATABASE;

2) On standby, obtain the current SCN
SELECT TO_CHAR(current_scn) FROM V$DATABASE;

3) On standby flashback db
FLASHBACK STANDBY DATABASE TO SCN

4) On standby restart recovery
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;