转载自:http://blog.csdn.net/tianlesoftware/article/details/6319198
Oracle
Python
http://blog.csdn.net/tianlesoftware/archive/2011/02/17/6192202.aspx
Python
http://blog.csdn.net/tianlesoftware/archive/2011/04/12/6318199.aspx
一.
[oracle@rac1 admin]$
set echo off heading on underline on;
column inst_num
column inst_name heading "Instance"
column db_name
column dbid
prompt
prompt Current Instance
prompt ~~~~~~~~~~~~~~~~
select d.dbid
@@awrrpti
undefine num_days;
undefine report_type;
undefine report_name;
undefine begin_snap;
undefine end_snap;
[oracle@rac1 admin]$
set echo off;
set veri off;
set feedback off;
variable rpt_options number;
define NO_OPTIONS
define ENABLE_ADDM
begin
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
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
--
@@awrinpnm.sql 'awrrpt_' &&ext
--
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,
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]$
通过上面的2个脚本,我们将AWR报告简化一下:
table(dbms_workload_repository.&fn_name(:dbid, :inst_num,:bid, :eid,:rpt_options ));
这条语句就是整个AWR报告的核心:
(1)&fn_name
(2)dbid,inst_num,bid,eid
SQL> select * from (select snap_id,dbid,instance_number from dba_hist_snapshot
---------- ---------- ---------------
9 rows selected.
(3)rpt_options:该参数控制是否显示ADDM的。
--
--
--
--
--
--
--
--
--
--
define NO_OPTIONS
define ENABLE_ADDM
有了上面的数据之后,我们就可以使用如下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));
二.
[oracle@rac1 admin]$
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]$
三.
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
def mysendmail(fromaddr,toaddrs,subject):
if __name__=='__main__':
四.
[oracle@qs-wg-db1 scripts]$ crontab -l
40 17 * * * export ORACLE_HOME='/home/oracle_app' && ORACLE_SID=XX
我这里因为报了
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
的错误,所以把变量加了上去。