Create AWR Daily Report and Weekly Report run by crontab

General
  One of the most useful tasks for a DBA is to have an up to date summery of current activity in DB, including top SQLs, Disk IO usage, etc.
  One of the way to do that, is to have AWR Report Generated on an ongoing basis.

Steps
Setup AWR Retention Policy.
Create a bash script to run AWR Report.
Create a crontab task to automate AWR Report executions.

Setup AWR Retention Policy.
1. Check current retention poilicy.
SELECT
(EXTRACT( DAY FROM SNAP_INTERVAL) *24*60+ EXTRACT( HOUR FROM SNAP_INTERVAL) *60+ EXTRACT( MINUTE FROM SNAP_INTERVAL ))/60 AS "Snapshot Interval (Hour)",
(EXTRACT( DAY FROM RETENTION) *24*60+  EXTRACT( HOUR FROM RETENTION) *60+ EXTRACT( MINUTE FROM RETENTION ))/(24*60) AS "Retention Interval (Days)"
FROM DBA_HIST_WR_CONTROL;

------------------------ -------------------------
                       1                         7

By default snapshots are taken every hour and retained for 7 days.
To change the default settings, use DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 43200,  -- Minutes (= 30 Days). Current value retained if NULL.
    interval  => 30);    -- Minutes. Current value retained if NULL.
END;
/

Create a bash script to run AWR Report.
Oracle provides sql scripts to generate AWR report, either in text or html format.
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

Oracle awrrpt.sql script, requires runtime parameters input - such as report format, report file name, Begin snapshot ID, End snapshot ID.

The script below, provided sets the required input parameter values, and wraps the call to awrrpt.sql.

Flow of events:
cron is starting task: generate_awr_daily.sh or generate_awr_weekly.sh
generate_awr_daily.sh or generate_awr_weekly.sh call generate_awr.sh
generate_awr.sh calls generate_awr.sql
generate_awr.sql set all AWR report parameters, and calls Oracle awrrpt.sql.


create directoriesmkdir_for_awr.sh
#!/bin/bash
mkdir -p /software/oracle/oracle/.scripts/AWR/script
mkdir -p /software/oracle/oracle/.scripts/AWR/history

crontab entry
> crontab -l
00 05 * * *   . $HOME/.profile_for_awr; /somepath/userA/AWR_FILES/code/generate_awr_daily.sh  &>/somepath/userA/AWR_FILES/logs/daily_awr.log
01 05 * * 4 . $HOME/.profile_for_awr; /somepath/userA/AWR_FILES/code/generate_awr_weekly.sh &>/somepath/userA/AWR_FILES/logs/weekly_awr.log

The crontab file is at: /var/spool/cron
-rw-------  1 root groupA 258 May 29 13:36 userA
-rw-------  1 root groupA 235 Jun 21  2006 userB

A note about crontab entry:
A. Schedule

# Minute   Hour   Day of Month  Month             Day of Week      Command    
# (0-59)  (0-23)  (1-31)        (1-12 or Jan-Dec) (0-6 or Sun-Sat)                

  0        2      12            *                 *                /usr/bin/find

B. Envirnment Variables.

The crontab task creation is pretty much straight forward.
The only tweak is setting the environment variables.

cron knows nothing about your shell.

It is started by the system, so it has a minimal environment. 

Thus need to set the environment variables before running the script, by:
 . $HOME/.profile_for_awr;
Note the ";" at the end of the source command.

Another option would be to set environment variables from inside the shell script.

export ORACLE_HOME=/software/oracle/122
export ORACLE_BASE=/software/oracle
export ORACLE_SID=orasid
export ORA_VER=1220
export ORA_BIN=${ORACLE_HOME}/bin

export PATH=$PATH:$ORA_BIN

In this example:
generate_awr_daily.sh script is running every day, at 00 minutes, 05AM.
generate_awr_weekly.sh script is running once a week, at 01 minutes, 05AM on Thursday.
Before script execution, that $HOME/.bash_profile is sourced.
The job activity is logged to /starhome/iu/AWR_FILES/logs/daily_awr.log.

generate_awr_hourly.sh

#!/bin/bash

export AWR_HOME=/software/oracle/oracle/.scripts/AWR/script
export AWR_OUTPUT=/software/oracle/oracle/.scripts/AWR/history
export ORACLE_SID=igt
NUM_OF_DAYS=1
INSTANCE_NAME=igt
KEEP_DAYS=7

cd $AWR_HOME

./generate_awr.sh system Xen86Pga igt $NUM_OF_DAYS $INSTANCE_NAME

mv ${AWR_HOME}/AWR* ${AWR_OUTPUT}/
find ${AWR_OUTPUT}/AWR* -mtime +${KEEP_DAYS} -exec rm {} \;
exit 0

generate_awr_daily.sh

#!/bin/bash

export AWR_HOME=/software/oracle/oracle/.scripts/AWR/script
export AWR_OUTPUT=/software/oracle/oracle/.scripts/AWR/history
export ORACLE_SID=igt
NUM_OF_DAYS=1
INSTANCE_NAME=igt
KEEP_DAYS=7

cd $AWR_HOME

./generate_awr.sh system Xen86Pga igt $NUM_OF_DAYS $INSTANCE_NAME

mv ${AWR_HOME}/AWR* ${AWR_OUTPUT}/
find ${AWR_OUTPUT}/AWR* -mtime +${KEEP_DAYS} -exec rm {} \;
exit 0

generate_awr_weekly.sh
#!/bin/bash

export AWR_HOME=/software/oracle/oracle/.scripts/AWR/script
export AWR_OUTPUT=/software/oracle/oracle/.scripts/AWR/history
export ORACLE_SID=igt
NUM_OF_DAYS=7
INSTANCE_NAME=igt
KEEP_DAYS=7

cd $AWR_HOME

./generate_awr.sh system Xen86Pga igt $NUM_OF_DAYS $INSTANCE_NAME

mv ${AWR_HOME}/AWR* ${AWR_OUTPUT}/
find ${AWR_OUTPUT}/AWR* -mtime +${KEEP_DAYS} -exec rm {} \;
exit 0

generate_awr.sh
#!/bin/bash

USER=$1
PASS=$2
SID=$3
NUM_OF_DAYS=$4
INSTANCE_NAME=$5

sqlplus -S ${USER}/${PASS}@${SID} << EOF
@generate_awr.sql $NUM_OF_DAYS $INSTANCE_NAME
exit;
EOF

generate_awr.sql
DEFINE num_days=&1;
DEFINE i_instance=&2;

DEFINE inst_name='';
DEFINE report_type='html';
DEFINE report_name='';
DEFINE begin_snap=0;
DEFINE end_snap=0; 

column inst_name  heading "Instance Name" new_value inst_name format A16;
SELECT UPPER('&i_instance') inst_name FROM DUAL;

column begin_snap heading "Min SNAP ID"  new_value begin_snap format 9999999999;
column end_snap heading "Max SNAP ID"  new_value end_snap format 9999999999;

------------------------
--For Daily Report
------------------------ SELECT MIN(SNAP_ID) begin_snap FROM dba_hist_snapshot WHERE TRUNC(begin_interval_time) = TRUNC(SYSDATE-&num_days);
SELECT MAX(SNAP_ID) end_snap FROM dba_hist_snapshot WHERE TRUNC(begin_interval_time) = TRUNC(SYSDATE-&num_days);
------------------------
--For Hourly Report
------------------------
SELECT MAX(SNAP_ID)-1 begin_snap FROM dba_hist_snapshot;
SELECT MAX(SNAP_ID)   end_snap FROM dba_hist_snapshot;

column report_name heading "AWR file name"  new_value report_name format A30; 
SELECT 'AWR'||'_'||'&inst_name'||'_'||DECODE(&num_days,1,'Daily',7,'Weekly','Unknown Period')||'_'||TO_CHAR(SYSDATE,'YYYYMMDD_hhmmss')||'.'||'&report_type' report_name FROM DUAL; 


SELECT &num_days num_days FROM DUAL;
SELECT '&report_type'   report_type FROM DUAL;
SELECT '&report_name'   report_name FROM DUAL;
SELECT &begin_snap begin_snap FROM DUAL;
SELECT &end_snap end_snap FROM DUAL;

@@/software/oracle/122/rdbms/admin/awrrpt.sql

undefine num_days;
undefine report_type;
undefine report_name;
undefine begin_snap;

undefine end_snap;

crontab quick reference
Entry example:
00 05 * * *   . $HOME/.profile_for_awr; 

00 - min. 00-59
05 - hour. 00-23
* - Day of month. 1-31
* - Month. 1-12
* - Day of week (0 - 6) (Sunday=0)

Remove multiple files, older than 5 days with one command from crontab

15 23 * * * find . -type f -name "FILE-TO-FIND" -mtime +5 -exec rm -rf {} \;

Simple script to generate AWR

.set_profile

export ORACLE_HOME=/software/oracle/111
export ORACLE_SID=igt

export PATH=$PATH:/software/oracle/111/bin

.generate_awr.sh

#!/bin/bash

. .set_profile

sqlplus system/xen86pga@igt @/software/oracle/111/rdbms/admin/awrrpt.sql

Appendix
AWR Retention period sizing guidelines
Automatic Workload Repository (AWR) in Oracle Database 10g
Crontab – Quick Reference