94 lines
2.6 KiB
Bash
94 lines
2.6 KiB
Bash
#!/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
|