See my previous post on how to toggle a Data Guard snapshot standby between snapshot and physical recovery here.
It is a prerequisite to use the FRA if you are doing Oracle Data Guard Snapshots (available 11g and forward). The FRA handles some of the purging, but it is not well documented by Oracle what is being done. Just in case, I scripted a korn shell script and schedule in the crontab to purge.
Another assumption here is you are not backing up the standby. In this case we don’t need to.
Setup…
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;.
Here the crontab to execute a purge – run from the oracle account on the standby side.
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
Korn shell – easily convert to bash on your dime…
#!/bin/ksh -x
#
# Name : purge_standby_arch.ksh
# Date Written: 03/2016 mf
#
# Description : Purge Archive logs after apply on Standby DB
#
# Modification History:
#
# delete expired and cleaned up some inaccurate comments
# ————————————————————————–
#
USAGE() {
echo “-a /oracle/oarch/ggtarget # Archive log file Location “
echo “-l /oracle/log/ggtarget # Log file Location “
echo “-p ggtarget Primary Database “
echo “-u sys # username to use in open RMAN channels “
echo “-s ggstby # Standby Database ”
echo ” purge_standby_arch.ksh -p ggtarget -s ggstby -a /oracle/oarch/ggtarget -l /oracle/log/ggtarget -u sys “
echo “#################################################################################################”
}
export ORACLE_SID=ggstby
if [ $# -eq 0 ]; then USAGE; exit; fi
StartTime=`date +”%Y%m%d.%H%M%S”`
while getopts a:p:l:s:u:x OPTIONS; do
case $OPTIONS in
a) ARCH_LOCATION=$OPTARG;;
p) PRIMARY_INSTANCE_NAME=$OPTARG;;
s) STANDBY_INSTANCE_NAME=$OPTARG;;
l) BACKUP_LOGS_DIR=$OPTARG;;
u) USER_NAME=$OPTARG;;
x) DUMMY=Y;;
?) 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 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
ORA_ENV_SET() {
echo ‘Function : ORA_ENV_SET’
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 Backup 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;
S_BDBNAME=”$STANDBY_INSTANCE_NAME”
BSPOOL=/tmp/rman_hotbackup.$$
TEMPBKMAIL=/tmp/rman_hotbackupbkmail.$$
if [ -f /oscripts/mydba/scripts/common/mydba_scripts.conf ]
then
. /oscripts/mydba/scripts/common/mydba_scripts.conf
fi
if [ -f ${MYDBA_TOOLS}/${S_BDBNAME}/${S_BDBNAME}.env ]
then
. ${MYDBA_TOOLS}/${S_BDBNAME}/${S_BDBNAME}.env
. ${MYDBA_TOOLS}/${S_BDBNAME}/${S_BDBNAME}_pwd.env
fi
if [ -f /usr/local/bin/dbhome ]
then
export ORACLE_HOME=`/usr/local/bin/dbhome $ORACLE_SID`
export PATH=$ORACLE_HOME/bin:$PATH
fi
}
############### 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’
}
############### purge expired archive log from FRA ########################
S_PURGE_EXP_ARC_FRA() {
echo ‘Function Start : S_PURGE_EXP_ARC_FRA’
cd $BACKUP_LOGS_DIR
echo ‘Clearing control file of expired archive, current dir is:’+${PWD}
v_rman_purge=`\$ORACLE_HOME/bin/rman target “/ log rman_expd_arc_purge.log “<< EOF
crosscheck archivelog all;
delete noprompt expired archivelog all;
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 ‘%9i%’;”
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 standby Archive need to purge ##############
S_ARCHIVE_COUNT() {
echo ‘Function Start : S_ARCHIVE_COUNT’
msql=”select count(*) from gv\$archived_log where applied = ‘YES’ and REGISTRAR=’RFS’ and trunc(COMPLETION_TIME) > trunc(sysdate)-2 and exists (select DATABASE_ROLE from v\$database where DATABASE_ROLE=’PHYSICAL STANDBY’ and OPEN_MODE=’MOUNTED’ and SWITCHOVER_STATUS in(‘NOT ALLOWED’,’SESSIONS ACTIVE’) ) order by COMPLETION_TIME asc;”
v_S_ARCHIVE_COUNT=`\$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_ARCHIVE_COUNT=`expr $v_S_ARCHIVE_COUNT + 0`
#
echo ‘Function End : S_ARCHIVE_COUNT’
}
##### purge standby archivelog files already applied ##############
S_PURGE_ARCHIVE() {
############
echo ‘Function Start : S_PURGE_ARCHIVE’
msql=”select name from gv\$archived_log where applied = ‘YES’ and REGISTRAR=’RFS’ and trunc(COMPLETION_TIME) > trunc(sysdate)-2 and exists (select DATABASE_ROLE from v\$database where DATABASE_ROLE=’PHYSICAL STANDBY’ and OPEN_MODE=’MOUNTED’ and SWITCHOVER_STATUS in(‘NOT ALLOWED’,’SESSIONS ACTIVE’) ) order by COMPLETION_TIME asc;”
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`
cd $ARCH_LOCATION
if [[ `pwd` == “$ARCH_LOCATION” ]]
then
for i in ${v_S_DATABASE_ROLE}; do
rm -f $i
done
fi
echo ‘Function End : S_PURGE_ARCHIVE’
}
##### purge archivelog created while db is a snapshot standby / or open for read write ##############
S_PURGE_SNAPSHOT_ARCHIVE() {
############
echo ‘Function Start : S_PURGE_SNAPSHOT_ARCHIVE’
msql=”select name from gv\$archived_log where REGISTRAR=’ARCH’ and COMPLETION_TIME < sysdate-1 order by COMPLETION_TIME asc;”
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`
cd $ARCH_LOCATION
if [[ `pwd` == “$ARCH_LOCATION” ]]
then
for i in ${v_S_DATABASE_ROLE}; do
rm -f $i
done
fi
echo ‘Function End : S_PURGE_SNAPSHOT_ARCHIVE’
}
############### GET_INFO #########################
GET_INFO() {
ORA_ENV_SET;
S_INSTANCE_NAME;
S_DB_NAME;
S_DB_VERSION;
S_DATABASE_ROLE;
S_ARCHIVE_COUNT;
}
## Executing all set and gather scripts
GET_INFO;
if [[ “$v_INSTANCE_NAME” == “$STANDBY_INSTANCE_NAME” ]]
then
if [[ “$v_S_DATABASE_ROLE” == “PHYSICAL STANDBY” ]]
then
if [[ -d $ARCH_LOCATION ]]
then
if [[ $v_S_ARCHIVE_COUNT -gt 0 ]]
then
S_PURGE_ARCHIVE;
fi
fi
fi
# purge old archivelogs generated during snapshot (OS file delete)
# no matter what mode we are in purge – delete the old snapshot archivelogs older than 1 day
S_PURGE_SNAPSHOT_ARCHIVE;
# just crosscheck archivelog all and delete noprompt expired archivelog from control file
S_PURGE_EXP_ARC_FRA;
fi
################ BACKUP_TYPE CASES ################
#BACKUP_TYPE=$(echo $BACKUP_TYPE | tr “[a-z]” “[A-Z]”)
#case $BACKUP_TYPE in
#FULL) DELE;BKUP;BACKUP_MISC;;
#ARCH) BKUP;;
#INC) DELE;BKUP;;
#CUM) DELE;BKUP;;
#DIFF) DELE;BKUP;;
#DATAPUMP) DELE_LOGICAL;LOGICAL_BACKUP;;
#*) ERR_BACKUP_TYPE;;
#esac
#################################################################################