cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle alert log script

Nago
Advisor

Oracle alert log script

does anyone have a script that can scan through an alert log for ORA-errors in HP-UX 11 on HP 9000
thx
3 REPLIES
James A. Donovan
Honored Contributor

Re: Oracle alert log script

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
}
Remember, wherever you go, there you are...
Printaporn_1
Esteemed Contributor

Re: Oracle alert log script

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';
enjoy any little thing in my life
Yogeeraj_1
Honored Contributor

Re: Oracle alert log script

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)