Oracle11g SNAPSHOT standby database

Till now you have come across two types of standby databases namely Physical standby database and Logical standby database. From Oracle 11g onwards there is one more addition to this. It is called Snapshot standby database.

Snapshot standby is 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 back 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??

As told earlier snapshot standby database is updatable. It provides the replicated version of the primary database which can be used for development, testing and reporting purposes. You can always refresh the snapshot standby database to be in sync with the primary by converting it to physical standby database. Again you can convert to snapshot version do the testing and resynchronize with primary. This cycle can be repeated any number of times!

The conversion from physical standby to snapshot standby database and vice versa can be done in three ways.
1.Dataguard Broker(If the physical standby is managed by DGMGRL)
2.Enterprise Manager(If the physical standby is managed by DGMGRL)
3.Manual method.

Let us see how you can convert the physical standby database to snapshot standby database manually. This can be done by issuing the below SQL command in the physical standby database.

Sql>alter database convert to snapshot standby;

If the standby database is a RAC setup then you need to shutdown all the instances except one on which you will be using the conversion commands.

You may wonder how you will be able to get back to the original state .Here Oracle uses its own features within, nothing else! Oracle uses restore point and flashback feature to accomplish this. If you remember, restore point and flashback database concepts were first introduced in Oracle10g.

When you convert the physical standby database to snapshot standby database a guaranteed restore point is created. This guaranteed restore point is used to flashback a snapshot standby database to its original state. The name of this guaranteed restore points begins with ‘SNAPSHOT_STANDBY_REQUIRED_’. The database is dismounted during this conversion and you have to restart the database (which will be the snapshot standby database).

Once it is converted the database can be opened in read/write mode, you can update the database. Meanwhile you will be receiving the redo from the primary, which will be automatically applied when snapshot standby database is converted back to physical standby database.

Now, to convert the snapshot standby back to the physical standby state use the following SQL command:

Sql>alter database convert to physical standby;

Using the guaranteed restore point the snapshot standby database will be flashed back to its original state and subsequently the restore point will be dropped. After the conversion the redo will be applied which were received by the snapshot standby database.

Note:

When you convert a physical standby database to snapshot standby database, Then

• Redo gap detection and resolution will work as before.
• You cannot perform switchover or failover on a snapshot standby database. To perform role transitions you have to convert the snapshot standby database back to physical standby database.
• If the primary database has a new incarnation (because of Flashback database or Open resetlogs ),the snapshot database continue to receive the redo from the primary.
• In case of role transition of one of the standby database (like failover or switchover), the snapshot standby database continues to receive the redo from the new primary.

Comments are always welcome :-)