How to Automate the process for fetching AWR report and send over mail.

Step 1) Login to server and create below script (fetch_AWR.sh).

[oracle@oracleforensics~]$ vi fetch_AWR.sh

export ORACLE_HOSTNAME=hostname
export ORACLE_UNQNAME=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2/db_1
export ORACLE_SID=orcl
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

sqlplus -s '/as sysdba' << EOF

set echo off

set head off

set feed off

spool /tmp/tmpScr.sql

select 'Define begin_snap= '|| snap_id from dba_hist_snapshot where

EXTRACT(HOUR FROM BEGIN_INTERVAL_TIME)=6 and --- Begin Snapshot Id time

EXTRACT(year FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,'YYYY') and

EXTRACT(month FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,'MM') and

EXTRACT(day FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,'DD');

SELECT 'Define end_snap= '|| snap_id from dba_hist_snapshot where

EXTRACT(HOUR FROM BEGIN_INTERVAL_TIME)=7 and --- End  Snapshot Id

EXTRACT(year FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,'YYYY') and

EXTRACT(month FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,'MM') and

EXTRACT(day FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,'DD');

select 'define  report_type  = ' || '''text''' from dual;

select 'define  report_name  = /tmp/awrrep.txt' 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     = 3' from dual;

select '@$ORACLE_HOME/rdbms/admin/awrrpti.sql' from dual;

exit

EOF

# Following part runs the script composed earlier with snap ids and define statements

sqlplus -s '/as sysdba' << EOF

@/tmp/tmpScr.sql

exit

EOF

## END

[oracle@oracleforensics~]$ ls -lrth fetch_AWR.sh

[oracle@oracleforensics~]$ chmod 775 fetch_AWR.sh

Step2) Scheduled the job in crontab as below (This job will execute daily at 8PM and  'Begin Snapshot Id' and 'End  Snapshot Id' time between 7:30 and 8:30 AM)

Snap Id details:

                                                         Snap

Instance     DB Name        Snap Id    Snap Started      Level

------------ ------------ ---------  ------------------   -----

orcl         ORCL               37    09 Feb 2018 07:30      1

                                38    09 Feb 2018 08:30      1

[oracle@oracleforensics~]$ crontab –e

00 20 * * * /home/oracle/fetch_AWR.sh | mailx -s "AWR Today" ashwin1409@gmail.com 2>&1