1 2 Previous Next 18 Replies Latest reply: Jul 18, 2013 9:49 AM by rukbat RSS

    who has a check alert log script?

    s9225
      Hi,
      Can anyone provide me some good linux script that will read my alert.log
      file and report any ORA- error through email dailly
      thank you
        • 1. Re: who has a check alert log script?
          427492
          You can prepare an executable shell script via cron to do something like this:

          cat alert.log|grep ORA->oracle_errors.log
          mail email_reciepient oracle_error.log (not sure of the syntax)

          try it out.

          Message was edited by:
          FeNiCrC_Neil
          • 2. Re: who has a check alert log script?
            247514
            I use this script to monitor my instances. I setup cron job to run this every hour.
            5 * * * * /home/oracle/bin/check_alert <SID> 2
            it's ksh
            #!/bin/ksh
            #-----------------------------------------------------------------------
            # PROGRAM       check_alert.ksh
            # FUNCTION      Checks ORACLE Alert logs and pages in case of
            #               any new errors. SID is Oracle database identifier.
            # CALLED BY     cron
            #-----------------------------------------------------------------------

            SID=$1                          # Oracle database identifier
            PAGEMESSAGES=$2                 # Maximum number of new messages that get paged
            PARAM=$#
            TMP=/tmp                        # Temporary directory
            MAILX=/bin/mailx            # UNIX Mail Program
            LIBDIR=/home/oracle/bin    # Directory where useful information is saved
            ALERTDIR=/home/oracle/admin/${SID}/bdump # Directory where
                                                        # Oracle alert file resides
            FILE=alert_${SID}.log           # Oracle alert file name
            MAILDBA=<dba email>                     # DBA email address
            PAGEDBA=<pagermail>         # Page only DBA staff
            PAGEOTHER=NULL                  # Do not page OTHER staff members

            export PAGEMESSAGES
            export PARAM
            export TMP
            export MAILX
            export LIBDIR
            export ALERTDIR
            export FILE
            export SID
            export MAILDBA
            export PAGEDBA
            export PAGEOTHER

            checkParameters()
            {
               if [ $PARAM -ne 2 ]
               then
                  echo "**USAGE** : $0 <SID> <Count>"
                  exit 1
               fi

            }

            LASTCOUNT=`cat $LIBDIR/.oraErrCount_${SID}` # Count of ORA- errors
                                                        # detected during last program run
            export LASTCOUNT

            sendAlertMessage()

            {
                  MESSAGE="**ALARM**:${SID}:`grep "ORA-" $ALERTDIR/$FILE | tail  -${count} | head -1`"
                  echo $MESSAGE | $MAILX ${PAGEDBA}
                  echo $MESSAGE | $MAILX -s"`uname -n`:${SID}:ORACLE Trace file Alert" ${MAILDBA}
                  echo "$NAME:$MESSAGE:`date`"

            }


            probeAlertLog()

            {
               #set -x
               # Count all Oracle errors - search for string "ORA-"
               CheckError=`grep "ORA-" $ALERTDIR/$FILE | wc -l`

               # keep a count of current errors present in the Alert file
               echo $CheckError > $LIBDIR/.oraErrCount_${SID}

               count=1
               # If new errors are detected (same alert log)
               if [ $CheckError -gt $LASTCOUNT ]
               then
                  while [ $LASTCOUNT -lt $CheckError ]
                  do
                     sendAlertMessage;
                     if [ $count -eq $PAGEMESSAGES ]
                     then
                        break;
                     fi
                     ((count=$count+1))
                     ((LASTCOUNT=$LASTCOUNT+1))
                  done
               else
                  # Looks like alert log file has been switched!
                  if [ $CheckError -lt $LASTCOUNT ]
                  then
                     while [ $count -le $CheckError ]
                     do
                        sendAlertMessage;
                        if [ $count -eq $PAGEMESSAGES ]
                        then
                           break;
                        fi
                        ((count=$count+1))
                     done
                  fi
               fi

            }

            checkParameters;
            probeAlertLog;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
            • 3. Re: who has a check alert log script?
              Jaffy
              #! /usr/bin/bash -xv
              #
              # @(#) Filename: chk_alertlog
              #
              #
              # USAGE
              # chk_alertlog
              # DESCRIPTION
              # Script checks for oracle errors in the alert log.
              # New errors are reported.
              ##set -xv ## uncomment to debug
              # Setup environment
              #
              #.
              #

              ALERTDIR="" # location of alert log
              ALERTLOG="" # fully qualified path for alert log
              LASTNEWERR="" # current error found in alert log
              LASTERR=""
              ERRLINE=0 # line number of the current error

              # ALERTDIR=`grep background_dump_dest $ORACLE_HOME/dbs/init$ORACLE_SID.ora | awk -F= '{print $2}'`
              ALERTDIR= "Your Alert dir dest"
              ALERTDIR=`echo $ALERTDIR | sed 's/^ //'` # strip off leading spaces
              ALERTDIR=`echo $ALERTDIR | sed 's/^ //'` # strip off leading tabs
              ALERTLOG=$ALERTDIR/alert_$ORACLE_SID.log
              LASTERR=$ALERTDIR/alert_$ORACLE_SID.last # file where last alert log

              # Look for last new error
              #
              LASTNEWERR=`egrep -n "DBA-|ORA-" $ALERTLOG | tail -1`

              # If error found, save error information
              # Exit if no errors were found
              #
              LAST=$LASTNEWERR

              if [ `echo $LASTNEWERR | wc -w` -eq 0 ]
              #if [ ${LAST:-1} -eq 0 ]
              then
              exit 0
              fi

              # Determine whether this error was already identified
              #

              if [ -s $LASTERR ]
              then
              LASTERRLINE=` cat $LASTERR | awk -F: '{print $1}'`
              if [ "`echo $LASTNEWERR`" != "`cat $LASTERR`" ]
              then
              # This is a new error, replace last error found with this one
              #
              echo $LASTNEWERR > $LASTERR
              else
              # Already identified, nothing new to report
              #
              exit 0
              fi
              else
              # No previous error to look at, save this error
              #
              LASTERRLINE=0
              echo $LASTNEWERR > $LASTERR
              fi

              # Now create a file with all new error #s
              CURRERRFILE=$ALERTDIR/$ORACLE_SID.currerrs
              sed "1,${LASTERRLINE}d" $ALERTLOG | egrep "^DBA-|^ORA-" > $CURRERRFILE

              cat $CURRERRFILE|mailx -s "Database Errors-Check Alert Log-`date`" Your email id.
              exit 0
              • 4. Re: who has a check alert log script?
                s9225
                kuan, thank you very much.
                • 5. Re: who has a check alert log script?
                  s9225
                  jafar, thank you very much.
                  • 6. Re: who has a check alert log script?
                    Oviwan
                    Check this script:
                    http://oraalertcheck.blogspot.com/

                    Regards
                    • 7. Re: who has a check alert log script?
                      917386
                      Thanks Jaffar... Currenctly I'm using the same script for my dev boxes, it's really good. I have a small question, sometimes we are getting the empty emails to my mail box. Could you please suggest me If I need any changes. Thank you!
                      • 8. Re: who has a check alert log script?
                        EdStevens
                        914383 wrote:
                        Thanks Jaffar... Currenctly I'm using the same script for my dev boxes, it's really good. I have a small question, sometimes we are getting the empty emails to my mail box. Could you please suggest me If I need any changes. Thank you!
                        Did you notice that you just dredged up a 5+ year old thread?

                        All these scripts aside, dbcontrol and/or grid control can handle that for you.
                        • 9. Re: who has a check alert log script?
                          jgarry
                          I wonder if you get that when you rotate logs?

                          @Ed: I'm not so sure it is wrong to dredge up an old thread, to ask about something only in that thread. May be silly, or may be legitimate. Complaining about it may set off a hair-trigger moderator to lock too quickly.
                          • 10. Re: who has a check alert log script?
                            EdStevens
                            jgarry wrote:
                            I wonder if you get that when you rotate logs?

                            @Ed: I'm not so sure it is wrong to dredge up an old thread, to ask about something only in that thread. May be silly, or may be legitimate. Complaining about it may set off a hair-trigger moderator to lock too quickly.
                            Point taken.
                            • 11. Re: who has a check alert log script?
                              917386
                              Thanks for this script... I need a small change with this script I need to print the above two lines of the script. Could you please let me know the changes that I need to do with same script. Thanks!
                              • 12. Re: who has a check alert log script?
                                jgarry
                                What "above two lines?"
                                • 13. Re: who has a check alert log script?
                                  yxes2013

                                  Hi all,

                                   

                                  Is this script being scheduled every hour in the CRONTAB?

                                   

                                  Thanks

                                  • 14. Re: who has a check alert log script?
                                    sb92075

                                    yxes2013 wrote:

                                     

                                    Hi all,

                                     

                                    Is this script being scheduled every hour in the CRONTAB?

                                     

                                    Thanks

                                    post your crontab entry.

                                    1 2 Previous Next