![oracle_ren]( "oracle_ren")
Oracle 的AWR 报告能很好的提供有关DB性能的信息。 所以DBA 需要定期的查看AWR的报告。 有关AWR报告的说明参考： Oracle AWR 介绍 http://blog.csdn.net/tianlesoftware/archive/2009/10/17/4682300.aspx 如果说每天都去手工的出创建AWR报告，是个费时的活。 所以，最好就是弄成脚本来执行。 这篇文章就是说明这个问题的。 注意的一个问题，AWR 报告的两个snap 之间不能有重启DB的操作。 Python 执行 系统命令http://blog.csdn.net/tianlesoftware/archive/2011/02/17/6192202.aspx Python 发送带 附件 邮件 脚本http://blog.csdn.net/tianlesoftware/archive/2011/04/12/6318199.aspx 一. 准备工作 一般我们都是条用awrrpt.sql 来创建我们的AWR报告。 我们先看下这个脚本的具体内容：[oracle@rac1 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 , d.name db_name , i.instance_number inst_num , i.instance_name inst_name from v$database d, v$instance i; @@awrrpti undefine num_days;undefine report_type;undefine report_name;undefine begin_snap;undefine end_snap; 在以上的脚本里，我们发现它只是生成了一些变量，然后把这些变量传给了另一个脚本：awrrpti.sql。 我们看下awrrpti.sql 脚本的具体内容： [oracle@rac1 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 'html' column 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_name undefine report_typeundefine extundefine fn_nameundefine lnsz undefine NO_OPTIONSundefine ENABLE_ADDM undefine top_n_eventsundefine num_daysundefine top_n_sqlundefine top_pct_sqlundefine sh_mem_thresholdundefine top_n_segstat whenever sqlerror continue;[oracle@rac1 admin]$ 这个脚本才是我们真正生成AWR的脚本。 在这个脚本里面，提示我们选择AWR报告的类型。 通过上面的2个脚本，我们将AWR报告简化一下： select output fromtable(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 SNAP_ID DBID INSTANCE_NUMBER---------- ---------- --------------- 184 809910293 2 184 809910293 1 185 809910293 2 185 809910293 1 186 809910293 2 186 809910293 1 187 809910293 2 187 809910293 1 188 809910293 2 9 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报告了。SQL>select output from table(dbms_workload_repository.awr_report_html(809910293, 2,220,230,0)); SQL>select output from table(dbms_workload_repository.awr_report_text(809910293, 2,220,230,0)); 二. 生成AWR报告 SQL脚本 以上写了这么多，就是为了一个脚本：myawrrpt.sql. 这个脚本就是自动的去收集信息。 因为如果我们是调用awrrpt.sql的话，需要输入一些参数。 我们修改一下脚本，让它根据我们的需求来收集信息，这样就不用输入参数了。 [oracle@rac1 admin]$ cat myawrrpt.sqlconn / 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 'html'define report_type='html';begin:rpt_options := &NO_OPTIONS;end;/ variable dbid number;variable inst_num number;variable bid number;variable eid number;beginselect 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_OPTIONSexit[oracle@rac1 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-12 import osimport sysimport smtplibimport pickleimport mimetypesfrom email.MIMEText import MIMETextfrom email.MIMEImage import MIMEImagefrom email.MIMEMultipart import MIMEMultipart SMTP_SERVER='192.168.1.120'EMAIL_USER='user'EMAIL_PASSWD='pwd'EMAIL_SUBJECT='192.168.88.209 AWR Report'FROM_USER='daimingming@1876.cn'TO_USERS=['daimingming@1876.cn','dvd.dba@gmail.com'] 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() msg['From'] = fromaddr msg['To'] = COMMASPACE.join(toaddrs) msg['Subject'] = subject txt = MIMEText("192.168.88.209 AWR Report, The report be send at 9 AM every day ") msg.attach(txt) fileName = r'/home/oracle/awr.html' ctype, encoding = mimetypes.guess_type(fileName) if ctype is None or encoding is not None: ctype = 'application/octet-stream' maintype, subtype = ctype.split('/', 1) att = MIMEImage((lambda f: (f.read(), f.close()))(open(fileName, 'rb'))[0], _subtype = subtype) att.add_header('Content-Disposition', 'attachment', filename = fileName) msg.attach(att) server=smtplib.SMTP(SMTP_SERVER) server.login(EMAIL_USER,EMAIL_PASSWD) server.sendmail(fromaddr,toaddrs,msg.as_string()) server.quit() if __name__=='__main__': createawr() mysendmail(FROM_USER, TO_USERS, EMAIL_SUBJECT) #print 'send successful' 四. 将Python 添加到crontab [oracle@qs-wg-db1 scripts]$ crontab -l 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 我这里因为报了SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory的错误，所以把变量加了上去。 Linux Crontab 定时任务 命令详解 http://blog.csdn.net/tianlesoftware/archive/2010/02/21/5315039.aspx 12-29 23:35