mail – Gokhan Atil’s Blog

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:

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;
/

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:

September 13, 2012Gokhan Atil10 CommentsOracleawr, mail, plsql, utl_smtp

Tag: mail

Create AWR Reports and Send Them via Email (RAC Compatible)

Create AWR and ADDM Reports and Send Them via Email