|
下面的 script可以利用cron排程来自动执行清除超过保留数目的旧有snapshot资料这个script不需要知道PERFSTAT此帐号的密码就可执行并已经经由Oracle和上测试过 步骤 )储存这个script取名为sp_purgeksh在Unix主机上 )注意你的系统上tmp目录是否存在如果你不想所有产生的log写到/tmp去你必须去更改script )如果你的oratab这个目录位置不是在/var/opt/oracle你就必须手动去更新script来配合你的环境 )设定可执行权限给script chmod u+x sp_purgeksh )设定cron job来执行这个script执行这个script需要三个参数 要清除 snapshot的资料库名称 要保留的 snapshot数量 执行后要寄发电子邮件的对象 * * /scripts/sp_purgeksh prod >>/tmp/sp_purge_portallog >& & 这个范例是说星期一到星期五每天晚上七点执行此 script针对 prod 这个资料库只保留最近的个snapshots纪录多余的则清除并且寄发讯息给 )注意这个 script应该配合指定的instance一起执行如果这台主机上并没有这个script所指定的instance在执行中一个简单的讯息可在tmp目录下找到 *** ERROR: The ORACLE_SID specified in parameter is not a valid SID (Note that the SID is case sensitive) )所有产生的执行纪录都可以在/tmp下找到 #!/bin/ksh # Script Name: sp_purgeksh # This script is designed to purge StatsPack snapshots # # Parameter $ is the name of the database # Parameter $ is the maximum number of snapshots to retain # Parameter $ 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_purgeksh prod us # Script History: # # Who Date Action # # Mark J Rogers Sep Script creation # # # tmp_dir=/tmp # Validate the parameters if [[ $# ne ]]; then echo echo *** ERROR: You must specify these parameters: echo echo : the name of the database echo : the maximum # of snapshots to retain echo : the mail recipient for success messages echo exit fi grep ^${}: /var/opt/oracle/oratab >> /dev/null if [[ $? ne ]]; then echo echo *** ERROR: The ORACLE_SID specified in parameter is not a valid SID echo (Note that the SID is case sensitive) echo exit fi if [[ ! (${} ge ) ]]; then echo echo *** ERROR: Parameter must specify the # of snapshots to retain echo exit fi # Ensure that the instance is running on the current machine ps ef | grep pmon | grep $ >> /dev/null if [[ $? ne ]]; then echo echo *** ERROR: Instance $ 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 fi # Establish error handling for this UNIX script function errtrap { the_status=$? echo echo *** ERROR: Error message $the_status occured on line number $ echo echo *** The script is aborting echo exit $the_status } trap
errtrap $LINENO
ERR # Set up the Oracle environment export ORACLE_SID=${} export ORAENV_ASK=NO oraenv script_name=${##*/} 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 $ StatsPack snapshots echo tmp_script=${tmp_dir}/sp_purge_tmp_$ORACLE_SIDksh # script to actually purge tmp_output=${tmp_dir}/sp_purge_tmp_$ORACLE_SIDout # 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 := ${}; :P_LOSNAPID := ; :P_HISNAPID := ; 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 := ; 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 ddbid iinstance_number INTO v_DBID V_INSTANCE_NUMBER from v$database d v$instance i; select count(snap_id) into v_count from perfstatstats$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_OUTPUTPUT_LINE (NOTE: There are only || to_char(v_count) || snapshots currently saved); ELSE We DO need to perform a purge DBMS_OUTPUTPUT_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 perfstatstats$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 perfstatstats$snapshot WHERE dbid = V_DBID AND instance_number = V_INSTANCE_NUMBER ORDER BY SNAP_ID DESC) LOOP V_COUNTER := V_COUNTER + ; IF V_COUNTER > V_SNAPS_TO_RETAIN THEN V_HISNAPID := V_HISNAPID_RECSNAP_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 spool $tmp_script begin IF (:P_LOSNAPID <> ) THEN /* Build the script to purge the StatsPack snapshots */ dbms_outputput_line(#!/bin/ksh); dbms_outputput_line(#THIS IS THE SCRIPT TO ACTUALLY PERFORM THE PURGE); dbms_outputput_line(trap exit $? ERR); dbms_outputput_line(sqlplus s << SP_EOF); dbms_outputput_line(/ as sysdba); dbms_outputput_line(whenever sqlerror exit failure rollback); dbms_outputput_line(whenever oserror exit failure rollback); dbms_outputput_line(@ $ORACLE_HOME/rdbms/admin/sppurgesql); dbms_outputput_line(:P_LOSNAPID); dbms_outputput_line(:P_HISNAPID); dbms_outputput_line( the following are needed again); dbms_outputput_line(whenever sqlerror exit failure rollback); dbms_outputput_line(whenever oserror exit failure rollback); dbms_outputput_line(commit;); dbms_outputput_line(exit); dbms_outputput_line(SP_EOF); dbms_outputput_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 fi if [[ `cat ${tmp_script} | wc l` ne ]]; 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 ]]; then echo echo *** ERROR: The purge did not complete successfully echo Check the log file $tmp_output echo exit fi trap errtrap $LINENO ERR # reestablish 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_purgeksh in $ORACLE_SID on `uname n` completed successfully ${} < $tmp_output # End of script sp_purgeksh |
