Snapshot standby database in Oracle

We already know about standby databases i.e. dataguard. Here we will learn about snapshot standby database in Oracle

What is the snapshot standby database in Oracle?

• With 11gR, we can convert the physical standby database into a read-write database for some time .and later again convert it back into physical standby mode using the snapshot database feature
• Redo data from a primary database is received when a standby database is transformed into a snapshot database, but it does not apply that redo data. i.e MRP process is put on hold
• All local modifications made in a snapshot standby database are discarded when it is turned back into a physical standby database
• Redo data stored in a standby server is then applied to make it sync with the primary
• Snapshot standby uses the flashback database feature. An implicit guaranteed restore point is created for the standby database
• when the snapshot standby is converted back to physical standby mode, the guaranteed restore point is dropped

How to convert into Snapshot Standby Database

Here are the steps to be used in the Physical standby database if not using DGMGRL.

sqlplus / as sysdba
alter database recover managed standby database cancel;
shutdown immediate

sqlplus / as sysdba
startup mount
alter database convert to snapshot  database;
alter database open;

Here are the steps to be used in the Physical standby database if using DGMGRL.

dgmgrl
DGMGRL>connect /
DGMGRL>show configuration
Configuration  - TEST
Protection Mode : MaxPerformance
Members
TESTPRI - Primary Database
TESTSTD - Physical Standby Database
Fast-Start Failover : DISABLED
Configuration status
SUCCESS( status updated 26 sec ago)
DGMGRL> convert database "TESTSTD" to snapshot standby;
Once this completes Successfully
DGMGRL>show configuration
Configuration  - TEST
Protection Mode : MaxPerformance
Members
TESTPRI - Primary Database
TESTSTD - Snapshot Standby Database
Fast-Start Failover : DISABLED
Configuration status
SUCCESS( status updated 26 sec ago)

How to check if Standby Database converted into Snapshot Standby database

select status, instance_name, database_role,open_mode from v$database, v$instance;

How to convert back to Physical standby mode

Here are the steps to be used in the snapshot standby database if not using DGMGRL.

sqlplus / as sysdba
shutdown immediate
startup mount
alter database convert to physical standby;
alter database recover managed standby database disconnect nodelay;

Here are the steps to be used in the Physical standby database if using DGMGRL.

dgmgrl
DGMGRL>connect /
DGMGRL>show configuration
Configuration  - TEST
Protection Mode : MaxPerformance
Members
TESTPRI - Primary Database
TESTSTD - Snapshot Standby Database
Fast-Start Failover : DISABLED
Configuration status
SUCCESS( status updated 26 sec ago)
DGMGRL> convert database "TESTSTD" to physical standby;
Once this completes Successfully
DGMGRL>show configuration
Configuration  - TEST
Protection Mode : MaxPerformance
Members
TESTPRI - Primary Database
TESTSTD - Physical Standby Database
Fast-Start Failover : DISABLED
Configuration status
SUCCESS( status updated 26 sec ago)

In Oracle, Snapshot Standby databases are an effective tool for organizations seeking a balance between data protection, testing, and development needs. Through careful management and utilization, they can maximize the utility of standby databases while maintaining robust disaster recovery capabilities.

I hope you like this post on Snapshot standby database in Oracle. This is quite a useful feature of Oracle

Related Articles

duplicate target database for standby from active database
physical Standby database
how to recover standby database from a missing archive log