Opening a Oracle 10gR2 standby database in Read-Write mode for DML, DDL operations without affecting the primary database.

Task

Opening the Standby Database in Read-Write mode, do a test and bring it back to a standby database without recopying of datafiles, logfiles or recreating the standby controlfile. Then showing that the test did not have an effect on primary db.

Summary

Before proceeding, one must have a working dataguard configuration. Make sure that logs are being passed from the primary db to the secondary db. The task here is to open the standby database in read-write mode, insert some data and then revert it back to its previous consistent state with the primary db without recopying of datafiles or recreating the standby controlfile. This is similar to the snapshot feature available in 11g.

Benefit

This is helpful when one has a working dataguard configuration and wants to test some scripts on live data. After the tests, data can be reverted to its original state. This also avoids the need to create a test database or perform export or import to get the live data to test on as this is time consuming.


I. STEPS

NOTE: Make sure that the dataguard is working

NOTE: The following steps are performed on the PRIMARY

Create a table and insert some values
SQL> create table mytest(id number);
SQL> insert into mytest values(1);
SQL> insert into mytest values(2);
SQL> insert into mytest values(3);
SQL> commit;

NOTE: The following steps are performed on the STANDBY

Stop the Dataguard
a. SQL> alter database recover managed standby database cancel;

Enable Flashback
a. Check whether flashback is enabled
i. SQL> select flashback_on from v$database;
ii. If no, then
· SQL> alter database flashback on;

Create restore point
SQL> create restore point before_open guarantee flashback database;

NOTE: The following steps are performed on the PRIMARY

Archive the current log file
a. SQL> alter system archive log current;

Stop logs being shipped to the standby
a. SQL> alter system set log_archive_dest_state_2=DEFER

NOTE: The following steps are performed on the STANDBY

Activate the standby database
a. SQL> alter database activate standby database

Open the standby database
a. SQL> alter database open

Query mytest table and insert some values
a. SQL> select * from mytest; (Here you will get 3 rows)
b. SQL> insert into mytest values(4);
c. SQL> insert into mytest values(5);
d. SQL> insert into mytest values(6);
e. SQL> select * from mytest; (Here you will get 6 rows)

NOTE: The following steps are performed on the STANDBY

To revert the standby back to its previous configuration, use the restore point we created. This would means any changes to the done to the data while the standby was open is lost.
a. SQL> shutdown immediate;
b. SQL> startup mount;
c. SQL> flashback database to restore point before_open;

Switch it back to a standby database
a. SQL> alter database convert to physical standby;

NOTE: The following steps are performed on the PRIMARY

Enable logs to be shipping from primary again
a. SQL> alter system set log_archive_dest_state_2=ENABLE;

NOTE: The following steps are performed on the STANDBY

Drop the restore point
a. SQL> drop restore point before_open;

Activate the Dataguard
a. SQL> alter database recover managed standby database disconnect from session;

NOTE: The following steps are performed on the PRIMARY

Query the mytest table
a. SQL> select * from mytest (Here you will get the 3 original rows)

Check whether the logs are being shipped from the primary to the standby
a. SQL> archive log list;
b. SQL> alter system switch logfile;

NOTE: The following steps are performed on the STANDBY

Check whether the standby is receiving the logs
a. SQL> select sequence#, applied, archived from v$archived_log;