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

# ------------------------------------------------------------

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

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

end_date=${3}

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='[email protected]'

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}

# ----------------------------------------------

# 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

# ------------------------------------------------

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

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

SELECT snap_id

INTO :bid

FROM dba_hist_snapshot

WHERE TO_CHAR (end_interval_time, 'yyyymmddhh24') = '&1';

INTO :eid

WHERE TO_CHAR (end_interval_time, 'yyyymmddhh24') = '&2';

SELECT dbid INTO :dbid FROM v$database;

SELECT instance_number INTO :inst_num FROM v$instance;

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

WHERE LOWER ('&report_type') = 'html';

SELECT 'awr_report_text' fn_name

SELECT 'awr_report_html' fn_name

SELECT '80' lnsz

SELECT '1500' lnsz

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;

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

4、部署參考

#如果僅僅需要一整天的awr report，直接将其部署到crontab即可。

#如果需要一整天以及不同時段的awr report，則可以考慮采用如下方式來部署，将其合并到一個shell檔案

robin@SZDB:~/dba_scripts/custom/awr> more awr.sh

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

文章可以轉載，必須以連結形式标明出處。

本文轉自張沖andy 部落格園部落格，原文連結： http://www.cnblogs.com/andy6/p/5877500.html ，如需轉載請自行聯系原作者

Linux/Unix shell 自動發送AWR report(二)

繼續閱讀

Cloud Studio初體驗

使用 ctypes 進行 Python 和 C 的混合程式設計

【python】【資料處理】畫多元資料分布圖

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

samba伺服器的功能

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

【Linux】UDP廣播封包接收速率問題

SequoiaDB巨杉資料庫C++驅動概述

【python】netconf協定對接管理裝置

「Python 網絡自動化」NETCONF —— Python 使用 NETCONF 管理配置 H3C 網絡裝置

Linux裝置模型（中）之上層容器

Oracle 批量查詢傳入List 傳回List

PowerPC平台 Linux移植三

在python中建立excel并寫入