Alert Log Monitoring using ADRCI

This has been tested on: SunOS 5.10 Generic_142909-17 sun4u sparc SUNW,SPARC-Enterprise

If my memory serves me correctly, you will need to change “ps -eo args” for Linux and other OS accordingly.

Note: db_unique_name = lax_${sid} (primary) / san_${sid} (standby), which you might have already guessed.

Here is the script:

#!/bin/sh

. /home/oracle/.common.conf > /dev/null

DN=`dirname $0`
BN=`basename $0`
EXCEPTIONS=$SCRIPT_DIR/alert_exceptions

DCP=lax # primary data center
DCS=san # standby data center
for sid in `ps -eo args|grep ora_smon|grep -v grep|awk -F_ '{print $3}'`
do
  OUTF=$LOG/${sid}.alert.log
  ORACLE_SID=$sid
  . oraenv
  PRIM=diag/rdbms/${DCP}_${sid}/$sid
  STBY=diag/rdbms/${DCS}_${sid}/$sid
  if [ -r $ORACLE_BASE/$PRIM ]; then
    DIAG=$PRIM
    DB=${DCP}_${sid}
  else
    if [ -r $ORACLE_BASE/$STBY ]; then
      DIAG=$STBY
      DB=${DCS}_${sid}
    fi
  fi
  adrci > $OUTF << EOF   
  set home ${DIAG}   
  SHOW ALERT -term -P "MESSAGE_TEXT like 'ORA-%' and originating_timestamp > systimestamp-1/1440"
  exit
EOF
  if [ `egrep '^ORA-' $OUTF|egrep -cvf $EXCEPTIONS` != 0 ]; then
    mailx -r oracle@domain.com -s "OERR: $BN $DB" $EMAIL < $OUTF
  fi
done
exit

Here is an example of email notification:

From: oracle@domain.com
Sent: Wednesday, June 01, 2011 1:43 PM
To: dba
Subject: OERR: monitor_alert.sh dw02

ADRCI: Release 11.2.0.2.0 - Production on Wed Jun 1 13:43:01 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/oracle"
adrci> adrci>
ADR Home = /u01/app/oracle/diag/rdbms/lax_dw02/dw02:
*************************************************************************
2011-06-01 13:42:10.542000 -07:00
ORA-1652: unable to extend temp segment by 4 in tablespace                 TEMP
ORA-1652: unable to extend temp segment by 4 in tablespace                 TEMP
adrci>

UPDATE: There was an error in the original cut & paste. Improved script.

About these ads