Linux/Unix shell 自动发送AWR report(二)

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

1、shell脚本

robin@SZDB:~/dba_scripts/custom/awr> more autoawr_by_time.sh
#!/bin/bash
# --------------------------------------------------------------------------+
#                 Generate AWR report and send mail automatically           |
#   Filename: autoawr_by_time.sh                                            |
#   Desc:                                                                   |
#       The script use to generate awr report by time period.               |
#       Three parameter for it.                                             |
#           para1: <ORACLE_SID>   mandatory parameter                       |
#           para2: [begin time]   optional parameter                        |  
#           para3: [end time  ]   optional parameter                        |
#       Deploy it by crontab as requirement                                 |  
#   Usage:                                                                  |
#       ./autoawr_by_time.sh <instance_name> [begin time] [end time]        |  
#   Example:                                                                |
#       ./autoawr_by_time.sh TESTDB                                         |   
#            --default,time period is from last midnight to today midnight  |
#       ./autoawr_by_time.sh TESTDB 2013031009                              |
#            --time period is from 2013031009 to now                        |
#       ./autoawr_by_time.sh TESTDB 2013031009 2013031012                   |
#            --time period by speicifed                                     | 
#   Author : Robinson                                                       | 
#   Blog   : http://blog.csdn.net/robinson_0612                             |
# --------------------------------------------------------------------------+
#
# -------------------------------
#  Set environment here 
# ------------------------------

if [ -f ~/.bash_profile ]; then
    . ~/.bash_profile
fi

# ------------------------------------------------------------
#  Check the parameter, if no specify,then use default value
# ------------------------------------------------------------

if [ -z "${1}" ] ;then
    echo "Usage: "
    echo "      `basename $0` <ORACLE_SID> [begin_date] [end_date]"
fi

if [ -z "${3}" ] && [ -z "${2}" ];then
    begin_date=`date -d yesterday +%Y%m%d`'00'
    end_date=`date +%Y%m%d`'00'
elif [ -z "${3}" ]; then
    begin_date=${2}
    end_date=`date +%Y%m%d%H`
else
    begin_date=${2}
    end_date=${3}
fi

ORACLE_SID=${1}
export ORACLE_SID begin_date end_date 
export MACHINE=`hostname`
export MAIL_DIR=/users/robin/dba_scripts/sendEmail-v1.56
export MAIL_LIST='Robinson.chen@12306.com'
export AWR_CMD=/users/robin/dba_scripts/custom/awr
export AWR_DIR=/users/robin/dba_scripts/custom/awr/report/${ORACLE_SID}
export MAIL_FM='oracle@szdb.com'
RETENTION=31

echo $ORACLE_SID 
echo $begin_date
echo $end_date
# --------------------------------------------------------------------
#  Check the directory for store awr report,if not exist, create it
# --------------------------------------------------------------------

if [ ! -d "${AWR_DIR}" ]; then
    mkdir -p ${AWR_DIR}
fi

# ----------------------------------------------
# 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 the awr report
# ---------------------------------------------

sqlplus -S "/ as sysdba" @${AWR_CMD}/autoawr_by_time.sql $begin_date $end_date 

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
# ------------------------------------------------

filename=`ls ${AWR_DIR}/${ORACLE_SID}_awrrpt_?_${begin_date}_${end_date}*`
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`.Time period: $begin_date,$end_date. "
    $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脚本

robin@SZDB:~/dba_scripts/custom/awr> more autoawr_by_time.sql
SET ECHO OFF;
SET VERI OFF;
SET FEEDBACK OFF;
SET TERMOUT ON;
SET HEADING OFF;
SET TRIMSPOOL ON;

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 snap_id
        INTO :bid
        FROM dba_hist_snapshot
       WHERE TO_CHAR (end_interval_time, 'yyyymmddhh24') = '&1';

      SELECT snap_id
        INTO :eid
        FROM dba_hist_snapshot
       WHERE TO_CHAR (end_interval_time, 'yyyymmddhh24') = '&2';

   SELECT dbid INTO :dbid FROM v$database;

   SELECT instance_number INTO :inst_num FROM v$instance;
END;
/

--print dbid;
--print bid;
--print eid;
--print inst_num;

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 || '_' ||'&&1'||'_'||'&&2'|| '.' || '&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;
--SPOOL &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;
SET TRIMSPOOL OFF;

UNDEFINE report_name
UNDEFINE report_type
UNDEFINE fn_name
UNDEFINE lnsz
UNDEFINE no_options
exit; 

3、补充说明 a、该脚本实现了基于不同时段,不同instance自动生成awr report,具体如下 b、用法为./autoawr_by_time.sh <instance_name> [begin time] [end time],可以用于随时随地直接生成awr report c、在省略[begin time] [end time]的情形下会自动生成昨天凌晨至今天凌晨的awr report d、当仅仅省略[end time]时则从[begin time]开始至当前的最大snap_id来生成awr report e、当[begin time] [end time]都被指定时则生成指定时段的awr report f、通过调用sendEmail发送awr report,具体参考:不可或缺的 sendEmail

4、部署参考

#如果仅仅需要一整天的awr report,直接将其部署到crontab即可。
#如果需要一整天以及不同时段的awr report,则可以考虑采用如下方式来部署,将其合并到一个shell文件
robin@SZDB:~/dba_scripts/custom/awr> more awr.sh
#!/bin/bash
dt=`date +%Y%m%d`
start_date=$dt'05'
end_date=$dt'09'
/users/robin/dba_scripts/custom/awr/autoawr_by_time.sh CNMMBO               #获取一整天的awr report
/users/robin/dba_scripts/custom/awr/autoawr_by_time.sh CNMMBO $start_date   #获取指定起始时间至今的awr report
/users/robin/dba_scripts/custom/awr/autoawr_by_time.sh CNMMBO $start_date $end_date #获取指定时间段的awr report
exit 
robin@SZDB:~/dba_scripts/custom/awr> crontab -l
# DO NOT EDIT THIS FILE - edit the master and reinstall.
45  11 * * * /users/robin/dba_scripts/custom/awr/awr.sh

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

Linux/Unix shell 监控Oracle告警日志(monitor alter log file)

    使用shell脚本实现对Oracle数据库的监控与管理将大大简化DBA的工作负担,如常见的对实例的监控,监听的监控,告警日志的监控,以及数据库的备份,A...

882
来自专栏数据和云

续:跨平台版本迁移之 XTTS 方案操作指南

运行数据库对比脚本,通过创建 dblink,运行相关的数据库对象比对脚本。这里我们主要比对了存储过程,函数,触发器,试图,索引,表等等。

1984
来自专栏杨建荣的学习笔记

impdp ORA-39002,ORA-39166,ORA-39164的问题及解决(r2第6天)

今天在做imp和impdp的性能测试时,发现如果表中存在lob字段,加载真是慢的厉害,每秒钟大概1000条的样子,按照这种速度,基本上不用干活了。 比如5千万条...

3177
来自专栏乐沙弥的世界

对比 PL/SQL profiler 剖析结果

      使用PL/SQL PROFILER 剖析PL/SQL代码是快速定位PL/SQL代码段最有效的方法。在上一篇文章使用PL/SQL PROFILER 定...

1201
来自专栏杨建荣的学习笔记

生产环境sql语句调优实战第五篇(r2笔记41天)

今天在生产环境中发现一条sql语句尽管走了主键索引,但是查询还是很慢。 sql语句类似下面的形式: SELECT /*+ index (bl1_cyc_paye...

3468
来自专栏杨建荣的学习笔记

归档问题导致的数据库无法启动 (80天)

本地有一个小的环境,今天照例登上sqlplus,突然发现报了如下的错误。一看原来归档满了。我记得前几天做一个批量操作临时把temp文件resize了很大,限于本...

33010
来自专栏乐沙弥的世界

Oracle expdp 时遭遇ORA-39125 ORA-04063

    数据库在使用DataPump导出时碰到了ORA-39125与ORA-04063。完整的ORA-39125提示是Worker unexpected fat...

1011
来自专栏乐沙弥的世界

PL/SQL 包编译时hang住的处理

       最近PL/SQL包在编译时被hang住,起初以为是所依赖的对象被锁住。结果出乎意料之外。下面直接看代码演示。

906
来自专栏杨建荣的学习笔记

数据库11g升级中一次奇怪的问题 (30天)

客户的测试环境已经从10g升级到11g了。但是没过几天,数据hang住了,登都登不了了,而且通过sys,system,普通用户连接的错误都不一样 首先通过 一下...

3214
来自专栏杨建荣的学习笔记

海量数据迁移之外部表切分(r2笔记52天)

在前几篇中讨论过海量数据的并行加载,基本思路就是针对每一个物理表都会有一个对应的外部表,在做数据迁移的时候,如果表有上百G的时候,一个物理表对应一个外部表性能上...

2967

扫码关注云+社区