GENERATING MULTIPLE AWR REPORTS IN ONE STEP

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