Snapshot standby database Oracle 11g dataguard new feature.

Snapshot Standby Database


Oracle 11g introduces a new Feature in Dataguard – the Snapshot Standby Database which can be in read-write mode. A snapshot standby database is a fully updatable standby database that is created by converting a physical standby database into a snapshot standby database.

As this database is in the read-write mode it is possible to process transactions apart from the primary database (used for Real Application Testing). It still maintains protection by archiving the logs that are shipped from the primary database.

Characteristics of snapshot standby database:

1. Snapshot standby database receives the logs but does not apply the redo data.

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

3. All updates that were made in the snapshot standby database will be discarded when it is converted back to physical standby database.

4. Snapshot standby database cannot be the candidate for a switchover or failover. A snapshot standby database must first be converted back into a physical standby database before performing a role transition.

5. 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.

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

Steps to Convert Physical Standby Database to the Snapshot Standby Database

The conversion from physical standby to snapshot standby database and vice versa can be done in three ways.

1. Dataguard Broker.

2. Enterprise Manager.

3. Manual method. This can be done by issuing the below SQL command in the physical standby database.

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

1. If flash recovery is not already configured, configure it as shown below:

first set the size of the recovery area

SQL> alter system set db_recovery_file_dest_size=’xxxx’;

2. Next, set the location

SQL> alter system set db_recovery_file_dest=’yyyy’;

3. Bring the physical standby database to mount stage:

4. Stop managed recovery if it is active and then convert the physical standby database to snapshot standby database

SQL> alter database convert to snapshot standby;

The database is dismounted during conversion and must be restarted after the conversion completes.

The database is now ready for transactions.



When a physical standby database is converted into a snapshot standby database, an implicit restore point is created and this restore point is used to flashback a snapshot standby database to its original state when it is converted back into a physical standby database from a snapshot standby database.

5. Steps to Convert the Snapshot Standby Database Back to the Physical Standby Database:

1. Shutdown the snapshot standby database

2. Bring the database to the mount state

3. Issue the following command

SQL> alter database convert to physical standby;

6. Shutdown the database and mount it again

SQL> select open_mode, database_role from v$database;

You are now ready to start the media recovery process.

Once a snapshot standby database has been converted back into a physical standby database and restarted – start the process of applying the redo logs and all of the redo received by the snapshot standby database will be applied to the physical standby database to roll it forward.