oracle awr监控报告,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为生产数据库账号

1. 文件名: awr.cmd

内容:  cmd.exe /c sqlplus username/password@servername @awr.sql

2. 文件名: awr.sql

内容:

@@E:\AWR\myawr.sql 0 8

@@E:\AWR\myawr.sql 8 12

@@E:\AWR\myawr.sql 12 14

@@E:\AWR\myawr.sql 14 18

@@E:\AWR\myawr.sql 18 24

exit

3. 文件名: 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