Oracle AWR 報告 每天自動生成并發送郵箱 Python腳本

轉載自：http://blog.csdn.net/tianlesoftware/article/details/6319198

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;

prompt

prompt Current Instance

prompt ~~~~~~~~~~~~~~~~

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;

/

prompt

prompt Specify the Report Type

prompt ~~~~~~~~~~~~~~~~~~~~~~~

prompt Would you like an HTML report, or a plain text report?

prompt Enter 'html' for an HTML report, or 'text' for plain text

prompt  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_type

undefine ext

undefine fn_name

undefine lnsz

undefine NO_OPTIONS

undefine ENABLE_ADDM

undefine top_n_events

undefine num_days

undefine top_n_sql

undefine top_pct_sql

undefine sh_mem_threshold

undefine top_n_segstat

whenever sqlerror continue;

[oracle@rac1 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               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.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 'html'

define 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 noprint

column 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_name

undefine report_type

undefine fn_name

undefine lnsz

undefine NO_OPTIONS

exit

[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 os

import sys

import smtplib

import pickle

import mimetypes

from email.MIMEText import MIMEText

from email.MIMEImage import MIMEImage

from 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

的錯誤，所以把變量加了上去。