Create AWR Reports and Send Them via Email (RAC Compatible)
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: