Listing 3 Automated script to check LGWR state

#!/bin/ksh
#########################################################################
#  Script       : lgwr_check.ksh
#
#  Description  : This script checks the status of Archive Log reception on 
#                 the Standby Database.
#                 
#  Options      : None
#
#  Diagnostics  : The exit status is 0 or 1
#
#  Author       : Aaron Diehl    
#  Date         : 10/31/2006
#
#  Modification : 11/27/2006, Aaron Diehl
#                 Corrected a logic error where the v$managed_standby 
#                 view may have a stale record
#
#########################################################################

############################
# Get Parameters
############################

if (( $# != 1 )) 
then
    ERROR="usage: $0 SID"
    RETURN_CODE=1
    print "$0:$ERROR"
    exit $RETURN_CODE
fi

############################
# Initialize UNIX variables
############################

. /opt/admin/scripts/unix_vars.ksh

####################################
# Initialize Global Variables
####################################
SCRIPT=lgwr_check
TYPE=DBA
SEVERITY=4
DATE=`date +%b%d`_`date +%H%M`
LOGFILE=$UNIX_OUTPUT_DIR/"$SCRIPT"."$DATE"
SUBJECT="Info: Script $SCRIPT was successful."

####################################
# Initialize job specific parameters
####################################
spool_dir=$BASE_DIR/tmp
run_date=`date +'%Y-%m-%d_%H:%M'`
tmp_sql=$spool_dir/$SCRIPT.tmp
scr_stat=$spool_dir/$SCRIPT.sql
cat /dev/null > $tmp_sql

#################
# Create log file
#################

print "Starting $SCRIPT on `date`" > $LOGFILE 2>&1
print "****************************************************" >>$LOGFILE 2>&1
print "" >> $LOGFILE 2>&1

##########################################
# Main
##########################################
SID=$1             # Set Oracle Environment for this Database
. define                #

print "Checking Log Writer Sequence for $SID ..." >> $LOGFILE

lgwr_stat=`sqlplus -s '/ as sysdba' <<LGWRstat
SET HEAD OFF
SELECT COUNT(*)||':', MAX(sequence#)
FROM v\\$managed_standby
WHERE client_process = 'LGWR';

exit;

LGWRstat
`
if (( $? != 0 ))
then
  RETURN_CODE=1
  ERROR="Error: Failed to get LGWR sequence from $SID."
  print "$ERROR" >> $LOGFILE 2>&1
  SUBJECT="$TYPE Alert: $SCRIPT failed on $DATE"
  $UNIX_SCRIPT_DIR/alert.ksh $SCRIPT $RETURN_CODE $SEVERITY $LOGFILE \
    $TYPE "$SUBJECT"
  exit $RETURN_CODE
fi
print "Checking Managed Recovery Sequence for $SID ..." >> $LOGFILE
mrp_seq=`sqlplus -s '/ as sysdba' <<MRPseq
SET HEAD OFF
SELECT sequence#||''
FROM v\\$managed_standby
WHERE process = 'MRP0';

exit;

MRPseq
`
if (( $? != 0 ))
then
  RETURN_CODE=1
  ERROR="Error: Failed to get MRP sequence from $SID."
  print "$ERROR" >> $LOGFILE 2>&1
  SUBJECT="$TYPE Alert: $SCRIPT failed on $DATE"
  $UNIX_SCRIPT_DIR/alert.ksh $SCRIPT $RETURN_CODE $SEVERITY $LOGFILE \
    $TYPE "$SUBJECT"
  exit $RETURN_CODE
else
  lgwr_count=`echo $lgwr_stat|cut -f 1 -d ":"|tail -1`
  lgwr_seq=`echo $lgwr_stat|cut -f 2 -d ":"|tail -1`
  lgwr_seq=`echo $lgwr_seq|tr -s " " ""`
  mrp_seq=`echo $mrp_seq|tail -1`
  print "LGWR count is $lgwr_count" >> $LOGFILE
  print "LGWR sequence is $lgwr_seq" >> $LOGFILE
  print "MRP sequence is $mrp_seq" >> $LOGFILE
  if [[ $lgwr_seq < $mrp_seq ]] || [[ $lgwr_count = 0 ]]
  then
    RETURN_CODE=1
    ERROR="Error: $SID is not in Log Writer Mode."
    print "$ERROR" >> $LOGFILE 2>&1
    SUBJECT="$TYPE Alert: $SID is not in Log Writer Mode."
    print "INSERT INTO lgwr_status" >> $tmp_sql
    print "  VALUES ('$SCRIPT','$run_date','$SID','0','1');" >> $tmp_sql
  fi
fi

######
# Exit 
######

print "\nEnding $SCRIPT on `date`" >> $LOGFILE 2>&1
print "****************************************************" >>$LOGFILE 2>&1
print "" >> $LOGFILE 2>&1

$UNIX_SCRIPT_DIR/alert.ksh $SCRIPT $RETURN_CODE $SEVERITY $LOGFILE \
  $TYPE "$SUBJECT"