Alert log errors

This is located in a common library


#!/usr/bin/ksh
# Purpose:    Search the alert log of $ORACLE_SID for error
# messages.
# If any error messages are found, send notification
#               (see Notes below).
#
# Prereq's:    $ORACLE_SID environment variable must be set or
#        passed as a positional parameter. Also, $ORATAB
#        environment variable must be set.
#
# Notes: If this script is invoked from a terminal session,
#        display the results to the screen (STDOUT); otherwise,
#        send a mail message to the specified mail accounts.
#
# Exit Status:  99 = Required input, $ORACLE_SID, not supplied.
#               90 = Invalid Oracle sid.
#               95 = init.ora file not found for input sid.
#        97 = Alert log file does not exist.
#
# Author: Michael Culp
# Date:   May 2002
#===============================================================
#        R E V I S I O N    H I S T O R Y
#===============================================================
# Who:
# Date:
# Comments:
################################################################

function error_notification
{
if [ "$TERMINAL" = "Y" ]; then
   echo "$ERROR_MSG"
else
   env|grep SEND_MAIL_TO|while read LINE
   do
      alias sendvar="echo "`echo $LINE|awk 'BEGIN { FS="=" }; { print "$" $1 }'`
      echo "$ERROR_MSG" | mail $(sendvar)
   done
fi
}

export SEND_MAIL_TO='mike.culp@gmail.com'
export SEND_MAIL_TO2='mike.culp@gmail.com'

SERVER=$(uname -n)
DATE=$(date +%c%t)
ERROR_MSG_FILE=/home/oracle/alertlog_message

# check to see if command is being executed interactively
/usr/bin/tty -s
STATUS=$?
if [ $STATUS = 0 ]
   then
   clear
   TERMINAL=Y
else
   TERMINAL=N
fi

# Test for required inputs.
# Positional parameters override env variables.

if [ ${1:-0} = 0 ]; then
   if [ ${ORACLE_SID:-0} = 0 ]; then
      ERROR_MSG=`echo "ORACLE_SID must be supplied as input to $0"`
      error_notification
      exit 99
   fi
else
   export ORACLE_SID=$1
fi

# Call the validate_sid function to verify ORACLE_SID is valid.
# The function also sets the ORACLE_HOME and ORACLE_BASE variables.

validate_sid
if [ "$VALID_SID" != "Y" ]; then
   ERROR_MSG=`echo "ORACLE_SID ($ORACLE_SID) is invalid. Script is $0"`
   error_notification
   exit 90
fi

INIT_PATH="$ORACLE_HOME"/dbs
INIT_FILE="$INIT_PATH"/init"$ORACLE_SID".ora

if [ ! -s "$INIT_FILE" ]; then
   ERROR_MSG=`echo "$INIT_FILE does not exist. Script is $0"`
   error_notification
   exit 95
fi

LOG_PATH="/"`awk -F= '/background_dump_dest/ { print $2 }' "$INIT_FILE" |tr -s " "|cut -f2-30 -d/`

ALERTLOG="$LOG_PATH"/alert_"$ORACLE_SID".log

# Create a list of valid Oracle error message prefixes by parsing
# the oerr script in $ORACLE_HOME/bin

/home/oracle/bin/awk_message_prefixes > /tmp/ora_prefixes

# Archiver error message prefix is not included in the oerr
# script. Add an entry for this into the "ora_prefixes" file.

echo '^ARC[0-9]: Error' >> /tmp/ora_prefixes

# Search the alert.log for any occurrences of an Oracle error
# message prefix.

grep -bsf /tmp/ora_prefixes $ALERTLOG > $ERROR_MSG_FILE
status=$?
if [ $status -eq 1 ]; then   # no matches found
if [ $TERMINAL = 'Y' ]; then
echo "Amazingly enough, there are NO errors in $ALERTLOG"
fi
elif [ $status -gt 1 ]; then
ERROR_MSG=`echo "$ALERTLOG does not exist."`
error_notification
exit 97
fi

# If there are any messages in the message file, send
# appropriate notifications.

if [ -s $ERROR_MSG_FILE ]; then
   echo "\nMessages from server $SERVER on $DATE" >> $ERROR_MSG_FILE
   if [ $TERMINAL = 'Y' ]; then
      cat $ERROR_MSG_FILE
   else
      env|grep SEND_MAIL_TO|while read LINE
      do
      alias sendvar="echo "`echo $LINE|awk 'BEGIN { FS="=" }; { print "$" $1 }'`
      mail $(sendvar) < $ERROR_MSG_FILE
      done
   fi
fi

if [ -f $ERROR_MSG_FILE ]; then
rm $ERROR_MSG_FILE
fi
if [ -f /tmp/ora_prefixes ]; then
rm -f /tmp/ora_prefixes
fi

exit

Leave a Reply

Your email address will not be published. Required fields are marked *