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"