Re: Freebie: read/grep alert log from sqlplus

From: rob <rob_at_dsdelft.nl>
Date: Tue, 28 May 2002 10:35:45 +0200
Message-ID: <acvfh1$gkq$1@news.tudelft.nl>

And here is a korn shell version :-)
Rob.

#!/bin/ksh
. $HOME/.profile

CHECKLOGS () {
   # get all databases from /etc/oratab
   cat /etc/oratab | grep -v "#" |grep -v ^$ | awk -F":" '{ print $1}' |    while read SID
   do

      if [ ! -s ${ORACLE_BASE}/admin/${SID}/bdump/alert_${SID}.log ]
      then


         # if alert.log doesn't exist create empty alert_sid.new

         cat /dev/null >  /tmp/alert.${SID}.new
      else


         # if alert.log does exist create alert_sid.new with the errors

         cat ${ORACLE_BASE}/admin/${SID}/bdump/alert_${SID}.log |
         while read ALERTREGEL
         do
            case ${ALERTREGEL} in
               ORA-*)
                  echo "+---------------------------------+"
                  echo "| Date : ${PREVREGEL} |"
                  echo "+---------------------------------+"
                  echo ${ALERTREGEL}
                  oerr ora `echo ${ALERTREGEL} | awk -F":" '{print
$1}'|awk -F"-" '{print $2}'`;;
               Sat*|Sun*|Mon*|Tue*|Wed*|Thu*|Fri*)
                  PREVREGEL=${ALERTREGEL};;
            esac
         done > /tmp/alert.${SID}.new
      fi

   done
}

MAILLOGS () {
   # get all databases from /etc/oratab
   cat /etc/oratab | grep -v "#" |grep -v ^$ | awk -F":" '{ print $1}' |    while read SID
   do

      if [ ! -f /tmp/alert.${SID}.old ]
      then


         # if alert.sid.old doesn't exist create empty one

  cat /dev/null > /tmp/alert.${SID}.old
      fi

      diff /tmp/alert.${SID}.new /tmp/alert.${SID}.old > /dev/null
      if [ ! $? = 0 -a -s /tmp/alert.${SID}.new ]
      then

  # if alert.sid.new is not empty and is different from alert.sid.old send mail

         cat /tmp/alert.${SID}.new | mailx -s "Errors in alert_${SID}.log on `uname -n` " you_at_yourcompany.com

      fi
      # copy alert.sid.new to alert.sid.old
      mv /tmp/alert.${SID}.new /tmp/alert.${SID}.old

      if [ -f ${ORACLE_BASE}/admin/${SID}/bdump/alert_${SID}.log ]
      then

  ALERTSIZE=`ls -al ${ORACLE_BASE}/admin/${SID}/bdump/alert_${SID}.log | awk -F" " '{print $5}'`

         if [ ${ALERTSIZE:=0} -gt 1000000 ]
         then
            echo "Please clear alert.log" | mailx -s "alert_${SID}.log on
`uname -n` is greater than 1MB" you_at_yourcompany.com
         fi
      fi

   done
}

#Main
CHECKLOGS
MAILLOGS Received on Tue May 28 2002 - 03:35:45 CDT