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, the 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. This is done even if the flashback is not enabled in the 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 the 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