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

短命女 2022-06-05 22:47 212阅读 0赞

观察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 1157758655@qq.com                       \#  
  
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="1157758655@qq.com"  
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