1

How to send a notification email when a deadlock is encountered in alert log on any node like the deadlocks encountered below? (an alert any time a deadlock is detected in alert log)

Node 1:

Mon Sep 08 16:23:59 2014
Global Enqueue Services Deadlock detected. More info in file
 /u01/oracle/diag/rdbms/luma/LUMA1/trace/LUMA1_lmd0_4814.trc.

Node 2:

Mon Sep 08 16:37:47 2014
Global Enqueue Services Deadlock detected. More info in file
 /u01/oracle/diag/rdbms/luma/LUMA2/trace/LUMA2_lmd0_2602.trc.

I am using DB Control (11g).

1

You might try using ADRCI.

ADRCI>
ADRCI> show alert -p "message_text like '%Global Enqueue Services Deadlock detected%' and originating_timestamp > systimestamp + INTERVAL '-5' MINUTE"

This will show your error within the past 5-minutes. You could put this into a shell script and add a cron job that runs it every 5-minutes.

#!/bin/bash
LOG = /home/oracle/log.txt
adrci_homes=( $(adrci exec="show homes" | grep -e rdbms ))

for adrci_home in ${adrci_homes[@]}
do
   echo $adrci_home' Alert Log' >> $LOG
   adrci exec="set home ${adrci_home}; show alert -p \\\"%Global Enqueue Services Deadlock detected%' and originating_timestamp > systimestamp + INTERVAL '-5' MINUTE\\\"" -term >> $LOG
done

num_errors=`grep -c -e 'Global Enqueue Services Deadlock detected' $LOG`

if [ $num_errors != 0 ]
then
     mutt -a $LOG -s "Deadlock Detected" admin@mydomain.com 
fi
0

I know someone who wrote a Perl program that woke up every few minutes to read the new lines of the alert log and send an email if there are errors. If you have grid you can probably configure that to notify if there are deadlocks.

0

You can use the x$dbgalertext table or v$diag_alert_ext view) which is a fixed table driven by the XML alert logs.

This way you can use a SQL statement to filter the entries. Here are a few examples: http://www.dba-oracle.com/t_11g_dbgalertext_alert_log_sql.htm

And just for completeness the Enterprise Manager offer HTML views and alerts.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.