自动生成发送awr脚本（RAC&&单机）

#!/bin/bash
source ~/.bash_profile

DATETIME=`date +%Y%m%d_%H%M`
#Create oracle directory
if [ ! -d "/home/oracle/awr" ]; then
mkdir -p /home/oracle/awr
chown -R oracle:oinstall /home/oracle/awr
fi
INSTANCE_NUM=${ORACLE_SID:0-1}

#Judge whether or not RAC
IF_RAC=`sqlplus -S / as sysdba <<EOF
set heading off trimspool on feedback off pagesize 0 verify off echo off;
select value from v\\$option where PARAMETER='Real Application Clusters';
EOF`

WORKPATH=/home/oracle/awr
AWR_FORMAT=html
NUM_DAYS=7
MAX_SNAP_ID=`sqlplus -S / as sysdba << EOF       
set heading off trimspool on feedback off
SELECT trim(max(SNAP_ID)) FROM DBA_HIST_SNAPSHOT;
EOF`
MIN_SNAP_ID=`expr $MAX_SNAP_ID - 144`

#Judge whether or not need input INSTANCE_NUM
if [ -z "`echo $INSTANCE_NUM|sed 's/[0-9]//g'`" ];then
        SNAP_ID=`sqlplus -s / as sysdba << EOF       
set pages 0
set heading off
select begin_snap x from (
select begin_snap,end_snap,timestamp, to_number(round(a / 1000000 / 60, 2)) dbtime
  from (select e.snap_id end_snap,
               lag(e.snap_id) over(order by e.snap_id) begin_snap,
               lag(s.end_interval_time) over(order by e.snap_id) timestamp,
               s.instance_number inst,
               e.value,
               nvl(value - lag(value) over(order by e.snap_id), 0) a
          from dba_hist_sys_time_model e, DBA_HIST_SNAPSHOT s
         where s.snap_id = e.snap_id
           and e.instance_number = s.instance_number
           and to_char(e.instance_number) like nvl(${INSTANCE_NUM},to_char(e.instance_number))
           and stat_name = 'DB time')
 where begin_snap between ${MIN_SNAP_ID} and ${MAX_SNAP_ID}
 order by dbtime desc
) where rownum = 1;
EOF`
else
        SNAP_ID=`sqlplus -s / as sysdba << EOF       
set pages 0
set heading off
select begin_snap x from (
select begin_snap,end_snap,timestamp, to_number(round(a / 1000000 / 60, 2)) dbtime
               lag(s.end_interval_time) over(order by e.snap_id) timestamp,
               s.instance_number inst,
               e.value,
         where s.snap_id = e.snap_id
           and e.instance_number = s.instance_number
           and stat_name = 'DB time')
 where begin_snap between ${MIN_SNAP_ID} and ${MAX_SNAP_ID}
 order by dbtime desc
) where rownum = 1;
EOF`
fi

BEGIN_SNAP_ID=`expr $SNAP_ID + 0`
END_SNAP_ID=`expr $SNAP_ID + 1`

AWR_RPT=$WORKPATH/AWR_${ORACLE_SID}_${DATETIME}.html
AWR_RAC_RPT=$WORKPATH/AWR_RAC_${DATETIME}.html
HOSTNAME=`hostname|cut -f 1 -d '.'`
IP_ADD=`cat /etc/hosts | grep ${HOSTNAME} |awk '{print $1}'`
DBALIST="xiaoxuesong@longi-silicon.com" export DBALIST

#Judge whether or not RAC
if [[ $IF_RAC = "TRUE" ]] && [[ $INSTANCE_NUM = 1 ]]; then
        echo -e "$AWR_FORMAT\n$NUM_DAYS\n$BEGIN_SNAP_ID\n$END_SNAP_ID\n$AWR_RPT\n"|(sqlplus -S / as sysdba @?/rdbms/admin/awrrpt.sql) >/dev/null 2>/$WORKPATH/awr.log &
        sleep 60
        echo -e "$AWR_FORMAT\n$NUM_DAYS\n$BEGIN_SNAP_ID\n$END_SNAP_ID\n$AWR_RAC_RPT\n"|(sqlplus -S / as sysdba @?/rdbms/admin/awrgrpt.sql) >/dev/null 2>/$WORKPATH/awr_rac.log &
        #wait report create
        sleep 80
        echo -e "This is the AWR report of the highest DBtime of the week for the database, please check it out.\n\nHOSTNAME: ${HOSTNAME} \nSID: ${ORACLE_SID}" | mailx -v -s "AWR report of ${ORACLE_SID}" -a $AWR_RPT -a $AWR_RAC_RPT $DBALIST
else
        echo -e "$AWR_FORMAT\n$NUM_DAYS\n$BEGIN_SNAP_ID\n$END_SNAP_ID\n$AWR_RPT\n"|(sqlplus -S / as sysdba @?/rdbms/admin/awrrpt.sql) >/dev/null 2>/$WORKPATH/awr.log &
        sleep 60
        echo -e "This is the AWR report of the highest DBtime of the week for the database, please check it out.\n\nHOSTNAME: ${HOSTNAME} \nSID: ${ORACLE_SID}" | mailx -v -s "AWR report of ${ORACLE_SID}" -a $AWR_RPT $DBALIST
fi

#clear his report
/usr/bin/find $WORKPATH -type f -mtime +30 -exec rm {} \;