I’ve seen a question on OTN forum about how to create a job in Grid Control for generating AWR/ADDM reports and send these reports via email. As I know OEM Grid Control doesn’t have such a job template but we can write a PL/SQL script for this task and define it as a job, so we can automate it for all databases.

First, let’s check how we can generate AWR reports. To be able to get AWR reports in plain text, we can use:

Syntax (for Oracle 10.2)

DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(
l_dbid IN NUMBER,
l_inst_num IN NUMBER,
l_bid IN NUMBER,
l_eid IN NUMBER,
l_options IN NUMBER DEFAULT 0)
RETURN awrrpt_text_type_table PIPELINED;

If we want the report in HTML, we can use:

DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
l_dbid IN NUMBER,
l_inst_num IN NUMBER,
l_bid IN NUMBER,
l_eid IN NUMBER,
l_options IN NUMBER DEFAULT 0)
RETURN awrrpt_text_type_table PIPELINED;

As you see both functions accept same arguments, only difference is the format of the report. Arguments of these functions:

l_dbid: The database identifier
l_insT_num: The instance number
l_bid: The ‘Begin Snapshot’ ID
l_eid: The ‘End Snapshot’ ID
l_options: A flag to specify to control the output of the report. Default vaule is 0, if we set it to 8, report will include the ADDM specific sections include the Buffer Pool Advice, Shared Pool Advice, and PGA Target Advice.

One of the important points is, we need to determine the database ID, instance number and snapshots dynamically to be able to define it as a job in OEM Grid Control. I query DBA_HIST_SNAPSHOT to find the beginning and end snapshot IDs according to variables starttime and endtime. I read the database ID and instance number from GV$DATABASE.

Here’s the simple PL/SQL block to create AWR report in HTML and mail it:

To be able to create ADDM Reports, we can use DBMS_ADVISOR package:

ADDM reports are created as jobs, so we wait until they’re completed and get the report:

Here’s the simple PL/SQL block to create ADDM report and mail it:

I have tested these scripts on both 10.2 and 11.2, and they worked fine. By the way, don’t forget to set ACL if you’ll run them in 11g+

I wrote these script only for demonstration purposes. They are not optimized and they have almost no error checking, so be careful when you use them in production environments.

Anyway, our scripts are ready for test, now we can create the job in Grid Control. Login to OEM Grid Control, click the Jobs tab then choose SQL Script as job type and click Go. Give the new job a name and description, then click Add to add the target databases. Click to “Parameters” tab, and copy paste one of the above scripts into the textbox labeled “SQL Script”. Set the credentials and schedule as you wish and click submit when all done.

9 Responses to “Create AWR and ADDM Reports and Send Them via Email”

  1. Hi Gokhan !

    Very nice script , thanks for sharing.

    Mahir

  2. Can you explain how this can be working? I got a whole bunch of errors such as:
    starttime := ’06:00′;
    *
    ERROR at line 2:
    ORA-06550: line 2, column 4:
    PLS-00201: identifier ‘STARTTIME’ must be declared
    ORA-06550: line 2, column 4:
    PL/SQL: Statement ignored
    ORA-06550: line 3, column 4:
    PLS-00201: identifier ‘ENDTIME’ must be declared
    ORA-06550: line 3, column 4:
    PL/SQL: Statement ignored
    ORA-06550: line 9, column 53:
    PL/SQL: ORA-00904: “ENDTIME”: invalid identifier
    ORA-06550: line 5, column 4:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 14, column 11:
    PLS-00201: identifier ‘DBID’ must be declared
    ORA-06550: line 15, column 6:
    PL/SQL: ORA-00904: : invalid identifier
    ORA-06550: line 13, column 4:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 17, column 26:
    PLS-00201: identifier ‘HOST_NAME’ must be declared
    ORA-06550: line 18, column 6:
    PL/SQL: ORA-00904: : invalid identifier
    ORA-06550: line 17, column 4:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 20, column 4:
    PLS-00201: identifier ‘V_FROM’ must be declared
    ORA-06550: line 20, column 4:
    PL/SQL: Statement ignored
    ORA-06550: line 22, column 4:
    PLS-00201: identifier ‘V_MAIL_CONN

    I ran your script in sqlplus , and it should work, right? I only modified my email address and our smtpserver name.
    Please advice. Thanks.

    • Gokhan Atil says:

      You probably miss the deceleration part or a syntax error occurred while you modify it. This is why you get these errors. Try to save the PL/SQL block into a file and then execute it in sqlplus.

    • Thanks.

  3. Awesome script, thanks.

  4. prabhjot Singh says:

    when i tried to run this, i got below error

    DECLARE
    *
    ERROR at line 1:
    ORA-20000: Unable to send mail: ORA-29278: SMTP transient error: 421 Service
    not available
    ORA-06512: at line 63

    Could you please tell me what should i do receive the awr report on mail

  5. prabhjot Singh says:

    I also got this one

    SQL> BEGIN
    2 DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl => ‘smtpserver.xml’,
    3 4 description => ‘Connection to SMTP’,
    5 principal => ‘BMS’,
    6 is_grant => TRUE,
    7 privilege => ‘connect’,
    8 start_date => SYSTIMESTAMP,
    end_date => NULL);
    9 10 COMMIT;
    11 END;
    12 /
    BEGIN
    *
    ERROR at line 1:
    ORA-44416: Invalid ACL: Unresolved principal ‘BMS’
    ORA-06512: at “SYS.DBMS_NETWORK_ACL_ADMIN”, line 252
    ORA-06512: at line 2

    What is principal here

  6. Fantastic script!!

    Thanks!

    H.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">