Emre Baransel - Oracle Blog

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

As we don't want the database user, which has reporting jobs, to connect the database at working hours, following trigger was used to apply this restriction.

CREATE OR REPLACE TRIGGER SYS.DENY_LOGIN
AFTER LOGON
ON DATABASE
BEGIN
IF sys_context('USERENV', 'SESSION_USER') = 'REPORT_USER' THEN
IF to_char(SYSDATE, 'hh24') BETWEEN 08 AND 22 THEN
raise_application_error(-20001,
'Sorry, it is not permitted to logon to the DB at this time as this user');
END IF;
END IF;
END deny_login;
/