熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

自動清除statspack所產生的snapshot舊記錄

2013-11-13 15:29:36  來源: Oracle 

  下面的 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
From:http://tw.wingwit.com/Article/program/Oracle/201311/16809.html
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.