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_profilefi# ------------------------------------------------------------#  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]"fiif [ -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}fiORACLE_SID=${1}export ORACLE_SID begin_date end_date export MACHINE=`hostname`export MAIL_DIR=/users/robin/dba_scripts/sendEmail-v1.56export MAIL_LIST='Robinson.chen@12306.com'export AWR_CMD=/users/robin/dba_scripts/custom/awrexport AWR_DIR=/users/robin/dba_scripts/custom/awr/report/${ORACLE_SID}export MAIL_FM='oracle@szdb.com'RETENTION=31echo $ORACLE_SID echo $begin_dateecho $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 1fi;# ---------------------------------------------#  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    exitfi# ------------------------------------------------# 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.sqlSET 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 NOPRINTCOLUMN 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_nameSELECT 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_nameUNDEFINE report_typeUNDEFINE fn_nameUNDEFINE lnszUNDEFINE no_optionsexit; 

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/bashdt=`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 reportexit 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

更多參考:

有關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安裝環境

有關Oracle 網路設定相關基礎以及概念性的問題請參考：
     配置非預設連接埠的動態服務註冊
     配置sqlnet.ora限制IP訪問Oracle
     Oracle 監聽器日誌配置與管理
     設定 Oracle 監聽器密碼(LISTENER)
     配置ORACLE 用戶端串連到資料庫

有關基於使用者管理的備份和備份恢複的概念請參考
     Oracle 冷備份
     Oracle 熱備份
     Oracle 備份恢複概念
     Oracle 執行個體恢複
     Oracle 基於使用者管理恢複的處理
     SYSTEM 資料表空間管理及備份恢複
     SYSAUX資料表空間管理及恢複
     Oracle 基於備份控制檔案的恢複(unsing backup controlfile)

有關RMAN的備份恢複與管理請參考
     RMAN 概述及其體繫結構
     RMAN 配置、監控與管理
     RMAN 備份詳解
     RMAN 還原與恢複
     RMAN catalog 的建立和使用
     基於catalog 建立RMAN儲存指令碼
     基於catalog 的RMAN 備份與恢複
     RMAN 備份路徑困惑
     使用RMAN實現異機備份恢複(WIN平台)
     使用RMAN遷移檔案系統資料庫到ASM
     linux 下RMAN備份shell指令碼
     使用RMAN遷移資料庫到異機

有關ORACLE體繫結構請參考
     Oracle 資料表空間與資料檔案
     Oracle 密碼檔案
     Oracle 參數檔案
     Oracle 聯機重做記錄檔(ONLINE LOG FILE)
     Oracle 控制檔案(CONTROLFILE)
     Oracle 歸檔日誌
     Oracle 復原(ROLLBACK)和撤銷(UNDO)
     Oracle 資料庫執行個體啟動關閉過程
     Oracle 10g SGA 的自動化管理
     Oracle 執行個體和Oracle資料庫(Oracle體繫結構)