How to generate Multiple AWR reports

Multiple AWR report generation script

The following script can be used for AWR reports generation, for specific intervals between the required Snapshots generated.


The below script (awr_report_generate.sh) is to generate AWR reports for the specific intervals between the required Snapshots generated.
Let say, there is a requirement to generate awr reports for every 2 hours of previous day from 00:00 hrs to current date 00:00 hrs ie., 24 hrs.

Reuirements:-
===========

First we need to get the Begin Snap ID and End Snap ID, in order to generate multiple awr reports using the below script.

$ sqlplus "/as sysdba"
SQL> @?/rdbms/admin/awrrpt.sql       (For RAC, SQL> @?/rdbms/admin/awrrpti.sql)
.
.
.

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type:                    ---------------> press 'Enter' Key

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for num_days:

Listing the last n days of Completed Snapshots  ---------------> here 'n' represents the number of days we have entered for generating awr reports.

NOTE:-
====
Here, for the required period, make a note of the required Begin Snap ID and End Snap ID.


Create a script awr_report_generate.sh:-
======================================
SQL> exit

$ vi awr_report_generate.sh

#!/usr/bin/sh
echo "Enter the value for Begin Snapshot Id :"
read beginid
echo "Enter the value for End Snapshot Id:"
read endid
echo "Enter the value for interval between Snapshot Id's. To generate reports between consecutive Snapshop Id's, Enter '1'. Else, enter desired value:"
read snapint
echo "Enter the value for report type: html/text"
read repfmt
echo "Enter the path for unix directory to generate the reports. Press 'Enter' to generate the reports in current working directory:"
read repdir
if [ "$repdir" = "" ]
then
repdir=$PWD
fi

while [ $beginid -lt $endid ]
do
tempid=`echo $beginid + $snapint |bc`
sqlplus -s '/as sysdba'<<EOF
set verify off
set feedback off
set pages 0
set serveroutput on
clear break compute;
repfooter off;
ttitle off;
btitle off;

set heading on;
set timing off veri off space 1 flush on pause off termout on numwidth 10;
set echo off feedback off pagesize 0 linesize 1500 newpage 1 recsep off;
set trimspool on trimout on define "&" concat "." serveroutput on;
set underline on;
col endid new_value endid;
col repname new_value repname;
col dbid new_value dbid;
col inst_num new_value inst_num;

define beginid=$beginid;
define tempid=$tempid;

variable repname varchar2(60);
variable dbid varchar2(10);
variable inst_num varchar2(2);

select dbid dbid from v\$database;
select instance_number inst_num from v\$instance ;
select '$repdir/AWR_'||(select instance_name inst_name from v\$instance)||'_'||(select to_char(END_INTERVAL_TIME,'DDMONYY_HH24MI')from dba_hist_snapshot where snap_id='$beginid' and instance_number=(select instance_number from v\$instance))||'_'||(select to_char(END_INTERVAL_TIME,'DDMONYY_HH24MI')from dba_hist_snapshot where snap_id='$tempid' and instance_number=(select instance_number from v\$instance))||'.$repfmt' repname from dual;
spool &repname
select output from table(dbms_workload_repository.awr_report_$repfmt(&dbid,&inst_num,&&beginid,&&tempid,0));
spool off
exit
EOF
beginid=`echo $beginid + $snapint |bc`
done


-- Press esc
:wq

$ ls -lrt awr_report_generate.sh

Run the script:-
==============

$ sh awr_report_generate.sh


$ ls -lrt