Oracle AWR report hourly by email

The users are concerned about the database performance, so in order to rule out the database as a source of slowness, we've received the task to send the AWR report hourly to a distribution list. We don't want to do this manually due that this stops us from working on important activities and draws our attention from other monitoring.

Due that AWR snaps are set up to be collected hourly, the script below: retrieves the last snap, generates the report, sends the report and deletes the file:

export ORACLE_BASE=/opt/app/oracle
export ORACLE_HOME=/opt/app/oracle/product/19.0.0.0/db_1
export ORACLE_SID=ORCLDB1
end_snap=$(sqlplus -s / as sysdba << EOF
set pages 0
set head off
set feed off
select trim(max(snap_id)) es from dba_hist_snapshot;
exit;
EOF
)
let begin_snap=end_snap-1
sqlplus / as sysdba << EOF
column inst_num  new_value inst_num;
column inst_name new_value inst_name;
column db_name   new_value db_name;
column dbid      new_value dbid;
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 = 0;
define report_type='html';
define begin_snap = $begin_snap;
define end_snap   = $end_snap;
define report_name = '/path_to_report/awrrpt_&db_name._&inst_name._&begin_snap._&end_snap..html'
@@?/rdbms/admin/awrrpti
exit
EOF
to_list='[email protected]'
cc_list='[email protected]'
mailx -a "/path_to_report/awrrpt_ORCLDB1_INST1_${begin_snap}_${end_snap}.html" -s "DB Performance" -c "$cc_list" "$to_list" <<< 'Please find attached'
rm "/path_to_report/awrrpt_ORCLDB1_INST1_${begin_snap}_${end_snap}.html"

For this to be automated, you need to: add the shebang line at the beginning of the script specifying "bash", make the file executable, and add a cron entry similar to the following:

05 * * * * /path_to_script/script.sh 1>/dev/null 2>&1

Enjoy!