观察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@<span style="color:#000000;">12306</span>.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
转：http://blog.csdn.net/leshami/article/details/8687690
