Getting AWR report every hour automatically

This week I got a request to share a script I wrote years ago — a script to generate AWR report automatically every hour and email it out to whoever is interested in it. Bascially it uses Oracle’s script awrrpti.sql. As the comments in the SQL script say, once the following variables defined, running it will create an AWR report.

Rem    NOTES
Rem      Run as SYSDBA.  Generally this script should be invoked by awrrpt,
Rem      unless you want to pick a database other than the default.
Rem
Rem      If you want to use this script in an non-interactive fashion,
Rem      without executing the script through awrrpt, then
Rem      do something similar to the following:
Rem
Rem      define  inst_num     = 1;
Rem      define  num_days     = 3;
Rem      define  inst_name    = 'Instance';
Rem      define  db_name      = 'Database';
Rem      define  dbid         = 4;
Rem      define  begin_snap   = 10;
Rem      define  end_snap     = 11;
Rem      define  report_type  = 'text';
Rem      define  report_name  = /tmp/swrf_report_10_11.txt
Rem      @@?/rdbms/admin/awrrpti

Here it is as showed below written in BASH:

#!/bin/bash

# Getting most recent begin and end snap_id
# It also generate define statements which are required to run awrrpti.sql from background

TEMPFILE=/tmp/tmpawr.sql
WORKDIR=/opt/home/oracle/DB_Tools
AWRDIR=/opt/home/oracle/DB_Tools/awr_reports
SMTPSERVER=smtp_server_address

SQLPLUS=$ORACLE_HOME/bin/sqlplus

cd ${AWRDIR}

# tar and gzip old reports for past 7 days 24 x 7 = 168
NUM_REPORTS=`ls -1 *.html|wc -l`
if [ ${NUM_REPORTS} -ge 168 ]; then
    tar czvf `date +%y%m%d`.tar.gz awrrpt*.html
    rm -rf awrrpt*.html
fi

# Set tmp dir for mailx or we will get the error "temporary mail file: Permission denied", strace is used to find out that
# "/" is used for temporary directory of mailx if TEMDIR is not set which non-root user can not write to it.
TMPDIR=/tmp
export TMPDIR

email()
{
    # $1 SMTP server
    # $2 title
    # $3 attachment
    # $4 sender
    # $5 recipients

    # put the $1 parameter in double quote because it contains space
    /bin/mailx -S smtp=$1 -v -s "$2" -a $3 -r $4  $5 < /dev/null > /dev/null 2>&1
}

${SQLPLUS} -S '/as sysdba' << EOF > /dev/null
set echo off
set head off
set feed off
spool ${TEMPFILE}
select 'Define begin_snap= '|| (max(snap_id)-1) from dba_hist_snapshot;
SELECT 'Define end_snap= '|| max(snap_id) from dba_hist_snapshot;
select 'define report_type  = ' || '''html''' from dual;
select 'define inst_name    = ' || INSTANCE_NAME from v\$instance;
select 'define db_name    = ' || name from v\$database;
select 'define dbid = ' || dbid from v\$database;
select 'define inst_num = ' || INSTANCE_NUMBER from v\$instance;
select 'define  num_days     = 1' from dual;
select '@$ORACLE_HOME/rdbms/admin/awrrpti.sql' from dual;
spool off
exit
EOF

# Following part runs the script composed earlier with snap ids and define statements
# There is a blank line so that return is entered for report name
${SQLPLUS} -S '/as sysdba' << EOF >/dev/null
@${TEMPFILE}

exit
EOF

SENDER=replace_this
LIST_RECIPIENTS=replace_this@gmail.com

TITLE="Hourly database Oracle AWR report."
ATTCHMENT=`ls -1rt awr*.html|tail -1`

email $SMTPSERVER "$TITLE" $ATTCHMENT $SENDER $LIST_RECIPIENTS

# TEMPFILE remove
if [ -f "$TEMPFILE" ]; then
	rm $TEMPFILE
fi

cd ${WORKDIR}

exit 0

In the script, it also uses a SMTP server to send the latest AWR report out. So replace the variables SMTPSERVER, SENDER, LIST_RECIPIENTS accordingly. If there are multiple recipients, use comma “,” to seperate them. Put the script in the crontab, you will get AWR reports as snapshots are taken.

5 * * * * /bin/bash -l /opt/home/oracle/DB_Tools/get_latest_awr.bash > /dev/null 2>&1