Re: Searching the Alert log file

From: Tim Willson <tim.willson-1_at_umn.edu>
Date: 1997/04/11
Message-ID: <334E9599.6D922E0C@umn.edu>#1/1

John Hough wrote:
>
> Does anyone have a script that searches the alert log for pre-defined
> errors and e-mails a distribution list is errors occur. A script
> written in PERL would be perfect since we have to run this on unix,
> NT and VMS.
>
> Thanks,
>
> John Hough

John,

This is an AIX script I use, running every 3 min. from an AT job owned by an account having rights to the database code. It prints b/4 and after lines around an ORA- error message found in the log file. It only prints the lines if new lines have been added to the log file. Lines_$base is a line count check file residing in the same sub-dir used for time stamping and log file line count.

Perhaps you can convert it to perl. If so post it back to me..... -tw

# !/bin/ksh
# Full path name to the alert logs to be checked.
#
# set -x

the_host=`hostname`
the_oracle=`whoami`
case $the_oracle in
V6_owner)

alertlogs="/oracle6/alert_sid_PROD.log \
           /oracle6/alert_sid_FTE.log \
           /oracle6/alert_sid_TRAI.log \
           /oracle6/alert_sid_DEV.log";;

V7_owner)

alertlogs="/oracle7/bdump/alert_v7_instance0.log \
           /oracle7/bdump/alert_v7_instance1.log";;
*)echo "You must be V6_owner or V7_owner to run this script exiting ..."

   exit;;
esac
#
for file in `echo "$alertlogs"`
do
diet=`dirname $file`
base=`basename $file`
cd $diet
if [ -f lines_$base ]
then
: # File lines found
else
wc -l $base|awk '{print $1}'>lines_$base fi
newer=`find $diet -name $base -newer $diet/lines_$base -print` if [ "${newer:=null}" = "null" ]
then
: skip this one
else
lines=`awk '{print $1}' lines_$base`
new_lines=`wc -l $base|awk '{print $1}'`

        if [ $new_lines != $lines ]
        then
        new=`expr $lines + 1 `
        error=`tail +$new $base|grep ORA-|uniq`
                if [ "${error:=null}" = "null" ]
                then
                : # Nothing to do alert log changed no error
                else

name="$base: "
mail -s "Server:$the_host - Error denoted in the database $base file" \ UNIX_mail_users <<EOF2  

New logged lines error denoted:  

`tail +$new $base|\
sed -n -e "/ORA-/{
x
s!\(.*\)!$name\1!p
x
s!\(.*\)!$name\1!p
n
s!\(.*\)!$name\1!p
}" -e h
`
End of output.
EOF2

                fi
        fi

fi
wc -l $base|awk '{print $1}'>lines_$base done
#End of File

This second file looks at the trace files for internal or fatal errors. Mails a message, copies the file for reference and removes old trace files. This runs once a night.

#!/bin/ksh
 # set -x
trace=oracle7/tmp/ora_trace
storage=scratch/internal_error
the_host=`hostname`
cd /$trace
for file in `grep -i "internal or fatal error" *.trc|uniq|\ awk -F: '{ print $1 }'`
do
echo "$file"|cpio -pumd /$storage>/dev/null 2>&1 chmod 666 /$storage/$file
echo "\nError in Trace file on $the_host: $file\nFile copied at `date` to /$storage\n"
grep -i "internal or fatal error" $file|uniq grep -i "internal error" $file|uniq
done>>/$storage/error.log
empty=`awk '/^Error/ {print $1}' /$storage/error.log|uniq` if [ ${empty:=null} = "null" ]
then
:
else
# echo "mail sent"
mail -s "Interal or Fatal Error in trace file on $the_host" \

                UNIX_mail_user < /$storage/error.log

cat /$storage/error.log>>/$storage/internal_error.log fi
mv /$storage/error.log /$storage/error.old rm /$trace/*.trc
#End of File Received on Fri Apr 11 1997 - 00:00:00 CDT