How do you purge old statspack snapshots automatically

The Oracle (tm) Users' Co-Operative FAQ

How do you purge old statspack snapshots automatically ?

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