This post applies to Oracle on Linux and provides a bash shell script with bash functions to switch an 11gR2 or higher Data Guard physical standby database to snapshot mode and back to physical mode depending on how it is called.
Prerequisites. An existing standby in physical standby mode that uses an FRA. FRA is required for snapshots. RMAN on the primary side is configured for archivelog retention “shipped to standby” and RMAN archivelog retention on the standby is configured as “applied on standby”.
Primary RMAN configuration for archive deletion policy:
CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;.
Standby RMAN configuration for archive deletion policy:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED TO STANDBY;.
What is missing? Automated purge of standby archivelog (shipped from primary) and archivelog generated while standby is in snapshot mode. Both types of archive are put into the FRA on the standby and as such should be subject to automated purging… but, Oracle’s documentation on the subject is sorely missing and I assume the automated FRA purge on the standby side does not work completely work (especially the snapshot archive purge). I will cover that subject in another post here.
#
# Name : DG-snapshot-toggle.ksh
# Date Written: 20-OCT-2017
# Author : Michael F
#
# Description : Switch DG Standby DB from physical standby to snapshot standby and back to physical
# This bash script supports both mode via the -a (action switch)
#
# Modification History:
#
# When Who Did what
# --------------------------------------------------------------------------
# 20-OCT-2017 MF Created
# --------------------------------------------------------------------------
#
USAGE() {
echo "-a snapshot | physical # snapshot or physical
echo "-l /home/oracle/logs # Log file Location "
echo "-p ggtarget # Primary Database SID"
echo "-u sys # username to use in open RMAN channels "
echo "-s ggstby # Standby ORACLE_SID"
echo "-h $ORACLE_HOME" # the oracle home
echo " DG-snapshot-toggle.ksh -p ggtarget -s ggstby -a tosnapshot -l /home/oracle/logs -u sys "
echo " DG-snapshot-toggle.ksh -p ggtarget -s ggstby -a tophysical -l /home/oracle/logs -u sys "
echo "*****"
}
if [ $# -eq 0 ]; then USAGE; exit; fi
StartTime=`date +"%Y%m%d.%H%M%S"`
while getopts h:a:p:l:s:u: OPTIONS; do
case $OPTIONS in
h) ORA_HOME=$OPTARG;;
a) OPT_ACTION=$OPTARG;;
p) PRIMARY_INSTANCE_NAME=$OPTARG;;
s) STANDBY_INSTANCE_NAME=$OPTARG;;
l) BACKUP_LOGS_DIR=$OPTARG;;
u) USER_NAME=$OPTARG;;
?) USAGE; exit 2;;
esac
done
shift `expr $OPTIND - 1`
echo "==============================================="
echo "Script Name : " $0
echo "==============================================="
################################ ORACLE ENV START ################################
################ ORACLE ENV Error ####################
################ ORACLE ENV FUNCTIONS ################
echo PATH : $PATH
echo ORA_HOME : $ORA_HOME
echo ACTION : $OPT_ACTION
echo PRIMARY_INSTANCE_NAME : $PRIMARY_INSTANCE_NAME
echo STANDBY_INSTANCE_NAME : $STANDBY_INSTANCE_NAME
echo ARCH_LOCATION : $ARCH_LOCATION
echo BACKUP_LOGS_DIR : $BACKUP_LOGS_DIR
echo USER_NAME : $USER_NAME
#export ORACLE_HOME=$ORA_HOME
ORA_ENV_SET() {
export ORACLE_HOME=$ORA_HOME
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
echo 'Function : ORA_ENV_SET'
echo "oracle home is: " $ORACLE_HOME
echo "PATH is: " $PATH
echo "LD_LIBRARY_PATH is: " $LD_LIBRARY_PATH
# cd $BACKUP_LOGS_DIR
STAMP=`date '+%Y-%m-%d_%H%M'`
YEAR=`date '+%Y'`
BKP_START_TIME=`date '+%Y-%m-%d_%H%M'`
echo "$DB Job Start Time: $BKP_START_TIME"
STAMP=`date '+%Y-%m-%d_%H%M'`
DATE=`date '+%m/%d/%Y'`
export NODE=`hostname -s`
ORAENV_ASK=NO; export ORAENV_ASK;
ORACLE_SID=$STANDBY_INSTANCE_NAME ; export ORACLE_SID;
echo "oracle SID is: " $ORACLE_SID
}
############### Get Instance Name ########################
S_INSTANCE_NAME() {
echo 'Function Start : S_INSTANCE_NAME'
msql="select INSTANCE_NAME from v\$instance;"
v_INSTANCE_NAME=`\$ORACLE_HOME/bin/sqlplus -s "/ as sysdba "<< EOF
set feedback off
set linesize 100
whenever sqlerror exit failure;
set lines 150
set pages 0
set head off
set serveroutput on size 10000
$msql
exit;
EOF`
echo 'Function End : S_INSTANCE_NAME'
}
############### Get DB Name ########################
S_DB_NAME() {
echo 'Function Start : S_DB_NAME'
msql="select NAME from v\$database;"
v_S_DB_NAME=`\$ORACLE_HOME/bin/sqlplus -s "/ as sysdba "<< EOF
set feedback off
set linesize 100
whenever sqlerror exit failure;
set lines 150
set pages 0
set head off
set serveroutput on size 10000
$msql
exit;
EOF`
echo 'Function End : S_DB_NAME'
}
##### DB Version ############# ##############
S_DB_VERSION() {
echo 'Function Start : S_DB_VERSION'
#integer v_S_DB_VERSION=0;
msql="select count(*) from v\$version where banner like '%Database%';"
v_S_DB_VERSION=`\$ORACLE_HOME/bin/sqlplus -s "/ as sysdba "<< EOF
set feedback off
set linesize 100
whenever sqlerror exit failure;
set lines 150
set pages 0
set head off
set serveroutput on size 10000
$msql
exit;
EOF`
v_S_DB_VERSION=`expr $v_S_DB_VERSION + 0`
echo $v_S_DB_VERSION
echo 'Function End : S_DB_VERSION'
}
##### Check database ROLE ##############
S_DATABASE_ROLE() {
echo 'Function Start : S_DATABASE_ROLE'
msql="select DATABASE_ROLE from v\$database;"
v_S_DATABASE_ROLE=`\$ORACLE_HOME/bin/sqlplus -s "/ as sysdba " << EOF
set feedback off
set linesize 100
whenever sqlerror exit failure;
set lines 150
set pages 0
set head off
set serveroutput on size 10000
$msql
exit;
EOF`
echo 'Function End : S_DATABASE_ROLE'
}
##### Check number of Archive need to purge ##############
############### GET_INFO #########################
GET_INFO() {
ORA_ENV_SET;
S_INSTANCE_NAME;
S_DB_NAME;
S_DB_VERSION;
S_DATABASE_ROLE;
}
#### Convert the physical to a data guard snapshot - 11gR2 or higher
Convert_phys_to_snap() {
$ORACLE_HOME/bin/sqlplus / as sysdba << EOF
set echo on
set verify on
select db_unique_name, status, open_mode, database_role from v\$database, v\$instance;
alter database recover managed standby database cancel;
shutdown immediate;
startup mount;
alter database convert to snapshot standby;
alter database open;
select db_unique_name, status, open_mode, database_role from v\$database, v\$instance;
exit
EOF
}
#### Convert the snap back to physical
Convert_snap_to_phys() {
$ORACLE_HOME/bin/sqlplus / as sysdba << EOF
select db_unique_name, status, open_mode, database_role from v\$database, v\$instance;
set echo on
set verify on
shutdown immediate;
startup mount;
alter database convert to physical standby;
alter database recover managed standby database using current logfile disconnect;
select db_unique_name, status, open_mode, database_role from v\$database, v\$instance;
exit
EOF
}
## Executing all set and gather scripts
GET_INFO;
if [[ "$v_INSTANCE_NAME" == "$STANDBY_INSTANCE_NAME" ]]
then
if [[ "$OPT_ACTION" == "tosnapshot" ]]
then
if [[ "$v_S_DATABASE_ROLE" == "PHYSICAL STANDBY" ]]
then
echo "converting phys to snap"
Convert_phys_to_snap;
else
echo "Action not taken. Database role must be PHYSICAL STANDBY to convert, current role is: " $v_S_DATABASE_ROLE
fi
elif [[ "$OPT_ACTION" == "tophysical" ]]
then
if [[ "$v_S_DATABASE_ROLE" == "SNAPSHOT STANDBY" ]]
then
echo "converting snapshot to phys"
Convert_snap_to_phys;
else
echo "Action not taken. Database role must be SNAPSHOT STANDBY to convert, current role is: " $v_S_DATABASE_ROLE
fi
else
echo "Invalid Action Option: valid options are tosnapshot or tophyscial"
fi
fi
#################################################################################
Here is an example crontab - your may vary
5,35 8-20 * * * /home/oradb/oscripts/insertJBig.bash >/dev/null 2>&1
15,45 * * * * /home/oradb/oscripts/purge_standby_arch.ksh -p ggtarget -s ggstby -a /oracle/oarch/ggtarget -l /home/oradb/oscripts/log -u sys >> /home/oradb/oscripts/log/purge_standby_arch.log.`date +\%Y\%m` 2>&1 # convert to snapshot daily 7am
0 5 * * * /home/oradb/oscripts/DG-snapshot-toggle.bash -h /oracle/obase/ggtarget/12102 -a tosnapshot -s ggstby -u sys >> /home/oradb/oscripts/log/DG-snapshot-toggle.log
0 23 * * * /home/oradb/oscripts/DG-snapshot-toggle.bash -h /oracle/obase/ggtarget/12102 -a tophysical -s ggstby -u sys >> /home/oradb/oscripts/log/DG-snapshot-toggle.log