Testing a Physical Standby using Snapshot Standby in Oracle 10g

TESTING A PHYSICAL STANDBY DATABASE IN ORACLE 10G Using SNapshot Standby
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.
Activation of the standby
On the standby database

1. Stop dataguard brokers on standby:

SQL> alter system set dg_broker_start=FALSE;

Get the SCN

SQL> select current_scn from v$database;

2. Cancel the managed recovery:

SQL> alter database recover managed standby database cancel;

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

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';

4. Prepare the primary
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;

Stop shipment of logs

SQL> alter system set log_archive_dest_state_2=DEFER

5. Activate the Physical Standby
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;

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

SQL> alter system set log_archive_dest_state_2=DISABLE;

6. Revert the snapshot database to physical standby
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;

Re-enable log shipping on the primaryEnable shipment:

SQL> alter system set log_archive_dest_state_2=ENABLE

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

SQL> alter system set log_archive_dest_state_2=ENABLE