Ameer Khan

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