#!/bin/bash
################## AWR output path ##################
AWR_export_path=/home/oracle
echo -e "######################################################################################"
echo -e "################## Now start to Generate BO RAC AWR report ##############"
echo -e "######################################################################################"
################ clear AWR report which generate at 720 mins before ###############
find ${AWR_export_path} -type f -name 'AWR_RAC_BO_*zip' -mmin +720 -exec rm -rf {} \; -o -name 'AWR_RAC_BO_*html' -mmin +720 -exec rm -rf {} \;
######################################################################################
################## Get data from database for yesterday AWR information ##############
######################################################################################
yesterday_snapstop_var=(
$(
sqlplus -S / as sysdba << EOF
set heading off
set trimspool on
set feedback off
select Min(SNAP_ID), Max(SNAP_ID),to_char(sysdate - 1,'yyyymmdd')FROM DBA_HIST_SNAPSHOT where BEGIN_INTERVAL_TIME>=(trunc(sysdate)- (13/24)) and BEGIN_INTERVAL_TIME <= (trunc(sysdate) - 2/24) order by 1;
EOF
)
)
################## Parameter for yesterday RAC AWR ##################
yesterday_Min_value=${yesterday_snapstop_var[0]}
yesterday_Max_value=${yesterday_snapstop_var[1]}
yesterday_date_var=${yesterday_snapstop_var[2]}
yesterday_start_time='12'
days_yesterday_num=2
################## Print yesterday RAC AWR ##################
while [ ${yesterday_Min_value} -lt ${yesterday_Max_value} ]
do
yesterday_AWR_log=${AWR_export_path}/AWR_RAC_BO_${yesterday_date_var}_${yesterday_start_time}_$(expr ${yesterday_start_time} + 1).html
echo -e "Generating BO RAC AWR: ${yesterday_AWR_log}"
echo -e "html\n${days_yesterday_num}\n${yesterday_Min_value}\n$(expr ${yesterday_Min_value} + 1)\n${yesterday_AWR_log}\n"|(sqlplus -S / as sysdba @?/rdbms/admin/awrgrpt.sql) > /dev/null
yesterday_Min_value=$(expr ${yesterday_Min_value} + 1)
yesterday_start_time=$(expr ${yesterday_start_time} + 1)
done
zip -q -r AWR_RAC_BO_${yesterday_date_var}_12_22.zip AWR_RAC_BO_${yesterday_date_var}_*html
######################################################################################
################## Get data from database for today AWR information ##################
######################################################################################
today_snapstop_var=(
$(
sqlplus -S / as sysdba << EOF
set heading off
set trimspool on
set feedback off
select Min(SNAP_ID), Max(SNAP_ID),to_char(sysdate,'yyyymmdd')FROM DBA_HIST_SNAPSHOT where BEGIN_INTERVAL_TIME >= (trunc(sysdate) - (1/24)) and BEGIN_INTERVAL_TIME<=(trunc(sysdate) + (12/24)) order by 1;
EOF
)
)
################## Parameter for today RAC AWR ##################
today_Min_value=${today_snapstop_var[0]}
today_Max_value=${today_snapstop_var[1]}
today_date_var=${today_snapstop_var[2]}
today_start_time='00'
days_today_num=1
################## Print today RAC AWR ##################
while [ ${today_Min_value} -lt ${today_Max_value} ]
do
today_AWR_log=${AWR_export_path}/AWR_RAC_BO_${today_date_var}_$(echo ${today_start_time}|awk '{printf("%02d\n",$0)}')_$(echo $(expr ${today_start_time} + 1)|awk '{printf("%02d\n",$0)}' ).html
echo -e "Generating BO RAC AWR: ${today_AWR_log}"
echo -e "html\n${days_today_num}\n${today_Min_value}\n$(expr ${today_Min_value} + 1)\n${today_AWR_log}\n"|(sqlplus -S / as sysdba @?/rdbms/admin/awrgrpt.sql) > /dev/null
today_Min_value=$(expr ${today_Min_value} + 1)
today_start_time=$(expr ${today_start_time} + 1)
done
zip -q -r AWR_RAC_BO_${today_date_var}_00_12.zip AWR_RAC_BO_${today_date_var}_*html
echo -e "######################################################################################"
echo -e "########### Complete to generate BO RAC AWR report successfully! ###########"
echo -e "######################################################################################"