To automate the generation of an AWR Report

To automate the generation of an AWR (Automatic Workload Repository) report every hour in Oracle, you can follow these steps. This will involve creating a job using Oracle DBMS_SCHEDULER to automate the execution of a script that generates the AWR report at an hourly frequency.

Step 1: Set Up AWR Snapshot Interval (if necessary)

Before automating AWR report generation, ensure that AWR snapshots are taken at appropriate intervals (in this case, every hour). By default, AWR snapshots are taken every hour, but you can verify and modify the snapshot interval.

1. Check the current snapshot interval:

   sql

   SELECT snap_interval, retention FROM dba_hist_wr_control;

   The output will show the snap_interval and retention time in minutes.

2. Modify snapshot interval (if necessary): If the snapshot interval is not hourly (60 minutes), you can modify it using the following command:

   sql

   EXEC DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(interval => 60);

Step 2: Create a SQL Script to Generate AWR Report

Create a SQL script to generate an AWR report for the last hour. You can save this script on your server as generate_awr.sql.

Example of generate_awr.sql:

sql

SET PAGESIZE 0 SET LINESIZE 1000 SET FEEDBACK OFF SET TERMOUT OFF SPOOL /path_to_directory/awr_report_`date +%Y%m%d%H%M`.html -- Fetch snapshot IDs for the last one hour DECLARE v_snap_id1 NUMBER; v_snap_id2 NUMBER; BEGIN SELECT MAX(snap_id)-1, MAX(snap_id) INTO v_snap_id1, v_snap_id2 FROM dba_hist_snapshot WHERE begin_interval_time >= (SYSDATE - 1/24); -- Look for snapshots in the last hour -- Generate the AWR report DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML( l_dbid => (SELECT dbid FROM v$database), l_inst_num => 1, -- If RAC, you can iterate this for multiple instances l_bid => v_snap_id1, l_eid => v_snap_id2, l_options => 0 ); END; / SPOOL OFF

This script generates an AWR report for the last hour and stores it in the specified path. Ensure that the SPOOL directory exists on your file system and has write permissions.

Step 3: Create an OS Script to Run the SQL Script

Next, create a shell or batch script to execute the SQL script. Let's assume you are on a Linux system, and the script is named run_awr_report.sh:

run_awr_report.sh:

bash

#!/bin/bash ORACLE_HOME=/path_to_oracle_home ORACLE_SID=your_oracle_sid export ORACLE_HOME ORACLE_SID $ORACLE_HOME/bin/sqlplus -S / as sysdba @/path_to_directory/generate_awr.sql

Make sure the shell script has execution permissions:

bash

chmod +x /path_to_directory/run_awr_report.sh

Step 4: Schedule the Job Using Oracle DBMS_SCHEDULER

Now, use Oracle's DBMS_SCHEDULER to schedule the shell script to run every hour.

1. Connect to the database as SYS or another privileged user.

2. Create a scheduler job to run the shell script:

sql

BEGIN DBMS_SCHEDULER.create_job ( job_name => 'GENERATE_AWR_HOURLY', job_type => 'EXECUTABLE', job_action => '/path_to_directory/run_awr_report.sh', -- Path to the shell script start_date => SYSTIMESTAMP, repeat_interval => 'FREQ=HOURLY; BYHOUR=0; BYMINUTE=0; BYSECOND=0', enabled => TRUE ); END; /

• job_action is the path to the shell script.

• repeat_interval sets the job to run every hour.

Step 5: Verify the Scheduled Job

You can check if the job is scheduled and running correctly by querying the job status:

sql

SELECT job_name, state, last_start_date, next_run_date FROM dba_scheduler_jobs WHERE job_name = 'GENERATE_AWR_HOURLY';

Step 6: Monitor the Job Execution

If you want to monitor the execution log for the scheduler job, you can query the scheduler job log:

sql

SELECT job_name, status, log_date, additional_info FROM dba_scheduler_job_run_details WHERE job_name = 'GENERATE_AWR_HOURLY';

Step 7: (Optional) Modify or Disable the Job

• Disable the job:

  sql

  BEGIN DBMS_SCHEDULER.disable('GENERATE_AWR_HOURLY'); END; /

• Drop the job:

  sql

  BEGIN DBMS_SCHEDULER.drop_job('GENERATE_AWR_HOURLY'); END; /

Conclusion

By following these steps, you can automate the generation of AWR reports on an hourly basis. The combination of a shell script and Oracle’s DBMS_SCHEDULER provides a robust method to automate the process and ensure regular performance monitoring through AWR reports.