Tuesday, January 21, 2014

ksh: script that will send alert when flash recovery are is filling up

#!/usr/bin/ksh
# Vegard Kasa
# 11.07.2013
#
# Alert when FRA is filling up...
#############################################################################################################
export NOW=`date +\%d.\%m.\%y`
export NLS_DATE_FORMAT='DD.MM.YYYY'
####################################################################
# Set the script base to either $DBA_ADMIN in case of original build
# or $DBA_ADMIN_HOME in case of newer build.
####################################################################
if [ -n "${DBA_ADMIN}" ]; then
 ##############
 # Original build
 ##############
 SCRIPT_BASE=$DBA_ADMIN
elif [ -n "${DBA_ADMIN_HOME}" ]; then
 #################
 # Another build
 # uses different
 # variable names
 #################
 SCRIPT_BASE=${DBA_ADMIN_HOME}
fi


cat << EoF > ${SCRIPT_BASE}/sql/get_sum_fra.sql
set termout   off
set trimspool on
set verify    off
set feedback  off
set echo      off
set linesize  200
set heading   off
set pagesize  0
spool ${SCRIPT_BASE}/log/alert_fra_full_${NOW}.log
SELECT    LTRIM(sum(PERCENT_SPACE_USED))
FROM      V\$FLASH_RECOVERY_AREA_USAGE;
exit
EoF
sqlplus -s / as sysdba @${SCRIPT_BASE}/sql/get_sum_fra.sql
##########################################################
# Send the output via e-mail to the designated receipients
##########################################################
PRC=`cat ${SCRIPT_BASE}/log/alert_fra_full_${NOW}.log`
if [ ${PRC} -gt 80 ]; then
 echo "Warning: In database ${ORACLE_SID}, the FRA is ${PRC} percent used!"  |mailx -r monitoring@yourdomain.com -s "FRA usage report for database ${ORACLE_SID}" dba@yourdomain.com
fi
exit

No comments:

Post a Comment