Snapshot Standby Databases in Oracle

Oracle  - Snapshot Standby Databases

Till Oracle 10g, we have two types of standby databases namely physical standby database and logical standby database.

From Oracle 11g, we got two more types of standby databases, called ADG (Active Data Guard) and snapshot standby database.

Oracle Active Data Guard - Standby databases can simultaneously be in read only mode and recovery mode, mostly used for reporting purposes.

Snapshot standby is updatable version of the standby database.

What is snapshot standby database in Oracle?

The use of snapshot standby database is in the situation where if we want to clone a production database for testing something, we can convert existing physical standby database to snapshot standby database which is as close as to the production database w.r.t data, do required testing on the snapshot standby database and convert it back to physical standby database. Snapshot standby database is updatable and it provides the replicated version of the primary database, which can be used for development, testing purposes. We can always refresh the snapshot standby database to be in sync with the primary by converting it to physical standby database. Again we can convert to snapshot version do the testing and resynchronize with primary. This cycle can be repeated any number of times. Snapshot standby databases are best used in scenarios where the benefit of having a temporary, updatable snapshot of the primary database justifies additional administrative complexity and increased time to recover from primary database failures.

A snapshot standby database receives and archives redo data from a primary database, but are not applied. 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. So data from the primary database is always protected. It gives data protection and disaster recovery exactly like the physical standby database.

Introduced in Oracle 11gR1, a snapshot standby database is a physical standby that is temporarily disconnected from Data Guard configuration and able to be used as a fully updatable stand-alone database. As this is in READ WRITE mode, any kind of testing or changes can be done, including features like using Database Replay.

Converting Physical Standby Database into Snapshot Standby Database

There are three restrictions where we cannot convert to a snapshot.

a) If it is the only standby of a Maximum Protection configuration.
b) If it is the target of a current switchover operation.
c) If it is the designated failover database for Fast Start Failover (FSFO).

If this is your only standby database, if you have to failover to it, the failover is going to take longer - because the standby has to be flashed back, and caught up, before the transition can complete, be aware that worst case scenario. It’s always recommended considering a Data Guard configuration that consists of two physical standby databases (at least), one is always kept physical standby database, and the other can be used/tested as a snapshot standby.

Perform the following steps to convert a physical standby database into a snapshot standby database:

Step 1: Check if Flashback is enabled or not.

This conversion is achieved using flashback database, but the physical standby database does not need to have flashback database explicitly enabled. Even if the flashback is not enabled explicitly the conversion will work.

i) SQL> select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;

NAME OPEN_MODE DATABASE_ROLE
---------------------------------------- ---------- ----------------
PHYSTD2 MOUNTED PHYSICAL STANDBY

ii) SQL> Show parameter db_recovery_file_dest
NAME TYPE VALUE
--------------------------------------------------------------- ----------- ------------------------------
db_recovery_file_dest string /u01/oradata/phystd2/FRA/
db_recovery_file_dest_size big integer 5G

iii) SQL> select flashback_on from v$database;
FLASHBACK
---------
YES

Step 2: Stop Redo Apply, if it is active.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

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

Step 4: Ensure that the database is mounted.

Step 5: Perform the conversion from physical standby to snapshot standby database:

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

I). Manual method. This can be done by issuing the below SQL command in the physical standby database. Once it is converted the database can be opened in read/write mode.

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

II). Data Guard Broker

A physical standby database that is managed by the Data Guard broker can be converted into a snapshot standby database using either DGMGRL or Oracle Enterprise Manager.

DGMGRL> show configuration;

   Configuration - fsfo_config

   Protection Mode:     MaxAvailability

   Databases:

     prima - Primary database

     phystd1 - Physical standby database

     phystd2 - Physical standby database

   Fast-Start Failover: DISABLED

   Current status:

    SUCCESS

The DGMGRL command that converts the database is:

DGMGRL> CONVERT DATABASE standby_unique_name TO SNAPSHOT STANDBY

DGMGRL> CONVERT DATABASE phystd2 TO SNAPSHOT STANDBY

DGMGRL> show configuration;

Configuration - fsfo_config

Protection Mode:     MaxAvailability

Databases:

 prima - Primary database

 phystd1 - Physical standby database

 phystd2 - Snapshot standby database

Fast-Start Failover: DISABLED

Current status:

SUCCESS

III). Enterprise Manager (If the physical standby is managed by DGMGRL)

Step 6: The database is dismounted during this conversion and we have to restart the database, which will be the snapshot standby database.

SQL> startup

Step 7: Check for database role

SQL> Select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;
NAME OPEN_MODE DATABASE_ROLE
-------------------------------------- ---------- -------------------------------
PHYSTD2 READ WRITE SNAPSHOT STANDBY

Meanwhile it will be receiving the redo from the primary, which will be automatically applied when snapshot standby database is converted back to physical standby database.

When we convert a physical standby database to snapshot standby database, then 

• Redo gap detection and resolution will work as before.
• A 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.
• 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.
• Even if the primary database has a new incarnation (because of Flashback database or Open resetlogs), the snapshot database continues to receive the redo from the primary.

Converting Snapshot Standby Database into Physical Standby Database

Oracle uses restore point and Flashback Database feature to accomplish this conversion of snapshot standby database to physical standby database. Restore point and Flashback Database concepts were first introduced in Oracle 10g. When we 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_’.

When the database is converted back, it is flashed back to the guaranteed restore point, put back into the full Data Guard configuration, and the redo is applied eventually catching the standby database back up to the primary. Once this is completed, the guaranteed restore point is removed automatically. 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.

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

A snapshot standby database must be opened at least once in read-write mode before it can be converted into a physical standby database.

Perform the following steps to convert a snapshot standby database into a physical standby database:

Step 1: Check for current database role

SQL> Select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;
NAME      OPEN_MODE DATABASE_ROLE
----------------------------------------- ---------- ----------------
PHYSTD2 READ WRITE SNAPSHOT STANDBY

SQL> select name, guarantee_flashback_database from v$restore_point;
NAME                                                                       GUA
----------------------------------------------------------------- -------
SNAPSHOT_STANDBY_REQUIRED_08/18/2012 17:13:31   YES

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

Step 3: Ensure that the database is mounted.

Step 4: Do the conversion of snapshot standby database to physical standby database.

I). Manual method.

Issue the following SQL statement to convert the snapshot standby back to the physical standby:

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY; 

II). Data Guard Broker

DGMGRL> show configuration;

Configuration - fsfo_config

Protection Mode:     MaxAvailability

Databases:

 prima - Primary database

 phystd1 - Physical standby database

 phystd2 - Snapshot standby database

Fast-Start Failover: DISABLED

Current status:

SUCCESS

The DGMGRL command that converts the database is:

DGMGRL> CONVERT DATABASE standby_unique_name TO PHYSICAL STANDBY

DGMGRL> CONVERT DATABASE phystd2 TO PHYSICAL STANDBY

DGMGRL> show configuration;

Configuration - fsfo_config

Protection Mode:     MaxAvailability

Databases:

 prima - Primary database

 phystd1 - Physical standby database

 phystd2 - Physical standby database

Fast-Start Failover: DISABLED

Current status:

SUCCESS

III). Enterprise Manager (If the physical standby is managed by DGMGRL).

Step 5: Start database in mount, if not already mounted.

SQL> alter database mount standby database;

Step 6: Recover Physical Standby database

SQL> recover managed standby database disconnect from session;

Step 7: Check for database role

SQL> select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;

NAME OPEN_MODE DATABASE_ROLE
--------------------------------- ---------- ----------------
PHYSTD2 MOUNTED PHYSICAL STANDBY

Related Oracle Articles: Logical Standby Databases   Physical Standby Databases    dgmgrl utility