Testing a Physical Standby Database in Oracle 10g

If you don’t want to deal with missteps, I recommend that you test your standby database to facilitate the failover or switchover process.

This procedure is very useful when you have physical standby databases for testing and other purposes that require read-write access to the standby database. Also, it improves your checklist in the event of an error or disaster.

By using Snapshot standby databases, redo data is not applied until you convert the snapshot standby database back into a physical standby database, and after all local updates to the snapshot standby database are discarded.

Requirements

The following requirements need to be met in order to create a snapshot standby.

1. The Data Guard environment (a primary and a physical standby) needs to be Oracle 10.2.0.1 version or higher.
2. The Primary and Standby databases need to be in archivelog mode (this is a default requirement for Data Guard).
3. Force logging should be set to TRUE (to avoid no-logging operations).
4. Flash Recovery Area (FRA) is required on the standby database to implement a Flashback database.
5. The Primary and standby are in sync at the time of the test, or the gap between primary and physical standby is nominal.

Activation of the standby

On the standby database

1. Stop dataguard brokers on standby:

   SQL> alter system set dg_broker_start=FALSE;

2. Get the SCN

   SQL> select current_scn from v$database;

3. Cancel the managed recovery:

   SQL> alter database recover managed standby database cancel;

4. Create the restore point.A restore point can be specified such that it guarantees the database can be recovered to a particular point-in-time and eliminates the need to manually record an SCN or timestamp to use with the Flashback database and Flashback table operations:

   SQL> create restore point TEST_NEW_FEATURE guarantee flashback database;

5. Now confirm the scn from restore point:

   SQL> col name form a40;
   SQL> select scn, time, name from v$restore_point where name = 'TEST_NEW_FEATURE';

Prepare the primary

1. Archive logsWhen using the standby redo logs, this step is essential to ensure that the database can be properly flashed back to the restore point.

   SQL> alter system archive log current;
   SQL> alter system switch logfile;
   SQL> alter system switch logfile;

2. Stop shipment of logs

   SQL> alter system set log_archive_dest_state_2=DEFER

Activate the Physical Standby

1. Activation of the standby:

   SQL> alter database activate standby database;
   SQL> startup mount force;
   SQL> alter database set standby database to maximize performance; 
   (This is used in case you have not set it before)
   	SQL> alter database open;

2. disable log_archive_dest_2 (this will prevent archive logs being sent to primary):

   SQL> alter system set log_archive_dest_state_2=DISABLE;

Time for testing

Once the standby database has been activated, it is a full-blown production system. You may run reports, test new code, or create objects. Remember that any results stored in the activated database will be lost when the database is flashed back to before activation time.. If you need to save the results, they must be copied or exported out of the activated database before flashing it back.

Revert the snapshot database to physical standby

After testing is completed, you need to resynchronize the activated database with the primary database.

1. Flashback to the restore point:

   SQL> STARTUP MOUNT FORCE;
   SQL> FLASHBACK DATABASE TO RESTORE POINT TEST_NEW_FEATURE;
   SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
   SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE;
   SQL> STARTUP MOUNT FORCE;

2. Re-enable log shipping on the primaryEnable shipment:

   SQL> alter system set log_archive_dest_state_2=ENABLE

3. To re-enable log shipping on the standby, enable this parameter:

   SQL> alter system set log_archive_dest_state_2=ENABLE

4. Check dgmgrl configurationDGMGRL> show configuration verbose;
   Current status for “xxx”:
   SUCCESS
   DGMGRL> enable configuration (if not success)

A status of data guard configuration successful indicates the success of the procedures.

On Oracle Database 11g, you can use the Data Guard command-line interface (DGMGRL), Oracle Active Data Guard, and the OEM interface. In my next blog I’ll describe the equivalent procedures on 11g.