Listing 6 LGWR weekly management detail report

#!/bin/ksh
#########################################################################
#  Script       :  weekly_lgwr.ksh
#
#  Description  :  This script is run once weekly and sends Management a
#                  Performance Report from Oracle Log Writer statistics.
# 
#  Options      :  None
#
#  Diagnostics  :  Standard Error
#
#  Author       :  Aaron Diehl
#  Date         :  12/30/2006
#
#  Modification :  01/16/2007  Aaron Diehl
#                  Added a WHERE clause to filter records for logs sent 
#                  to the 2nd Standby.
#
#########################################################################

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

####################################
# Initialize job specific parameters
####################################
spoolf=$BASE_DIR/tmp/$SCRIPT.lst
instance=prod
SID=$instance        # Set Oracle Environment for this Database
. define                #
t_end=`sqlplus -s '/ as sysdba' << EOW
SET SERVEROUTPUT ON
SET HEAD OFF
SET FEEDBACK OFF

SELECT (SYSDATE - 1) FROM DUAL
;

EOW
`

w_end=`echo $t_end|tail -1`

rep_subject="$SUBJECT for week ending $w_end"

#################
# 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 Week Ending $w_end'

COLUMN day HEADING 'Day'
COLUMN day FORMAT A12
COLUMN log HEADING 'Redo Logs (#)'
COLUMN log FORMAT 999
COLUMN mb  HEADING 'Redo Size (MB)'
COLUMN mb  FORMAT 999,999,999
COLUMN arch HEADING 'Arch Mode (#)'
COLUMN arch FORMAT 9,999
COLUMN lgwr HEADING 'LGWR Mode (#)'
SELECT TO_CHAR(TRUNC(completion_time),'DY MON DD') as Day, count(*) \
  as log, (SUM(blocks))*(512/1048576) as mb, SUM(DECODE(SUBSTR(name,16,4),'stan',1)) as arch, SUM(DECODE(SUBSTR(name,16,4),'arch',1)) as lgwr
FROM v\$archived_log 
WHERE TRUNC(completion_time) >= TRUNC(SYSDATE - 7)
AND TRUNC(completion_time) < TRUNC(SYSDATE)
--
--  Next AND clause only needed if a 2nd Standby is used
--
--  AND name <> 'prod.2ndstandby'
GROUP BY TRUNC(completion_time)
;

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 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"