Oracle Alert log Monitoring Script

Purpose:

This script can be used to check any Oracle error ("ORA-") in alertlog files.
This script can be configures as cronjob, so that it will scan the alert log at particular interval. When the script runs, it remembers the line number of each alertlog, this way when it runs next time it starts from where it left off.


Step (1)
Prerequisite

#
#
mkdir -p /opt/oracle/scripts/dba/monitoring/logs


# To remember the line number of the last scan,
# each alertlog will have a separate registry file under loglines directory
mkdir -p /opt/oracle/scripts/dba/monitoring/loglines

create database list file ("/u11/misc/ActiveDBs.list") to loop through all the DB instances.


(i)Copy the following .cronprofile and alert_check.sh into /opt/oracle/scripts/dba/monitoring directory.
(ii) Change environment variables, Pager (PAGE_TO) and email (EMAIL_TO) address.

Step (2)
$ cat .cronprofile
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/9.2.0
ORACLE_SID=my_sid
NLS_LANG=AMERICAN_AMERICA.UTF8
PATH=./:${ORACLE_HOME}/bin:/usr/ccs/bin:/usr/bin:/usr/sbin:/usr/openwin/bin:/usr/local/bin:${HOME}:/usr/ucb:/sbin:/usr/lib/vxvm/bin:/usr/lib/fs/vxfs:/opt/VRTSvxfs/sbin:/opt/VRTSvcs/bin:/opt/VRTSvcs/ops/bin:/opt/VRTSob/bin:/opt/VRTS/bin
MANPATH=$MANPATH:./:/usr/opt/SUNWmd/man
SERVER=`uname -n`
LD_LIBRARY_PATH=${ORACLE_HOME}/lib32:${ORACLE_HOME}/lib64:${ORACLE_HOME}/lib:${ORACLE_HOME}/jdbc/lib:/usr/openwin/lib:/usr/dt/lib:${ORACLE_HOME}/ctx/lib
#LD_LIBRARY_PATH=${ORACLE_HOME}/lib32:${ORACLE_HOME}/lib64:${ORACLE_HOME}/lib:${ORACLE_HOME}/jdbc/lib:/usr/openwin/lib:/usr/dt/lib:${ORACLE_HOME}/ctx/lib:/opt/ORCLcluster/lib

LD_LIBRARY_PATH_64=${ORACLE_HOME}/lib:$LD_LIBRARY_PATH
ORA_OEMAGENT_DIR=/opt/oracle/agent
TCL_LIBRARY=${ORACLE_HOME}/network/agent/tcl
export NLS_LANG ORACLE_BASE ORACLE_HOME PATH MANPATH SERVER LD_LIBRARY_PATH LD_LIBRARY_PATH_64 ORA_OEMAGENT_DIR TCL_LIBRARY

umask 022

export SCRIPT_DIR=/opt/oracle/scripts/dba/monitoring
export PAGETO="1234567890@vtext.com"
export MAILTO="first_name.last_name@your_company.com"
export TEMP_DIR=/tmp
export LOG_DIR=/opt/oracle/scripts/dba/monitoring/logs
export LOGTO=${LOG_DIR}/alertlog_check.log


Step (3)
$ cat alert_check.sh
#!/usr/bin/ksh -x
. /opt/oracle/scripts/dba/monitoring/.cronprofile
for SID in $(cat /u11/misc/ActiveDBs.list)
do
#
# In RAC environment, ORACLE_SID=DB_NAME+INSTANCE_NUMBER.
# Here 1 indicates the instance number. For 2nd node it will be 2 and so on.
#
export ORACLE_SID=$SID'1'
export OSID=$ORACLE_SID
export ALERT_DIR=/opt/oracle/admin/${SID}/bdump
export ALERTFILE=${ALERT_DIR}/alert_${ORACLE_SID}.log
ADMIN2=""
PAGEFREQ=5
COUNT=0
cat $ALERTFILE >/tmp/alertlog_${OSID}.out
LOGFILE="/tmp/alertlog_${OSID}.out"
touch ${SCRIPT_DIR}/loglines/loglines.${ORACLE_SID}
LINECOUNT=`cat ${SCRIPT_DIR}/loglines/loglines.${ORACLE_SID}`
LINECOUNT=`expr $LINECOUNT + 1`
tail +$LINECOUNT $LOGFILE > ${SCRIPT_DIR}/tailfile
wc -l $LOGFILE | tr -s ' ' ' ' | cut -d' ' -f2 >${SCRIPT_DIR}/loglines/loglines.${ORACLE_SID}
GROUP=UNIX
ADMIN=$PAGETO

NUM=`date +'%S'`

SERVER=`uname -n`
ALERT=`egrep -i 'ORA-' ${SCRIPT_DIR}/tailfile`
if test -n "$ALERT"
then
egrep 'ORA-' ${SCRIPT_DIR}/tailfile | uniq >${SCRIPT_DIR}/mailfile
MFILE=${SCRIPT_DIR}/mailfile
if [[ -s ${SCRIPT_DIR}/alertlog_check.cnt ]]
then
COUNT=`cat ${SCRIPT_DIR}/alertlog_check.cnt`
COUNT=`expr $COUNT + 1`
MODVAL=`expr $COUNT % $PAGEFREQ`
if [ $MODVAL -eq 0 -o $COUNT -eq 2 -o $COUNT -eq 3 ]
then
MESG="Oracle alert on ${SERVER}:$OSID at `date +'%a
%m/%d %H:%M'`"
echo $MESG >> $LOGTO
# echo "MSG:" >${SCRIPT_DIR}/pagemsg.$NUM
# echo "TO: $GROUP" >>${SCRIPT_DIR}/pagemsg.$NUM
# echo "CONTENTS: ${SERVER}:$OSID `date +'%a %m/%d %H:%M'` $MESG" >>${SCRIPT_DIR}/pagemsg.$NUM
fi
echo $COUNT >${SCRIPT_DIR}/alertlog_check.cnt
else
COUNT=1
echo $COUNT >${SCRIPT_DIR}/alertlog_check.cnt
MESG="Oracle alert on ${SERVER}:$OSID at `date +'%a %m/%d %H:%M'`"
echo $MESG >> $LOGTO
# echo "MSG:" >${SCRIPT_DIR}/pagemsg.$NUM
# echo "TO: $GROUP" >>${SCRIPT_DIR}/pagemsg.$NUM
# echo "CONTENTS: ${SERVER}:$OSID `date +'%a %m/%d %H:%M'` $MESG" >>${SCRIPT_DIR}/pagemsg.$NUM
mailx -s "$MESG" $ADMIN <$MFILE
fi
else
if [[ -s ${SCRIPT_DIR}/alertlog_check.cnt ]]
then
rm ${SCRIPT_DIR}/alertlog_check.cnt
#/bin/rm pagemsg.*
fi
echo ""
fi
done