Shell script to generate awr reports for a range of snapshots

We have been doing some performance testing in the recent days. We were running some workload using an application, capturing awr snapshots every 15 minutes on a 8 node rac cluster. I needed to generate the awr reports between each of the snapshots for each of the nodes. Running awrrpt.sql for each 15 minute intervals for each one of the 8 nodes would have been a tedious task. So i wrote a bash shell script that generates those awr reports.

It takes the database id, instance number, start snapshot id and end snapshot id as arguments (you can query dba_hist_snapshot to find out the start and end snapshot id’s you want to use). The directory in which it generates the reports is hardcoded in the script. The script has to be run as the oracle user  and it logs in as sys to generate the reports (You can modify as you need if you want it to be run as a different user). I have tested it only on Linux.

You can run it as ./genawrs.sh dbid  instancenumber beginsnap endsnap, to generate the reports.  This means that you have to run it once for each instance you need the report for. The script can be found below.

#!/bin/bash

if [ $# != 4 ]
then
echo “Syntax  genawrs.sh dbid instanceId startsnapid endsnapid”
exit 1
fi

l_dbid=$1
l_instid=$2
l_start_snapid=$3
let l_end_snapid=$4-1

# For all snapshot id’s
# Set the next snapshot id as current snapshot id + 1
# Spool a log file
# Log into sqlplus and call dbms_workload_repository.awr_report_text
# To generate the awr

for i in `seq $l_start_snapid $l_end_snapid`
do
let l_next_snapid=$i+1;
l_awr_log_file=”/awrs/out/awrrpt_${2}_${i}_${l_next_snapid}.log”

sqlplus -s / as sysdba << EOC
set head off
set pages 0
set lines 132
set echo off
set feedback off
spool $l_awr_log_file
SELECT
output
FROM
TABLE
(dbms_workload_repository.awr_report_text
($l_dbid,$l_instid,$i,$l_next_snapid)
);
spool off
EOC
done

2 thoughts on “Shell script to generate awr reports for a range of snapshots

  1. Can you please guide me to generate 2 hrs interval for AWR report.
    For example: Using the mentioned script If I use snapshot id + 2 ,then It is generating for 2 hours but it is taking 1 hr extra for every interval.For example: To generate from 10.00AM to 15.00PM , it will generate like 10-12,11-13,12-14,13-15.Can you put some inputs on this.

    Thanks in Advance,

    –Manoj

    1. Manoj.

      Its going to take a bit of a hack.
      Right after the line
      let l_next_snapid=$i+1;
      Add a new line
      let l_prev_snapid=$l_next_snapid-2;

      Modify the line
      l_awr_log_file=”/awrs/out/awrrpt_${2}_${i}_${l_next_snapid}.log”
      to
      l_awr_log_file=”/awrs/out/awrrpt_${2}_${l_prev_snapid}_${l_next_snapid}.log”

      Modify the line
      ($l_dbid,$l_instid,$i,$l_next_snapid)
      to
      ($l_dbid,$l_instid,$l_prev_snapid,$l_next_snapid)

      See if it works. (I havent tested it).

      -Rajeev.
      -Rajeev.

Leave a Reply to Manoj Cancel reply

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