Automating Oracle ADDM Reports with PL/SQL: A Guide to Sending Performance Reports via Email

Blog Article Title: "Automating Oracle ADDM Reports with PL/SQL: A Guide to Sending Performance Reports via Email"

Article Description:

In this article, we will explore how to automate the process of generating and sending Oracle Automatic Database Diagnostic Monitor (ADDM) reports using PL/SQL. ADDM provides valuable insights into database performance, identifying potential issues and offering recommendations for tuning. By integrating PL/SQL with email functionalities, you can streamline the delivery of these performance reports directly to your inbox, making it easier to monitor and address database health. This guide will cover the components of a PL/SQL package designed for this purpose, with detailed explanations of each section, making it an essential resource for Oracle DBAs looking to enhance their monitoring capabilities.

Note: You can use either DBMS_Sceduler to set job inside the database or trigger through any shell script through the command prompt (sqlplus)

This is an awesome script that I found online blog post by Gokhan Atil (ORACLE ACE). I wanted to share this with my friends, as it will be very helpful in daily maintenance.

PLSQL for ADDM sent via EMAIL:

DECLARE
   dbid           NUMBER;
   bid            NUMBER;
   eid            NUMBER;
   db_unique_name VARCHAR2(30);
   host_name      VARCHAR2(64);
   status         VARCHAR2(11);
   starttime      CHAR (5);
   endtime        CHAR (5);
   output         VARCHAR2 (32000);
   v_from         VARCHAR2 (80);
   v_recipient    VARCHAR2 (80) := 'arvind@domain.com';
   v_mail_host    VARCHAR2 (30) := 'YOUR_SMTP_SERVER';
   v_mail_conn    UTL_SMTP.connection;
   tname varchar2(50);
   tid   number;
BEGIN
   starttime := '01:00';
   endtime := '12: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 dbid, db_unique_name
     INTO dbid, db_unique_name
     FROM v$database;

   SELECT host_name INTO host_name
     FROM v$instance;
   
    DBMS_ADVISOR.CREATE_TASK('ADDM',tid,tname,'ADDM Report( '
    || bid || ',' || eid || ' )');
    DBMS_ADVISOR.SET_TASK_PARAMETER( tname,'START_SNAPSHOT',bid );
    DBMS_ADVISOR.SET_TASK_PARAMETER( tname,'END_SNAPSHOT',eid );
    DBMS_ADVISOR.EXECUTE_TASK( tname );  
   
    status := 0;
 
    while status <> 'COMPLETED' loop
    select status into status from dba_advisor_tasks where task_id = tid;
    dbms_lock.sleep(5);  
    end loop;

   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: '
        || 'ADDM Report of ' || v_from || ' '
        || SYSDATE || ' ' || starttime || '-' || endtime
        || UTL_TCP.CRLF || UTL_TCP.CRLF );


   SELECT DBMS_ADVISOR.GET_TASK_REPORT( tname) INTO output FROM DUAL;
   UTL_SMTP.WRITE_DATA (v_mail_conn, output );
 
   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;
/

Note : 

works only if you have set the SMTP server and ACL if you are working on Database 11G or higher



Extracted from :

http://www.gokhanatil.com/2011/07/create-awr-and-addm-reports-and-send-them-via-email.html