AWR automation

#!/bin/ksh
set -x
export RECIPENTS=abc@xyz.com
export DT1=`date +%d%m_%H%M`
export PATH=$PATH:/usr/bin:/usr/local/bin

cd /opt/prod/support/genawr/awr
rm *.zip *.html

#######################################################
# FORMAT TO TUN THE SCRIPT
# script_name connect_string duration instance_number
######################################################
/opt/prod/support/genawr/generate_awr.sh username/passwd@dbname 6 2


cd /opt/prod/support/genawr/awr

zip awr_prod_$DT1.zip *.html

uuencode awr_prod_$DT1.zip awr_prod_$DT1.zip | mailx -s "AWR Report for all Production databases for last 6 hrs " -m $RECIPENTS

$ cat /opt/prod/support/genawr/generate_awr.sh
#!/bin/ksh
set -x
export ORACLE_HOME=/opt/oracle/product/10.2.0
export PATH=$PATH:$ORACLE_HOME/bin
export HOMEDIR=/opt/prod/support/genawr/
export RECIPENTS=mark@abc.com
dbname=`echo $1 | cut -d'@' -f2`


if [[ $# -lt 1 ]];then
        echo "Please Enter valid parameter to this script as follows:"
        echo "\ngenerate_awr.sh <DB_USER>/DB_PASSWD@CONN_STRING"
        exit;
else
        echo "Connecting to $1"
fi

sqlplus -s ${1} << EOF > $HOMEDIR/generate_awr.log
set head off
set pages 0
set lines 132
set echo off
set feedback off
spool $HOMEDIR/snapid.txt
--select distinct trim(snap_id) from dba_hist_snapshot where to_number(to_char(end_interval_time,'yyyymmddHH')) = to_number(to_char(sysdate,'yyyymmddHH'));
select snap_id from dba_hist_snapshot where to_number(to_char(end_interval_time,'yyyymmddHH')) = to_number(to_char(sysdate,'yyyymmddHH')) order by end_interval_time desc;
spool off;

spool $HOMEDIR/dbid.txt

select distinct trim(dbid) from dba_hist_snapshot;

spool off;


EOF

sed 's/ //g' $HOMEDIR/snapid.txt > $HOMEDIR/temp.txt ;  mv $HOMEDIR/temp.txt $HOMEDIR/snapid.txt
sed 's/ //g' $HOMEDIR/dbid.txt > $HOMEDIR/temp1.txt ;  mv $HOMEDIR/temp1.txt $HOMEDIR/dbid.txt


l_end=`cat $HOMEDIR/snapid.txt | head -1`
echo "l_begin=`expr ${l_end} - ${2}`"
l_begin=`expr ${l_end} - ${2}`
l_dbid=`cat $HOMEDIR/dbid.txt`

#rm $HOMEDIR/*.html

sqlplus -s $1 << EOF >> $HOMEDIR/generate_awr.log
set head off
set pages 0
set lines 500
set echo off
set feedback off
spool $HOMEDIR/awr.txt

SELECT output FROM TABLE (dbms_workload_repository.awr_report_html($l_dbid,$3,$l_begin,$l_end));

spool off;

EOF

mv $HOMEDIR/awr.txt $HOMEDIR/awr/awr_$dbname.html

#cd $HOMEDIR

#zip awr_${l_begin}_${l_end}.zip awr_${l_begin}_${l_end}.html

#uuencode awr_${l_begin}_${l_end}.zip awr_${l_begin}_${l_end}.zip | mailx -s "AWR Report for last 6 hours" -m $RECIPENTS