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:

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

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;

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. 🙂

Please share

10 Comments

Database Scene

September 13, 2012 Reply

Retweeted on DS : https://twitter.com/Database_Scene
Thank you Gokhan!
- Gokhan Atil
  
  September 14, 2012
  
  Thanks for the tweet! 🙂
Pingback: Log Buffer #286, A Carnival of the Vanities for DBAs | The Pythian Blog
Sergey Golikov

September 21, 2012 Reply

Good job, Gokhan! Big thanks!
raju

October 23, 2012 Reply

good scripts.please post the script for single instance database

thanks
raju
Mani

December 3, 2012 Reply

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
Ergemp

January 6, 2017 Reply

Nice post Gokhan. works like a charm. Keep the spirit.
- Gokhan Atil
  
  January 10, 2017
  
  Thanks! 🙂
Anuj

January 27, 2017 Reply

Hello,

Very nice script, can we put multiple email ids v_recipient ..

Thanks

Anuj
- Gokhan Atil
  
  January 30, 2017
  Hi Anuj,
  
  Add UTL_SMTP.RCPT (v_mail_conn, v_recipient) multiple times, and also add the email address with comma to the “TO” string.
  
  UTL_SMTP.RCPT (v_mail_conn, 'mail1@mailserver.com'); UTL_SMTP.RCPT (v_mail_conn, 'mail2@mailserver.com'); UTL_SMTP.WRITE_DATA ( v_mail_conn, 'To: mail1@mailserver.com, mail2@mailserver.com' || UTL_TCP.CRLF );

Leave Comment Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.