Listing 5 Automated script to report redo lag

#!/bin/ksh
#########################################################################
#  Script       :  daily_apply_lag.ksh
#
#  Description  :  This script is run once daily and Reports the redo log
#                  apply lag on the cascaded Standby Database.
# 
#  Options    :  None
#
#  Diagnostics  :  Standard Error
#
#  Author       :  Aaron Diehl
#  Date         :  01/17/2007
#
#  Modification :  
#
#########################################################################

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

if (( $# != 0 )) 
then
    ERROR="usage: invalid number of parameters"
    RETURN_CODE=1
    print "$0:$ERROR"
    exit $RETURN_CODE
fi

####################################
# Initialize UNIX variables
####################################
. /opt/admin/scripts/unix_vars.ksh

####################################
# Initialize Global Variables
####################################
SCRIPT=daily_apply_lag
TYPE=DBA
SEVERITY=4
LOGFILE=$UNIX_OUTPUT_DIR/"$SCRIPT".`date +%b%d`_`date +%H%M`
SUBJECT="Daily Redo Log Apply Lag"

####################################
# Initialize job specific parameters
####################################
spoolf=$BASE_DIR/tmp/$SCRIPT.lst
instance=prod
SID=$instance        # Set Oracle Environment for this Database
. define                #
today=`date +"%a %b %e"`
rep_subject="$SUBJECT for $today"

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

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

##########################################
# Main
##########################################
sqlplus -s "/ as sysdba" << EOJ
SET FEEDBACK off
SET VERIFY off
SET HEADSEP |
SET LINESIZE 80
SET PAGESIZE 40
SET NEWPAGE 0

SPOOL $spoolf ;

TTITLE '$SUBJECT |For $today'

COLUMN last HEADING 'Registered Log' 
COLUMN last FORMAT  A20
COLUMN diff HEADING 'Apply Delay(min)'
COLUMN diff FORMAT  9,999

select max(sequence#)||' (current log)' as last, 0 as diff
  from v\$archived_log
UNION
select max(sequence#)||' (last applied)' as last, \
  (SYSDATE - max(completion_time))*1440 as diff
from v\$archived_log
where applied = 'YES'
;

SPOOL OFF;

/
EOJ

if (( $? != 0 ))
then
  RETURN_CODE=1
  SUBJECT="DBA Alert: $SCRIPT Failed"
  ERROR="$SCRIPT failed to execute SQLPlus"
  print "$ERROR" >> $LOGFILE
else
    cat $spoolf >> $LOGFILE
fi

status=`grep -c 'ERROR' $LOGFILE`
if (( $status > 0 )) 
then
  RETURN_CODE=1
  SUBJECT="DBA Alert: $SCRIPT failed"
else
  ##############################
  # Send the Report to the users
  ##############################
  $UNIX_SCRIPT_DIR/alert.ksh $SCRIPT $RETURN_CODE 5 $spoolf $TYPE \
    "$rep_subject"
  if (( $? != 0 ))
  then
    RETURN_CODE=1
    SUBJECT="DBA Alert: $SCRIPT Failed"
    ERROR="$SCRIPT failed to send report to Management."
    print "$ERROR" >> $LOGFILE
  fi
fi

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

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

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