Automatic Generation of Daily AWR Report

I have recently received a requirement to provide a daily automatic AWR report, and it should be delivered to the DBA team’s inbox. It looks like an interesting requirement, so I completed this using my test database first, having the following details:

Single Instance database namely "test19c", running in rdbms 19.3 version.
This is a cdb enabled database,running in RHEL 7.9 version.

Now back to this requirement. As usual, I checked MOS and got a good hint as follows:

How to Create AWR Snapshots Outside the Regular Automatic Intervals? (Doc ID 2100903.1)

I agreed with the suggestion provided in this note, i.e., to write a custom procedure, which means this code can be reused in other databases. While I was writing this procedure, I had issues with the size of the output html file generated. Once again, I took help from MOS with the following note:

How to Write CLOB Data > 32K Out to a File? (Doc ID 358641.1)

Once I could generate the required html reports and get the output into a file, I had issues sending this file attachment. This is because I didn’t want to call any OS utilities for the mailing purpose. Again, I got this help from MOS:

How To Send Multiple Attachments Of Size Greater Than 32 KB Using UTL_SMTP Package (Doc ID 357385.1)

In my test database, the snaps are generated once per hour. We are using the system date instead of parameters, which makes it dependent on when the job runs. The initial snapshot is taken from the first snapshot of the previous day, and the end snapshot is taken from the last snapshot of the previous day.

My custom procedure code looks like the following. You may need to adjust the logic to calculate begin and end snap IDs in this procedure to suit your needs.

CREATE OR REPLACE NONEDITIONABLE PROCEDURE "SYS"."GET_AND_SEND_AWR_REPORT_YESTERDAY" is
v_dbid v$database.dbid%type;
v_inst_num v$instance.instance_number%type;
v_end_snapid dba_hist_snapshot.snap_id%type;
v_begin_snapid dba_hist_snapshot.snap_id%type;
v_start_date     VARCHAR2 (20);
v_awr_report CLOB:=empty_clob();
v_html_report_row varchar2(1500);
po_err_msg varchar2(1000);
begin
select d.dbid, i.instance_number into v_dbid, v_inst_num from v$database d, v$instance i;
select MIN(snap_id) into v_begin_snapid from dba_hist_snapshot
where begin_interval_time > trunc(sysdate-1,'DD') and dbid = v_dbid and instance_number = v_inst_num;
select MIN(snap_id) into v_end_snapid from dba_hist_snapshot
where end_interval_time > round(sysdate,'DD') and dbid = v_dbid and instance_number = v_inst_num;
for cv in
(select output from table(dbms_workload_repository.awr_report_html(v_dbid,v_inst_num, v_begin_snapid, v_end_snapid)))
loop
v_html_report_row := cv.output;
-- now append v_html_report_row to v_awr_report
v_awr_report := v_awr_report || v_html_report_row;
end loop;
-- Convert the CLOB into a file
clob_to_file ('DATA_PUMP_DIR', 'AWRFILE_Latest_test_db.html', v_awr_report);
-- Mail this file to DBA
begin
demo_mail.send_email
(
     P_DIRECTORY   => 'DATA_PUMP_DIR',
     P_SENDER      => 'oracle@testserver.pythian.com',
     P_RECIPIENT   => '**@*.com',
     P_CC          => '**@*.com',
     P_BCC         => '**@*.com',
     P_SUBJECT     => 'AWR recent report for TEST database for '||to_char(sysdate-1,'MM/DD/YYYY'),
     P_BODY        => 'AWR report for test database',
     P_ATTACHMENT1 => 'AWRFILE_Latest_test_db.html',
     P_ATTACHMENT2 => null,
     P_ATTACHMENT3 => null,
     P_ATTACHMENT4 => null,
     P_ERROR       => po_err_msg
);
if po_err_msg <> '0' then
dbms_output.put_line('Call to demo_mail failed: '|| po_err_msg);
end if;
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error while calling demo_mail package: '||sqlerrm);
end;
end;
/

This procedure was tested manually and confirmed we’re getting the required AWR html report, hence this was submitted as the database scheduler job as follows:

begin
dbms_scheduler.create_job(
job_name=>'DAILY_AWR_JOB'
,job_type=>'PROCEDURE'
,job_action=>'get_and_send_awr_report_yesterday'
,start_date=>to_date(to_char(sysdate,'DD Mon YYYY')||' 01:20:00','DD Mon YYYY HH24:MI:SS') 
,repeat_interval=>'FREQ=DAILY'
,auto_drop => false
,enabled => true);
end;
/

Now, the required emails are on their way to the inbox(es) each day.

Looks easy, correct? I hope this post helps you!