The Oracle (tm) Users' Co-Operative FAQ

How do you purge old statspack snapshots automatically ?


Author's name: Mark Rogers

Author's Email:rogersmarkj@yahoo.com

Date written: 20th October 2003

Oracle version(s): 8i, 9i

You want to collect StatsPack snapshots regularly, right? But perhaps you don’t collect them because you’re concerned (as was I) that you will forget to purge the old snapshot data. Oracle’s snapshot purge script can only be run manually (even though it is documented to work in batch mode). So, how do you purge snapshot data automatically?

Back to index of questions


Below is a script that may be run via cron which will purge all old snapshots that exceed the specified count. This script requires no knowledge of the password for the PERFSTAT user. The script has been tested with Oracle 8.1.7 and 9.2.0.

To implement this script, do the following:

1)      Save the script (below) as sp_purge.ksh to each Unix machine on which the Oracle instance may be located.

2)      If the Unix tmp directory is anything other than /tmp then you will need to modify the script accordingly.

3)      If your oratab file is located in any directory other than /var/opt/oracle then you will need to update the script accordingly.

4)      Set execute privilege on the script: chmod u+x sp_purge.ksh

5)      Establish a cron job to call the script. The script requires three parameters:

        The name of the database in which to purge snapshots.

        The maximum number of snapshots to retain.

        The email recipient for success messages.

Here is an example cron entry:

00 19 * * 1-5 /scripts/sp_purge.ksh prod 60 mrogers@company.com >>/tmp/sp_purge_portal.log 2>&1 &

This entry causes the script to run at 19:00 each weekday, to retain no more than 60 snapshots for the ‘prod’ database, and send success messages to: mrogers@company.com

6)      Note that this script may be invoked on any machine on which the instance may run. If the instance is not on the current machine, then a simple message to that effect will be sent to a file in the tmp directory.

7)      Note also that all log files are written to the tmp directory.

 

 

Automatic StatsPack snapshot purge script:
-----------------------CUT----------CUT----------CUT-------------------------
#!/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
/ 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.
-----------------------CUT----------CUT----------CUT-------------------------

Further reading: N/A


Back to top

Back to index of questions