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? :)
