Configuration of Snapshot Standby Database in Oracle 11g

Configuration of Snapshot Standby Database in Oracle 11g



Snapshot Standby is a new features introduced in Oracle 11g. A snapshot standby database is a type of updatable standby database that provides full data protection for a primary database. A snapshot standby database receives and archives, but does not apply, redo data from its primary database. Redo data received from the primary database is applied when a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.



The main benefits of snapshot standby database is that we can convert the physical standby database into a read-write real time clone of the production database and we can then temporarily use this environment for carrying out any kind of development testing, QA type work or to test the impact of a proposed production change on an application.



The best part of this snapshot features is that the Snapshot Standby database in turn uses the Flashback Database technology to create a guaranteed restore point to which the database can be later flashed back to.All the features of the flashback are inherent in the snapshot standby.



Here we will configure the snapshot standby database



Step 1 : Create the physical standby database

Create the physical standby database .



Step 2: Enable Flashack Parameter



SQL> alter system set db_recovery_file_dest_size=4G scope=both ;

System altered.



SQL> alter system set db_recovery_file_dest='D:\standby\fra\' scope=both ;

System altered.



SQL> show parameter db_recovery

NAME TYPE VALUE

----------------------------- ----------- -------------------------

db_recovery_file_dest string D:\standby\fra\

db_recovery_file_dest_size big integer 4G



Step 3 : Stop the media recovery process

SQL> alter database recover managed standby database cancel;

Database altered.



Step 4 : Ensure that the database is mounted, but not open.

SQL> shut immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount



Step 5 : Create guaranteed restore point

SQL> create restore point snapshot_rspt guarantee flashback database;

Restore point created.



Step 6 : Perform the conversion to snapshot standby database

SQL> alter database convert to snapshot standby ;

Database altered.



SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE

--------- -------------- --------------------------

NOIDA MOUNTED SNAPSHOT STANDBY



SQL> alter database open;

Database altered.



SQL> select name,db_unique_name ,open_mode,database_role from v$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE

------- ---------------------- --------------- ------------------------

NOIDA gurgoan READ WRITE SNAPSHOT STANDBY



Since the database is in read-write mode , so we can make some changes or even change the parameter say tunning parameter and check there performance after converting to physical standby and further flashback the whole changes.



SQL> select name,guarantee_flashback_database from v$restore_point;

NAME GUA

----------------------------------------------------------------- -------

SNAPSHOT_STANDBY_REQUIRED_11/18/2011 20:41:01 YES

SNAPSHOT_RSPT YES



While the original physical standby database has been now converted to snapshot database, some changes are happening on the Primary database and those changes are shipped to the standby site but not yet applied. They will accumulate on the standby site and will be applied after the snapshot standby database is converted back to a physical standby database.



Step 7 : Convert snapshot standby to physical standby

SQL> shut immediate

SQL> startup mount



SQL> alter database convert to physical standby ;

Database altered.



SQL>shut immediate

SQL> startup mount



SQL> alter database recover managed standby database disconnect from session;

Database altered.



SQL> alter database recover managed standby database cancel;

Database altered.



SQL> alter database open;

Database altered.



SQL> select name,open_mode,db_unique_name,database_role from v$database;

NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE

------- -------------- ----------------------- ----------------------

NOIDA READ ONLY gurgoan PHYSICAL STANDBY



SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.