In one of my old posts, I tried to explain how to create AWR and ADDM reports by PL/SQL and send them as email. One of my reader asked me how to run them in RAC environment. As I see, my script for ADDM is already compatible but AWR was not, so I added 1-2 lines to make it compatible, and wanted to share it:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
DECLARE bid NUMBER; eid NUMBER; host_name VARCHAR2(64); starttime CHAR (5); endtime CHAR (5); v_from VARCHAR2 (80); v_recipient VARCHAR2 (80) := 'gokhan@myemailaddress.com'; v_mail_host VARCHAR2 (30) := 'oursmtpserver'; v_mail_conn UTL_SMTP.connection; BEGIN starttime := '06:00'; endtime := '10:00'; SELECT MIN (snap_id), MAX (snap_id) INTO bid, eid FROM dba_hist_snapshot WHERE TO_CHAR (begin_interval_time, 'hh24:mi') >= starttime AND TO_CHAR (end_interval_time, 'hh24:mi') <= endtime AND TRUNC (begin_interval_time) = TRUNC (SYSDATE) AND TRUNC (end_interval_time) = TRUNC (SYSDATE); FOR rac IN (SELECT dbid, inst_id, db_unique_name FROM gv$database) LOOP SELECT host_name INTO host_name FROM gv$instance where inst_id = rac.inst_id; v_from := rac.db_unique_name || '@' || host_name; v_mail_conn := UTL_SMTP.OPEN_CONNECTION (v_mail_host, 25); UTL_SMTP.HELO (v_mail_conn, v_mail_host); UTL_SMTP.MAIL (v_mail_conn, v_from); UTL_SMTP.RCPT (v_mail_conn, v_recipient); UTL_SMTP.OPEN_DATA( v_mail_conn ); UTL_SMTP.WRITE_DATA ( v_mail_conn, 'From:' || v_from || UTL_TCP.CRLF ); UTL_SMTP.WRITE_DATA ( v_mail_conn, 'To:' || v_recipient || UTL_TCP.CRLF ); UTL_SMTP.WRITE_DATA ( v_mail_conn, 'Subject: ' || 'AWR Report of ' || v_from || ' ' || SYSDATE || ' ' || starttime || '-' || endtime || UTL_TCP.CRLF ); UTL_SMTP.WRITE_DATA ( v_mail_conn, 'Content-Type: text/html; charset=utf8' || UTL_TCP.CRLF || UTL_TCP.CRLF ); FOR c1_rec IN (SELECT output FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(rac.dbid, rac.inst_id, bid, eid, 8 ))) LOOP UTL_SMTP.WRITE_DATA (v_mail_conn, c1_rec.output || UTL_TCP.CRLF ); END LOOP; UTL_SMTP.CLOSE_DATA (v_mail_conn); UTL_SMTP.QUIT (v_mail_conn); END LOOP; EXCEPTION WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN RAISE_APPLICATION_ERROR (-20000, 'Unable to send mail: ' || SQLERRM); END; / |
It sends one email for each instance. So you get 3 emails if you have 3-node RAC. It doesn’t seem quite right to me, so I modified the code to send these reports as attachments in one mail:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 |
DECLARE bid NUMBER; eid NUMBER; db_unique_name VARCHAR2(30); host_name VARCHAR2(64); starttime CHAR (5); endtime CHAR (5); v_from VARCHAR2 (80); v_recipient VARCHAR2 (80) := 'gokhan@myemailaddress.com'; v_mail_host VARCHAR2 (30) := 'oursmtpserver'; v_mail_conn UTL_SMTP.connection; BEGIN starttime := '06:00'; endtime := '10:00'; SELECT MIN (snap_id), MAX (snap_id) INTO bid, eid FROM dba_hist_snapshot WHERE TO_CHAR (begin_interval_time, 'hh24:mi') >= starttime AND TO_CHAR (end_interval_time, 'hh24:mi') <= endtime AND TRUNC (begin_interval_time) = TRUNC (SYSDATE) AND TRUNC (end_interval_time) = TRUNC (SYSDATE); SELECT host_name INTO host_name FROM v$instance; SELECT db_unique_name INTO db_unique_name FROM v$database; v_from := db_unique_name || '@' || host_name; v_mail_conn := UTL_SMTP.OPEN_CONNECTION (v_mail_host, 25); UTL_SMTP.HELO (v_mail_conn, v_mail_host); UTL_SMTP.MAIL (v_mail_conn, v_from); UTL_SMTP.RCPT (v_mail_conn, v_recipient); UTL_SMTP.OPEN_DATA( v_mail_conn ); UTL_SMTP.WRITE_DATA ( v_mail_conn, 'From:' || v_from || UTL_TCP.CRLF ); UTL_SMTP.WRITE_DATA ( v_mail_conn, 'To:' || v_recipient || UTL_TCP.CRLF ); UTL_SMTP.WRITE_DATA ( v_mail_conn, 'Subject: ' || 'AWR Report of ' || v_from || ' ' || SYSDATE || ' ' || starttime || '-' || endtime || UTL_TCP.CRLF ); UTL_SMTP.WRITE_DATA ( v_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.CRLF ); UTL_SMTP.WRITE_DATA ( v_mail_conn, 'Content-Type: multipart/mixed; boundary=NEXTSLIDEPLEASE' || UTL_TCP.CRLF || UTL_TCP.CRLF ); UTL_SMTP.WRITE_DATA ( v_mail_conn, '--NEXTSLIDEPLEASE' || UTL_TCP.CRLF || 'Content-Type: text/plain;' || UTL_TCP.CRLF || UTL_TCP.CRLF ); UTL_SMTP.WRITE_DATA ( v_mail_conn, 'AWR Reports are attached.' || UTL_TCP.CRLF || UTL_TCP.CRLF ); FOR rac IN (SELECT dbid, inst_id FROM gv$database) LOOP UTL_SMTP.WRITE_DATA ( v_mail_conn, '--NEXTSLIDEPLEASE' || UTL_TCP.CRLF || 'Content-Disposition: attachment; ' || 'filename="awr_' || db_unique_name || rac.inst_id || '.html"' || UTL_TCP.CRLF || 'Content-Type: text/html; charset=utf8; ' || UTL_TCP.CRLF || UTL_TCP.CRLF ); FOR c1_rec IN (SELECT output FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(rac.dbid, rac.inst_id, bid, eid, 8 ))) LOOP UTL_SMTP.WRITE_DATA (v_mail_conn, c1_rec.output || UTL_TCP.CRLF ); END LOOP; END LOOP; UTL_SMTP.CLOSE_DATA (v_mail_conn); UTL_SMTP.QUIT (v_mail_conn); EXCEPTION WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN RAISE_APPLICATION_ERROR (-20000, 'Unable to send mail: ' || SQLERRM); END; / |
I hope that PL/SQL script will be helpful, at least it demonstrates how to send attachments in emails. 
5 Responses to “Create AWR Reports and Send Them via Email (RAC Compatible)”
Trackbacks/Pingbacks
- Log Buffer #286, A Carnival of the Vanities for DBAs | The Pythian Blog - [...] Gokhan Atil is creating AWR reports and sending them via email. [...]
Retweeted on DS : https://twitter.com/Database_Scene
Thank you Gokhan!
Thanks for the tweet!
Good job, Gokhan! Big thanks!
good scripts.please post the script for single instance database
thanks
raju
Thanks Gokhan for sharing. Using your proc, I have written for ASH report:
http://idba-oracle.blogspot.com/2012/12/i-read-article-about-generating-awr-to.html