Create AWR and ADDM Reports and Send Them via Email

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.

Please share this post Share on Facebook0Share on Google+0Share on LinkedIn0Share on Reddit0Tweet about this on Twitter

Gokhan Atil is a database administrator who has hands-on experience with both RDBMS and noSQL databases (Oracle, PostgreSQL, Microsoft SQL Server, Sybase IQ, MySQL, Cassandra, MongoDB and ElasticSearch), and strong background on software development. He is certified as Oracle Certified Professional (OCP) and is awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for his continuous contributions to the Oracle users community.

13 Comments

    • Damodar Reddy

      Wonderful script. Thanks a lot Gokhan. Saved me hours.

      regards,
      damu

  1. Wendy

    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

      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.

  2. prabhjot Singh

    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

  3. prabhjot Singh

    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

    • Gokhan Atil

      There’s no FMB file, you can create one using the SQL query.

Leave Comment

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