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
Yannick Jaquier on LinkedinYannick Jaquier on RssYannick Jaquier on Twitter
Yannick Jaquier
Find more about me on social media.

2 thoughts on “Script to generate series of awr reports

  1. 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 !

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>