Using a combination of Data Guard, restore points, and Flashback Database, a physical standby database can be opened temporarily in read/write mode for development, reporting, or testing purposes, and then flashed back to a point in the past to be reverted back to a physical standby database. When the database is flashed back, Data Guard automatically synchronizes the standby database with the primary database, without the need to re-create the physical standby database from a backup copy of the primary database.

Figure 12-7 shows a physical standby database being activated as a read/write clone database, resynchronized with the primary database, and eventually flashed back and reverted to its physical standby database role. You can repeat this cycle of activate, flashback and revert as many times as is necessary.

Figure 12-7 Using a Physical Standby Database As a Testing and Reporting Database

Description of “Figure 12-7 Using a Physical Standby Database As a Testing and Reporting Database”
Caution:

While the database is activated, it is not receiving redo data from the primary database and cannot provide disaster protection. It is recommended that there be at least two physical standby databases participating in the configuration so that the primary database remains protected against data loss.

Perform the following steps to activate the physical standby database as a production database and later resynchronize it with the primary database.

Step 1 Prepare the physical standby database to be activated.

  1. Set up a flash recovery area.
  2. On the physical standby database that will be activated for read/write access, you should set the following initialization parameters to ensure a guaranteed restore point can be created. This scenario sets up the flash recovery area in the /arch/oradata location:

    SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=20G; SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/arch/oradata'; 
  3. Cancel Redo Apply and create a guaranteed restore point.

On the physical standby database, stop Redo Apply and create a restore point:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; SQL> CREATE RESTORE POINT before_application_patch GUARANTEE FLASHBACK DATABASE; 

When you create a guaranteed restore point, you associate an easy-to-remember name with a timestamp or SCN so that you can later flash back the database to a name instead of specifying an exact SCN or time.

Step 2 Prepare the primary database to have the physical standby be diverged.

  1. Archive the current log file.
  2. On the primary database, switch logs so the SCN of the restore point (created in step 1) will be archived on the physical standby database:

    SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; 

    When using standby redo log files, this step is essential to ensure the database can be properly flashed back to the restore point.

  3. Defer log archive destinations pointing to the standby that will be activated.

On the primary database (on all instances if this is a Real Applications Cluster), defer the archival of redo data to the destination associated with the physical standby database that will be opened. For example:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

Step 3 Activate the physical standby database.

On the physical standby database, perform the following steps:

  1. Activate the physical standby database:
  2. SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE; 
  3. If the physical standby database has been opened read-only since the instance was started, perform this step. Otherwise, skip to step 3.
  4. Enter the following statement to shut down and restart the physical standby database:

    SQL> STARTUP MOUNT FORCE; 
  5. Set the protection mode to maximum performance and open the database for read/write access:
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE; SQL> ALTER DATABASE OPEN; 

After the standby database is activated, its protection mode is downgraded to the maximum performance mode, because there is no standby database configured to protect the database against data loss while it is temporarily activated as a production database. Note that this protection mode setting does not affect the protection mode of the original primary database, it affects only the activated standby database.

When the activated standby database is converted back to a physical standby database, its protection mode is automatically changed to match that of the original primary database.

If the standby database that was opened read/write temporarily has remote archive log destinations, you might need to disable them. In this way, the read/write testing or reporting database will not propagate its temporary changes to other standby databases in the original Data Guard environment.

Step 4 Use the activated database for reporting or testing.

Once the standby database has been activated, you can run reporting tools or perform other testing and activities for days or even weeks, independent of the primary database.

Caution:

While the database is activated, it is not receiving redo data from the primary database and cannot provide disaster protection. It is recommended that there be at least two physical standby databases participating in the configuration so that the primary database remains protected against data loss.

Also, any results stored in the activated database will be lost when you later flash back the database. Results that should be saved must be copied out of the activated database before flashing it back.

Step 5 Revert the activated database back to a physical standby database.

After you finish testing, you need to resynchronize the activated database with the primary database. Issue the following statements on the activated database to quickly flash it back to the guaranteed restore point and resynchronize it with the primary database:

SQL> STARTUP MOUNT FORCE; SQL> FLASHBACK DATABASE TO RESTORE POINT before_application_patch; SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY; SQL> STARTUP MOUNT FORCE;

Step 6 Catch up the standby database to the primary database.

The method you use will depend on how far the activated standby database lags behind the primary database in its application of redo data:

Let archive gap resolution fetch all missing archived redo log files and allow Redo Apply to apply the gap.

If the activated database has not fallen too far behind the original primary database, issue the following statement on the standby database to resynchronize it with the primary database and restart Redo Apply. For example:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; 

Then, go to Step 7.

Create an incremental backup on the primary and apply it to the standby.

If the activated database has fallen too far behind the original primary database (for example, if there are not sufficient log files available), you can take an incremental backup from the primary database and apply it to the standby database. See Section 12.7.1 for information about using RMAN incremental backups to resynchronize the standby database with the primary database.

Note:

If the standby database lags very far behind the primary database, it may be quicker to apply an incremental backup created from the primary database using the steps in Section 12.7.1.

After you apply an incremental backup to the standby database, you typically need to apply more redo to the standby database to activate the physical standby database again for read/write testing or reporting purposes. More specifically, you might need to apply the redo generated by the primary database while the incremental backup was taken. Otherwise, issuing an ALTER DATABSE ACTIVATE STANDBY DATABASE will return an error.

Step 7 Reenable archiving to the physical standby database destination.

On the primary database, issue the following statement to reenable archiving to the physical standby database:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;