Oracle alert log script

[Nago]

does anyone have a script that can scan through an alert log for ORA-errors in HP-UX 11 on HP 9000
thx

[James A. Donovan]

here's a function you can adapt, you'll need to set the parameters as per your site, but the logic works just fine...

chk_alert()
{
# Description: This function looks into alert file of oracle & reports
# all matches to ORA- pattern thru e-mail.
# It also pages DBA.

#Input: $LINE_FILE that stores line_no from where to start search
#Output: A mail containing all ORA- messages. Resets the $PARFILE with the
# current no. of lines.

if [ -f $LINE_FILE ] ; then
from_line=`cat $LINE_FILE`
if [ "$from_line" = "" ] ; then
from_line=1
fi
else
from_line=1
fi

if [ ! -f $ORA_EXCEPTIONS_FILE ] ; then
touch $ORA_EXCEPTIONS_FILE
fi

rm -f $LOG_LOC/diffs_${ORACLE_SID}
cur_lines=`wc -l $ALERT_FILE|cut -f1 -d' '`
# If the $cur_lines is less than the last line the script looked at last,
# it probably means that the alert.log file has been cycled. So assume
# that the file has not been read from and set $from_line = 1
if [ $cur_lines -lt $from_line ] ; then
from_line=1
fi
if [ $from_line -ne $cur_lines ] ; then
rm -f $LOG_LOC/chk_alert_${ORACLE_SID}.log
tail -n +$from_line $ALERT_FILE| grep "ORA-" > $LOG_LOC/chk_alert_${ORACLE_SID}.log
if [ -s $LOG_LOC/chk_alert_${ORACLE_SID}.log ] ; then
rm -f $LOG_LOC/alert_chk_${ORACLE_SID}.non600.log $LOG_LOC/alert_chk_${ORACLE_SID
}.600.log $LOG_LOC/alert_except_${ORACLE_SID}.600.log $LOG_LOC/alert_except_${ORACLE_SI
D}.non600.log
cat $LOG_LOC/chk_alert_${ORACLE_SID}.log| grep "ORA-" | grep -v "ORA-00600" | awk
-F"[: ]" '{print $1}' | sort | uniq > $LOG_LOC/alert_chk_${ORACLE_SID}.non600.log
cat $LOG_LOC/chk_alert_${ORACLE_SID}.log| grep "ORA-00600" | awk -F": " '{print
$1, $3}' | awk -F"," '{print $1}' | sort | uniq > $LOG_LOC/alert_chk_${ORACLE_SID}.600
.log

cat ${ORA_EXCEPTIONS_FILE} | grep ORA-00600 | awk '{print $1, $2}' | sort | uniq
> $LOG_LOC/alert_except_${ORACLE_SID}.600.log
cat ${ORA_EXCEPTIONS_FILE} | grep -v ORA-00600 | sort | uniq > $LOG_LOC/alert_e
xcept_${ORACLE_SID}.non600.log

comm -23 $LOG_LOC/alert_chk_${ORACLE_SID}.non600.log $LOG_LOC/alert_except_${OR
ACLE_SID}.non600.log > $LOG_LOC/diffs_${ORACLE_SID}
comm -23 $LOG_LOC/alert_chk_${ORACLE_SID}.600.log $LOG_LOC/alert_except_${ORACL
E_SID}.600.log >> $LOG_LOC/diffs_${ORACLE_SID}

cat ${LOG_LOC}/chk_alert_${ORACLE_SID}.log | mailx -s "Error Messages in ${ALER
T_FILE} on ${Server}" $MAILLST
no_of_diffs=`cat ${LOG_LOC}/diffs_${ORACLE_SID} | wc -l`
if [ $no_of_diffs -ne 0 ] ; then
if [ "${PAGEDBA_ALERT}" = "YES" ] ; then
cat $LOG_LOC/chk_alert_${ORACLE_SID}.log | sort | uniq | while read Msgtext
do
echo "pager command here."
done
fi
fi
fi
echo $cur_lines >$LINE_FILE
fi
}

[Printaporn_1]

TMP_FILE=${TMP_PATH}/${1}_${TNSNAME}.tmp.${TIMESTAMP}
SQL_FILE=${HOME_DIR}/${1}.sql

echo "====================================================" | tee -a ${TMP_FILE}
echo "${2}, ${ORACLE_SID}@${HOSTNAME}" | tee -a ${TMP_FILE}
echo "====================================================" | tee -a ${TMP_FILE}

if [ ! -f ${SQL_FILE} ]; then
echo "SQL file ${SQL_FILE} not found."
echo ""
else
BACKDUMP=`sqlplus -s ${ORACLE_USER}/${USER_PASSWORD}@${TNSNAME} < ${SQL_FILE} | egrep -i "[a-z]|[0-9]"`
ALERT_FILE=${BACKDUMP}/alert_${ORACLE_SID}.log
echo "Alert file ${ALERT_FILE}" | tee -a ${TMP_FILE}
if [ -f ${ALERT_FILE} ]; then
grep "ORA-" ${ALERT_FILE} | awk '{ print "==> "$0 }' | tee -a ${
TMP_FILE}
mv ${ALERT_FILE} ${ALERT_FILE}.${TIMESTAMP}
echo "Rename to ${ALERT_FILE}.${TIMESTAMP}" | tee -a ${TMP_FILE}
MONTHSTAMP=`date "+%Y%m"`
cat ${ALERT_FILE}.${TIMESTAMP} >> ${ALERT_FILE}.mon${MONTHSTAMP}
echo "Append to ${ALERT_FILE}.mon${MONTHSTAMP}" | tee -a ${TMP_FILE}
else
echo "Not found." | tee -a ${TMP_FILE}
fi
echo "" | tee -a ${TMP_FILE}
fi

date "+Finished: %Y.%m.%d-%H:%M:%S" | tee -a ${TMP_FILE}
echo "" | tee -a ${TMP_FILE}

NEW_LINE=`grep "==> " ${TMP_FILE} | wc -l`
if [ ${NEW_LINE} -gt 0 ]; then
echo "Mail or Page DataBase Administrator ..."
for MAIL_ADDRESS in `cat ${MAIL_FILE} | awk '{ print $1 }'`; do
echo "${2} '${ORACLE_SID}@${HOSTNAME}'" >> ${TMP_FILE}
echo "Mail to '${MAIL_ADDRESS}'"
mailx -s "dbalert" "${MAIL_ADDRESS}" < ${TMP_FILE}
done
echo ""
fi
-----------
this is .sql file
set heading off

SELECT value
FROM V$PARAMETER
WHERE name = 'background_dump_dest';

[Yogeeraj_1]

hi,

please see my post at this address:

http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x4177ef70e827d711abdc0090277a778c,00.html

Filename: alerts.ksh
Description: Automatic monitoring of the production database's ALERT LOG file for exceptions.

Running it as the Oracle User, reschedules daily execution of script - morning, evening and night everyday (uses at)



hope this helps!

regards
Yogeeraj