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
  • 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.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

863 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

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now