Script to Generate AWR reports for all RAC nodes

-- This generates AWR reports from all the RAC nodes for the input start and end timings.
-- Works for 10gR2
--
-- How to execute
-- Save this script in AWR_gen_script.sql
-- sql> @AWR_gen_script.sql 20100915_1400 20100915_1500 html
-- Param 1 = start time YYYYMMDD_HH24MI format
-- Param 2 = end time YYYYMMDD_HH24MI format
-- Param 3 = html or text
--
-- All the AWR reports ( one from each node ) will be generated in output_dir variable. Defaults to c:\
-- This can be run from client or server. Just change the output_dir depending on OS. Window c:\ or Unix $HOME/
-- This create a temporary sql file AWR_run_script.sql in the current directory


SET FEED OFF
SET VERIFY OFF
SET HEADING OFF
SET TERMOUT OFF
SET ECHO OFF
SET LINESIZE 32767
SET PAGES 0
SET WRAP OFF
SET SCAN ON
SET TRIM ON
SET TRIMS ON
SET TAB OFF
SET SERVEROUTPUT ON
SET PAUSE OFF
SET TIMING OFF

spool AWR_run_script.sql

DECLARE

output_dir VARCHAR2(40) := 'c:\';
-- output must end with a slash

awr_format VARCHAR2(4);
begin_time VARCHAR2(40) ;
end_time VARCHAR2(40) ;
snap_begin_time VARCHAR2(40);
snap_end_time VARCHAR2(40);
snap_begin_snap number;
snap_end_snap number;
v_dbid number;
v_dbname VARCHAR(20);
v_extention varchar(10);

BEGIN

begin_time := '&1';
end_time := '&2';
awr_format := '&3';

select dbid, name into v_dbid, v_dbname from v$database;

if awr_format = 'text' then
v_extention := 'txt';
else
v_extention := awr_format;
end if;

for x in ( select instance_number, instance_name from gv$instance order by 1 ) loop

SELECT to_char(max(end_interval_time),'YYYYMMDD_HH24MI'), max(snap_id) INTO snap_begin_time, snap_begin_snap
FROM DBA_HIST_SNAPSHOT
where instance_number= x.instance_number
and to_char(trunc(end_interval_time, 'MI'),'YYYYMMDD_HH24MI') <= begin_time;

SELECT to_char(min(end_interval_time),'YYYYMMDD_HH24MI'), min(snap_id) INTO snap_end_time, snap_end_snap
FROM DBA_HIST_SNAPSHOT
where instance_number= x.instance_number
and to_char(trunc(end_interval_time, 'MI'),'YYYYMMDD_HH24MI') >= end_time;

DBMS_OUTPUT.PUT_LINE(chr(10));
DBMS_OUTPUT.PUT_LINE('spool ' || output_dir || 'AWR_' || x.instance_name ||'_'|| snap_begin_time ||'_to_' || snap_end_time ||'.' || v_extention );
DBMS_OUTPUT.PUT_LINE('SELECT output FROM TABLE (dbms_workload_repository.awr_report_' || awr_format || '(' || v_dbid||','||x.instance_number||','|| snap_begin_snap ||','|| snap_end_snap||'));');
DBMS_OUTPUT.PUT_LINE('spool off');

end loop;
DBMS_OUTPUT.PUT_LINE('exit ');

end;
/

spool off;
@AWR_run_script.sql;
exit