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 => '[email protected]', 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!
the above code give the following error
24/1 PL/SQL: Statement ignored
24/1 PLS-00201: identifier ‘CLOB_TO_FILE’ must be declared
27/1 PL/SQL: Statement ignored
27/1 PLS-00201: identifier ‘DEMO_MAIL.SEND_EMAIL’ must be declared