Oracle DBA Health Checkup and performance scripts Part 2 Checking the database Alert Log in automatic manner for new alert or error



This script finds errors encountered in alert.log, if any. Also this script is written in the manner that it'll only show the errors those have not been
encountered in the last run.


++++++++++++++++++++++++++++++++++++++++++++++++++

###############################################################################
#
# Script Name : alert_log_ORA_errors.sh #
# Purpose : This script finds errors encountered in alert.log, if any #
# Author : oracle-latest-technology.com #
# Date of Creation: Dec 19, 2011 #
###############################################################################
clear
echo " "
#!/bin/ksh

. <DB Home>/<SID>_<Domain>.env

HOST=`hostname`
export HOST
#dt=`date +"%d.%m.%y"`

################
# Here, we are building the full path to the alert.log ...
alert_file=<DB Home>/admin/<SID>_<Domain>/diag/rdbms/<SID>/<SID>/trace/alert_$ORACLE_SID.log
##cd $WORKDIR
# If the file that keeps track of how many lines were last time processed doesn't exist, initialize it now ...
if [ ! -f <log location>/verified_alert_lines_cnt.lst ];then
echo 1 > <log location>/verified_alert_lines_cnt.lst
fi

# see how many lines we currently have in the alert.log
current_alert_lines_cnt=`cat $alert_file|wc -l`
# see how many lines we have verified at the last check ...
verified_alert_lines_cnt=`cat <log location>/verified_alert_lines_cnt.lst`
# in case that somebody/dba has cleared out the alert log, so we'll have less lines in alert log
# than we checked last Time, then re-initialize ...
if [ $current_alert_lines_cnt -lt $verified_alert_lines_cnt ];then
echo 1 > $HOME/scripts/verified_alert_lines_cnt.lst
verified_alert_lines_cnt=`cat <log location>/verified_alert_lines_cnt.lst`
fi

echo "Lines already read from the alert log for any possible error(s) till the last run:" $verified_alert_lines_cnt
echo " "
echo "Reading alert log to check if any error encountered since the last run..."

# see the actual lines newly generated in alert log since our last check ...
verified_alert_lines_cnt=`expr $verified_alert_lines_cnt + 1`
tail +$verified_alert_lines_cnt $alert_file > <log location>/verified_alert_lines.lst
# grep the newly generated alert log lines for ORA- errors ...
grep "ORA-00600" <log location>/verified_alert_lines.lst > <log location>/alert_log_err.lst
grep "ORA-00204" <log location>/verified_alert_lines.lst >> <log location>/alert_log_err.lst
grep "ORA-00206" <log location>/verified_alert_lines.lst >> <log location>/alert_log_err.lst
grep "ORA-00210" <log location>/verified_alert_lines.lst >> <log location>/alert_log_err.lst
grep "ORA-00333" <log location>/verified_alert_lines.lst >> <log location>/alert_log_err.lst
grep "ORA-00345" <log location>/verified_alert_lines.lst >> <log location>/alert_log_err.lst
grep "ORA-01578" <log location>/verified_alert_lines.lst >> <log location>/alert_log_err.lst
grep "ORA-19599" <log location>/verified_alert_lines.lst >> <log location>/alert_log_err.lst
grep "ORA-07445" <log location>/verified_alert_lines.lst >> <log location>/alert_log_err.lst
grep "ORA-03113" <log location>/verified_alert_lines.lst >> <log location>/alert_log_err.lst
grep "ORA-01401" <log location>/verified_alert_lines.lst >> <log location>/alert_log_err.lst
grep "ORA-03114" <log location>/verified_alert_lines.lst >> <log location>/alert_log_err.lst
grep -i error <log location>/verified_alert_lines.lst >> <log location>/alert_log_err.lst
# set the next starting check at the max. number of lines currently available in alert log ...
echo $current_alert_lines_cnt > <log location>/verified_alert_lines_cnt.lst
# notify the dba ... if errors encountered in alert log ...

alert_log_err_cnt=`cat <log location>/alert_log_err.lst|wc -l`
if [ $alert_log_err_cnt -gt 0 ];then
{
echo "----------Following error(s) encountered in the alert log----------"
cat <log location>/alert_log_err.lst
echo "-------------------------------------------------------------------"

#--- Following block stores the error (and appends further error) in the
#--- file $HOME/scripts/alert_log_error.txt
echo "---Following error(s) encountered in the alert log dated:" `date` >>\
<log location>/alert_log_error.txt
echo "---between line numbers:" $verified_alert_lines_cnt "and" $current_alert_lines_cnt >>\
<log location>/alert_log_error.txt
echo "**************************************************************************************" >>\
<log location>/alert_log_error.txt
echo " " >> <log location>/alert_log_error.txt
cat <log location>/alert_log_err.lst >> <log location>/alert_log_error.txt
echo " " >> <log location>/alert_log_error.txt
echo "-------------------End of error(s) dated:" `date` "---------------" >> <log location>/alert_log_error.txt
echo "**************************************************************************************" >>\
<log location>/alert_log_error.txt
echo " " >> <log location>/alert_log_error.txt

echo "To see the whole list of error(s) in the ALERT log so far"
echo "you can look into:" <log location>/alert_log_error.txt

#msg=`echo "$HOST/$ORACLE_SID: Please be notified that Critical ORA- errors encountered in \
#$ORACLE_SID database since the last check. Please see attachments ... "`
#dba_list=xxxx@xxxx.xom
#mailx -s "$msg" "$dba_list"< <log location>/alert_log_err.lst > /dev/null 2>&1
}
else
{
echo " "
echo "No error encountered in the alert log."
echo " "
}
fi
echo "Number of lines in the alert log now is:" $current_alert_lines_cnt
rm <log location>/verified_alert_lines.lst <log location>/alert_log_err.lst



++++++++++++++++++++++++++++++++++++++++++++++++++

No comments :