RDBMS/RAC -- gathering AWR, an easy and efficient way

Today, I wanto to share a handy script with you.. It is prepared for gathering AWR reports, especially from Oracle Real Application Clusters (RAC).

This script help us to gather AWR reports without using the necessary tools such as awrrpt.sql, awrgrpt.sql, toad's AWR manager and so on.. It is also RAC aware :)

By using this script, we can just specify the AWR snapshot intervals and generate all the AWR reports between those intervals.

The count of the AWR reports generated, depends on our snapshot interval.

That is, if we have a 1-hour long AWR snapshot interval, and if we use this script to generate all the AWRs for a day; then we will end up with 24 AWR reports each generated for a 1-hour long interval.

The script is as follows;

set trimspool on trimout on

set lines 1500

set echo off

set heading off

set pages 0

set feedback off

set verify off

set trimspool on trimout on

spool generate_awr_reports&1..sql

select 'set heading off'   || chr(10) ||

      'set feedback off'  || chr(10) ||

      'set linesize 5000' || chr(10) ||

      'set trimspool on trimout on' || chr(10) ||

      'spool awr_'|| to_char(instance_number) || '_' || to_char(snap_id) || '_' || to_char(snap_id+1) || '.html' || chr(10) ||

      'select output from table(dbms_workload_repository.awr_report_html(' || to_char(dbid) || ',' || to_char(instance_number) || ',' ||

to_char(snap_id) || ',' || to_char(snap_id+1) || '));' || chr(10) ||

      'spool off'

from DBA_HIST_SNAPSHOT

where instance_number=&1

and snap_id < ( select max(snap_id) from dba_hist_snapshot where instance_number=&1)

order by snap_id;

spool off

So, we just copy this script in to a file and save it as AWR_WRAPPER.sql

Ofcourse we may modify it according to our snapshot interval.

I mean, we may modify the line starting with "and snap_id" according to our needs.

For instance; snap_id between <some_snap_id> and <some_other_snap_id>

Once, we modify the script and save it; we run it one by one for each of our Oracle RAC instances;

For example;

sqlplus /as sysdba

@AWR_WRAPPER.sql 1    -- for instance 1

@AWR_WRAPPER.sql 2    -- for instance 2

By running this script for each of our RAC instances; there will 2 scripts generated (in case we run it in a node RAC env)

These 2 scripts named with the prefix "generate_awr_reports" are actually the ones , which are supposed to be used for gatherin our AWRs.

So , once they are generated, we run them;

@generate_awr_reports1.sql    -- for instance 1

@generate_awr_reports2.sql    -- for instance 2

That 's it.. After the completing of these executions, we will end up with all the AWR reports generated for our snapshot interval in our current working directory.

Pretty cool right? :)