自动生成awr报告并发送到邮箱

两个主要脚本

生成48小时内awr报告
[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;
--awr报告脚本awrrpt.sql 的核心语句
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

执行awr脚本并发送带附件邮件的python脚本
[oracle@rac1 admin]$ cat sendMail.py
#!/usr/bin/python
#coding=gbk
#created by tianlesoftware
#2011/4/12
import os
import sys
import smtplib
import mimetypes
import time
from email.MIMEText import MIMEText
from email.MIMEImage import MIMEImage
from email.MIMEMultipart import MIMEMultipart
SMTP_SERVER='smtp.gmail.com'
EMAIL_USER='user'
EMAIL_PASSWD='pwd'
EMAIL_SUBJECT="AWR report %s"%time.strftime("%Y-%m-%d",time.localtime())
FROM_USER='dvd.dba@gmail.com'
TO_USERS=['dvd.dba@gmail.com','tianlesoftware@vip.qq.com']
def createawr():
pipe = os.popen('sqlplus /nolog @myawrrpt.sql')
def mysendmail(fromaddr,toaddrs,subject):
COMMASPACE=','
msg = MIMEMultipart()
msg['From'] = fromaddr
msg['To'] = COMMASPACE.join(toaddrs)
msg['Subject'] = subject
#邮件内容
txt = MIMEText("awr report")
msg.attach(txt)
#添加附件,如果有多个附件,下面的代码重复即可
fileName = r'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'

cron调用python脚本
>crontab -l
30 6 * * * cd /oracle/orabase/admin/awr && ./sendMail.py > sendMail.log 2>&1 &

 

 

转自：

http://blog.csdn.net/tianlesoftware/article/details/6319198