Oracle AWR報告自動產生並ftp指令碼

標籤：blog   http   io   os   ar   sp   div   art   c   

指令碼主要由以下幾個部分組成：

awr.sql 用來在sqlplus 中執行的指令碼,該指令碼從網上直接找的。

awr.sh 用來調用sqlplus來產生awr報告。

ftp.sh 用來打包壓縮每天產生的awr報告（壓縮率大於50倍），並進行ftp傳輸，清理到期的報告，對於linux和solaris略有不同。

crontab 用來執行定時任務,根據需求進行調整。

下面為具體的指令碼內容，可以根據需要進行調整。

awr.sql :

 1 set echo off; 2 set veri off; 3 set feedback off; 4 set termout on; 5 set heading off; 6  7 variable rpt_options number; 8  9 define NO_OPTIONS = 0;10 -- define ENABLE_ADDM = 8;11 12 rem according to your needs, the value can be ‘text‘ or ‘html‘13 define report_type=‘html‘;14 begin15 :rpt_options := &NO_OPTIONS;16 end;17 /18 19 variable dbid number;20 variable inst_num number;21 variable bid number;22 variable eid number;23 begin24 select max(snap_id)-1 into :bid from dba_hist_snapshot;25 select max(snap_id) into :eid from dba_hist_snapshot;26 select dbid into :dbid from v$database;27 select instance_number into :inst_num from v$instance;28 end;29 /30 31 column ext new_value ext noprint32 column fn_name new_value fn_name noprint;33 column lnsz new_value lnsz noprint;34 35 select ‘txt‘ ext from dual where lower(‘&report_type‘) = ‘text‘;36 select ‘html‘ ext from dual where lower(‘&report_type‘) = ‘html‘;37 select ‘awr_report_text‘ fn_name from dual where lower(‘&report_type‘) = ‘text‘;38 select ‘awr_report_html‘ fn_name from dual where lower(‘&report_type‘) = ‘html‘;39 select ‘80‘ lnsz from dual where lower(‘&report_type‘) = ‘text‘;40 select ‘1500‘ lnsz from dual where lower(‘&report_type‘) = ‘html‘;41 42 set linesize &lnsz;43 44 column report_name new_value report_name noprint;45 46 select ‘sp_‘||:bid||‘_‘||:eid||‘.‘||‘&ext‘ report_name from dual;47 set termout off;48 spool &report_name;49 50 select output from table(dbms_workload_repository.&fn_name(:dbid, :inst_num,:bid, :eid,:rpt_options ));51 spool off;52 set termout on;53 clear columns sql;54 ttitle off;55 btitle off;56 repfooter off;57 undefine report_name58 undefine report_type59 undefine fn_name60 undefine lnsz61 undefine NO_OPTIONS

awr.sh:

 1 mydate=‘date +%y%m%d‘ 2 ORACLE_SID=orcl; export ORACLE_SID 3 ORACLE_BASE=/opt/app/ora11g; export ORACLE_BASE 4 ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME 5 cd /opt/awr 6 $ORACLE_HOME/bin/sqlplus /nolog<<! 7 connect / as sysdba; 8 @awr.sql 9 exit10 ! 

ftp.sh(RHEL6版本)：

 1 #!/usr/bin/bash 2 mydate=`date +%y%m%d` 3 mydir=/opt/awr 4 cd ${mydir} 5 find *.html -daystart -mtime -1 | xargs tar -zcvf awr_${mydate}.tar.gz 6 echo "======================FTP start=========================" 7 ftp -n<<! 8 open 11.11.11.11 21 9 user username passwd10 binary11 lcd /opt/awr12 cd /ftp/orcl13 put awr_${mydate}.tar.gz14 close15 bye16 !17 echo "=======================FTP end============================" 18 echo "=================delete the tar file====================="19 rm awr_${mydate}.tar.gz20 echo "=================delete the tar file end====================="21 echo "=================delete the old file ====================="22 find ${mydir} -name "*.html" -type f -mtime +3 -exec rm {} \;23 echo "=================delete the old file end====================="

ftp.sh (Solaris 10版本)：

 1 #!/usr/bin/sh 2 mydate=`date +%y%m%d` 3 mytoday=`date +%m%d` 4 mydir=/opt/awr 5 cd ${mydir} 6 touch ${mytoday}0000 TODAY 7 find *.html -newer TODAY | xargs tar -cvf awr_${mydate}.tar 8 gzip -c  awr_${mydate}.tar > awr_${mydate}.tar.gz 9 echo "======================FTP start========================="10 ftp -n<<!11 open 11.11.11.11 2112 user username passwd13 binary14 lcd /opt/awr15 cd /ftp/orcl16 put awr_${mydate}.tar.gz17 close18 bye19 !20 echo "=======================FTP end============================"21 echo "=================delete the tar and temp file====================="22 rm awr_${mydate}.tar23 rm TODAY24 rm awr_${mydate}.tar.gz25 echo "=================delete the tar and temp file end====================="26 echo "=================delete the old file ====================="27 find ${mydir} -name "*.html" -type f -mtime +0 -exec rm -rf {} \;28 echo "=================delete the old file end====================="

crontab：

0 0-23 * * * sh /opt/awr/awr.sh5 23 * * * sh /opt/awr/ftp.sh

Oracle AWR報告自動產生並ftp指令碼