Script to generate series of awr reports
If you want to generate series of AWR reports, it is really boring to use the provided script awrrpt.sql as you have to launch it per each step of the interval. Following script automate this generation and will ask for the start and last snapshot and then generate all intermediate AWR reports.
#!/bin/ksh
#
# Y.JAQUIER 03-FEB-2010 Creation
#
# script to generate series of awr reports
#
LOGFILE=/tmp/awr.log
sqlplus -s / as sysdba << EOF > $LOGFILE
set lines 130
set feedback off
set pages 0
select
'export dbid=' || d.dbid as dbid
from
v\$database d;
select
'export db_name=' || d.name as db_name
from
v\$database d;
select
'export inst_num=' || i.instance_number as inst_num
from
v\$instance i;
select
'export inst_name=' || i.instance_name as inst_name
from
v\$instance i;
EOF
while read line
do
eval $line
done < $LOGFILE
sqlplus -s / as sysdba << EOF
set lines 130
set pages 1000
select
snap_id,
to_char(end_interval_time,'dd-Mon-YYYY hh24:mi') as snapdat
from dba_hist_snapshot
order by snap_id;
EOF
echo enter initial snapshot
read ininum
echo enter final snapshot
read endnum
echo ininum = $ininum
echo endnum = $endnum
while [ $ininum -lt $endnum ];
do
nxtnum=`expr $ininum + 1`
repnam='awrrpt_'$inst_num'_'$ininum'_'$nxtnum'.html'
sqlplus -s / as sysdba << EOF
define inst_num = $inst_num;
define num_days = 100;
define inst_name = $inst_name;
define db_name = $db_name;
define dbid = $dbid;
define report_type = 'html';
define begin_snap = $ininum;
define end_snap = $nxtnum;
define report_name = $repnam;
@@?/rdbms/admin/awrrpti
EOF
ininum=$nxtnum
done




Thanks for this idea of a script, this is a lot easier to work out what’s required compare to the other AWR generator in MOS. BTW, had to escape the $, so v\$database instead of v$database. Manage to make several versions of the same script to run awrgrpt which is the one I can’t find from Googling. This script gives me the solution on what to do.
Welcome ! You probably right for escaping the $ symbol. Probably a wrong copy/paste, let me review and retest it and I’ll update the post. Thanks for constructive comment !