Thinking Out Loud

June 2, 2011

Alert Log Monitoring using ADRCI

Filed under: 11g,oracle — mdinh @ 1:48 am

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

4 Comments »

  1. Nice script :)

    Comment by Arindam Paul — February 20, 2012 @ 1:19 pm | Reply

  2. How to pass variable in adrci to monitor log?

    adrci exec=”set home diag/asm/+asm/+ASM1 ; show alert -p \\\”message_text like ‘%ORA-%’ and originating_timestamp > “$d_time” \\\”” -term

    ADR Home = /u001/app/oracle/diag/asm/+asm/+ASM1:
    *************************************************************************
    DIA-48415: Syntax error found in string [message_text like '%ORA-%' and originating_timestamp > 22-MAR-12] at column [64]

    > echo “$d_time”
    22-MAR-12 02.02.48.784339 PM -04:00

    Comment by Ashish — March 22, 2012 @ 6:24 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 272 other followers

%d bloggers like this: