GENERATING MULTIPLE AWR REPORTS IN ONE STEP

by Victor Khalturin 16.01.2018

AWR 2

REQUEST

I want to generate AWR reports for all available SNAP ID.

SOLUTION

This post contains a dbs_gawrr.sh script designed to generate multiple Automatic Workload Repository (AWR) reports automatically based on snapshots ranges. If you need AWR reports, for instance, for 2 weeks, it’s much easier running the script than generating it manually one by one.

The script accepts following parameters as input values:

    s|start     : Snapshot to start from
    e|end       : Snapshot to end to
    i|increment : [Optional] How many snapshots a report should contain. Default value is 1.
    d|directory : [Optional] Directory for saving reports. The default directory is a directory where script is located.
    debug       : [Optional] Enables debug output when generating an AWR report

for example

./dbs_gawrr.sh s=1 e=5 i=5 will generate 1 AWR reports  
./dbs_gawrr.sh s=1 e=5 i=1 will generate 5 AWR reports

NOTE: To run downloaded script you should convert it to linux format by

{
wget http://dbpilot.net/wp-content/uploads/2020/05/dbs_gawrr.zip
unzip dbs_gawrr.zip
chmod 500 dbs_gawrr.sh
dos2unix dbs_gawrr.sh
}

The body of the script is the following

#!/bin/bash

######################################################
## Victor Khalturin
##
## Version 6:20 PM 5/17/2020 (002)
##
## dbs_gawrr.sh generates AWR reports based on snapshots ranges.
##
## The following variables must be set up before invoking the script:
## export ORACLE_SID=
## export ORACLE_HOME=
## export PATH=${ORACLE_HOME}/bin:${PATH}
######################################################


## *************************************************
## * To apply automatically your Oracle environment variables
## *************************************************
## source /home/oracle/your_env.env


## *************************************************
## * Terminate an execution of the script when pressing CTRL+C
## *************************************************
trap 'exit' SIGINT


## *************************************************
## * Environment verification
## *************************************************
if [[ -z "${ORACLE_SID}" || -z "${ORACLE_HOME}" || -z "${PATH}" ]];
then 
	echo
    echo "  Some of environment variables are not set. Terminated."
    echo
	echo "  =========================================="
	echo "  ORACLE_SID  : ${ORACLE_SID}"
	echo "  ORACLE_HOME : ${ORACLE_HOME}"
	echo "  PATH        : ${PATH}"
	echo "  =========================================="
	echo 
	exit 100 
fi


## *************************************************
## * Get script arguments 
## *************************************************
ONLY_NUMBERS_ARE_ALLOWED () {

if [[ ! "${1}" =~ ^[0-9]+$ ]];
then 
	echo
	echo " You have provided an incorrect parameter in the following argument : ${agrument}" 
	echo " Only numbers are allowed."
    echo " Terminated."
	echo 
	exit 100
else 
	return "${1}"
fi 

}


VERIFY_A_DIRECTORY_EXISTENCE () {

if [[ -z "${1}" || ! -d "${1}" || ! -w "${1}" ]];
then 
	echo 
	echo " The specified directory does not exist or it's not writable."
	echo " Directory : ${1}"
	echo 
	echo " Terminated."
	echo 
	exit 100
fi 

}

SCRIPT_ARGUMENTS=("$@")

for agrument in "${SCRIPT_ARGUMENTS[@]}";
do 
	case $(echo ${agrument} | tr '[:lower:]' '[:upper:]' ) in 
	     S=* )                FIRST_SNAPSHOT=$(echo ${agrument} | sed 's/[a-zA-Z]*=//g') && ONLY_NUMBERS_ARE_ALLOWED "${FIRST_SNAPSHOT}" ;;
		 START=* )            FIRST_SNAPSHOT=$(echo ${agrument} | sed 's/[a-zA-Z]*=//g') && ONLY_NUMBERS_ARE_ALLOWED "${FIRST_SNAPSHOT}" ;;
	     E=* )                LAST_SNAPSHOT=$(echo ${agrument} | sed 's/[a-zA-Z]*=//g') && ONLY_NUMBERS_ARE_ALLOWED "${LAST_SNAPSHOT}" ;;
		 END=* )              LAST_SNAPSHOT=$(echo ${agrument} | sed 's/[a-zA-Z]*=//g') && ONLY_NUMBERS_ARE_ALLOWED "${LAST_SNAPSHOT}" ;;
	     I=* )                INCREMENTAL_VALUE=$(echo ${agrument} | sed 's/[a-zA-Z]*=//g') && ONLY_NUMBERS_ARE_ALLOWED "${INCREMENTAL_VALUE}" ;;
		 INCREMENT=* )        INCREMENTAL_VALUE=$(echo ${agrument} | sed 's/[a-zA-Z]*=//g') && ONLY_NUMBERS_ARE_ALLOWED "${INCREMENTAL_VALUE}" ;;
		 D=* )                DIRECTORY_FOR_REPORTS=$(echo ${agrument} | sed 's/[a-zA-Z]*=//g') && VERIFY_A_DIRECTORY_EXISTENCE "${DIRECTORY_FOR_REPORTS}" ;;
		 DIRECTORY=* )        DIRECTORY_FOR_REPORTS=$(echo ${agrument} | sed 's/[a-zA-Z]*=//g') && VERIFY_A_DIRECTORY_EXISTENCE "${DIRECTORY_FOR_REPORTS}" ;;
         DEBUG )              DEBUG=TRUE ;;
		 * )                  echo -e "\n Unsupported parameter [${agrument}].\n Terminated.\n" && exit 100 ;;
	esac 
done


## ********************************************************************
## * The debug function
## ********************************************************************

silent () {

  if [ ! -z "${DEBUG}" ];
  then
    "$@"
  else
    "$@" &>/dev/null
  fi
}

## *************************************************
## * Functions
## *************************************************

LIST_OF_ALL_AVAILABLE_SNAPSHOTS () {
sqlplus -s '/ as sysdba' <<EOF 
SET PAGES 50000
SET LINES 50
SET FEEDBACK OFF
COL SNAP_ID FOR 99999999999
COL STARTED_SNAP FOR A30 
SELECT SNAP_ID,TO_CHAR(END_INTERVAL_TIME,'DD-MON-YYYY HH24:MI') STARTED_SNAP FROM DBA_HIST_SNAPSHOT ORDER BY SNAP_ID;

SET HEADING OFF
SELECT '' FROM DUAL
UNION ALL 
SELECT 'Distinct snapshots ' || COUNT(*) FROM DBA_HIST_SNAPSHOT
UNION ALL 
SELECT '' FROM DUAL
UNION ALL 
SELECT 'Lower snapshot ' || SNAP_ID || ' (' || TO_CHAR(END_INTERVAL_TIME,'DD-MON-YYYY HH24-MI') || ')' FROM DBA_HIST_SNAPSHOT WHERE SNAP_ID=(SELECT MIN(SNAP_ID) FROM DBA_HIST_SNAPSHOT)
UNION ALL 
SELECT 'Upper snapshot ' || SNAP_ID || ' (' || TO_CHAR(END_INTERVAL_TIME,'DD-MON-YYYY HH24-MI') || ')' FROM DBA_HIST_SNAPSHOT WHERE SNAP_ID=(SELECT MAX(SNAP_ID) FROM DBA_HIST_SNAPSHOT)
UNION ALL 
SELECT '' FROM DUAL;
EOF

}



GET_MIN_AND_MAX_SNAPSHOTS () {

MIN_SNAPSHOT=$(sqlplus -s '/ as sysdba' <<EOF
SET HEAD OFF
SET PAGES 0
SET FEED OFF 
SELECT MIN(SNAP_ID) FROM DBA_HIST_SNAPSHOT;
EOF
)
## Remove extra space 
MIN_SNAPSHOT=$(echo ${MIN_SNAPSHOT} | tr -s " ")

MAX_SNAPSHOT=$(sqlplus -s '/ as sysdba' <<EOF
SET HEAD OFF
SET PAGES 0
SET FEED OFF 
SELECT MAX(SNAP_ID) FROM DBA_HIST_SNAPSHOT;
EOF
)
MAX_SNAPSHOT=$(echo ${MAX_SNAPSHOT} | tr -s " ")

NUMBER_OF_SNAPSHOTS=$(sqlplus -s '/ as sysdba' <<EOF
SET HEAD OFF
SET PAGES 0
SET FEED OFF 
SELECT count(*) FROM DBA_HIST_SNAPSHOT;
EOF
)
NUMBER_OF_SNAPSHOTS=$(echo ${NUMBER_OF_SNAPSHOTS} | tr -s " ")

}


## Print list of all available snapshots if no arguments are provided.
if [[ -z "${SCRIPT_ARGUMENTS[@]}" ]];
then
	LIST_OF_ALL_AVAILABLE_SNAPSHOTS

    line="-------------------"
    echo -e "\n${line}\nThe script usage:\n${line}\n\n  ${0} s= e= [i= d= debug]\n\n  where\n  s= is first snapshot (s stand for start)\n  e= is last snapshot (e stands for end)"
    echo -e "  i= is snapshot interval (i stands for incremental). The default value is 1.\n  d= is directory for a report (d stands for directory). The default directory is a directory where script is located."
	echo -e "  debug is optional and used to debug any issues with generating of an awr report.\n\n"
    echo -e "${line}\nUsage example:\n${line}\n\n  ${0} s=11 e=20\n  it generates 9 reports with snaphost interval of 1. \n\n"
	exit 0
fi


## *************************************************
## * Set default values 
## *************************************************
GET_MIN_AND_MAX_SNAPSHOTS

if [[ -z "${FIRST_SNAPSHOT}" ]];
then 
	FIRST_SNAPSHOT="${MIN_SNAPSHOT}"
fi 

if [[ -z "${LAST_SNAPSHOT}" ]];
then 
	LAST_SNAPSHOT="${MAX_SNAPSHOT}"
fi

if [[ -z "${INCREMENTAL_VALUE}" ]];
then 
	INCREMENTAL_VALUE=1 
fi 

if [[ -z "${DIRECTORY_FOR_REPORTS}" ]];
then 
	DIRECTORY_FOR_REPORTS="$(dirname $(readlink -f ${0}))"
fi


## *************************************************
## * Generate reports 
## *************************************************
WARNING_MESSAGE () {
echo 
echo "   Specified ${1} snapshot [${2}] does not exists."
echo "   Default one [${3}] will be used instead."
echo 
}


echo
echo "   Number of snapshots         : ${NUMBER_OF_SNAPSHOTS}"
echo "   Lower available snapshot    : ${MIN_SNAPSHOT}"
echo "   Highest available snapshot  : ${MAX_SNAPSHOT}"
echo 
echo "   Requested lower snapshot    : ${FIRST_SNAPSHOT}"
echo "   Requested upper snapshot    : ${LAST_SNAPSHOT}"
echo "   Requested snapshot interval : ${INCREMENTAL_VALUE}"
echo "   Directory for reports       : ${DIRECTORY_FOR_REPORTS}"
echo     

## Non existence snapshot 
if [[ "${FIRST_SNAPSHOT}" -lt "${MIN_SNAPSHOT}" || "${FIRST_SNAPSHOT}" -gt "${MAX_SNAPSHOT}" ]];
then 
	WARNING_MESSAGE "lower" "${FIRST_SNAPSHOT}" "${MIN_SNAPSHOT}"
	FIRST_SNAPSHOT=${MIN_SNAPSHOT}
fi 

if [[ "${LAST_SNAPSHOT}" -gt "${MAX_SNAPSHOT}" || "${LAST_SNAPSHOT}" -lt "${MIN_SNAPSHOT}" ]];
then
	WARNING_MESSAGE "upper" "${LAST_SNAPSHOT}" "${MAX_SNAPSHOT}"
	LAST_SNAPSHOT=${MAX_SNAPSHOT}
fi

## Generate
for (( snapshot_id=${FIRST_SNAPSHOT}; snapshot_id<=${LAST_SNAPSHOT}-${INCREMENTAL_VALUE}; snapshot_id+=${INCREMENTAL_VALUE} ));
do
	v_start_snapshot=${snapshot_id}
	v_end_snapshot=$((${snapshot_id} + ${INCREMENTAL_VALUE}))
	v_awr_name="${DIRECTORY_FOR_REPORTS}"/awrrpt_${v_start_snapshot}_${v_end_snapshot}.html
	
silent sqlplus -s '/ as sysdba' <<EOF
define report_type  = 'html'
define num_days     = ''
define begin_snap   = ${v_start_snapshot}
define end_snap     = ${v_end_snapshot}
define report_name  = ${v_awr_name}
@?/rdbms/admin/awrrpt.sql
EOF

printf "\\40\\40\\40%-35s\n" "awrrpt_${v_start_snapshot}_${v_end_snapshot}.html"

done

#####################################################
## End of dbs_gawrr.sh script 
## Version 6:20 PM 5/17/2020 (002)
#####################################################

An output of the script will look like


oracle@dbpilot|16:28|~/awr: ./dbs_gawrr.sh s=14291 e=14295 i=1

   Number of snapshots         : 205
   Lower available snapshot    : 14100
   Highest available snapshot  : 14304

   Requested lower snapshot    : 14291
   Requested upper snapshot    : 14295
   Directory for reports       : /home/oracle/awr

   awrrpt_14291_14292.html
   awrrpt_14292_14293.html
   awrrpt_14293_14294.html
   awrrpt_14294_14295.html
   awrrpt_14295_14296.html

   
oracle@dbpilot|16:29|~/awr: ls -ltrh
total 4.2M
-rwx------ 1 oracle oinstall 7.2K Nov 23 16:28 dbs_gawr.sh
-rw-r--r-- 1 oracle oinstall 826K Nov 23 16:28 awrrpt_14291_14292.html
-rw-r--r-- 1 oracle oinstall 838K Nov 23 16:29 awrrpt_14292_14293.html
-rw-r--r-- 1 oracle oinstall 828K Nov 23 16:29 awrrpt_14293_14294.html
-rw-r--r-- 1 oracle oinstall 873K Nov 23 16:29 awrrpt_14294_14295.html
-rw-r--r-- 1 oracle oinstall 831K Nov 23 16:29 awrrpt_14295_14296.html

The dbs_gawrr.sh script can be downloaded here : dbs_gawrr

Version : 16:42 23.11.2017
Updated : 6:29 PM 5/17/2020
Platform : Red Hat 6.8
Database : Oracle database 12.2.0.1.0

Tags In

Automatic Workload Repository AWR Scripts

6 / 73

2 Comments

ManPau 25.10.2019 at 13:38

Thanks for your cool script 🙂

Found a little mistake where the check for non existing snapshots is done:
Instead of the less-than-sign ( ) you should use -lt and -gt when comparing the given snapshots.

## Non existence snapshot
if [[ “${FIRST_SNAPSHOT}” -lt “${MIN_SNAPSHOT}” || “${FIRST_SNAPSHOT}” -gt “${MAX_SNAPSHOT}” ]];
then
DO_SNAPSHOT_WARNING_MESSAGE “lower” “${FIRST_SNAPSHOT}” “${MIN_SNAPSHOT}”
FIRST_SNAPSHOT=${MIN_SNAPSHOT}
fi

if [[ “${LAST_SNAPSHOT}” -gt “${MAX_SNAPSHOT}” || “${LAST_SNAPSHOT}” -lt “${MIN_SNAPSHOT}” ]];
then
DO_SNAPSHOT_WARNING_MESSAGE “upper” “${LAST_SNAPSHOT}” “${MAX_SNAPSHOT}”
LAST_SNAPSHOT=${MAX_SNAPSHOT}
fi

Cheers

Reply
1. Victor Khalturin 28.10.2019 at 12:58
  
  Thank you for your feedback. The script has been corrected.
  
  Reply

GENERATING MULTIPLE AWR REPORTS IN ONE STEP

Tags In

2 Comments

Leave a Reply Cancel reply

Categories

GENERATING MULTIPLE AWR REPORTS IN ONE STEP

Tags In

Related Posts

CREATING A LOCAL YUM REPOSITORY USING AN ISO IMAGE

ORA-1555: SNAPSHOT TOO OLD DUE TO CORRUPTED LOBS

WRITING AN OPERATING SYSTEM FILE THROUGH A PL/SQL BLOCK

2 Comments

Leave a Reply Cancel reply

Categories

Tags