#!/bin/bash
######################################################################
# Author       : Vishal Gupta
# Date Created : 13-Jul-2008
# Purpose      : To purge statspack data from consolidated statspack
#                repository
#
# Modification History
# Date      Ver Author         Remarks
# --------  --- -------------- ---------------------------------------
# 13/07/08  0.1 Vishal Gupta   First Draft
######################################################################

ORACLE_ENV_TYPE=$1
PERFSTAT_VERSION=$2
INSTANCE_NAME="$3"
ORACLE_SID=STATSREP
EMAILS="fm.eu.DBAAlerts@rabobank.com"
#EMAILS="vishal.gupta@rabobank.com"
TIMESTAMP=`date +%Y%m%d_%H%M%S`
LOGFILE=/appl/oracle/admin/logs/statspack_consolidation_purge_${ORACLE_ENV_TYPE}_${PERFSTAT_VERSION}_$TIMESTAMP.log

export ORACLE_HOME=/appl/oracle/product/10.2.0
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export NLS_LANG="ENGLISH_UNITED KINGDOM.WE8ISO8859P1"
export PATH=$ORACLE_HOME/bin:$PATH

function Usage()
{
  echo "`basename $0`  <envtype> <db_version> [<instance_name>] "
  echo ""
  echo "     where <envtype>         = PROD|TEST|DEV "
  echo "           <db_version>      =  10.2 | 9.2"
  echo "           [<instance_name>] =  optionally instance for single instance purging"
  exit 1;
}

if [ $# -lt 2 ]
then
  Usage;
fi


case "$ORACLE_ENV_TYPE" in
   PROD|TEST|DEV) ;;
   *) Usage;;
esac

case "$PERFSTAT_VERSION" in
   10.2|9.2) ;;
   *) Usage;;
esac


sqlplus /nolog > $LOGFILE <<EOF
conn perfstat_control/tun1ngstats@${ORACLE_SID}
WHENEVER SQLERROR EXIT SQLCODE
DECLARE
   lv_schema_suffix VARCHAR2(10);
BEGIN
  IF '$PERFSTAT_VERSION' = '10.2' THEN 
      lv_schema_suffix := '10G';
  ELSIF '$PERFSTAT_VERSION' = '9.2' THEN 
      lv_schema_suffix := '9I';
  END IF;
execute immediate 'alter session set current_schema = PERFSTAT_' || '$ORACLE_ENV_TYPE' || '_' || lv_schema_suffix ;
END;
/
set verify off
@`dirname $0`/statspack_consolidation_purge.sql "$ORACLE_ENV_TYPE" "$PERFSTAT_VERSION" "$INSTANCE_NAME"
EOF

if [ $? -eq 0 ]
then
   SUBJECT_SUFFIX="successful"
   HPOV_SEVERITY=NORMAL
else
   SUBJECT_SUFFIX="failed"
   HPOV_SEVERITY=MAJOR
fi


if [ ! -z "$INSTANCE_NAME" ]
then
  SUBJECT_SUFFIX="$INSTANCE_NAME - $SUBJECT_SUFFIX"
fi


#/opt/OV/bin/OpC/opcmsg severity=$HPOV_SEVERITY application=Oracle msg_grp=Oracle object="Statspack Consolidation"  msg_text="Statspack consolidation (PURGE) - $ORACLE_ENV_TYPE - $PERFSTAT_VERSION - $SUBJECT_SUFFIX"

mailx -s "Statspack consolidation (PURGE) - $ORACLE_ENV_TYPE - $PERFSTAT_VERSION - $SUBJECT_SUFFIX" "$EMAILS" <<EOF
Statspack consolidation (PURGE) - $ORACLE_ENV_TYPE - $PERFSTAT_VERSION - $SUBJECT_SUFFIX
EOF