Using Snapshot Standby Database On Oracle 11g

A snapshot standby database is a fully update-able standby database that is created by converting a physical standby database into a snapshot standby database. A snapshot Standby is open in the read-write mode and hence it is possible to process transactions independently of the primary database. At the same time, it maintains protection by continuing to receive data from the production database, archiving it for later use. 

Snapshot database has following characteristics

1. Snapshot standby database receives and archives, but does not apply the redo data.

2. Redo data received from the primary database is applied automatically once it is converted back into a physical standby database.

3. Data from the primary database is always protected as the archives are being received and stored in place.

4. All local updates will be discarded when snapshot database is converted back to physical standby database.

5. If the primary database moves to new database branch (for example, because of a Flashback Database or an OPEN RESETLOGS), the snapshot standby database will continue accepting redo from new database branch.

6. Snapshot standby database cannot be the target of a switchover or failover. A snapshot standby database must first be converted back into a physical standby database before performing a role transition to it.

7. After a switchover or failover between the primary database and one of the physical or logical standby databases in a configuration, the snapshot standby database can receive redo data from the new primary database after the role transition.

8. Snapshot standby database cannot be the only standby database in a Maximum Protection Data Guard configuration.

Once the snapshot standby is activated this database diverges from its primary database over time because redo data from the primary database is not applied.Again local updates to the snapshot standby database will cause additional divergence.

--> Steps to convert Physical Standby Database to the Snapshot Standby Database:

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;  

1) If not already configured , configure flash recovery area as given below 

a) Set the size for recovery area. 

Alter system set db_recovery_file_dest_size=<size> 

b) Set Flash recovery area. 

Alter system set db_recovery_file_dest=<path> 

2) Bring the physical standby database to mount stage.  

3) Stop managed recovery if it is active.  

4) Convert physical standby database to snapshot standby database.  

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;  

The database is dismounted during conversion and must be restarted. 

Once the database is restarted  any transaction can be executed . 

SQL> select open_mode,database_role from v$database; 

OPEN_MODE                                  DATABASE_ROLE 
----------                                         ---------------- 
READ WRITE                                 SNAPSHOT STANDBY

Remember a guaranteed restore point is created when a physical standby database is converted into a snapshot standby database and this restore point is used to flashback a snapshot standby to its original state when it is converted back into a physical standby database.

--> Steps to convert the Snapshot Standby Database to the Physical Standby Database


1. Shutdown the snapshot standby database. 

2. Bring the database to the mount stage. 

3. Issue the command 

ALTER DATABASE CONVERT TO PHYSICAL STANDBY; 

4. Shutdown the database and mount it

SQL> select open_mode,database_role from v$database; 

OPEN_MODE        DATABASE_ROLE 
----------              ---------------- 
MOUNTED          PHYSICAL STANDBY 

5. Start the media recovery process.

Once a snapshot standby database has been converted back into a physical standby database and restarted, Redo Apply can be started and all redo received by the snapshot standby database will be applied to the physical standby database.

Flashback Database is used to convert a snapshot standby database back into a physical standby database. Any operation that cannot be reversed using Flashback Database technology will prevent a snapshot standby from being converted back to a physical standby.

If you are using the Data Guard Broker, you should use the below command to convert a physical standby database to a snapshot standby database and back.

DGMGRL> CONVERT DATABASE <db_unique_name> TO {SNAPSHOT | PHYSICAL} STANDBY;