AWR Reports automation

We can find the retention and snap interval from below query.

select * from DBA_HIST_WR_CONTROL;

DBID SNAP_INTERVAL RETENTION TOPNSQL
3439180179 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT

we want to change the retention to 15 days and intervel to 30 mins. For that we executed below scripts

BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 21600, -- in minutes
interval => 30);
END;
/

## We can automate this awr report generation process by following scripts. Before we start we need to keep in mind that a table contains username and password of a user who will generate awr report from different databases. Let say the Role is role_awr and the user name is usr_awr. This user and role will need to be created on each database.

create role role_awr;
GRANT SELECT ON SYS.V_$DATABASE TO role_awr;
GRANT SELECT ON SYS.V_$INSTANCE TO role_awr;
GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO role_awr;
GRANT SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE TO role_awr;
GRANT SELECT ON SYS.DBA_HIST_SNAPSHOT TO role_awr;
GRANT ADVISOR TO role_awr;

create user usr_awr identified by *****;
grant create session to usr_awr;
grant role_rpt_awr to usr_awr;

Now the below scripts will run from the repository machine where all the reports will be gathered.

#!/bin/bash
#before use this script
# - create as described in user_awr.txt on target DB
# - Add the tns on local tnanames.ora file
# - insert TNS_name,user,pass in table user.awr_db_list@ur_db for the target DB

echo "-- ---------------"
echo "-- start of script"
echo "-- ---------------"

export ORACLE_HOME=/oracle/product/11.2.0/dbhome_1
BASE_DIR=/data01/oradata/awr_reports_dir
NUM_DAYS=10 #change it if need to SNAP of longer than this

MONTH=`date +"%b%y"`
DAY=`date +"%d%b%y"`

CURR_DIR="$BASE_DIR"/reports/"$MONTH"/"$DAY"
mkdir -p $CURR_DIR

AWR_SQL_SCRIPT="$BASE_DIR"/scripts/awrrpti.sql

# SQL to find first snap of first day(sun) of last week
SQL_BEGIN_SNAP_ID="select 'BEGIN_SNAP_ID:'||min(snap_id) from DBA_HIST_SNAPSHOT where trunc(begin_interval_time) >= trunc(sysdate - 6, 'DAY');"
# SQL to find first snap of first day(sun) of current week
SQL_END_SNAP_ID="select 'END_SNAP_ID:'||min(snap_id) from DBA_HIST_SNAPSHOT where trunc(begin_interval_time) >= trunc(sysdate, 'DAY');"
SQL_DB_NAME="select 'DB_NAME:'||name||':'||dbid from v\$database;"
SQL_INST_NAME="select 'INST_NAME:'||instance_name||':'||instance_number from v\$instance;"

#to get TNS,USER & PASS of target DBs from local DB
SQL_DBUSER="select 'DBUSER:'||username||':'||password||':'||TNS from user.awr_db_list where status='A';"

#connecting silent mode (-s) and using (set heading off) to avoid extra output from SQLPLUS
#connecting to local database (ur_db,where user/pass of target DB are stoted) as sysdba to avoid password
export ORACLE_SID=ur_db
OUTPUT=`$ORACLE_HOME/bin/sqlplus -s '/ as sysdba' <set pagesize 0
set heading off
set feedback off
$SQL_DBUSER
exit
<# converting into ARRAY by using $OUTPUT without ("),
# to convert the query output as space separated
DB_ARRAY=(`echo $OUTPUT|grep DBUSER:`)

# ********* START OF LOOP ***********#
for (( i = 0 ; i < ${#DB_ARRAY[@]} ; i++ ))
do

DBUSER="`echo ${DB_ARRAY[$i]}|cut -d: -f2`"
DBPASS="`echo ${DB_ARRAY[$i]}|cut -d: -f3`"
DBNAME="`echo ${DB_ARRAY[$i]}|cut -d: -f4`" #TNS

echo "CURRENT DB: $DBNAME"
echo ""

OUTPUT=`$ORACLE_HOME/bin/sqlplus $DBUSER/$DBPASS@$DBNAME <set heading off
$SQL_BEGIN_SNAP_ID
$SQL_END_SNAP_ID
$SQL_DB_NAME
$SQL_INST_NAME
exit
<echo "$OUTPUT"|grep SNAP_ID:

# output from SQL_BEGIN_SNAP_ID
BEGIN_SNAP=`echo "$OUTPUT"|grep BEGIN_SNAP_ID:|cut -d: -f2`
# output from SQL_END_SNAP_ID
END_SNAP=`echo "$OUTPUT"|grep END_SNAP_ID:|cut -d: -f2`
# output from SQL_DB_NAME
DB_NAME=`echo "$OUTPUT"|grep DB_NAME:|cut -d: -f2`
DBID=`echo "$OUTPUT"|grep DB_NAME:|cut -d: -f3`
# output from SQLINST_NAME
INST_NAME=`echo "$OUTPUT"|grep INST_NAME:|cut -d: -f2`
INST_NUM=`echo "$OUTPUT"|grep INST_NAME:|cut -d: -f3`

REPORT_NAME="$CURR_DIR"/"$DBNAME"_"$DAY".html

OUTPUT=`$ORACLE_HOME/bin/sqlplus $DBUSER/$DBPASS@$DBNAME <define inst_num = "$INST_NUM";
define num_days = $NUM_DAYS;
define inst_name = '"$INST_NAME"';
define db_name = '"$DB_NAME"';
define dbid = "$DBID";
define begin_snap = "$BEGIN_SNAP";
define end_snap = "$END_SNAP";
define report_type = 'html';
define report_name = "$REPORT_NAME"
@$AWR_SQL_SCRIPT
exit
<
#echo "$OUTPUT"
echo "-- -------------"
echo ""

done
# ********* END OF LOOP ***********#

This will be applicable for all 10g and above version of databases.

For 9i Statspack is a good choice. detail can be found in below link

http://download.oracle.com/docs/cd/E14981-01/wli/docs1031/dbtuning/statsApdx.html