How to use bash script to send out Oracle AWR report automatically

Jephe Wu - http://linuxtechres.blogspot.com
Objective: write bash shell script as cronjob to send out Oracle AWR report every 2 hours automatically
Environment: Oracle 11g 11.1.0.6 64bit default installation, CentOS 5.5 64bit
Concept: use base number and current unix seconds to get the latest snap id, use the latest snap id to get the begin snap id(current snap id -2). Use template awrrpt.sql file to generate the predefined filename, num_days, begin snap id and end snap id.

Steps:
1. preparing the template files
cd $ORACLE_HOME/rdbms/admin
# do not touch the original file awrrpt.sql and awrrpti.sql, use awr.sql and awri.sql instead.
cp awrrpt.sql awrtemp.sql
cp awrrpti.sql awri.sql

vi awri.sql --> to append 'exit;' at the end of the file so that sqlplus will exit automatically during script run
vi awrtemp.sql to modify it like this:
---------------
select d.dbid            dbid
     , d.name            db_name
     , i.instance_number inst_num
     , i.instance_name   inst_name
  from v$database d,
       v$instance i;


define num_days = '1';
define report_type = 'html';
define report_name = 'TEMPLATE0';
define begin_snap = 'TEMPLATE1';
define end_snap = 'TEMPLATE2';

@@awri
--
-- End of file
-------------------------

2. get the latest snap id unix timestamp(seconds)
sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/awrrpt.sql
----------
Enter value for num_days: 1

Listing the last day's Completed Snapshots

                            Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
orcl         ORCL          12953 06 Apr 2011 00:00       1
                  12954 06 Apr 2011 01:00       1
                  12955 06 Apr 2011 02:00       1
                  12956 06 Apr 2011 03:00       1
                  12957 06 Apr 2011 04:00       1
                  12958 06 Apr 2011 05:00       1
                  12959 06 Apr 2011 06:00       1
                  12960 06 Apr 2011 07:00       1
                  12961 06 Apr 2011 08:00       1
                  12962 06 Apr 2011 09:00       1
                  12963 06 Apr 2011 10:00       1
                  12964 06 Apr 2011 11:00       1



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:
----------------

we use the latest snap id 12964 as base number, let's convert it to unix timestamp.
date --date="Wed Apr  6 11:00:00 SGT 2011" +%s
1302058800

3. preparing shell script
[oracle@hpaydb admin]$ more ~/bin/awr.sh
#!/bin/sh

export ORACLE_SID=orcl
export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
export PATH=$ORACLE_HOME/bin:/usr/bin:/bin

# variables
SEC=`date +%s`
INIT=12964
INCR=`echo "($SEC-1302058800)/3600" | bc`
#1302058800 is  Wed Apr  6 11:00:00 SGT 2011
SNAPEND=$[$INIT+$INCR]
SNAPBEGIN=$[${SNAPEND}-2]

# program starts here , do not change anything below
sed -e "s#TEMPLATE1#$SNAPBEGIN#g" -e "s#TEMPLATE2#$SNAPEND#g" -e "s#TEMPLATE0#/tmp/prod_awr_${SNAPBEGIN}_${SNAPEND}.html#g" $ORACLE_HOME/rdbms/admin/awrtemp.sql > $ORACLE_HOME/rdbms/admin/awr.sql

sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/awr.sql

# send out email
rm -f /tmp/prod_alert_awr_$SNAPEND.zip
zip -r /tmp/prod_alert_awr_$SNAPEND.zip /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log /tmp/prod_awr_${SNAPBEGIN}_${SNAPEND}.html
mutt -a /tmp/prod_alert_awr_$SNAPEND.zip -s "Prod alert log and AWR report between snap id $SNAPBEGIN and $SNAPEND" jephewu_email_address < /dev/null


4. put in cronjob
login as oracle, crontab -e
30 */2 * * * /home/oracle/bin/awr.sh
# every 2 hours to send out report

4. References
a. convert unix second to normal date
date -d @1187769064
b. put the following into /home/oracle/bin/awr to simplify the normal awr report generation
[oracle@hpaydb admin]$ more ~/bin/awr
#!/bin/sh

sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/awrrpt.sql

chmod +x ~/bin/awr
Just run awr to generate normal awr report and awr.sh to generate awr every 2 hours automatically.