Shell Script Standby Database Gap Detection Script.

Updated: 02-Jun-2005
Version: 1.0

Description

Detects if a standby database is up to date. Send an OEM alert is not.

Script assumes that you have a TNS alias set up for the standby database of the format "STBY_(PRIMARY ALIAS)"

Parameters

$1 - TNS Alias of primary database
$2 - Sys password

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

#!/bin/ksh 
################################################################
# check the status of the standby database and reports it to OMS
#
################################################################

. /home/oracle/ora_profile

ALIAS=$1
STBY_ALIAS=stby_${ALIAS}
SYS_PASS=$2
TARGET=$ALIAS.world
EVENTNAME="/myevent/rdbms/standby/status"
ERRFILE=${ALIAS}_standby.err

LOCAL_SEQ=`sqlplus -s "sys/$SYS_PASS@$ALIAS as sysdba" << EOF
set pages 0 feed off verify off heading off echo off
select max(sequence#) from v\\\$archived_log;
exit  
EOF`
if [ "$?" -ne "0" ]
then
   ERRTXT="$TARGET $LOCAL_SEQ"
   SEV=alert
   echo $ERRTXT > $ERRFILE
   oemevent $EVENTNAME $TARGET $SEV "$ERRTXT"
   exit 1
fi

#echo $LOCAL_SEQ

if [ -z "${LOCAL_SEQ}" ]
then
   ERRTXT="$TARGET : Primary Database has archived no logs."
   SEV=alert
   if [ ! -f "$ERRFILE" ]; then
      #only send an event if we do not have an error file
      echo $ERRTXT > $ERRFILE
      oemevent $EVENTNAME $TARGET $SEV "$ERRTXT"
   fi
   exit 1
fi

STBY_SEQ=`sqlplus -s "sys/$SYS_PASS@$STBY_ALIAS as sysdba" << EOF
set pages 0 feed off verify off heading off echo off
select max(sequence#) from v\\\$archived_log;
exit
EOF`
if [ "$?" -ne "0" ]
then
   ERRTXT="$TARGET $STBY_SEQ"
   SEV=alert
   echo $ERRTXT > $ERRFILE
   oemevent $EVENTNAME $TARGET $SEV "$ERRTXT"
   exit 1
fi

#echo $STBY_SEQ

if [ -z "${STBY_SEQ}" ]
then
   ERRTXT="$TARGET: Standby Database has received no archive logs from primary."
   SEV=alert
   if [ ! -f "$ERRFILE" ]; then
      #only send an event if we do not have an error file
      echo $ERRTXT > $ERRFILE
      oemevent $EVENTNAME $TARGET $SEV "$ERRTXT"
   fi
   exit 1
fi

if [ "${LOCAL_SEQ}" -gt "${STBY_SEQ}" ]
then
   ERRTXT="$TARGET standby has not RECEIVED archive logs. LOCAL SEQ=$LOCAL_SEQ STANDBY SEQ=$STBY_SEQ"
   SEV=alert
   if [ ! -f "$ERRFILE" ]; then
      #only send an event if we do not have an error file
      echo $ERRTXT > $ERRFILE
      oemevent $EVENTNAME $TARGET $SEV "$ERRTXT"
   fi
fi

STBY_SEQ=`sqlplus -s "sys/$SYS_PASS@$STBY_ALIAS as sysdba" << EOF
set pages 0 feed off verify off heading off echo off
select max(sequence#) from v\\\$log_history;
exit
EOF`
if [ "$?" -ne "0" ]
then
   ERRTXT="$TARGET $STBY_SEQ"
   SEV=alert
   echo $ERRTXT > $ERRFILE
   oemevent $EVENTNAME $TARGET $SEV "$ERRTXT"
   exit 1
fi

#echo $STBY_SEQ

if [ -z "${STBY_SEQ}" ]
then
   ERRTXT="$TARGET: Standby Database archived no logs."
   SEV=alert
   if [ ! -f "$ERRFILE" ]; then
      #only send an event if we do not have an error file
      echo $ERRTXT > $ERRFILE
      oemevent $EVENTNAME $TARGET $SEV "$ERRTXT"
   fi
   exit 1
fi

if [ "${LOCAL_SEQ}" -gt "${STBY_SEQ}" ]
then
   ERRTXT="$TARGET standby has not APPLIED all archive logs. LOCAL SEQ=$LOCAL_SEQ STANDBY SEQ=$STBY_SEQ"
   SEV=alert
   if [ ! -f "$ERRFILE" ]; then
      #only send an event if we do not have an error file
      echo $ERRTXT > $ERRFILE
      oemevent $EVENTNAME $TARGET $SEV "$ERRTXT"
   fi
else
   ERRTXT="$TARGET standby up to date"
   SEV=clear
   echo $ERRTXT
   # Check if have an error file. If we do we can send a clear event
   if [ -f "$ERRFILE" ]
   then
      rm -f $ERRFILE
      oemevent $EVENTNAME $TARGET $SEV "$ERRTXT"
   fi
fi

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links