Linux/Unix shell 自动发送AWR report

jingshen · 发表于 2013-3-11 08:58:04

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

1、shell脚本

[Python] 纯文本查看 复制代码

robin@SZDB:~/dba_scripts/custom/awr> more autoawr.sh  
#!/bin/bash  
# --------------------------------------------------------------------------+  
#                  CHECK ALERT LOG FILE                                     |  
#   Filename: autoawr.sh                                                    |  
#   Desc:                                                                   |  
#       The script use to generate AWR report and send mail automatic.      |  
#       The sql script autoawr.sql call by this shell script.               |                            
#       Default, the whole day AWR report will be gathered.                 |    
#       Deploy it to crontab at 23:30                                        |  
#       If you want to change the snap interval,please change autoawr.sql   |  
#          and crontab configuration                                        |  
#   Usage:                                                                  |  
#       ./autoawr.sh $ORACLE_SID                                            |    
#                                                                           |  
#   Author : Robinson                                                       |   
#   Blog   : [url]http://blog.iyunv.com/robinson_0612[/url]                             |  
# --------------------------------------------------------------------------+  
#  
# --------------------------  
#   Check SID  
# --------------------------  
  
if [ -z "${1}" ];then  
    echo "Usage: "  
    echo "      `basename $0` ORACLE_SID"  
    exit 1  
fi  
  
# -------------------------------  
#  Set environment here   
# ------------------------------  
  
if [ -f ~/.bash_profile ]; then  
    . ~/.bash_profile  
fi  
  
export ORACLE_SID=$1  
export MACHINE=`hostname`  
export MAIL_DIR=/users/robin/dba_scripts/sendEmail-v1.56  
export MAIL_LIST='Robinson.cheng@12306.com'  
export AWR_CMD=/users/robin/dba_scripts/custom/awr  
export AWR_DIR=/users/robin/dba_scripts/custom/awr/report  
export MAIL_FM='oracle@szdb.com'  
RETENTION=31  
  
# ----------------------------------------------  
# check if the database is running, if not exit  
# ----------------------------------------------  
  
db_stat=`ps -ef | grep pmon_$ORACLE_SID | grep -v grep| cut -f3 -d_`  
if [ -z "$db_stat" ]; then  
    #date >/tmp/db_${ORACLE_SID}_stauts.log  
    echo " $ORACLE_SID is not available on ${MACHINE} !!!"   # >>/tmp/db_${ORACLE_SID}_stauts.log   
    MAIL_SUB=" $ORACLE_SID is not available on ${MACHINE} !!!"  
    MAIL_BODY=" $ORACLE_SID is not available on ${MACHINE} at `date` when try to generate AWR."  
    $MAIL_DIR/sendEmail -u $MAIL_SUB -f $MAIL_FM -t $MAIL_LIST -m $MAIL_BODY   
    exit 1  
fi;  
  
# ----------------------------------------------  
# Generate awr report  
# ----------------------------------------------  
$ORACLE_HOME/bin/sqlplus /nolog<<EOF  
connect / as sysdba;  
@${AWR_CMD}/autoawr.sql;  
exit;  
EOF  
  
status=$?  
if [ $status != 0 ];then  
    echo " $ORACLE_SID is not available on ${MACHINE} !!!"   # >>/tmp/db_${ORACLE_SID}_stauts.log  
    MAIL_SUB=" Occurred error while generate AWR for ${ORACLE_SID}  !!!"  
    MAIL_BODY=" Some exceptions encountered during generate AWR report for $ORACLE_SID on `hostname`."  
    $MAIL_DIR/sendEmail -u $MAIL_SUB -f $MAIL_FM -t $MAIL_LIST -m $MAIL_BODY  
    exit  
fi  
  
# ------------------------------------------------  
# Send email with AWR report  
# ------------------------------------------------  
dt=`date -d yesterday +%Y%m%d`  
filename=`ls ${AWR_DIR}/${ORACLE_SID}_awrrpt_?_${dt}*`  
if [ -e "${filename}" ];then  
    MAIL_SUB="AWR report from ${ORACLE_SID} on `hostname`."  
    MAIL_BODY="This is an AWR report from ${ORACLE_SID} on `hostname`."  
    $MAIL_DIR/sendEmail -u $MAIL_SUB -f $MAIL_FM -t $MAIL_LIST -m $MAIL_BODY -a ${filename}  
    echo ${filename}  
fi  
  
# ------------------------------------------------  
# Removing files older than $RETENTION parameter   
# ------------------------------------------------  
  
find ${AWR_DIR} -name "*awrrpt*" -mtime +$RETENTION -exec rm {} \;  
exit      
2、产生awr report 的sql脚本
[sql] view plaincopyprint?
robin@SZDB:~/dba_scripts/custom/awr> more autoawr.sql  
SET ECHO OFF;  
SET VERI OFF;  
SET FEEDBACK OFF;  
SET TERMOUT ON;  
SET HEADING OFF;  
  
VARIABLE rpt_options NUMBER;  
DEFINE no_options = 0;  
  
define ENABLE_ADDM = 8;  
  
REM according to your needs, the value can be 'text' or 'html'  
  
DEFINE report_type='html';  
  
BEGIN  
   :rpt_options := &no_options;  
END;  
/  
  
VARIABLE dbid NUMBER;  
VARIABLE inst_num NUMBER;  
VARIABLE bid NUMBER;  
VARIABLE eid NUMBER;  
  
BEGIN  
  SELECT MIN (snap_id) INTO :bid  
    FROM dba_hist_snapshot  
   WHERE TO_CHAR (end_interval_time, 'yyyymmdd') = TO_CHAR (SYSDATE, 'yyyymmdd');  
  
   SELECT MAX (snap_id) INTO :eid FROM dba_hist_snapshot;  
  
   SELECT dbid INTO :dbid FROM v$database;  
  
SELECT instance_number INTO :inst_num FROM v$instance;  
END;  
/  
  
COLUMN ext NEW_VALUE ext NOPRINT  
COLUMN fn_name NEW_VALUE fn_name NOPRINT;  
COLUMN lnsz NEW_VALUE lnsz NOPRINT;  
SELECT 'txt' ext  
  FROM DUAL  
 WHERE LOWER ('&report_type') = 'text';  
  
SELECT 'html' ext  
  FROM DUAL  
 WHERE LOWER ('&report_type') = 'html';  
  
SELECT 'awr_report_text' fn_name  
  FROM DUAL  
 WHERE LOWER ('&report_type') = 'text';  
  
SELECT 'awr_report_html' fn_name  
  FROM DUAL  
 WHERE LOWER ('&report_type') = 'html';  
  
SELECT '80' lnsz  
  FROM DUAL  
 WHERE LOWER ('&report_type') = 'text';  
  
SELECT '1500' lnsz  
  FROM DUAL  
 WHERE LOWER ('&report_type') = 'html';  
  
set linesize &lnsz;  
COLUMN report_name NEW_VALUE report_name NOPRINT;  
  
SELECT instance_name || '_awrrpt_' || instance_number || '_' || b.timestamp || '.' || '&ext'  
          report_name  
  FROM v$instance a,  
       (SELECT TO_CHAR (begin_interval_time, 'yyyymmdd') timestamp  
          FROM dba_hist_snapshot  
         WHERE snap_id = :bid) b;  
  
SET TERMOUT OFF;  
SPOOL $AWR_DIR/&report_name;  
  
SELECT output  
  FROM TABLE (DBMS_WORKLOAD_REPOSITORY.&fn_name (:dbid,  
                                                 :inst_num,  
                                                 :bid,  
                                                 :eid,  
                                                 :rpt_options));  
SPOOL OFF;  
SET TERMOUT ON;  
CLEAR COLUMNS SQL;  
TTITLE OFF;  
BTITLE OFF;  
REPFOOTER OFF;  
  
UNDEFINE report_name  
UNDEFINE report_type  
UNDEFINE fn_name  
UNDEFINE lnsz  
[color=silver][font=Verdana, Geneva, Arial, Helvetica, sans-serif][size=9px][color=#000][font=Consolas,]UNDEFINE no_options

3、补充说明
a、shell脚本中首先判断指定的实例是否处于available，如果不可用则退出
b、接下来调用autoawr.sql脚本来产生awr report
c、产生awr report后，如果文件存在则自动发送邮件
d、autoawr.sql脚本中是产生awr report的主要部分，主要是调用了DBMS_WORKLOAD_REPOSITORY.&fn_name过程
e、该脚本是生成一整天awr report，即从当天的零点至第二天零点
f、sql脚本的几个参数需要确定的是dbid，实例号，以及snap的开始与结束id，rpt_options用于确定报告是否带addm项
g、可以根据需要定制所需的snap的起止id，需修改SQL来获取正确的snap id，来生成所需的报告
h、根据需要修改fn_name定制生成awr报告为txt或html类型，report_name则是确定最终文件名
i、AWR 报告的两个snap 之间不能有重启DB的操作,否则有可能错误(未测试过)
j、该脚本支持Oracle 10g/11g，有关详细的产生awr report脚本说明请参考oracle自带的awrrpt.sql,awrrpti.sql

更多参考:

有关Oracle RAC请参考
   使用crs_setperm修改RAC资源的所有者及权限    使用crs_profile管理RAC资源配置文件    RAC 数据库的启动与关闭    再说 Oracle RAC services    Services in Oracle Database 10g    Migrate datbase from single instance to Oracle RAC    Oracle RAC 连接到指定实例    Oracle RAC 负载均衡测试(结合服务器端与客户端)    Oracle RAC 服务器端连接负载均衡(Load Balance)    Oracle RAC 客户端连接负载均衡(Load Balance)    ORACLE RAC 下非缺省端口监听配置(listener.ora tnsnames.ora)
   ORACLE RAC 监听配置 (listener.ora tnsnames.ora)    配置 RAC 负载均衡与故障转移    CRS-1006 , CRS-0215 故障一例
   基于Linux (RHEL 5.5) 安装Oracle 10g RAC
   使用 runcluvfy 校验Oracle RAC安装环境