Oracle AWR 报告 每天自动生成并发送邮箱

  观察Oracle数据库性能，Oracle自带的awr 功能为我们提供了一个近乎完美的解决方案，通过awr特性我们可以随时从数据库提取awr报告。不过awrrpt.sql脚本执行时需要我们提供一些交互信息，因此可以将其整合到shell脚本中来实现自动产生指定时段的awr报告并发送给相关人员

环境：

Oracle 11.2.0.4+Centos6.5

1.编辑脚本

orcl:/u01/monitor@oracle>vi awr_report.sh

#!/bin/sh
#########################################################################
#                  AWR Automatic generation Report                          #
#    2017-10-24   writen by [e-mail]                       #

ORACLE_SID=orcl
export ORACLE_SID
source ~/.bash_profile
WORKPATH=/u01/monitor/statspack/report
AWR_FORMAT=html
NUM_DAYS=2

#date_yyyymmdd=`date -d 'yesterday' '+%Y%m%d'`
date_yyyymmdd=`date '+%Y%m%d'`
orasid=$ORACLE_SID
DBALIST="[e-mail]"
END_SNAP_ID=`sqlplus -S / as sysdba << EOF      
set heading off trimspool on feedback off
select SNAP_ID from DBA_HIST_SNAPSHOT where to_char(begin_interval_time,'yyyy-mm-dd-hh24')=to_char(sysdate-1,'yyyy-mm-dd')||'-20';
EOF`
export END_SNAP_ID

START_SNAP_ID=`expr $END_SNAP_ID - 12`
export START_SNAP_ID
echo $START_SNAP_ID


AWR_LOG=$WORKPATH/${date_yyyymmdd}_awr_orcl_${START_SNAP_ID}.html
export AWR_LOG
echo $AWR_LOG

echo -e "$AWR_FORMAT\n$NUM_DAYS\n$START_SNAP_ID$END_SNAP_ID\n$AWR_LOG\n"|(sqlplus -S / as sysdba @?/rdbms/admin/awrrpt.sql) > /dev/null
mailx -s "${orasid} AWR Automatic generation Report!!)" -a $WORKPATH/${date_yyyymmdd}_awr_orcl_${START_SNAP_ID}.html $DBALIST < $WORKPATH/read.txt
#rm -rf $WORKPATH/${date_yyyymmdd}_awr_orcl_${MIN_SNAP_ID}.html

2.制定定时任务

orcl:/u01/monitor@oracle>crontab -e

0 23 * * * /u01/monitor/awr_report.sh