Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Statspack Purge Shell script aborting

Posted on 2009-04-30
3
Medium Priority
?
1,053 Views
Last Modified: 2013-12-26
Hi all,

Script below is not running in crontab whereas it is running normally manually.
Would you have an idea?

Thank you

#!/bin/ksh
#-------------------------------------------------------------------------------------------------
# NAME    : wdms_purge_statspack.ksh
#
# FUNCTION :  Purge statspack
#
# PARAMETER : 1 : Instance name
# PARAMETER  : 2 : Number of snapshots to retain
# AUTHOR   : BLD       01/O3/2008
# MODIF    : BLD        29/03/2009
#-------------------------------------------------------------------------------------------------
#
#
tmp_dir=/tmp
# Validate the parameters.
if [[ $# -ne 2 ]]; 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 ""
   exit 1
fi

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}

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
conn / 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
## Reorg tablespace tools
sqlplus -s <<EOF_SP
/ as sysdba
spool $tmp_script
alter session set current_schema=PERFSTAT;
alter table stats\$sql_summary move tablespace tools;
/
spool off
exit
echo ""
echo "The ${script_name} script appears to have completed "
echo " successfully on `date`."
echo ""
# End of script sp_purge.ksh.

The error is    *** ERROR: Error message 127 occured on line number 96.

   *** The script is aborting.

Thanks

Regards

Bibi
0
Comment
Question by:bibi92
  • 2
3 Comments
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 24269743
Hi,
cron doesn't supply an environment as a login shell does.
Consequently, there are no environment variables, and no PATH.
Two ways to circumvent -
1) at the beginning of your script "source" a profile containing all the environment needed,
2) run the scritp as root, and in crontab enter
* * * * * su - oracleuser -c scriptname
 
0
 
LVL 68

Accepted Solution

by:
woolmilkporc earned 2000 total points
ID: 24269759
To be specific, under 2) I meant to say "run the script from root's crontab"
0
 

Author Closing Comment

by:bibi92
ID: 31576413
Thanks a lot.

Regards
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, an awarded photographer, Selina De Maeyer (http://www.selinademaeyer.com/), completed a photo shoot of a beautiful event (http://www.sintjacobantwerpen.be/verslag-en-fotoreportage-van-de-sacramentsprocessie-door-antwerpen#thumbnails) in An…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question