by http://webgeektutorials.blogspot.com

Monday, May 14, 2012

Oracle DBA Script : Check error message in alert log

This Script search for Oracle error messages in last 100 lines in the alert log file , send a email message to concerned and keep log to a file.
You should pass name of ORACLE_SID as a parameter.
For eg: Ck_alerlog FINL


Code:
#!/usr/bin/sh
# Script Type: Shell (Bourne)
#
# Script name: ck_alertlog.sh
# Comments: Script checks last 100 lines of
# the alert log for specific
# Oracle errors, then pages or e-mails depending #on the error.
# Parameter: ORACLE_SID
# -------------------
# Revision Log
#
# 00/00/00 : Name of modifier - description
#of Modifications
#
#--------------------------------------------------
#-------------------------
SID=$1
DIR=/usr/local/bin/dbabin
ORACLE_SID=$SID; export ORACLE_SID
ORACLE_HOME=`grep -v "^[#]" /var/opt/oracle/oratab|grep $ORACLE_SID|cut -d: -f2
export ORACLE_HOME
cd $ORACLE_HOME/../..

ALERT_DEST=`pwd`
ALERT_DEST=$ALERT_DEST/admin/$SID/bdump
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
COLLECTOR=`tail -100 $ALERT_DEST/alert_$SID.log |egrep "ORA-255 |ORA-255:|ORA-0255|ORA-214|ORA-214:|ORA-00214|ORA-270|ORA-270:|ORA-00270|ORA-272|ORA-272:|ORA-00272|ORA-600|ORA-600:|ORA-00600|ORA-1122|ORA-1122:|ORA-01122|ORA-1578|ORA1578:|ORA-01578|ORA-1628|ORA-1628:|ORA-01628|ORA-1630|ORA-1630:|ORA-01630|ORA1631|ORA-1631:|ORA-01631|ORA-1632|ORA-1632:|ORA-01632|ORA-1650|ORA-1650:|ORA01650|ORA-1652|ORA-1652:|ORA-01652|ORA-1653|ORA-1653:|ORA-01653|ORA-1654|ORA1654:|ORA-01654|ORA-1655|ORA-1655:|ORA-01655"`
for i in $COLLECTOR
do

ORAERR=`echo $i | grep ORA-...`
if [ $ORAERR ] then
i=`echo $i | awk '
{
print $1
}'`
y=`cat $DIR/tmpalert$SID.log|egrep -ch$i`
if [ $y -ge 2 ] then
continue
else
echo $i Paged at `date '+%m/%d/%y %H:%M'` >> $DIR/tmpalert$SID.log
echo "Oracle errors in alert log on $SID" | /usr/bin/mailx -s "check aler_$SID.log" xxx@myorg.com;
fi;
fi;
--Done



Thanks to Thomas Kuruvilla for this share.

No comments:

Post a Comment