Script/Automation: Generate AWR Report across all RAC nodes

I have found it useful many a time using the following script to generate AWR report for a given snap shot range for all the Nodes in a RAC environment.

I plan to post several automation scripts over a period of time.


#!/bin/ksh
REPORT_DIR=/outputdir/awr;

run_awr_report()
{
sqlplus -s / as sysdba <<EOF
define  inst_num     = $INSTANCE_ID;
define  num_days     = 3;
define  inst_name    = '$INSTANCE_NAME';
define  db_name      = '$DB_NAME';
define  dbid         = $DB_ID;
define  begin_snap   = $BEGIN_SNAP_ID;
define  end_snap     = $END_SNAP_ID;
define  report_type  = 'text';
define  report_name  = $REPORT_NAME
@@?/rdbms/admin/awrrpti
EOF
}


if (( $# < 2 ))
   then
     print "Required parms not specified"
     print "Usage snap_report.sh <Begin Snap ID> <End Snap Id>";
     exit 3
 fi
BEGIN_SNAP_ID=$1
END_SNAP_ID=$2

echo "
    set feedback off verify off heading off pagesize 0 echo off;
    select d.name, i.instance_number, i.instance_name, d.dbid \
    from   gv\$database d, gv\$instance i \
    where  i.inst_id = d.inst_id;
    exit;
    " |  sqlplus  -s / as sysdba  | while read DB_NAME INSTANCE_ID INSTANCE_NAME DB_ID
    do
         echo Running the report for:
         echo $DB_NAME - $INSTANCE_NAME
         REPORT_FILE=${INSTANCE_NAME}_${BEGIN_SNAP_ID}_${END_SNAP_ID}.lst
         REPORT_NAME=${REPORT_DIR}/${REPORT_FILE}
         run_awr_report

    done