脚本主要由以下几个部分组成：
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 begin 15 :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 begin 24 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 noprint 32 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_name 58 undefine report_type 59 undefine fn_name 60 undefine lnsz 61 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 exit 10 !
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 passwd 10 binary 11 lcd /opt/awr 12 cd /ftp/orcl 13 put awr_${mydate}.tar.gz 14 close 15 bye 16 ! 17 echo "=======================FTP end============================" 18 echo "=================delete the tar file=====================" 19 rm awr_${mydate}.tar.gz 20 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 21 12 user username passwd 13 binary 14 lcd /opt/awr 15 cd /ftp/orcl 16 put awr_${mydate}.tar.gz 17 close 18 bye 19 ! 20 echo "=======================FTP end============================" 21 echo "=================delete the tar and temp file=====================" 22 rm awr_${mydate}.tar 23 rm TODAY 24 rm awr_${mydate}.tar.gz 25 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.sh 5 23 * * * sh /opt/awr/ftp.sh