#!/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