GENERATING MULTIPLE AWR REPORTS IN ONE STEP
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
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
Updated : 6:29 PM 5/17/2020
Platform : Red Hat 6.8
Database : Oracle database 12.2.0.1.0
2 Comments
Leave a Reply
Social Link(s)
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
Thank you for your feedback. The script has been corrected.