Listing 7 LGWR monthly management summary report

#!/bin/ksh
#########################################################################
#  Script       :  monthly_lgwr.ksh
#
#  Description  :  This script is run once monthly and sends Management a
#                  Performance Report from Oracle Log Writer statistics.
# 
#  Options      :  None
#
#  Diagnostics  :  Standard Error
#
#  Author       :  Aaron Diehl
#  Date         :  12/05/2006
#
#########################################################################

############################
# 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=monthly_lgwr
TYPE=DBA
SEVERITY=4
LOGFILE=$UNIX_OUTPUT_DIR/"$SCRIPT".`date +%b%d`_`date +%H%M`
SUBJECT="Oracle Connectivity: Monthly Log Writer Performance Report"

####################################
# Initialize job specific parameters
####################################
spoolf=$BASE_DIR/tmp/$SCRIPT.lst
run_date=`date +'%Y-%m-%d_%H:%M'`
month=`date +%m`
year=`date +%Y`
instance=prod

if (( $month == "01" ))
then  
   ((year=$year - 1))
fi

case $month in
   "01")  lmonth="December"
          ;;
   "02")  lmonth="January"
          ;;
   "03")  lmonth="February"
          ;;
   "04")  lmonth="March"
          ;;
   "05")  lmonth="April"
          ;;
   "06")  lmonth="May"
          ;;
   "07")  lmonth="June"
          ;;
   "08")  lmonth="July"
          ;;
   "09")  lmonth="August"
          ;;
   "10")  lmonth="September"
          ;;
   "11")  lmonth="October"
          ;;
   "12")  lmonth="November"
          ;;
   "")    ;;
esac

rep_subject="$SUBJECT for $lmonth $year"

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

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

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

sqlplus -s '/ as sysdba' <<EOJ
SET FEEDBACK off
SET VERIFY off
SET HEADSEP |
SPOOL $spoolf ;

TTITLE '$SUBJECT |for $lmonth $year'

COLUMN log HEADING 'Redo Logs (#)'
COLUMN log FORMAT 99,999
COLUMN mb  HEADING 'Redo Size (GB)'
COLUMN mb  FORMAT 9,999
COLUMN arch HEADING 'ARCH Mode (#)'
COLUMN arch FORMAT 99,999
COLUMN lgwr HEADING 'LGWR Mode (#)'
COLUMN lgwr FORMAT 99,999
COLUMN PERF         HEADING 'LGWR Success Rate(%)'
COLUMN PERF         FORMAT 999.99

SET LINESIZE 80
SET PAGESIZE 40
SET NEWPAGE 0

SELECT 
       COUNT(*)                    log,
       (SUM(blocks))*(512/1073741824)        mb,
       SUM(DECODE(SUBSTR(name,16,4),'stan',1))    arch,
       SUM(DECODE(SUBSTR(name,16,4),'arch',1))    lgwr,
       100*((NVL(SUM(DECODE(SUBSTR(name,16,4),'arch',1)), \
         0.001))/COUNT(*)) as PERF
FROM   v\$archived_log
WHERE  TRUNC(completion_time) >= TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -2)) + 1)
AND    TRUNC(completion_time) <  TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -1)) +1)
--
--  Next AND clause needed if a second standby is used
--
--     AND    name <> 'prod.2ndstandby'
;

spool off;
exit;
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 MGR "$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"