Shell script to convert physical standby database into snapshot standby and vice versa

 

Today I will show a scenario where a customer had a requirement to refresh their snapshot standby database. The setup was 1 primary database and 2 physical standby databases among which one standby database was always kept in physical standby mode but the other one was used by the developer and a daily refresh was the requirement.

Since they wanted to convert snapshot standby into physical standby for sync and later convert it back to snapshot standby I prepared two shell script to perform the entire activity.

 

·       Shell script to convert physical standby database into snapshot standby database

#!/bin/bash  # Set environment variables export ORACLE_HOME=/u01/oracle/19.3.0 export ORACLE_SID=PROD export PATH=$ORACLE_HOME/bin:$PATH   # Define database names PRIMARY_DB_NAME=PROD STANDBY_DB_NAME=SBDB   # Define log file path rm $LOG_FILE LOG_FILE=/u01/snapshot_convert_logfile.log   # Define maximum number of retries MAX_RETRIES=2   # Function to log messages to log file log() {   echo "$(date +'%Y-%m-%d %H:%M:%S') $1" >> $LOG_FILE }   # Function to perform conversion perform_conversion() {   log "Performing conversion of $STANDBY_DB_NAME to snapshot standby..."   dgmgrl <<EOF   connect /   convert database $STANDBY_DB_NAME to snapshot standby; EOF } >> $LOG_FILE # Retry loop retries=0 while [ $retries -lt $MAX_RETRIES ]; do   perform_conversion   if grep -q "ORA-" $LOG_FILE; then     log "Error occurred during conversion. Retrying..."     ((retries++))     sleep 5   else     log "Conversion completed successfully. Standby database is now a snapshot standby."     break   fi done   # Check for maximum retries if [ $retries -eq $MAX_RETRIES ]; then   log "Maximum retries reached. Conversion failed. Please check the log for details." fi

 

 

·       Shell script to convert snapshot standby database into physical standby database

#!/bin/bash   # Set environment variables export ORACLE_HOME=/u01/oracle/19.3.0 export ORACLE_SID=PROD export PATH=$ORACLE_HOME/bin:$PATH   # Define database names PRIMARY_DB_NAME=PROD STANDBY_DB_NAME=SBDB   # Define log file path rm $LOG_FILE LOG_FILE=/u01/physical_convert_logfile.log   # Define maximum number of retries MAX_RETRIES=2   # Function to log messages to log file log() {   echo "$(date +'%Y-%m-%d %H:%M:%S') $1" >> $LOG_FILE }   # Function to perform conversion perform_conversion() {   log "Performing conversion of $STANDBY_DB_NAME to physical standby..." dgmgrl sys/Prodpassword@prod<<EOF convert database $STANDBY_DB_NAME to physical standby; EOF } >> $LOG_FILE # Retry loop retries=0 while [ $retries -lt $MAX_RETRIES ]; do   perform_conversion   if grep -q "ORA-" $LOG_FILE; then     log "Error occurred during conversion. Retrying..."     ((retries++))     sleep 5   else     log "Conversion completed successfully. Standby database is now a physical standby."     break   fi done   # Check for maximum retries if [ $retries -eq $MAX_RETRIES ]; then   log "Maximum retries reached. Conversion failed. Please check the log for details." fi

 

Once the snapshot standby is converted to physical standby, give it time to synchronize with primary database.

What is Standby snapshot Database?

What is Standby snapshot Database?
Oracle provides an unique feature where the physical standby database can be opened in READ WRITE mode to perform update able transactions.
A snapshot standby database is a fully updatable standby database that is created by converting a physical standby database into a snapshot standby database. A snapshot standby database
receives and archives, but does not apply redo data from a primary database. 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.

A snapshot standby database typically diverges from its primary database over time because redo data from the primary database is not applied as it is received. Local updates to the
snapshot standby database will cause additional divergence. The data in the primary database is fully protected however, because a snapshot standby can be converted back into a
physical standby database at any time, and the redo data received from the primary will then be applied.

A snapshot standby database will allow you to make use of the data available on the physical standby database (which is the same data of the primary database), which allows the users
to test the application on a standby database which has the primary database's data before implementing it into production environment. Whenever a physical standby database is converted
into a snapshot standby database, a guaranteed restore point is automatically created. Once when the updateable transactions are completed for testing purposes on the snapshot standby
database and when you are converting back the snapshot standby to physical standby, oracle flashbacks to the restore point that was created earlier which means the transactions
which were made in standby database while it was open in READ WRITE mode will be flushed out.

The only requirement to have the snapshot standby is that FRA (Flash Recovery Area) must be configured on physical standby database. It is not necessary to have flashback enabled.