oracle alert log script to check for errors

Posted on 2014-04-07
Last Modified: 2014-04-07
I have several databases on several machines
unix - aix - oracle 10 & 11
I need a script to run in cron that will check each alert log for oracle errors and email me if there are any errors, what they are and what database/system they come from
Question by:bkreynolds48
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 39983527
Check burleson site, this has the script which you are looking for
LVL 23

Expert Comment

ID: 39983593
bk, I read into your requirements that you want one script, on one host, to query multiple systems.  As Burleson mentions, you need to be looking at Oracle Grid Control if that's accurate.  One overall job scheduling / system monitoring tool, with agents on each managed host.
LVL 22

Expert Comment

by:Steve Wales
ID: 39983600
Alternatively, you could set up your alert log as an external table and query it from each instance.  Excellent example of that here:

You could then schedule a job to query that table and send you output periodically if anything new appears that you aren't specifically excluding, and if you get new messages that you decide are OK, you can go back to the scripting and exclude them as well.
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users


Author Comment

ID: 39983766

I looked at his script - I have one pretty much the same
so my only option for multiple databases - lets just say on one system - is to duplicate the script for each of the databases I have?  there does not appear to be any loop to grap the database names from the oratab file

Steve Wales - I can't make that kind of change on production systems.
LVL 22

Accepted Solution

Steve Wales earned 500 total points
ID: 39983800
Here's a script I wrote ages ago that would do that.

#set -x
# Description:
# This script examines Oracle alert logs for each instance specified in a
# server's /etc/oratab file and reports on errors.  This script can run
# at whatever frequency the DBA desires, setup in crontab.  In conjunction
# with this script, if you have a script that ages alert logs, at the time 
# of the alert log aging, the temporary file that keeps track of where you
# where in the file needs to have it's ling counter reset to 0.
# Usage:
# Revision History
# 02-24-2005 
# ............ Initial Coding
# 01-10-2006 
# ............ Added ^M at the end of each line to prevent line wrap

# Define a function for processing any errors that have been reported
# so that it doesn't clutter up the errors a little further down the 
# script


# Define useful variables

LAST=`cat $FILE`

# Create an empty output file to email to DBA's after processing is 
# done if there are any recent errors.


# Start processing the errorfile.

cat $ERRORS | while read ERR
  ERRLIN=`echo $ERR | cut -d: -f1`

  if [ $ERRLIN -gt $LAST ]
    echo $ERR >> $OUTFILE
    echo ^M >> $OUTFILE   #This is a Control-M character

# If $OUTFILE actually ended up having anything written to it, we need
# to email it to the DBA's

if [ -s "$OUTFILE" ]

# Create an empty temporary file

  > /tmp/$$.1
  echo "Database $ORACLE_SID on Host $HOSTNAME has reported the " >> /tmp/$$.1
  echo "following errors, please review ASAP" >> /tmp/$$.1
  echo " " >> /tmp/$$.1

  cp $OUTFILE /tmp/$$.2

  cat /tmp/$$.1 /tmp/$$.2 > $OUTFILE

  cat $OUTFILE | mailx -s "Errors detected in Alert Log for $ORACLE_SID" $MAILLIST

# Cleanup temp files before exit.

rm $OUTFILE > /dev/null
rm /tmp/$$.1 > /dev/null 2>&1
rm /tmp/$$.2 > /dev/null 2>&1



# Process /etc/oratab for each instance that is defined to autostart.  

cat /etc/oratab | while read LINE
  case $LINE in
  \#*) # A comment line in oratab
     if [ "$LINE" ]         # If this is not a blank line....
       if [ "`echo $LINE | awk -F: '{print $3}' -`" = "Y" ]
	 ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
	 if [ "$ORACLE_SID" = "*" ]
         export ORACLE_SID
	 ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
         export ORACLE_HOME
         PATH=$PATH:$ORACLE_HOME/bin ; export PATH

         cd /oracle/admin/$ORACLE_SID/bdump

# In each oracle admin area, in the alert_log directory, we'll create
# a tracker file to keep track of the last line number processed.
# This is also reset as a part of the script that 
# runs once a week.  If it doesn't exist, create it.


# If there is an alert log present (test databases may not have one...)

	 if [ -s "$LOG" ]

           if [ -f "$FILE" ]
             LAST=`cat $FILE`
             echo '0' > $FILE

# At this point, $LAST contains the number of the last line processed
# last time in the alert log for this SID.
# Now we need to find out how many lines are in the file at the moment

           CURRENT=`cat $LOG | wc -l`

# if CURRENT > LAST, then we have more data in the alert log than the
# last time we checked, so we'll continue processing, otherwise we 
# are done with this iteration of the loop.
           if [ "$CURRENT" -gt "$LAST" ]
             errcnt=`cat $LOG | grep -v ORA-000060 | egrep -c 'ORA-|crash|Error|abort|cannot allocate|not complete|waiting|All online logs needed archiving|Shutting down|ALTER DATABASE CLOSE|alter database close|Starting|FULL|kccrsz|not  valid|terminate|Failed|error|Archival stopped'` 

             if [ errcnt -gt 0 ]
               cat $LOG | grep -v ORA-000060 | egrep -n 'ORA-|crash|Error|abort|cannot allocate|not complete|waiting|All online logs needed archiving|Shutting down|ALTER DATABASE CLOSE|alter database close|Starting|FULL|kccrsz|not  valid|terminate|Failed|error|Archival stopped' > /tmp/oracle_alert_scanner.tmp
               echo $CURRENT > $FILE

Open in new window

This was originally written against Oracle 9 databases.

Lots of things you could do to tweak it, but it's a possible start.   For 11g, for example, you'd need a different location for your alert logs.

It keeps track of the last line processed in a little temp file and then only checks from there to end of file each time it runs.

I had another script that aged / rotated the alert log each week and as a part of that it rewrote the tracker file with 0 in it.
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 39983821
you could refer to one of sites which has a scenario where in there are multiple databases hosted on a single system.. I hope this would suffice your requirement to monitor multiple databases by querying /etc/oratab. he had done it so via some file

for SID in `cat $ORACLE_HOME/sidlist`

check this link for further details

Author Closing Comment

ID: 39983828

thanks I will give it a try

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question