Oracle 批量生成AWR报告

在日常的工作中需要每天监控AWR报告，消除隐患，下面是自动运维脚本。Oracle的ADDM也会给出部分优化建议，ADDM报告自动生成脚本参见：Oracle下批量生成ADDM报告（见http://www.linuxidc.com/Linux/2014-12/110085.htm）。使用方法: 在E盘下面建立AWR的目录，将三个文件放入到其中awr.cmd、awr.sql、myawr.sql. 通过window的任务计划建立定时任务触发awr.cmd.前提连上的用户需要被赋予两个权限。grant select any dictionary to user;-----user为生产数据库账号grant execute on dbms_workload_repository to user;-------user为生产数据库账号Oracle AWR报告生成与查看 http://www.linuxidc.com/Linux/2011-04/34397.htm在CentOS 6.4下安装Oracle 11gR2（x64） http://www.linuxidc.com/Linux/2014-02/97374.htmOracle 11gR2 在VMWare虚拟机中安装步骤 http://www.linuxidc.com/Linux/2013-09/89579p2.htmDebian 下安装 Oracle 11g XE R2 http://www.linuxidc.com/Linux/2014-03/98881.htmOracle AWR报告生成步骤 http://www.linuxidc.com/Linux/2014-06/102685.htm1. 文件名: awr.cmd 内容: cmd.exe /c sqlplus username/password@servername @awr.sql2. 文件名: awr.sql 内容:@@E:AWRmyawr.sql 0 8
@@E:AWRmyawr.sql 8 12
@@E:AWRmyawr.sql 12 14
@@E:AWRmyawr.sql 14 18
@@E:AWRmyawr.sql 18 24
exit3. 文件名: myawr.sql内容:set echo off;
set veri off;
set feedback off;
set termout on;
set heading off;
set linesize 1500;variable dbid number;
variable inst_num number;
variable bid number;
variable eid number;
variable bhour number;
variable ehour number;
begin
:bhour := &1;
:ehour := &2;
end;
/
begin
select min（t.snap_id） + :bhour - 1 into :bid from sys.dba_hist_snapshot t where to_char（t.begin_interval_time,"yyyy-mm-dd"） = to_char（sysdate-1,"yyyy-mm-dd"）;
select min（t.snap_id） + :ehour - 1 into :eid from sys.dba_hist_snapshot t where to_char（t.begin_interval_time,"yyyy-mm-dd"） = to_char（sysdate-1,"yyyy-mm-dd"）;
select dbid into :dbid from v$database;
select instance_number into :inst_num from v$instance;
end;
/
set termout off;
column report_name new_value report_name noprint;
select "awrrpt_"||to_char（sysdate-1,"yyyymmdd"）||"_"||:bhour||"-"||:ehour||".html" report_name from dual;
set termout on;
spool &report_name;
select output from table（dbms_workload_repository.awr_report_html（:dbid,:inst_num,:bid,:eid,0 ））;
spool off;set termout on;
clear columns sql;
ttitle off;
btitle off;
repfooter off;
undefine report_name更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址