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.