Looking for Oracle Alert Log shell management scripts.

[Jack C. Mahaffey]

I'm thinking of writing a shell script to evaluate the daily Oracle alert logs and then extract and log non-normal oracle events to the syslog so that the messages can be picked up by our system management tools or action and notification.

Does anyone have something that does something similar to what I'm wanting. I want to catch the various oracle errors, such as unable to extend, snapshot too old, etc, without needing to review numerous alert logs on a daily basis.

My first thought is to compare the existing alert log against a copy of the log made on a previous day and run diff to catch all changes. I would then use grep to exclude normal messages, such as begin backup, redo log switches, etc.

Another idea may be to have a script run tail against the alert log, pipe the output to use the logger application to add the timestamp.





jack...


jack...

[VanZandt, David]

FWIW I highly recommend the every5 script found in "Oracle Scripts" published by O'Reily. It's copyrighted, hence I will not provide it -- but I've relied upon it for about three years now.

HTH,
Dave

[Reinhard Burger]

Hi

If you shutdown the db over night for backup then you can do something very easy. Just create a daily copy of the alert file and clear the alert file with cat /dev/null > "alertfile" . After that start the db instance . If you add the systemdate to the file name of the copy you created you will get daily alert files containing only messages of the actual day.
To do this you should take care on permissions of the alert file. Tha copy made can be added easily to the systemlog in different ways. The most easy way is just to do :
cat "alertfile" >> syslog.log.
I think it's easier at all to isolate the logentries on a daily basis then to try to extract them out of a big file

[Printaporn_1 · Accepted solution]

Hi,

this is dbalert
-----------------------
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}'"
mail "${MAIL_ADDRESS}" < ${TMP_FILE}
done
echo ""
fi
-------------------------------------------
this is dbalert.sql
set heading off

SELECT value
FROM V$PARAMETER
WHERE name = 'background_dump_dest';
-----------------------------------------
make modification as needed.

[Andreas D. Skjervold]

Hi

This can also be done using the Alert Event in Oracle Enterprise Manager.
You get a alert for each new Warnig or Alert in the alert.log since last alert.

rgds
Andreas

(will he never stop advocating the OEM...)

[Jack C. Mahaffey]

Shutting down the database is not an option. I also currently don't have access to the OReily publication.

Thanks...

[Jack C. Mahaffey]

The dbalert script is closer to what I'm looking for. I see the script separates the log entries on a daily basis.


I'll test the script and see if it's what I need.

Thanks...


As for OEM... I love OEM, but it does have it's limitations and I was looking for using a local script.

I've had problems with OEM anytime I change the IP address and failover to another server. The job scripts had to be re-created.

jack...

[Jack C. Mahaffey]

Here's what I've done on an interim basis to at least copy the oracle messages to syslog.log.

I've created a script in /sbin/init.d that does the following:

for n in db1 db2 db3 db4
do
nohup tail -f ${ORACLE_ADMIN}/$n/bdump/alert_${n}.log | logger &
done


This will give me timetamps attached to each event and allows me to write other scripts for catching events.

I've tried adding awk to the command, but had no luck.

jack...