Windows环境自动获取AWR报告

时间：2022-03-28 11:34:41

1.双击awr.cmd，通过cmd窗口运行awr.sql

cmd.exe /c sqlplus lcam_1230/zcpzg1z_1230@54_orcl @awr.sql

awr.cmd

2.awr.sql，定义需要生成的AWR报告的时间区间,调用myawr.sql

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

@@D:\AWR\myawr.sql 8 10

@@D:\AWR\myawr.sql 10 12

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

@@D:\AWR\myawr.sql 14 16

@@D:\AWR\myawr.sql 16 18

@@D:\AWR\myawr.sql 20 24

exit

2.awr.sql

3.myawr.sql，获取AWR报告数据

set echo off;

set veri off;

set feedback off;

set termout on;

set heading off;

set linesize 500;

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,'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,'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,'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;

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

3.myawr.sql

相关文章