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;
/

Automating ADDM report Generation

Some times we may have a requirement to generate addm report for peak hours every day. Below are the steps , by which we can automate addm reports generation.



1) Create a task in DB.


begin
DBMS_ADVISOR.create_task (
advisor_name => 'ADDM',
task_name => 'ADDM2',
task_desc => 'Addm report generation schedule');
end;
/






2) Create this procedure in database. This procedure can generate awr report for 10:00 AM to 14:00 AM.



create or replace procedure auto_addm as
start_time number;
end_time number;
BEGIN
DBMS_ADVISOR.RESET_TASK(task_name =>'ADDM1');
select (MAX(SNAP_ID)-1) into start_time from dba_hist_snapshot where to_char(BEGIN_INTERVAL_TIME,'HH24') = '10';
select (MAX(SNAP_ID)-1) into end_time from dba_hist_snapshot where to_char(BEGIN_INTERVAL_TIME,'HH24') = '14';
DBMS_ADVISOR.set_task_parameter (
task_name => 'ADDM1',
parameter => 'START_SNAPSHOT',
value => start_time);
DBMS_ADVISOR.set_task_parameter (
task_name => 'ADDM1',
parameter => 'END_SNAPSHOT',
value => end_time);
-- Execute the task.
DBMS_ADVISOR.execute_task(task_name =>'ADDM1');
END auto_addm;
/




3) Schedule the bleow script in crontab.



sqlplus -s '/as sysdba' << EOF
SET LONG 100000
SET PAGESIZE 50000
exec auto_addm;
spool addmrpt.txt
SELECT DBMS_ADVISOR.get_task_report('ADDM1') AS report FROM dual;
spool off;

exit;

EOF
cat addmrpt.txt |mailx -s "ADDMREPORT For TODAY" "MAIL-ID"