Friday, September 23, 2011

How to generate AWR report in batch mode

Below scripts created by myself and tested in 11gR2 RAC databases.

---------------------------------------------------------------------
-- script awrrpt_donghua.sql
---------------------------------------------------------------------
set serveroutput on
spool master_awr_control.sql
declare
cursor c is
select to_char(s.startup_time,'dd Mon "at" HH24:mi:ss') instart_fmt
, di.instance_name inst_name
, di.instance_number instance_number
, di.db_name db_name
, di.dbid dbid
, lag (s.snap_id,1,0) over (partition by di.instance_number order by s.snap_id) begin_snap_id
, s.snap_id end_snap_id
, to_char(s.begin_interval_time,'yyyymmddhh24mi') beginsnapdat
, to_char(s.end_interval_time,'yyyymmddhh24mi') endsnapdat
, s.snap_level lvl
from dba_hist_snapshot s
, dba_hist_database_instance di
,gv$instance i
,v$database d
where s.dbid = d.dbid
and di.dbid = d.dbid
and s.instance_number = i.instance_number
and di.instance_number = i.instance_number
and di.dbid = s.dbid
and di.instance_number = s.instance_number
and di.startup_time = s.startup_time
and s.begin_interval_time > trunc(sysdate -7) -- last last 7 days
order by di.db_name, i.instance_name, s.snap_id;
begin
for c1 in c
loop
if c1.begin_snap_id > 0 then
dbms_output.put_line('spool '||c1.inst_name||'_'
||c1.begin_snap_id||'_'||c1.end_snap_id||'_'||c1.beginsnapdat||'_'||c1.endsnapdat||'.html');
dbms_output.put_line('select output from table(dbms_workload_repository.awr_report_html( '||c1.dbid||','||
c1.instance_number||','||
c1.begin_snap_id||','||
c1.end_snap_id||',0 ));');
dbms_output.put_line('spool off');
end if;
end loop;
end;
/
spool off;
set heading off
set pages 50000
set linesize 1500
set trimspool on
set trimout on
set term off
set verify off;
set feedback off;
@master_awr_control.sql
exit
-----------------------------------------------------------------------------------
---- output -----------------------------------------------------------------------
-----------------------------------------------------------------------------------
orcl_10_11_201109130000_201109130100.html
orcl_11_12_201109130100_201109130200.html
orcl_12_13_201109130200_201109130300.html
orcl_13_14_201109130300_201109130400.html
orcl_14_15_201109130400_201109130500.html
orcl_15_16_201109130500_201109130601.html
orcl_16_17_201109130601_201109130700.html
orcl_17_18_201109130700_201109130800.html
orcl_18_19_201109130800_201109130900.html
orcl_19_20_201109130900_201109131000.html
orcl_20_21_201109131000_201109131100.html
orcl_21_22_201109131100_201109131200.html
orcl_22_23_201109131200_201109131300.html