Script to generate series of awr reports

If you want to generate multiple 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

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>