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 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.
