Notification email when deadlock is encountered in Oracle alert log

alertsdeadlockoracleoracle-11g-r2

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).

Best Answer

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
Related Question