Snapshot Standby In Oracle 10g

Introduction

Snapshot standby is an updatable version of the standby database. It is created from the existing standby database.You can modify the database and again you can convert it back to the physical standby database.
The snapshot standby database receives the archive logs(redo) from the primary database but does not apply the redo in standby .It is automatically applied when you convert the snapshot standby database backup to physical standby database and the conversion can be done at any point of time so data from the primary database is always protected .It gives data protection and disaster recovery exactly like the physical standby database.

Why you need a snapshot standby database

Let us consider a scenario, where the application owner wants to perform read/write testing using production database. This is not possible in a real environment as you cannot play with production data. Oracle provides a feature to use the standby database in read/write mode, so that application users can perform tests, and then revert the standby to physical standby status.

This feature is possible in both Oracle 10g and Oracle 11g. Oracle 11g calls it a snapshot standby and can be performed directly.
We will demonstrate the Oracle 10g method in this article.


Steps:
1.Set Flash Recovery Area parameters
2.Create a guranteed restore point
3.Activate standby database
4.Perform read/write testing
5.Revert to original standby

1.On the stansby database,set Flash Recovery Area parameters

SQL> alter system set db_recovery_file_dest_size=2G;

SQL> alter system set db_recovery_file_dest='D:\oracle\product\10.2.0\fra';


2.Create a guranteed restore point
to revert to when you need to return to the original state.

SQL> create restore point pre_activ_snap guarantee flashback database;

SQL> select name, time, storage_size,GUARANTEE_FLASHBACK_DATABASE from V$restore_point;

NAME TIME STORAGE_SIZE GUA
-------------------------------------------------- -------------------------------- ------------ ---
PRE_ACTIV_SNAP 20-MAR-10 07.59.35.000000000 PM 8192000 YES

3.Activate the standby database

SQL> alter database activate standby database;

SQL> select name, open_mode, controlfile_type from V$database;

NAME OPEN_MODE CONTROL
-------------------------------------------------- -------------------- -------
ORCL MOUNTED CURRENT

SQL> alter database open;

SQL> select name, open_mode, controlfile_type from V$database;

NAME OPEN_MODE CONTROL
-------------------------------------------------- -------------------- -------
ORCL READ WRITE CURRENT

Now the standby database is in READ/WRITE mode after the activation.

4.Perform read/write testing

SQL> create table test as select * from v$datafile;

5.Revert to standby setup
Now we finished testing and want to revert to the original state.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2209600 bytes
Variable Size 159385792 bytes
Database Buffers 50331648 bytes
Redo Buffers 5230592 bytes
Database mounted.

SQL> flashback database to restore point PRE_ACTIV_SNAP;

SQL> alter database convert to physical standby;

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2209600 bytes
Variable Size 159385792 bytes
Database Buffers 50331648 bytes
Redo Buffers 5230592 bytes
Database mounted.

SQL> select name, open_mode, controlfile_type from V$database;

NAME OPEN_MODE CONTROL
-------------------------------------------------- -------------------- -------
ORCL MOUNTED STANDBY

Now the standby database is back again in MOUNT mode after revert.