Automating AWR report Generation

Here is steps for awr report automization.





=========AWR REPORT AUTOMIZATION===========


CREATE OR REPLACE PROCEDURE CreateAwrReports (begin_snap number,end_snap number, directory varchar2 ) as
v_Instance_number v$instance.instance_number%TYPE;
v_Instance_name v$instance.instance_name%TYPE;
v_dbid V$database.dbid%TYPE;
v_file UTL_FILE.file_type;
BEGIN
SELECT instance_number, instance_name into v_Instance_number,v_Instance_name FROM gv$instance ORDER BY 1;
SELECT dbid INTO v_dbid FROM v$database;
EXECUTE IMMEDIATE('CREATE OR REPLACE DIRECTORY AWR_DIR AS '''||directory||'''');
BEGIN
v_file := UTL_FILE.fopen('AWR_DIR', 'awr_' || v_Instance_name ||'_'|| v_Instance_number || '_' || begin_snap || '_' || (end_snap-1) || '.txt', 'w', 32767);
FOR c_AWRReport IN (
SELECT output FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT( v_dbid, v_Instance_number, begin_snap,end_snap-1))
) loop
UTL_FILE.PUT_LINE(v_file, c_AWRReport.output); end loop;
UTL_FILE.fclose(v_file);
END;
EXECUTE IMMEDIATE('DROP DIRECTORY AWR_DIR');
END;






create or replace procedure generate_awrreport as
begin_snap number;
end_snap number;
begin
select (MAX(SNAP_ID)-1) into begin_snap from dba_hist_snapshot where to_char(BEGIN_INTERVAL_TIME,'HH24') = '10';
select (MAX(SNAP_ID)-1) into end_snap from dba_hist_snapshot where to_char(BEGIN_INTERVAL_TIME,'HH24') = '14';
CreateAwrReports(begin_snap, end_snap,'/export/home/oracle/');
end;
/