Imagine a comprehensive monitoring script that will allow you to customize how many times an alert will be sent. Not only does it tell you the alert but it also informs you the incident number of the alert. Here’s an example of the subject of the message that you will receive:
Tablespace Threshold:DB:WARNING:rac01.dbaexpert.com:gc::Alert#: 5 for Tbsp_Free < 15
And here’s an example of the message detail:
TABLESPACE_NAME %_FREE
—————————— ——
DATA 14.8
SYSAUX 15.4
#!/bin/ksh
######################################################################################################
#
# TITLE……: tbspmon.ksh
# LOCATION…: /apps/oracle/general/sh
# PARAMETERS : PAGER Threshold and Database
#
# USAGE……: tbspmon.ksh $PAGER_THRESHOLD $DB
# OUTPUTS….: List of databases that have tablespaces that are about to run out of space
# DESCRIPTION
# FUNCTION: 1) Runs SQL code that saves info for tablespaces that have less free space than the
# specified have less free space than the specified pager threshold.
# 2) Sends appropriate e-mail and pager notifications, for tablespaces that have less free
# space than the specified e-mail threshold and pager threshold respectively.
# NOTES……: This function was written for the Korn shell and may have
# minor portability problems between different platforms.
# AUTHOR…..:
#
# Modification
#-Date———-Author & Description——————————————————————
#
#
######################################################################################################
export PAGER_THRESHOLD=$1
export DB=$2
FN=`echo $0 | sed s/\.*[/]//`
MAX_NUMBER_ALERTS=5
TMPDIR=/tmp
TBSP_CONFIG=$(echo $0 | sed s/\.*[/]// |awk -F”.” {‘print $1 “.exceptions”‘})
#———————————————
# Parameters from the ${0}.conf file
# And define error and log files
#———————————————
export LOGDIR=${TMPDIR}
export LOGFILE=${TMPDIR}/${DB}_tbsp_free_email_notif.alert
echo $LOGFILE
if [ -f "$LOGFILE" ]; then
rm $LOGFILE
fi
function show_debug_parameters
{
echo “Max Number of Alerts: $MAX_NUMBER_ALERTS”
echo “Log Directory: $LOGDIR”
echo $COUNTER
}
function exam_tablespace_threshold
{
print “TBSP CONFIG: $TBSP_CONFIG”
# — Generate Ignore List of database/tablespaces from being monitored
cat $SH/$TBSP_CONFIG |egrep -v “\^#|grep”| grep $DB |awk -F”:” {‘print $2′} |read IGNORE_LIST
[ "$IGNORE_LIST" = "" ] && export IGNORE_LIST=”‘SOME_BOGUS_TABLESPACE’”
print “IGNORE LIST: $IGNORE_LIST”
#export TNS_ADMIN=/var/opt/oracle/dba
export PW=$(cat $SH/.rodba.pw)
export CONNECT_STRING=rodba/${PW}@${DB}
echo “Connect String: $CONNECT_STRING”
sqlplus -s $CONNECT_STRING 2>&1 <<__ENDSQL
whenever sqlerror exit sql.sqlcode;
set pages 60
set lines 90
set verif off
set trims on
col pct_free for 999.9 hea ‘%_FREE’
define ORA_SID=’$DB’
define PAGE_THRESHOLD=$PAGER_THRESHOLD
define LOG_DIR=’$LOGDIR’
spool $LOGFILE
select tablespace_name, pct_free
from (
SELECT b.tablespace_name, b.tablespace_size_mb,
sum(nvl(fs.bytes,0))/1024/1024 free_size_mb,
(sum(nvl(fs.bytes,0))/1024/1024/b.tablespace_size_mb *100) pct_free
FROM dba_free_space fs,
(SELECT tablespace_name, sum(bytes)/1024/1024 tablespace_size_mb
FROM dba_data_files
GROUP BY tablespace_name
)b
where fs.tablespace_name = b.tablespace_name
and b.tablespace_name NOT IN (SELECT tablespace_name FROM dba_tablespaces WHERE status=’READ ONLY’)
and b.tablespace_name NOT IN (${IGNORE_LIST})
group by b.tablespace_name, b.tablespace_size_mb
) tbsp_pct_free_v
WHERE PCT_FREE < &PAGE_THRESHOLD
ORDER BY pct_free;
spool off
set pages 14
set lines 80
set verif on
__ENDSQL
grep “TABLESPACE_” $LOGFILE
if [ "$?" -eq 0 ]
then
(( EMAIL_COUNTER = ${COUNTER} + 1 ))
# —————————————————————
# Set ALERT_COUNTER to be passed into alert_notification.ksh
# —————————————————————
export ALERT_COUNTER=$EMAIL_COUNTER
print “Sending Alert ${FN}_PG `hostname` $DB Alert#: $EMAIL_COUNTER for Tbsp_Free”
$SH/alert_notification.ksh ${FN}_PG `hostname` $DB “” “Alert#: $EMAIL_COUNTER for Tbsp_Free < $PAGER_THRESHOLD” $LOGFILE
# Increment the counter by one and append it to the LOGFILE filename
(( COUNTER = ${COUNTER} + 1 ))
echo $COUNTER
mv ${LOGFILE} ${LOGFILE}.${COUNTER}
fi
}
#+++++++++++++++++++++++++++++++++++++++++++++++++++++
# MAIN LOGIC
#+++++++++++++++++++++++++++++++++++++++++++++++++++++
#——————————————–
# Count the number of error files (LOGFILE)
# in the /tmp directory
#——————————————–
# if [ -f ${LOGFILE}.* ]; then
export COUNTER=`ls ${LOGFILE}.* |wc -l`
echo “Counted x number of alert files in the $TMPDIR: $COUNTER”
# else
# COUNTER=0
# echo “Counter is set to 0 because there is no alert files”
# fi
show_debug_parameters;
#——————————————————
# If # of error files on disk is greater than
# $MAX_NUMBER_ALERTS, then skip to the next database
#——————————————————
echo “Current Counter: $COUNTER”
echo “Max number before stopping: $MAX_NUMBER_ALERTS”
if [ "${COUNTER}" -ge "$MAX_NUMBER_ALERTS" ]; then
echo “Exceeded Max number of Alerts: $MAX_NUMBER_ALERTS”
exit 1;
else
exam_tablespace_threshold;
fi
Technorati : Tablespace Monitoring Database Administration