• Linux/Unix shell 自动发送AWR report

     

    1、shell脚本

    [python] view plaincopyprint?
    1. robin@SZDB:~/dba_scripts/custom/awr> more autoawr.sh 
    2. #!/bin/bash 
    3. # --------------------------------------------------------------------------+ 
    4. #                  CHECK ALERT LOG FILE                                     | 
    5. #   Filename: autoawr.sh                                                    | 
    6. #   Desc:                                                                   | 
    7. #       The script use to generate AWR report and send mail automatic.      | 
    8. #       The sql script autoawr.sql call by this shell script.               |                           
    9. #       Default, the whole day AWR report will be gathered.                 |   
    10. #       Deploy it to crontab at 00:30                                        | 
    11. #       If you want to change the snap interval,please change autoawr.sql   | 
    12. #          and crontab configuration                                        | 
    13. #   Usage:                                                                  | 
    14. #       ./autoawr.sh $ORACLE_SID                                            |   
    15. #                                                                           | 
    16. #   Author : Robinson                                                       |  
    17. #   Blog   : http://blog.csdn.net/robinson_0612                             | 
    18. # --------------------------------------------------------------------------+ 
    19. # 
    20. # -------------------------- 
    21. #   Check SID 
    22. # -------------------------- 
    23.  
    24. if [ -z "${1}" ];then 
    25.     echo "Usage: " 
    26.     echo "      `basename $0` ORACLE_SID" 
    27.     exit 1 
    28. fi 
    29.  
    30. # ------------------------------- 
    31. #  Set environment here  
    32. # ------------------------------ 
    33.  
    34. if [ -f ~/.bash_profile ]; then 
    35.     . ~/.bash_profile 
    36. fi 
    37.  
    38. export ORACLE_SID=$1 
    39. export MACHINE=`hostname` 
    40. export MAIL_DIR=/users/robin/dba_scripts/sendEmail-v1.56 
    41. export MAIL_LIST='Robinson.cheng@12306.com' 
    42. export AWR_CMD=/users/robin/dba_scripts/custom/awr 
    43. export AWR_DIR=/users/robin/dba_scripts/custom/awr/report 
    44. export MAIL_FM='oracle@szdb.com' 
    45. RETENTION=31 
    46.  
    47. # ---------------------------------------------- 
    48. # check if the database is running, if not exit 
    49. # ---------------------------------------------- 
    50.  
    51. db_stat=`ps -ef | grep pmon_$ORACLE_SID | grep -v grep| cut -f3 -d_` 
    52. if [ -z "$db_stat" ]; then 
    53.     #date >/tmp/db_${ORACLE_SID}_stauts.log 
    54.     echo " $ORACLE_SID is not available on ${MACHINE} !!!"   # >>/tmp/db_${ORACLE_SID}_stauts.log  
    55.     MAIL_SUB=" $ORACLE_SID is not available on ${MACHINE} !!!" 
    56.     MAIL_BODY=" $ORACLE_SID is not available on ${MACHINE} at `date` when try to generate AWR." 
    57.     $MAIL_DIR/sendEmail -u $MAIL_SUB -f $MAIL_FM -t $MAIL_LIST -m $MAIL_BODY  
    58.     exit 1 
    59. fi; 
    60.  
    61. # ---------------------------------------------- 
    62. # Generate awr report 
    63. # ---------------------------------------------- 
    64. $ORACLE_HOME/bin/sqlplus /nolog<<EOF 
    65. connect / as sysdba; 
    66. @${AWR_CMD}/autoawr.sql; 
    67. exit; 
    68. EOF 
    69.  
    70. status=$? 
    71. if [ $status != 0 ];then 
    72.     echo " $ORACLE_SID is not available on ${MACHINE} !!!"   # >>/tmp/db_${ORACLE_SID}_stauts.log 
    73.     MAIL_SUB=" Occurred error while generate AWR for ${ORACLE_SID}  !!!" 
    74.     MAIL_BODY=" Some exceptions encountered during generate AWR report for $ORACLE_SID on `hostname`." 
    75.     $MAIL_DIR/sendEmail -u $MAIL_SUB -f $MAIL_FM -t $MAIL_LIST -m $MAIL_BODY 
    76.     exit 
    77. fi 
    78.  
    79. # ------------------------------------------------ 
    80. # Send email with AWR report 
    81. # ------------------------------------------------ 
    82. dt=`date -d yesterday +%Y%m%d` 
    83. filename=`ls ${AWR_DIR}/${ORACLE_SID}_awrrpt_?_${dt}*` 
    84. if [ -e "${filename}" ];then 
    85.     MAIL_SUB="AWR report from ${ORACLE_SID} on `hostname`." 
    86.     MAIL_BODY="This is an AWR report from ${ORACLE_SID} on `hostname`." 
    87.     $MAIL_DIR/sendEmail -u $MAIL_SUB -f $MAIL_FM -t $MAIL_LIST -m $MAIL_BODY -a ${filename} 
    88.     echo ${filename} 
    89. fi 
    90.  
    91. # ------------------------------------------------ 
    92. # Removing files older than $RETENTION parameter  
    93. # ------------------------------------------------ 
    94.  
    95. find ${AWR_DIR} -name "*awrrpt*" -mtime +$RETENTION -exec rm {} \; 
    96. exit      
    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 00:30                                        |
#       If you want to change the snap interval,please change autoawr.sql   |
#          and crontab configuration                                        |
#   Usage:                                                                  |
#       ./autoawr.sh $ORACLE_SID                                            |  
#                                                                           |
#   Author : Robinson                                                       | 
#   Blog   : http://blog.csdn.net/robinson_0612                             |
# --------------------------------------------------------------------------+
#
# --------------------------
#   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?
    1. robin@SZDB:~/dba_scripts/custom/awr> more autoawr.sql 
    2. SET ECHO OFF; 
    3. SET VERI OFF; 
    4. SET FEEDBACK OFF; 
    5. SET TERMOUT ON; 
    6. SET HEADING OFF; 
    7.  
    8. VARIABLE rpt_options NUMBER; 
    9. DEFINE no_options = 0; 
    10.  
    11. define ENABLE_ADDM = 8; 
    12.  
    13. REM according to your needs, the value can be 'text'or'html' 
    14.  
    15. DEFINE report_type='html'; 
    16.  
    17. BEGIN 
    18.    :rpt_options := &no_options; 
    19. END; 
    20. / 
    21.  
    22. VARIABLE dbid NUMBER; 
    23. VARIABLE inst_num NUMBER; 
    24. VARIABLE bid NUMBER; 
    25. VARIABLE eid NUMBER; 
    26.  
    27. BEGIN 
    28.   SELECTMIN (snap_id) INTO :bid 
    29.     FROM dba_hist_snapshot 
    30.    WHERE TO_CHAR (end_interval_time, 'yyyymmdd') = TO_CHAR (SYSDATE-1, 'yyyymmdd'); 
    31.  
    32.    SELECTMAX (snap_id) INTO :eid FROM dba_hist_snapshot WHERE TO_CHAR (begin_interval_time,'yyyymmdd') = TO_CHAR (SYSDATE-1, 'yyyymmdd'); 
    33.  
    34.    SELECT dbid INTO :dbid FROM v$database; 
    35.  
    36. SELECT instance_number INTO :inst_num FROM v$instance; 
    37. END; 
    38. / 
    39.  
    40. COLUMN ext NEW_VALUE ext NOPRINT 
    41. COLUMN fn_name NEW_VALUE fn_name NOPRINT; 
    42. COLUMN lnsz NEW_VALUE lnsz NOPRINT; 
    43. SELECT'txt' ext 
    44.   FROM DUAL 
    45. WHERELOWER ('&report_type') = 'text'; 
    46.  
    47. SELECT'html' ext 
    48.   FROM DUAL 
    49. WHERELOWER ('&report_type') = 'html'; 
    50.  
    51. SELECT'awr_report_text' fn_name 
    52.   FROM DUAL 
    53. WHERELOWER ('&report_type') = 'text'; 
    54.  
    55. SELECT'awr_report_html' fn_name 
    56.   FROM DUAL 
    57. WHERELOWER ('&report_type') = 'html'; 
    58.  
    59. SELECT'80' lnsz 
    60.   FROM DUAL 
    61. WHERELOWER ('&report_type') = 'text'; 
    62.  
    63. SELECT'1500' lnsz 
    64.   FROM DUAL 
    65. WHERELOWER ('&report_type') = 'html'; 
    66.  
    67. set linesize &lnsz; 
    68. COLUMN report_name NEW_VALUE report_name NOPRINT; 
    69.  
    70. SELECT instance_name || '_awrrpt_' || instance_number || '_' || b.timestamp || '.' || '&ext' 
    71.           report_name 
    72.   FROM v$instance a, 
    73.        (SELECT TO_CHAR (begin_interval_time, 'yyyymmdd') timestamp 
    74.           FROM dba_hist_snapshot 
    75.          WHERE snap_id = :eid) b; 
    76.  
    77. SET TERMOUT OFF; 
    78. SPOOL $AWR_DIR/&report_name; 
    79.  
    80. SELECToutput 
    81.   FROMTABLE (DBMS_WORKLOAD_REPOSITORY.&fn_name (:dbid, 
    82.                                                  :inst_num, 
    83.                                                  :bid, 
    84.                                                  :eid, 
    85.                                                  :rpt_options)); 
    86. SPOOL OFF; 
    87. SET TERMOUT ON; 
    88. CLEAR COLUMNS SQL; 
    89. TTITLE OFF; 
    90. BTITLE OFF; 
    91. REPFOOTER OFF; 
    92.  
    93. UNDEFINE report_name 
    94. UNDEFINE report_type 
    95. UNDEFINE fn_name 
    96. UNDEFINE lnsz 
    97. UNDEFINE no_options   
    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-1, 'yyyymmdd');

   SELECT MAX (snap_id) INTO :eid FROM dba_hist_snapshot WHERE TO_CHAR (begin_interval_time,'yyyymmdd') = TO_CHAR (SYSDATE-1, 'yyyymmdd');

   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 = :eid) 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
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
  • 相关阅读:
    个人工作总结6
    使用java对文件批量重命名
    导入csv文件到数据库
    关于微信小程序的Request请求错误处理
    基于jPlayer的三分屏制作
    条件查询时，如果有的输入框不输入，后台应该怎么处理？
    mysql 1449 ： The user specified as a definer ('root'@'%') does not exist 解决方法
    C++&&Objective-c
    加载ttf字体
    ios6去除高光效果,和判断iphone或ipad设备
  • 原文地址：https://www.cnblogs.com/weixun/p/3087600.html
Copyright © 2020-2023  润新知