#!/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 {} \;