Oracle AWR reports Auto-generated and FTP scripts

The script consists mainly of the following parts:

Awr.sql the script that is used to execute in Sqlplus, which is found directly from the Web.

Awr.sh is used to call Sqlplus to generate an AWR report.

Ftp.sh is used to package and compress the AWR reports generated daily (compression rate is greater than 50 times times), and FTP transfer, cleaning out overdue reports, is slightly different for Linux and Solaris.

Crontab is used to perform timed tasks and adjust according to requirements.

The following is a specific script content that can be adjusted as needed.

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 REM According to your needs, the value can ' text ' or ' htm L ' define report_type= ' HTML '; begin15:rpt_options: = &no_options;16 end;17/18 variable dbid number;20 variabl E inst_num number;21 variable bid number;22 variable eid number;23 begin24 select Max (snap_id)-1 into:bid from dba_hist_s NAPSHOT;25 select Max (snap_id) Into:eid from dba_hist_snapshot;26 select dbid to:d bid from v$database;27 Select Instan Ce_number Into:inst_num from v$instance;28 end;29/30 to column ext new_value ext noprint32 column Fn_name new_value fn_n Ame noprint;33 column Lnsz new_value lnsz noprint;34 select ' txt ' ext from dual where lower (' &report_type ') = ' text '; $ Select ' HTML ' ext from dual where lower (' &report_type ') = ' html '; Notoginseng select ' Awr_report_text ' Fn_name from dual WH ere Lower (' &report_type ') = ' text '; awr_report_html ' fn_name from dual where lower (' &report_type ') = ' html '; Z from dual where lower (' &report_type ') = ' text '; "Lnsz from dual where lower (' &report_type ') = ' HT ML '; &lnsz;43 set linesize report_name new_value report_name noprint;45-select ' sp_ ' | |:bid| | ' _ ' | |:eid| | '. ' | | ' &ext ' Report_name from dual;47 set termout off;48 spool &report_name;49-Select output from table (Dbms_workload_ Repository.&fn_name (:d bid,: inst_num,:bid,: eid,:rpt_options)); spool off;52 set termout on;53 clear Columns Sql;5 4 ttitle off;55 btitle off;56 repfooter off;57 undefine report_name58 undefine report_type59 undefine fn_name60 undefine L nsz61 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 version):

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 9 user username Passwd10 binary11 lcd/opt/awr12 cd/ftp/orcl13 put awr_${mydate}.tar.gz14 close15 b Ye16!17 echo "=======================ftp end============================" echo "=================delete the tar file ===================== "rm awr_${mydate}.tar.gz20 echo" =================delete the tar file end===================== "Echo" =================delete the old file ===================== "find ${mydir}-name" *.html "-type f-mtime +3-ex EC RM {} \;23 echo "=================delete the old file end====================="

ftp.sh (Solaris version 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========================= "Ftp-n<<!11 open 11.11.11.11 2112 user username passwd13 binary14 lcd/opt/awr15 c D/ftp/orcl16 put awr_${mydate}.tar.gz17 close18 bye19!20 echo "=======================ftp end======================== = = "echo" =================delete the tar and temp file===================== "from RM awr_${mydate}.tar23 RM TODAY24 RM A Wr_${mydate}.tar.gz25 echo "=================delete the tar and temp file end=====================" echo "============  =====delete the old file ===================== "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 * * * * sh/opt/awr/ftp.sh

Oracle AWR reports Auto-generated and FTP scripts