Coskan’s Approach to Oracle

December 6, 2010

Alert Log Monitoring script via ADRCI

Filed under: How To, Tips — coskan @ 4:47 pm

Before I start to write about the blog series in my mind which will be the base to my first presentation again in my mind, I would like to share simple alert log monitoring with adrci.

At first I was completely against Diagnostics Dest idea but after a bit of searching and learning new tips tricks of adrci command like I think I am a big fan of diagnostics dest. Below is the script I wrote to monitor alert log hourly and with a daily summary. Script is self explanatory, the only thing it does is gets ADRCI homes put them in a bash array and grep the ORA-, TNS- errors for the last hour or last day. It is not rocket since and it is based on a simple command which can also be added to your profiles as an alias.

--last day
adrci exec="set home ${adrci_home}; show alert -p \\\"message_text like '%ORA-%' and originating_timestamp > systimestamp-1\\\"" 
--last hour 
adrci exec="set home ${adrci_home}; show alert -p \\\"message_text like '%ORA-%' and originating_timestamp > systimestamp-1/24\\\"" 

I am pretty sure this could be shortened but I did not bother to shorten it so I leave it to your imagination:)

I did not add listener checks to hourly checks but to daily summary, because there can be many which will cause you to get mail every hour.

Good part of using ADRCI and hourly checks is you do not need to edit the file to get rid of the error because you will never see that alert again in the next hour slot.

Script will send the output to the files and read those files back to the mail content and I am sure there may be better way to do this.

Complete script

#################################################
###### ALERT LOG CHECKING VIA ADRCI #############
#################################################
# Author : Coskan Gundogar
# Version Date: 02/12/2010
# Usage :  
# To run for last 24 hours - ./check_alert.sh D
# To run for last hour - ./check_alert.sh 
# Edit variables below for moving between servers 
# Changes To the Script 
#

export ORACLE_SID=+ASM1
export ORACLE_HOME=/u01/crs/oracle/product/11.2.0/grid/
export PATH=$PATH:$ORACLE_HOME/bin
DAILY_LOG=/home/oracle/bin/alert_log_check_daily.txt
HOURLY_LOG=/home/oracle/bin/alert_log_check_hourly.txt
MAIL_SUBJ="PRD:WARNING HOST: " 
MAIL_RECIPIENT="your_dba_group@your_company.com"

HOST_NAME=`hostname -a`


if [ "$1" = "D" ]
then

		############################################
		###############DAILY CHECKS ################
		############DBMS AND ASM CHECK##############
		############################################

		adrci_homes=( $(adrci exec="show homes" | grep -e rdbms -e asm))

		echo '####################################################' > $DAILY_LOG 
		echo '####### ALERT LOG OUTPUT FOR LAST 24 HOURS #########' >> $DAILY_LOG
		echo '####################################################' >> $DAILY_LOG 
		echo ''  >> $DAILY_LOG 
		echo ''  >> $DAILY_LOG 
		echo ''  >> $DAILY_LOG 

		for adrci_home in ${adrci_homes[@]}
		do 
			echo $adrci_home' Alert Log' >> $DAILY_LOG 
			adrci exec="set home ${adrci_home}; show alert -p \\\"message_text like '%ORA-%' and originating_timestamp > systimestamp-1\\\"" -term >> $DAILY_LOG 
		done


		############################################
		############## DAILY CHECKS ################
		############# LISTENER  CHECK###############
		############################################


		adrci_lsnr_homes=( $(adrci exec="show homes" | grep -e tnslsnr))

		echo ''  >> $DAILY_LOG 
		echo ''  >> $DAILY_LOG 
		echo ''  >> $DAILY_LOG 
		echo '####################################################' >> $DAILY_LOG 
		echo '###### LISTENER LOG OUTPUT FOR LAST 24 Hours #######' >> $DAILY_LOG
		echo '####################################################' >> $DAILY_LOG 
		echo ''  >> $DAILY_LOG 
		echo ''  >> $DAILY_LOG 
		echo ''  >> $DAILY_LOG 


		for adrci_lsnr_home in ${adrci_lsnr_homes[@]}
		do 
			echo $adrci_lsnr_home' Listener Log' >> $DAILY_LOG 
			adrci exec="set home ${adrci_lsnr_home}; show alert -p \\\"message_text like '%TNS-%' and originating_timestamp > systimestamp-1\\\""  -term >> $DAILY_LOG 
		done
		

		num_errors=`grep -c -e 'TNS' -e 'ORA' $DAILY_LOG`
		if [ $num_errors != 0 ]
		then 
		MAIL_SUBJ=$MAIL_SUBJ$HOST_NAME" Errors Found in Daily Alert Summary"
		mailx -s "$MAIL_SUBJ" $MAIL_RECIPIENT  $HOURLY_LOG 
		echo ''  >> $HOURLY_LOG 
		echo ''  >> $HOURLY_LOG 
		echo '####################################################' >> $HOURLY_LOG 
		echo '######### ALERT LOG OUTPUT FOR LAST HOUR ###########' >> $HOURLY_LOG
		echo '####################################################' >> $HOURLY_LOG 
		echo ''  >> $HOURLY_LOG 
		echo ''  >> $HOURLY_LOG 
		echo ''  >> $HOURLY_LOG 

		for adrci_home in ${adrci_homes[@]}
		do 
			echo $adrci_home' Alert Log' >> $HOURLY_LOG 
			adrci exec="set home ${adrci_home}; show alert -p \\\"message_text like '%ORA-%' and originating_timestamp > systimestamp-1/24\\\"" -term >> $HOURLY_LOG 
		done


		num_errors=`grep -c -e 'TNS' -e 'ORA' $HOURLY_LOG`
		if [ $num_errors != 0 ]
		then 
		MAIL_SUBJ=$MAIL_SUBJ$HOST_NAME" Errors Found in Hourly Alert Summary"
		mailx -s "$MAIL_SUBJ" $MAIL_RECIPIENT < $HOURLY_LOG
		fi

fi

About these ads

18 Comments »

  1. Hi Coskan,

    I think you have hit on a great idea for a presentation. I suspect not many dbas will be all that aware of what you can do with adrci.

    cheers,

    jason.

    Comment by jarneil — December 9, 2010 @ 8:26 pm

    • Thanks for your commens Jason I wish I would do for adrci and maybe in the future but my primary concern is plan stability :) I would do plan stability through upgrades and I hope I would give a little bit more info than Maria Calgan did at ukoug;)

      Comment by coskan — December 9, 2010 @ 8:30 pm

  2. Hi,

    plan stability? I too was thinking about that…

    jason.

    Comment by jarneil — December 9, 2010 @ 9:03 pm

    • What about doing it together with more cases :) I haven’t got any ukoug experience and you have.

      think about it, it could be good two expert is better than one expert isn’t it

      Comment by coskan — December 9, 2010 @ 9:10 pm

  3. Coskan, you can easily check for recent incidents from adrci with

    adrci exec=”show incident -all -orderby CREATE_TIME”

    Comment by John Hallas — December 10, 2010 @ 3:18 pm

  4. Good post which got me interested enough to check out the docs on the adrci command. :)

    BTW, your code won’t run as posted. You are missing an ‘else’ for ‘if [ "$1" = "D" ]‘ and a ‘fi’ for the first ‘if [ $num_errors != 0 ]‘.

    Comment by DougK — December 17, 2010 @ 2:40 pm

  5. Hello Coskan,

    First of all, many thanks for putting up such a wonderful script!

    I have an issue with adrci homes. I have multiple instances on the same box with their own diagnostic folder:
    /u01/app/oracle/admin//diag
    /u01/app/oracle/admin//diag

    How do I get ADRCI to set the homepath properly to work?
    Obviously, I am now getting “DIA-48494: ADR home is not set,..”

    Can you please help me out here?

    Please do suggest relevant documents so that I can try by myself.

    Thanks!
    Suhas

    Comment by Suhas D — January 27, 2011 @ 11:24 am

    • if you have multiple diagnostics desc then probably you may need to call the script for different home like

      check_alert.sh HOME1
      check_alert.sh HOME2

      in one single caller script and make ORACLE_HOME parameterized maybe ORACLE_SID as well

      Comment by coskan — January 27, 2011 @ 11:28 am

  6. How do you pass timestamp variable to adrci?

    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]

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

    Comment by Ashish — March 22, 2012 @ 6:29 pm

  7. Why don’t you use OraSentry tool.

    It has all you need and even may archive alert.log on size/day basis.
    Rg,
    Damir

    Comment by damirvadas — April 15, 2012 @ 6:39 am

    • and forgot to say …

      ability to send mail on any ORA error with filter on some which not raise mail send .. :-)

      Comment by damirvadas — April 15, 2012 @ 6:41 am

      • It maybe because I was unaware from the product :) It has a good cause like charity which makes the product beautiful. I maybe give it a go at some point but not at current shop.

        Thanks for sharing

        Comment by coskan — April 15, 2012 @ 7:36 am

        • I’m using it for more then 5 years … and of course have sent charity do now I have registered version…
          Rg,
          Damir

          Comment by damirvadas — April 15, 2012 @ 5:01 pm

  8. if you don’t use the -term opton and run this from cron you will get a bunch of hung vi sessesions out there. standard functionality displays the show alert to a vi session (actually default editor) but show alert -term -p for example will just display to the terminal.

    Comment by brian — April 18, 2012 @ 6:55 pm

  9. bash-3.2$ sh -x adcri.sh
    + export ORACLE_SID=OALIS
    + ORACLE_SID=OALISS
    + export ORACLE_HOME=/u01/oracle/product/11.2.0.3/db_1
    + ORACLE_HOME=/u01/oracle/product/11.2.0.3/db_1
    + export PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/usr/sbin:/u01/oracle/product/11.2.0.3/db_1/bin:/u01/oracle/product/11.2.0.3/db_1/lib:/u01/oracle/product/11.2.0.3/db_1/bin
    + PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/usr/sbin:/u01/oracle/product/11.2.0.3/db_1/bin:/u01/oracle/product/11.2.0.3/db_1/lib:/u01/oracle/product/11.2.0.3/db_1/bin
    + DAILY_LOG=/u01/app/oracle/scripts/alert_log_check_daily.txt
    + HOURLY_LOG=/u01/app/oracle/scripts/alert_log_check_hourly.txt
    + MAIL_SUBJ=’OASIS:WARNING HOST: ‘
    + MAIL_RECIPIENT=abc@.com
    ++ hostname
    + HOST_NAME=pprdoasdb300.abc.net
    adcri.sh: line 105: syntax error: unexpected end of file

    im getting this error

    Comment by avi — September 7, 2012 @ 6:50 pm

  10. and here is the script for monitoring log4j application logs on unix servers –

    http://www.buggybread.com/2012/03/log-monitoring-shell-script-to-send.html

    Comment by vikas — February 11, 2013 @ 11:34 pm

  11. I think the admin of this web page is actually working hard in
    support of his site, since here every material is quality based information.

    Comment by VImax Detox Review — July 29, 2013 @ 6:23 pm

  12. Thank you for the auspicious writeup. It in fact was a amusement account it.
    Look advanced to far added agreeable from you! However,
    how could we communicate?

    Comment by Bennie — February 5, 2014 @ 2:49 pm


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 Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 203 other followers

%d bloggers like this: