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

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

Powered by Blogger.

Page Views

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