How to schedule AWR report(awrrpt.sql) via crontab?

Hi All,


I came across a requirement to schedule a AWR snap report between 9 AM to 6 PM on certain days. 


The aim here was to automatically identify snap id for morning 9 AM and 6 PM and run the report from background and email the report.


Following is a sample script. In my test instance begin snap id is returning only 1 snap so I am good :)
If you get multiple snap id by given select statement please use min(snap_id) and max(snap_id) in begin and end respectively. I have commented the script to help understanding better.


Save the following as any shell script and schedule in cron.


myAWR.sh


## START


# Following mechanism will record begin and end snap_id by given time 9 and 18 hour of the day
# It also generate define statements which are required to run awrrpti.sql from background


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)=9 and
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)=18 and
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

Crontab Example (Linux)

## Running AWR everyday 7:00 PM

00 19 * * * /njadav/myAWR.sh | mailx -s "AWR Today" myemail@mydomain.com 2>&1

Note:

To further customize, please check parameters of $ORACLE_HOME/rdbms/admin/awrrpti.sql by opening it.

Following sample ..

define  inst_num     = 1;

define  num_days     = 3;

define  inst_name    = 'Instance';

define  db_name      = 'Database';

define  dbid         = 4;

define  begin_snap   = 10;

define  end_snap     = 11;

define  report_type  = 'text';

define  report_name  = /tmp/swrf_report_10_11.txt