Batch mode AWR report generation

Sometimes it is necessary to generate the awr report for the whole day or couple of interval (I generally do it for trend analysis). Doing it manually for every interval could be a tedious job. I have written a small script which you can run with starting and ending snap_id and it generates reports in below format:


instance_name_startsnapid_endsnapid_date_starttime_endtime.lst

Eg:
instancename_8837_8838_08_25_2008_15_00_16_00.lst
instancename_8838_8839_08_25_2008_16_00_17_00.lst
instancename_8839_8840_08_25_2008_17_00_18_00.lst
instancename_8840_8841_08_25_2008_18_00_19_00.lst
instancename_8841_8842_08_25_2008_19_00_20_00.lst
instancename_8842_8843_08_25_2008_20_00_21_00.lst
instancename_8843_8844_08_25_2008_21_00_22_00.lst
instancename_8844_8845_08_25_2008_22_00_23_00.lst
instancename_8845_8846_08_25_2008_23_00_00_00.lst


Script:

AWR_report_generation.sh

#!/bin/ksh

# AWR_report_generation.shV1.2 03-Aug-09
#
# Author - Kapil Goyal
# Batch mode AWR report generation
# This script needs minimum 2 arguments. First - Starting snap_id, Second - Ending Snap_id
# You need to change username/password. whomsoever has Grant execute on DBMS_WORKLOAD_REPOSITORY
# If you encounter any issue while running it contact me at kapil_goyal@yahoo.com
# It can also take third argument for incremental reports. Eg. if you take snapshots every 20 minutes but want a report
# for every hour then you can pass third argument - 3.

s_snap_id=$1
e_snap_id=$2

if [ "$3" = "" ]; then
step=1
else
step=$3
fi

while [ $s_snap_id -lt $e_snap_id ]
do
incr=`expr $s_snap_id + $step`

sqlplus /nolog << !
connect username/password

define num_days = 0;
define report_type='text';
define begin_snap='$s_snap_id'
define end_snap ='$incr'

column i_name new_value i_name
col dt new_value dt

select instance_name i_name from v\$instance;

select a.snap_id||'_'||b.snap_id||'_'||
to_char(a.end_interval_time,'MM_DD_YYYY_HH24_MI')||
'_'|| to_char(b.end_interval_time,'HH24_MI') dt
from dba_hist_snapshot A, dba_hist_snapshot B
where a.instance_number = (select instance_number from v\$instance) and
a.dbid = (select dbid from v\$database)
and a.snap_id=&begin_snap and b.snap_id=$incr;

define report_name = &i_name\_&dt

@?/rdbms/admin/awrrpt
!

s_snap_id=`expr $incr`;

done