Linux/Unix shell自動發送AWR report
觀察Oracle數據庫性能，Oracle自帶的awr 功能為我們提供了一個近乎完美的解決方案，通過awr特性我們可以隨時從數據庫提取awr報告。不過awrrpt.sql腳本執行時需要我們提供一些交互信息，因此可以將其整合到shell腳本中來實現自動產生指定時段的awr報告並發送給相關人員。本文即是描述linux shell腳本來實現此功能。
www.2cto.com
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 |
# |
# --------------------------------------------------------------------------+
#
# --------------------------
# 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='
[email protected]'
export AWR_CMD=/users/robin/dba_scripts/custom/awr
export AWR_DIR=/users/robin/dba_scripts/custom/awr/report
export MAIL_FM='
[email protected]'
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<
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]
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
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
