awr自动发送脚本

#!/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  
=================================================================
===========================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;   
=================================================================
========================使用教程==================================
#如果仅仅需要一整天的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 

复制