[p=24, null, left]如果说每天都去手工的出创建AWR报告，是个费时的活。 所以，最好就是弄成脚本来执行。 这篇文章就是说明这个问题的。[p=24, null, left] [p=24, null, left] 注意的一个问题，AWR 报告的两个snap 之间不能有重启DB的操作。[p=24, null, left] [p=24, null, left]Python 执行 系统命令[p=24, null, left][color=#696969]http://blog.csdn.net/tianlesoftware/archive/2011/02/17/6192202.aspx[p=24, null, left] [p=24, null, left]Python 发送带 附件 邮件 脚本[p=24, null, left][color=#696969]http://blog.csdn.net/tianlesoftware/archive/2011/04/12/6318199.aspx[p=24, null, left] [p=24, null, left] [p=24, null, left]一. 准备工作[p=24, null, left] [p=24, null, left] 一般我们都是条用awrrpt.sql 来创建我们的AWR报告。 我们先看下这个脚本的具体内容：[p=24, null, left][oracle@rac1 admin]$ cat awrrpt.sql | grep -v 'Rem'|grep -v '^--'[p=24, null, left]set echo off heading on underline on;[p=24, null, left]column inst_num heading "Inst Num" new_value inst_num format 99999;[p=24, null, left]column inst_name heading "Instance" new_value inst_name format a12;[p=24, null, left]column db_name heading "DB Name" new_value db_name format a12;[p=24, null, left]column dbid heading "DB Id" new_value dbid format 9999999999 just c;[p=24, null, left] [p=24, null, left]prompt[p=24, null, left]prompt Current Instance[p=24, null, left]prompt ~~~~~~~~~~~~~~~~[p=24, null, left] [p=24, null, left]select d.dbid dbid[p=24, null, left] , d.name db_name[p=24, null, left] , i.instance_number inst_num[p=24, null, left] , i.instance_name inst_name[p=24, null, left] from v$database d,[p=24, null, left] v$instance i;
[p=24, null, left] [p=24, null, left]@@awrrpti[p=24, null, left] [p=24, null, left]undefine num_days;[p=24, null, left]undefine report_type;[p=24, null, left]undefine report_name;[p=24, null, left]undefine begin_snap;[p=24, null, left]undefine end_snap;[p=24, null, left] [p=24, null, left] 在以上的脚本里，我们发现它只是生成了一些变量，然后把这些变量传给了另一个脚本：awrrpti.sql。 我们看下awrrpti.sql 脚本的具体内容：[p=24, null, left] [p=24, null, left][oracle@rac1 admin]$ cat awrrpti.sql | grep -v 'Rem'|grep -v '^--' [p=24, null, left] [p=24, null, left]set echo off;[p=24, null, left]set veri off;[p=24, null, left]set feedback off;[p=24, null, left] [p=24, null, left]variable rpt_options number;[p=24, null, left] [p=24, null, left]define NO_OPTIONS = 0;[p=24, null, left]define ENABLE_ADDM = 8;
[p=24, null, left] [p=24, null, left]begin[p=24, null, left] :rpt_options := &NO_OPTIONS;[p=24, null, left]end;[p=24, null, left]/[p=24, null, left] [p=24, null, left]prompt[p=24, null, left]prompt Specify the Report Type[p=24, null, left]prompt ~~~~~~~~~~~~~~~~~~~~~~~
[p=24, null, left]prompt Would you like an HTML report, or a plain text report?[p=24, null, left]prompt Enter 'html' for an HTML report, or 'text' for plain text[p=24, null, left]prompt Defaults to 'html'[p=24, null, left] [p=24, null, left]column report_type new_value report_type;[p=24, null, left]set heading off;[p=24, null, left]select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual;[p=24, null, left]set heading on;[p=24, null, left] [p=24, null, left]set termout off;[p=24, null, left]column ext new_value ext;[p=24, null, left]select '.html' ext from dual where lower('&&report_type') <> 'text';[p=24, null, left]select '.txt' ext from dual where lower('&&report_type') = 'text';
[p=24, null, left]set termout on;[p=24, null, left] [p=24, null, left]@@awrinput.sql
[p=24, null, left]-- 这个脚本主要是确定SNAP的。[p=24, null, left]@@awrinpnm.sql 'awrrpt_' &&ext[p=24, null, left]-- 这个脚本主要是确定AWR 文件名称的[p=24, null, left] [p=24, null, left]set termout off;[p=24, null, left]column fn_name new_value fn_name noprint;[p=24, null, left]select 'awr_report_text' fn_name from dual where lower('&report_type') = 'text';[p=24, null, left]select 'awr_report_html' fn_name from dual where lower('&report_type') <> 'text';[p=24, null, left] [p=24, null, left]column lnsz new_value lnsz noprint;[p=24, null, left]select '80' lnsz from dual where lower('&report_type') = 'text';[p=24, null, left]select '1500' lnsz from dual where lower('&report_type') <> 'text';[p=24, null, left] [p=24, null, left]set linesize &lnsz;[p=24, null, left]set termout on;[p=24, null, left]spool &report_name;[p=24, null, left] [p=24, null, left]select output from table(dbms_workload_repository.&fn_name( :dbid,[p=24, null, left] :inst_num,[p=24, null, left] :bid, :eid,[p=24, null, left] :rpt_options ));
[p=24, null, left] [p=24, null, left]spool off;[p=24, null, left] [p=24, null, left]prompt Report written to &report_name.[p=24, null, left] [p=24, null, left]set termout off;[p=24, null, left]clear columns sql;[p=24, null, left]ttitle off;[p=24, null, left]btitle off;[p=24, null, left]repfooter off;[p=24, null, left]set linesize 78 termout on feedback 6 heading on;[p=24, null, left]undefine report_name[p=24, null, left] [p=24, null, left]undefine report_type[p=24, null, left]undefine ext[p=24, null, left]undefine fn_name[p=24, null, left]undefine lnsz[p=24, null, left] [p=24, null, left]undefine NO_OPTIONS[p=24, null, left]undefine ENABLE_ADDM[p=24, null, left] [p=24, null, left]undefine top_n_events[p=24, null, left]undefine num_days[p=24, null, left]undefine top_n_sql[p=24, null, left]undefine top_pct_sql[p=24, null, left]undefine sh_mem_threshold[p=24, null, left]undefine top_n_segstat[p=24, null, left] [p=24, null, left]whenever sqlerror continue;[p=24, null, left][oracle@rac1 admin]$[p=24, null, left] [p=24, null, left] [p=24, null, left] 这个脚本才是我们真正生成AWR的脚本。 在这个脚本里面，提示我们选择AWR报告的类型。[p=24, null, left] [p=24, null, left] [p=24, null, left]通过上面的2个脚本，我们将AWR报告简化一下：[p=24, null, left] select output from[p=24, null, left]table(dbms_workload_repository.&fn_name(:dbid, :inst_num,:bid, :eid,:rpt_options ));[p=24, null, left] [p=24, null, left]这条语句就是整个AWR报告的核心：[p=24, null, left]（1）&fn_name ：决定AWR报告的类型，有2个值：awr_report_html和awr_report_text。[p=24, null, left]（2）dbid，inst_num,bid,eid 可以通过dba_hist_snapshot查询. bid 指的是begin snap_id, eid 指的是end snap_id.[p=24, null, left] [p=24, null, left] [p=24, null, left]SQL> select * from (select snap_id,dbid,instance_number from dba_hist_snapshot order by snap_id) where rownum<10;[p=24, null, left] [p=24, null, left] SNAP_ID DBID INSTANCE_NUMBER[p=24, null, left]---------- ---------- ---------------[p=24, null, left] 184 809910293 2[p=24, null, left] 184 809910293 1[p=24, null, left] 185 809910293 2[p=24, null, left] 185 809910293 1[p=24, null, left] 186 809910293 2[p=24, null, left] 186 809910293 1[p=24, null, left] 187 809910293 2[p=24, null, left] 187 809910293 1[p=24, null, left] 188 809910293 2[p=24, null, left] [p=24, null, left]9 rows selected.[p=24, null, left] [p=24, null, left] 我这里是个RAC 环境， 通过这个可以看出在每个节点上都保存着AWR的信息。[p=24, null, left] [p=24, null, left]（3）rpt_options：该参数控制是否显示ADDM的。[p=24, null, left]-- NO_OPTIONS -[p=24, null, left]-- No options. Setting this will not show the ADDM[p=24, null, left]-- specific portions of the report.[p=24, null, left]-- This is the default setting.[p=24, null, left]--[p=24, null, left]-- ENABLE_ADDM -[p=24, null, left]-- Show the ADDM specific portions of the report.[p=24, null, left]-- These sections include the Buffer Pool Advice,[p=24, null, left]-- Shared Pool Advice, PGA Target Advice, and[p=24, null, left]-- Wait Class sections.[p=24, null, left]define NO_OPTIONS = 0;[p=24, null, left]define ENABLE_ADDM = 8;[p=24, null, left] [p=24, null, left] [p=24, null, left]有了上面的数据之后，我们就可以使用如下SQL直接生成AWR报告了。[p=24, null, left]SQL>select output from table(dbms_workload_repository.awr_report_html(809910293, 2,220,230,0));[p=24, null, left] [p=24, null, left]SQL>select output from table(dbms_workload_repository.awr_report_text(809910293, 2,220,230,0));[p=24, null, left] [p=24, null, left] [p=24, null, left]二. 生成AWR报告 SQL脚本[p=24, null, left] 以上写了这么多，就是为了一个脚本：myawrrpt.sql. 这个脚本就是自动的去收集信息。 因为如果我们是调用awrrpt.sql的话，需要输入一些参数。我们修改一下脚本，让它根据我们的需求来收集信息，这样就不用输入参数了。[p=24, null, left] [p=24, null, left][oracle@rac1 admin]$ cat myawrrpt.sql[p=24, null, left]conn / as sysdba;[p=24, null, left]set echo off;[p=24, null, left]set veri off;[p=24, null, left]set feedback off;[p=24, null, left]set termout on;[p=24, null, left]set heading off;[p=24, null, left] [p=24, null, left]variable rpt_options number;[p=24, null, left] [p=24, null, left]define NO_OPTIONS = 0;[p=24, null, left]define ENABLE_ADDM = 8;[p=24, null, left] [p=24, null, left]-- according to your needs, the value can be 'text' or 'html'[p=24, null, left]define report_type='html';[p=24, null, left]begin[p=24, null, left]:rpt_options := &NO_OPTIONS;[p=24, null, left]end;[p=24, null, left]/[p=24, null, left] [p=24, null, left]variable dbid number;[p=24, null, left]variable inst_num number;[p=24, null, left]variable bid number;[p=24, null, left]variable eid number;[p=24, null, left]begin[p=24, null, left]select max(snap_id)-48 into :bid from dba_hist_snapshot;[p=24, null, left]select max(snap_id) into :eid from dba_hist_snapshot;[p=24, null, left]select dbid into :dbid from v$database;[p=24, null, left]select instance_number into :inst_num from v$instance;[p=24, null, left]end;[p=24, null, left]/[p=24, null, left] [p=24, null, left]column ext new_value ext noprint[p=24, null, left]column fn_name new_value fn_name noprint;[p=24, null, left]column lnsz new_value lnsz noprint;[p=24, null, left] [p=24, null, left]--select 'txt' ext from dual where lower('&report_type') = 'text';[p=24, null, left]select 'html' ext from dual where lower('&report_type') = 'html';[p=24, null, left]--select 'awr_report_text' fn_name from dual where lower('&report_type') = 'text';[p=24, null, left]select 'awr_report_html' fn_name from dual where lower('&report_type') = 'html';[p=24, null, left]--select '80' lnsz from dual where lower('&report_type') = 'text';[p=24, null, left]select '1500' lnsz from dual where lower('&report_type') = 'html';[p=24, null, left] [p=24, null, left]set linesize &lnsz;[p=24, null, left] [p=24, null, left]-- print the AWR results into the report_name file using the spool command:[p=24, null, left] [p=24, null, left]column report_name new_value report_name noprint;[p=24, null, left]select 'awr'||'.'||'&ext' report_name from dual;[p=24, null, left]set termout off;[p=24, null, left]spool &report_name;[p=24, null, left]select output from table(dbms_workload_repository.&fn_name(:dbid, :inst_num,:bid, :eid,:rpt_options ));[p=24, null, left]spool off;[p=24, null, left] [p=24, null, left] [p=24, null, left]set termout on;[p=24, null, left]clear columns sql;[p=24, null, left]ttitle off;[p=24, null, left]btitle off;[p=24, null, left]repfooter off;[p=24, null, left]undefine report_name[p=24, null, left]undefine report_type[p=24, null, left]undefine fn_name[p=24, null, left]undefine lnsz[p=24, null, left]undefine NO_OPTIONS[p=24, null, left]exit[p=24, null, left][oracle@rac1 admin]$[p=24, null, left] [p=24, null, left] 这个脚本是收集过去48个小时的snap 来生成AWR。 生成的文件名称是awr .html，这个也是spool 指定的，可以生成其他名称。[p=24, null, left] [p=24, null, left]
[p=24, null, left]三. 自动上传AWR的Python脚本[p=24, null, left] [p=24, null, left] 在这个脚本里做2件事，第一是调用第二步里的SQL脚本，生成awr报告，然后将AWR 发送到指定邮箱。[p=24, null, left] [p=24, null, left] [p=24, null, left]createSendAWR.py[p=24, null, left] [p=24, null, left]#!/usr/bin/python[p=24, null, left]#coding=gbk[p=24, null, left]#created by tianlesoftware[p=24, null, left]#2011-4-12[p=24, null, left] [p=24, null, left]import os[p=24, null, left]import sys[p=24, null, left]import smtplib[p=24, null, left]import pickle[p=24, null, left]import mimetypes[p=24, null, left]from email.MIMEText import MIMEText[p=24, null, left]from email.MIMEImage import MIMEImage[p=24, null, left]from email.MIMEMultipart import MIMEMultipart[p=24, null, left] [p=24, null, left] [p=24, null, left]SMTP_SERVER='192.168.1.120'[p=24, null, left]EMAIL_USER='user'[p=24, null, left]EMAIL_PASSWD='pwd'[p=24, null, left]EMAIL_SUBJECT='192.168.88.209 AWR Report'[p=24, null, left]FROM_USER='daimingming@1876.cn'[p=24, null, left]TO_USERS=['daimingming@1876.cn','dvd.dba@gmail.com'][p=24, null, left] [p=24, null, left] [p=24, null, left]def createawr():[p=24, null, left] pipe = os.popen(' /u01/app/oracle/product/10.2.0/db_1/bin/sqlplus /nolog @awrrpt.sql')[p=24, null, left] [p=24, null, left]def mysendmail(fromaddr,toaddrs,subject):[p=24, null, left] [p=24, null, left] COMMASPACE=','[p=24, null, left] msg = MIMEMultipart()[p=24, null, left] msg['From'] = fromaddr[p=24, null, left] msg['To'] = COMMASPACE.join(toaddrs)[p=24, null, left] msg['Subject'] = subject[p=24, null, left] [p=24, null, left] [p=24, null, left] txt = MIMEText("192.168.88.209 AWR Report, The report be send at 9 AM every day ")[p=24, null, left] msg.attach(txt) [p=24, null, left] [p=24, null, left] [p=24, null, left] fileName = r'/home/oracle/awr.html'[p=24, null, left] ctype, encoding = mimetypes.guess_type(fileName)[p=24, null, left] if ctype is None or encoding is not None:[p=24, null, left] ctype = 'application/octet-stream'[p=24, null, left] maintype, subtype = ctype.split('/', 1)[p=24, null, left] att = MIMEImage((lambda f: (f.read(), f.close()))(open(fileName, 'rb'))[0], _subtype = subtype)[p=24, null, left] att.add_header('Content-Disposition', 'attachment', filename = fileName)[p=24, null, left] msg.attach(att) [p=24, null, left] [p=24, null, left] server=smtplib.SMTP(SMTP_SERVER)[p=24, null, left] server.login(EMAIL_USER,EMAIL_PASSWD)[p=24, null, left] server.sendmail(fromaddr,toaddrs,msg.as_string())[p=24, null, left] server.quit()[p=24, null, left] [p=24, null, left]if __name__=='__main__':[p=24, null, left] createawr()[p=24, null, left] mysendmail(FROM_USER, TO_USERS, EMAIL_SUBJECT)[p=24, null, left] #print 'send successful'[p=24, null, left] [p=24, null, left] [p=24, null, left]四. 将Python 添加到crontab[p=24, null, left] [p=24, null, left] [p=24, null, left][oracle@qs-wg-db1 scripts]$ crontab -l[p=24, null, left] [p=24, null, left]40 17 * * * export ORACLE_HOME='/home/oracle_app' && ORACLE_SID=XX && cd /u01/backup/scripts && /u01/backup/scripts/createSendAWR.py >/u01/backup/scripts/createSendAWR.log 2>&1[p=24, null, left] [p=24, null, left]我这里因为报了[p=24, null, left]SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory[p=24, null, left]的错误，所以把变量加了上去。