I wanted to share the following shell script (which is written by my friend M. Selcuk Karaca) which let us to monitor dataguard log transport and log apply services. You may first think why to use a shell script rather than a tool like Data Guard Broker. Our environment works on a cold failover cluster which Oracle Data Guard Broker doesn't support and putting a shell script in crontab has been a simple and effective solution for Dataguard monitoring.

Simply what this shell script does: Check if there is a latency of more then 20 archive logs in log transport or log apply services by querying the V$ARCHIVE_DEST_STATUS view. If there is, send a mail to DBA's mentioning the problem with the following information.

"Last Archive Log Number produced on the primary side"
"Last Archive Log Number that was sent to the standby side"
"Last Archive Log Number that was applied on the standby side"

#check_dg.sh script (works on primary DB), monitors Data Guard log Apply and log Transport services..
#if local arc sequence is 20 greater than stby arc sequence, then there is a problem in log Transport..
#if stby arc sequence is 20 greater than stby apply sequence, then there is a problem in log Apply..

#set Oracle environment for Sql*Plus
ORACLE_HOME=/oracle/product/10.2.0 ; export ORACLE_HOME
ORACLE_SID=usagedb ; export ORACLE_SID
PATH=$PATH:/oracle/product/10.2.0/bin

#set working directory. script is located here..
cd /oracle/scripts

#Problem statement is constructed in message variable
MESSAGE=""

#hostname of the primary DB.. used in messages..
HOST_NAME=`/usr/bin/hostname`

#who will receive problem messages.. DBAs e-mail addresses seperated with space
DBA_GROUP='dba1@company.com dba2@company.com'

#SQL statements to extract Data Guard info from DB
LOCAL_ARC_SQL='select archived_seq# from V$ARCHIVE_DEST_STATUS where dest_id=1; \n exit \n'
STBY_ARC_SQL='select archived_seq# from V$ARCHIVE_DEST_STATUS where dest_id=2; \n exit \n'
STBY_APPLY_SQL='select applied_seq# from V$ARCHIVE_DEST_STATUS where dest_id=2; \n exit \n'

#Get Data guard information to Unix shell variables...
LOCAL_ARC=`echo $LOCAL_ARC_SQL | sqlplus -S / as sysdba | tail -2|head -1`
STBY_ARC=`echo $STBY_ARC_SQL | sqlplus -S / as sysdba | tail -2|head -1`
STBY_APPLY=`echo $STBY_APPLY_SQL | sqlplus -S / as sysdba | tail -2|head -1`

#Allow 20 archive logs for transport and Apply latencies...
let "STBY_ARC_MARK=${STBY_ARC}+20"
let "STBY_APPLY_MARK= ${STBY_APPLY}+20"
if [ $LOCAL_ARC -gt $STBY_ARC_MARK ] ; then
MESSAGE=${MESSAGE}"Error on $HOST_NAME Standby -log TRANSPORT- service! \n local_Arc_No=$LOCAL_ARC but stby_Arc_No=$STBY_ARC \n"
fi

if [ $STBY_ARC -gt $STBY_APPLY_MARK ] ; then
MESSAGE=${MESSAGE}"Error on $HOST_NAME Standby -log APPLY- service! \n stby_Arc_No=$STBY_ARC but stby_Apply_no=$STBY_APPLY \n"
fi

if [ -n "$MESSAGE" ] ; then
MESSAGE=${MESSAGE}"\This problem may cause the archive directories to get full!!! \n .\n "
echo $MESSAGE | mailx -s "$HOST_NAME DataGuard Problem" $DBA_GROUP
fi
Your comments, especially which will help us improve the functionality, will be greatly appreciated :)

10 Responses so far.

  1. Anand says:

    Hi..

    Would like to know what all settings need to be done for email notification regarding problems.How DBA_GROUP will automatically send the mail.


    Regrads,
    Anand

  2. Hi Anand;

    For mail settings on hp-ux servers you use /etc/mail/sendmail.cf configuration file. You specify the mail server adress in this file. Search for "^DS" and apend the name of your mail server.

    Also you must check if sendmail is running on your server. server (ps -ef|grep sendmail). If not, edit /etc/rc.config.d/mailsvrs and make "export SENDMAIL_SERVER=1". Start sendmail using /sbin/init.d/sendmail start.

    Mail addresses specified in DBA_GROUP variable will recieve the error mails send by the database server.

  3. Interesting article, added his blog to Favorites

  4. Great job this is a nice post for reading i will keep visiting thanks.

  5. BB. says:

    It's nice that you describe it.
    phone girls in London

  6. shivani says:

    The blog was absolutely fantastic! A lot of information is helpful in some or the other way...Great job, keep it up
    Oracle Fusion Financials Online Training
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training
    oracle Fusion Technical online training

  7. Roku empowers you to watch free and roku.com/link
    paid video content on your TV by methods for the Internet. For Activating Roku, go to www.roku.com/associate record enter Roku com association code appeared on Roku TV. Initiate Roku tv, go to rokucom link roku.com/link
    record enter Roku.com connection code showed on Roku TV.

    roku.com/link
    roku.com/link
    roku.com/link

  8. Amazon Prime TV, and MyTV code. To register your device on the Amazon website you go to our website and enter the given code. The program requires the customer to have an Amazon account and a streaming device or TV. Amazon Prime Video is available on almost all streaming devices like Roku, Amazon Fire TV, Chromecast. For this, you just have to go to Amazon amazon.com/mytv with the help of the amazon activation code. You can visit our website for details of the entire process. You can do this by installing the amazon.com/mytv Video application on your smart TV, or the digital media player or video game console connected to your TV by visiting the link provided by us in the de cription.or more information, you can contact our team at Amazon.com/mytv and get more and more information.

  9. yuvi says:

    Roku Streaming players are an increasingly advantageous roku.com/link and financially savvy approach to sit in front of the TV. roku.com/link Simply plug it into your TV, associate with the web, set up a Roku record, and start spilling your top choices.Roku gadgets are easy to set-up and simple to-utilize. roku.com/link They accompany a straightforward remote, and incredible highlights like Roku Search which makes it easy to discover what you need to watch.
    roku.com/link
    roku.com/link
    roku.com/link
    roku.com/link

Powered by Blogger.

Page Views

- Copyright © Emre Baransel - Oracle Blog -Metrominimalist- Powered by Blogger - Designed by Johanes Djogan -