Oracle AWR报告每天自动生成并发送邮箱.doc

Oracle AWR 报告 每天自动生成并发送邮箱一. 准备工作一般我们都是条用awrrpt.sql 来创建我们的AWR报告。 我们先看下这个脚本的具体内容：oraclerac1 admin$ cat awrrpt.sql | grep -v Rem|grep -v - set echo off heading on underline on;column inst_num heading Inst Num new_value inst_num format 99999;column inst_name heading Instance new_value inst_name format a12;column db_name heading DB Name new_value db_name format a12;column dbid heading DB Id new_value dbid format 9999999999 just c;promptprompt Current Instanceprompt select d.dbid dbid, db_name, i.instance_number inst_num, i.instance_name inst_namefrom v$database d,v$instance i;awrrptiundefine num_days;undefine report_type;undefine report_name;undefine begin_snap;undefine end_snap;在以上的脚本里，我们发现它只是生成了一些变量，然后把这些变量传给了另一个脚本：awrrpti.sql。 我们看下awrrpti.sql 脚本的具体内容：oraclerac1 admin$ cat awrrpti.sql | grep -v Rem|grep -v - set echo off;set veri off;set feedback off;variable rpt_options number;define NO_OPTIONS = 0;define ENABLE_ADDM = 8;begin:rpt_options := &NO_OPTIONS;end;/promptprompt Specify the Report Typeprompt prompt Would you like an HTML report, or a plain text report?prompt Enter html for an HTML report, or text for plain textprompt Defaults to htmlcolumn report_type new_value report_type;set heading off;select Type Specified: ,lower(nvl(&&report_type,html) report_type from dual;set heading on;set termout off;column ext new_value ext;select .html ext from dual where lower(&&report_type) <> text;select .txt ext from dual where lower(&&report_type) = text;set termout on;awrinput.sql - 这个脚本主要是确定SNAP的。awrinpnm.sql awrrpt_ &&ext- 这个脚本主要是确定AWR 文件名称的set termout off;column fn_name new_value fn_name noprint;select awr_report_text fn_name from dual where lower(&report_type) = text;select awr_report_html fn_name from dual where lower(&report_type) <> text;column lnsz new_value lnsz noprint;select 80 lnsz from dual where lower(&report_type) = text;select 1500 lnsz from dual where lower(&report_type) <> text;set linesize &lnsz;set termout on;spool &report_name;select output from table(dbms_workload_repository.&fn_name( :dbid,:inst_num,:bid, :eid,:rpt_options );spool off;prompt Report written to &report_name.set termout off;clear columns sql;ttitle off;btitle off;repfooter off;set linesize 78 termout on feedback 6 heading on;undefine report_nameundefine report_typeundefine extundefine fn_nameundefine lnszundefine NO_OPTIONSundefine ENABLE_ADDMundefine top_n_eventsundefine num_daysundefine top_n_sqlundefine top_pct_sqlundefine sh_mem_thresholdundefine top_n_segstatwhenever sqlerror continue;oraclerac1 admin$这个脚本才是我们真正生成AWR的脚本。 在这个脚本里面，提示我们选择AWR报告的类型。通过上面的2个脚本，我们将AWR报告简化一下：select output from table(dbms_workload_repository.&fn_name(:dbid, :inst_num,:bid, :eid,:rpt_options );这条语句就是整个AWR报告的核心：（1）&fn_name ：决定AWR报告的类型，有2个值：awr_report_html和awr_report_text。（2）dbid，inst_num,bid,eid 可以通过dba_hist_snapshot查询. bid 指的是begin snap_id, eid 指的是end snap_id.SQL> select * from (select snap_id,dbid,instance_number from dba_hist_snapshot order by snap_id) where rownum<10;SNAP_ID DBID INSTANCE_NUMBER- - -184 809910293 2184 809910293 1185 809910293 2185 809910293 1186 809910293 2186 809910293 1187 809910293 2187 809910293 1188 809910293 29 rows selected.我这里是个RAC 环境， 通过这个可以看出在每个节点上都保存着AWR的信息。（3）rpt_options：该参数控制是否显示ADDM的。- NO_OPTIONS - No options. Setting this will not show the ADDM- specific portions of the report.- This is the default setting.- ENABLE_ADDM - Show the ADDM specific portions of the report.- These sections include the Buffer Pool Advice,- Shared Pool Advice, PGA Target Advice, and- Wait Class sections.define NO_OPTIONS = 0;define ENABLE_ADDM = 8;有了上面的数据之后，我们就可以使用如下SQL直接生成AWR报告了。select output from table(dbms_workload_repository.awr_report_html(809910293, 2,220,230,0);select output from table(dbms_workload_repository.awr_report_text(809910293, 2,220,230,0);二. 生成AWR报告 SQL脚本以上写了这么多，就是为了一个脚本：myawrrpt.sql. 这个脚本就是自动的去收集信息。 因为如果我们是调用awrrpt.sql的话，需要输入一些参数。 我们修改一下脚本，让它根据我们的需求来收集信息，这样就不用输入参数了。oraclerac1 admin$ cat myawrrpt.sql conn / as sysdba;set echo off;set veri off;set feedback off;set termout on;set heading off;variable rpt_options number;define NO_OPTIONS = 0;define ENABLE_ADDM = 8;- according to your needs, the value can be text or htmldefine report_type=html;begin:rpt_options := &NO_OPTIONS;end;/variable dbid number;variable inst_num number;variable bid number;variable eid number;begin select max(snap_id)-48 into :bid from dba_hist_snapshot;select max(snap_id) into :eid from dba_hist_snapshot;select dbid into :dbid from v$database;select instance_number into :inst_num from v$instance;end;/column ext new_value ext noprintcolumn fn_name new_value fn_name noprint;column lnsz new_value lnsz noprint;-select txt ext from dual where lower(&report_type) = text;select html ext from dual where lower(&report_type) = html;-select awr_report_text fn_name from dual where lower(&report_type) = text;select awr_report_html fn_name from dual where lower(&report_type) = html;-select 80 lnsz from dual where lower(&report_type) = text;select 1500 lnsz from dual where lower(&report_type) = html;set linesize &lnsz;- print the AWR results into the report_name file using the spool command:column report_name new_value report_name noprint;select awr|.|&ext report_name from dual;set termout off;spool &report_name;select output from table(dbms_workload_repository.&fn_name(:dbid, :inst_num,:bid, :eid,:rpt_options );spool off;set termout on;clear columns sql;ttitle off;btitle off;repfooter off;undefine report_nameundefine report_typeundefine fn_nameundefine lnszundefine NO_OPTIONSexitoraclerac1 admin$这个脚本是收集过去48个小时的snap 来生成AWR。 生成的文件名称是awr .html，这个也是spool 指定的，可以生成其他名称。三. 自动上传AWR的Python脚本在这个脚本里做2件事，第一是调用第二步里的SQL脚本，生成awr报告，然后将AWR 发送到指定邮箱。createSendAWR.py#!/usr/bin/python#coding=gbk#created by tianlesoftware#2011-4-12import osimport sysimport smtplibimport pickleimport mimetypes from email.MIMEText import MIMEText from email.MIMEImage import MIMEImagefrom email.MIMEMultipart import MIMEMultipartSMTP_SERVER=20EMAIL_USER=userEMAIL_PASSWD=pwdEMAIL_SUBJECT=09 AWR ReportFROM_USER=TO_USERS=,def createawr():pipe = os.popen( /u01/app/oracle/product/10.2.0/db_1/bin/sqlplus /nolog awrrpt.sql)def mysendmail(fromaddr,toaddrs,subject):COMMASPACE=,msg = MIMEMultipart() msgFrom = fromaddrmsgTo = COMMASPACE.join(toaddrs)msgSubject = subjecttxt = MIMEText(09 AWR Report, The report be send at 9 AM every day ) msg.attach(txt) fileName = r/home/oracle/awr.htmlctype, encoding = mimetypes.guess_type(fileName) if ctype is None or encoding is not None: ctype = application/octet-streammaintype, subtype = ctype.split(/, 1) att =