Awr report mail every hour

You want to send an AWR (Automatic Workload Repository) report via email every hour. Here's a step-by-step guide to help you achieve this:

Prerequisites:

You have an Oracle database with AWR enabled.
You have an email client set up on your system (e.g., Outlook, Gmail, etc.).
You have the necessary permissions to execute the script and send emails.

Step 1: Create a script to generate the AWR report

Create a new file (e.g., awr_report.sql) with the following contents:

SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 200

SELECT 
  TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') AS REPORT_TIME,
  DB_NAME AS DATABASE_NAME,
  INSTANCE_NAME AS INSTANCE_NAME,
  TO_CHAR(SUM(BYTES_READ) / 1024 / 1024, '999,999,999') AS TOTAL_READ_MB,
  TO_CHAR(SUM(BYTES_WRITTEN) / 1024 / 1024, '999,999,999') AS TOTAL_WRITE_MB,
  TO_CHAR(AVG_WAIT_TIME, '999,999,999') AS AVG_WAIT_TIME
FROM 
  DBA_HIST_SYSSTAT
WHERE 
  STATISTIC_NAME IN ('physical reads', 'physical writes')
GROUP BY 
  DB_NAME, INSTANCE_NAME
ORDER BY 
  DB_NAME, INSTANCE_NAME;

This script generates a simple AWR report with the total read and write bytes, average wait time, and other relevant information.

Step 2: Create a script to send the email

Create a new file (e.g., send_awr_report.sql) with the following contents:

DECLARE
  l_body CLOB;
  l_subject VARCHAR2(200);
BEGIN
  l_subject := 'AWR Report - ' || TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS');
  l_body := 'SET HTML ON
  <html>
  <body>
  <h1>' || l_subject || '</h1>
  <p>AWR Report generated at ' || TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') || '</p>
  <table border="1">
  <tr>
  <th>Database Name</th>
  <th>Instance Name</th>
  <th>Total Read MB</th>
  <th>Total Write MB</th>
  <th>Avg Wait Time</th>
  </tr>
  ' || 
  (
    SELECT 
      DB_NAME || ' (' || INSTANCE_NAME || ')' AS DATABASE_NAME,
      INSTANCE_NAME,
      TO_CHAR(TOTAL_READ_MB, '999,999,999') AS TOTAL_READ_MB,
      TO_CHAR(TOTAL_WRITE_MB, '999,999,999') AS TOTAL_WRITE_MB,
      TO_CHAR(AVG_WAIT_TIME, '999,999,999') AS AVG_WAIT_TIME
    FROM 
      TABLE(
        (
          SELECT 
          TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') AS REPORT_TIME,
          DB_NAME AS DATABASE_NAME,
          INSTANCE_NAME AS INSTANCE_NAME,
          TO_CHAR(SUM(BYTES_READ) / 1024 / 1024, '999,999,999') AS TOTAL_READ_MB,
          TO_CHAR(SUM(BYTES_WRITTEN) / 1024 / 1024, '999,999,999') AS TOTAL_WRITE_MB,
          TO_CHAR(AVG_WAIT_TIME, '999,999,999') AS AVG_WAIT_TIME
        FROM 
          DBA_HIST_SYSSTAT
        WHERE 
          STATISTIC_NAME IN ('physical reads', 'physical writes')
        GROUP BY 
          DB_NAME, INSTANCE_NAME
        ORDER BY 
          DB_NAME, INSTANCE_NAME
        )
      )
  ) || '
  </table>
  </body>
  </html>
  ';

  DBMS_OUTPUT.PUT_LINE(l_body);

  -- Send the email using your preferred email client
  -- For example, using Oracle's UTL_SMTP package:
  BEGIN
    UTL_SMTP.SEND(
      p_smtp_host => 'your_smtp_host',
      p_smtp_port => 25,
      p_from => 'your_email_address',
      p_to => 'recipient_email_address',
      p_subject => l_subject,
      p_body => l_body
    );
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error sending email: ' || SQLERRM);
  END;
END;

This script generates an HTML email with the AWR report data and sends it using the UTL_SMTP package (you'll need to replace the placeholders with your own SMTP host, port, from address, and to address).

Step 3: Schedule the script to run every hour

Use your operating system's scheduling tool (e.g., cron on Linux/macOS or Task Scheduler on Windows) to schedule the send_awr_report.sql script to run every hour. You can use the following command:

crontab -e

Add the following line to schedule the script to run every hour:

0 * * * * sqlplus -s your_username/your_password @send_awr_report.sql

Replace your_username and your_password with your Oracle database credentials.

Step 4: Test the script

Run the send_awr_report.sql script manually to test the email sending functionality. If everything works correctly, you should receive an email with the AWR report data.

That's it! Your AWR report should now be sent via email every hour.

Mail Knowledge

Awr report mail every hour