自动清除statspack所产生的snapshot旧记录

　　下面的 script可以利用cron排程来自动执行清除超过保留数目的旧有snapshot资料，这个script不需要知道PERFSTAT此帐号的密码就可执行，并已经经由Oracle8.1.7和9.2.0上测试过。
　　
　　步骤：
　　
　　1)储存这个script取名为sp_purge.ksh在Unix主机上。
　　
　　2)注重你的系统上tmp目录是否存在，假如你不想所有产生的log写到/tmp去，你必须去更改script。
　　
　　3)假如你的oratab这个目录位置不是在/var/opt/oracle，你就必须手动去更新script来配合你的环境。
　　
　　4)设定可执行权限给script： chmod u+x sp_purge.ksh
　　
　　5)设定cron job来执行这个script。执行这个script需要三个参数：
　　
　　要清除 snapshot的资料库名称。
　　
　　要保留的 snapshot数量。
　　
　　执行后要寄发电子邮件的对象。
　　
　　00 19 * * 1-5 /scripts/sp_purge.ksh prod 60 mrogers@company.com >>/tmp/sp_purge_portal.log 2>&1 &
　　
　　这个范例是说：星期一到星期五天天晚上七点执行此 script，针对 'prod' 这个资料库只保留最近的60个snapshots纪录，多余的则清除，并且寄发讯息给 mrogers@company.com 。
　　
　　6)注重这个 script应该配合指定的instance一起执行，假如这台主机上并没有这个script所指定的instance在执行中，一个简单的讯息可在tmp目录下找到。
　　
　　*** ERROR: The ORACLE_SID specified in parameter 1 is not a valid SID.
　　
　　(Note that the SID is case sensitive.)
　　
　　7)所有产生的执行纪录都可以在/tmp下找到。
　　
　　#!/bin/ksh
　　# Script Name: sp_purge.ksh
　　# This script is designed to purge StatsPack snapshots.
　　#
　　# Parameter $1 is the name of the database.
　　# Parameter $2 is the maximum number of snapshots to retain.
　　# Parameter $3 is the mail recipient for sUCcess messages.
　　#
　　# To succeed, this script must be run on the machine on which the
　　# instance is running.
　　# Example for calling this script:
　　#
　　# sp_purge.ksh prod 30 username@mycompany.com
　　# Script History:
　　#
　　# Who Date Action
　　# --------------- ------------ --------------------------------------------
　　# Mark J. Rogers 22-Sep-2003 Script creation.
　　#
　　#
　　#
　　tmp_dir=/tmp
　　# Validate the parameters.
　　
　　if [[ $# -ne 3 ]]; then
　　echo ""
　　echo "*** ERROR: You must specify these parameters: "
　　echo ""
　　echo " 1: the name of the database"
　　echo " 2: the maximum # of snapshots to retain"
　　echo " 3: the mail recipient for success messages"
　　echo ""
　　exit 1
　　fi
　　
　　grep "^${1}:" /var/opt/oracle/oratab >> /dev/null
　　if [[ $? -ne 0 ]]; then
　　echo ""
　　echo "*** ERROR: The ORACLE_SID specified in parameter 1 is not a valid SID."
　　echo " (Note that the SID is case sensitive.)"
　　echo ""
　　exit 1
　　fi
　　
　　if [[ ! (${2} -ge 0) ]]; then
　　echo ""
　　echo "*** ERROR: Parameter 2 must specify the # of snapshots to retain."
　　echo ""
　　exit 1
　　fi
　　
　　# Ensure that the instance is running on the current machine.
　　ps -ef grep pmon grep $1 >> /dev/null
　　if [[ $? -ne 0 ]]; then
　　echo ""
　　echo "*** ERROR: Instance $1 is not running on machine `uname -n` "
　　echo " on `date`."
　　echo " The instance must be running on the current machine for this"
　　echo " script to function properly."
　　echo ""
　　echo " Exiting..."
　　echo ""
　　exit 1
　　fi
　　
　　# Establish error handling for this UNIX script.
　　function errtrap {
　　the_status=$?
　　echo ""
　　echo " *** ERROR: Error message $the_status occured on line number $1."
　　echo ""
　　echo " *** The script is aborting."
　　echo ""
　　exit $the_status
　　}
　　
　　trap
　　'
　　errtrap $LINENO
　　'
　　ERR
　　
　　# Set up the Oracle environment.
　　
　　eXPort ORACLE_SID=${1}
　　export ORAENV_ASK=NO
　　. oraenv
　　
　　script_name=${0##*/}
　　echo ""
　　echo "Script: $script_name"
　　echo " started on: `date`"
　　echo " by user: `id`"
　　echo " on machine: `uname -n`"
　　echo ""
　　echo "This script is designed to purge StatsPack snapshots for the "
　　echo " $ORACLE_SID database."
　　echo ""
　　echo "You have requested to retain no more than $2 StatsPack snapshots."
　　echo ""
　　
　　tmp_script=${tmp_dir}/sp_purge_tmp_$ORACLE_SID.ksh # script to actually purge
　　tmp_output=${tmp_dir}/sp_purge_tmp_$ORACLE_SID.out # output to be mailed
　　
　　rm -f $tmp_script
　　rm -f $tmp_output
　　
　　sqlplus -s <<EOF_SP<br />/ as sysdba
　　
　　whenever sqlerror exit failure rollback
　　whenever oserror exit failure rollback
　　
　　SET SERVEROUTPUT ON
　　SET FEEDBACK OFF
　　
　　VARIABLE P_SNAPS_TO_RETAIN NUMBER
　　VARIABLE P_LOSNAPID NUMBER
　　VARIABLE P_HISNAPID NUMBER
　　
　　BEGIN
　　/* Assign values to these variables. */
　　:P_SNAPS_TO_RETAIN := ${2};
　　:P_LOSNAPID := -1;
　　:P_HISNAPID := -1;
　　END;
　　/
　　
　　-- Identify the snapshot ids to purge, if any.
　　
　　DECLARE
　　
　　V_LOSNAPID NUMBER := NULL; -- Low snapshot ID to purge.
　　V_HISNAPID NUMBER := NULL; -- High snapshot ID to purge.
　　V_COUNT NUMBER := NULL; -- Number of snapshots current saved.
　　V_COUNTER NUMBER := 0; -- Temporary counter variable.
　　V_DBID NUMBER := NULL; -- Current database ID.
　　V_INSTANCE_NUMBER NUMBER := NULL; -- Current instance number.
　　V_SNAPS_TO_RETAIN NUMBER := :P_SNAPS_TO_RETAIN; -- Max snaps to retain.
　　
　　BEGIN
　　
　　select
　　d.dbid,
　　i.instance_number
　　INTO
　　v_DBID,
　　V_INSTANCE_NUMBER
　　from
　　v$database d,
　　v$instance i;
　　
　　select
　　count(snap_id)
　　into
　　v_count
　　from
　　perfstat.stats$snapshot
　　where
　　dbid = V_DBID AND
　　instance_number = V_INSTANCE_NUMBER;
　　
　　IF V_COUNT <= V_SNAPS_TO_RETAIN THEN
　　
　　-- We do NOT need to perform a purge.
　　
　　DBMS_OUTPUT.PUT_LINE ('NOTE: There are only '
　　to_char(v_count) ' snapshots currently saved.');
　　
　　ELSE
　　
　　-- We DO need to perform a purge.
　　
　　DBMS_OUTPUT.PUT_LINE ('There are currently '
　　to_char(v_count) ' snapshots saved.');
　　
　　-- OBTain the low snapshot id to be purged.
　　
　　select
　　min(snap_id)
　　into
　　V_LOSNAPID
　　from
　　perfstat.stats$snapshot
　　where
　　dbid = V_DBID AND
　　instance_number = V_INSTANCE_NUMBER;
　　
　　-- Obtain the high snapshot id to be purged.
　　
　　FOR V_HISNAPID_REC IN
　　(SELECT
　　SNAP_ID
　　FROM
　　perfstat.stats$snapshot
　　WHERE
　　dbid = V_DBID AND
　　instance_number = V_INSTANCE_NUMBER
　　ORDER BY
　　SNAP_ID DESC)
　　LOOP
　　V_COUNTER := V_COUNTER + 1;
　　IF V_COUNTER > V_SNAPS_TO_RETAIN THEN
　　V_HISNAPID := V_HISNAPID_REC.SNAP_ID;
　　EXIT; -- Exit this LOOP and proceed to the next statement.
　　END IF;
　　END LOOP;
　　
　　:P_LOSNAPID := V_LOSNAPID;
　　:P_HISNAPID := V_HISNAPID;
　　
　　END IF;
　　
　　END;
　　/
　　
　　prompt
　　-- Generate the specific purge script.
　　set linesize 60
　　spool $tmp_script
　　begin
　　IF (:P_LOSNAPID <> -1) THEN
　　/* Build the script to purge the StatsPack snapshots. */
　　dbms_output.put_line('#!
/bin/ksh');
　　dbms_output.put_line('#THIS IS THE SCRIPT TO ACTUALLY PERFORM THE PURGE');
　　dbms_output.put_line('trap '' exit $? '' ERR');
　　dbms_output.put_line('sqlplus -s << SP_EOF2');
　　dbms_output.put_line('/ as sysdba');
　　dbms_output.put_line('whenever sqlerror exit failure rollback');
　　dbms_output.put_line('whenever oserror exit failure rollback');
　　dbms_output.put_line('@ $ORACLE_HOME/rdbms/admin/sppurge.sql');
　　dbms_output.put_line(:P_LOSNAPID);
　　dbms_output.put_line(:P_HISNAPID);
　　dbms_output.put_line('-- the following are needed again');
　　dbms_output.put_line('whenever sqlerror exit failure rollback');
　　dbms_output.put_line('whenever oserror exit failure rollback');
　　dbms_output.put_line('commit;');
　　dbms_output.put_line('exit');
　　dbms_output.put_line('SP_EOF2');
　　dbms_output.put_line('exit $?');
　　END IF;
　　end;
　　/
　　spool off
　　
　　exit
　　EOF_SP
　　
　　if [[ ! (-f ${tmp_script}) ]]; then
　　echo ""
　　echo "*** ERROR: Temporary script: ${tmp_script} does not exist."
　　echo ""
　　exit 1
　　fi
　　
　　if [[ `cat ${tmp_script} wc -l` -ne 0 ]]; then
　　# Execute the newly generated StatsPack snapshot purge script.
　　chmod u+x $tmp_script
　　echo ""
　　echo "Performing the purge..."
　　echo ""
　　$tmp_script > $tmp_output
　　cat $tmp_output # display the output
　　# Check the output file for a success message:
　　trap ' ' ERR # temporarily reset error handling for the grep command
　　grep "^Purge of specified Snapshot range complete." $tmp_output >> /dev/null
　　if [[ $? -ne 0 ]]; then
　　echo ""
　　echo "*** ERROR: The purge did not complete successfully."
　　echo " Check the log file $tmp_output."
　　echo ""
　　exit 1
　　fi
　　trap ' errtrap $LINENO ' ERR # re-establish desired error handler
　　else
　　# No purge script was created.
　　echo "No snapshot purge was necessary." > $tmp_output
　　fi
　　
　　echo ""
　　echo "The ${script_name} script appears to have completed "
　　echo " successfully on `date`."
　　echo ""
　　
　　mailx
　　-s "sp_purge.ksh in $ORACLE_SID on `uname -n` completed successfully"
　　${3}
　　< $tmp_output
　　
　　# End of script sp_purge.ksh.