#!/usr/bin/bash
# Script: mkawrrpt.sh
# Author: Theodore Zacharia
# Version: 0.1 17/10/2011
# Version: 1.0 08/11/2011 - Added HTML report view output to
#                           work with Dashboard
#
# script to create AWR reports, just for laughs I've seperated
# the creation of the snapshot list from that of report creation
# even though the awr sql does this, it's been more educational doing
# it this way and gives you slightly more info on the available snapshot

# ***** Globals
LOGIN_USER=""
LOGIN_PASS=""
GETSNAPIDS=0
HTMLOUTPUT=0

# ***** Functions

_PressEnter ()
{
echo "$@"
echo "Press <ENTER> to continue"
read x
}


# ***** Mainline

while getopts shu:p: AOPT
do
case $AOPT in
        u) LOGIN_USER=$OPTARG ;; # set login user for sqlplus
        p) LOGIN_PASS=$OPTARG ;; # set login password for sqlplus
	s) GETSNAPIDS=1 ;; # set only display available snap ids
	h) HTMLOUTPUT=1 ;; # set output to only HTML
esac
done

shift $((OPTIND-1))


echo "***** IMPORTANT *****"
echo "Ensure you have run . oraenv and set your DB correctly"
echo " "
if [[ $ORACLE_SID == "" || $ORACLE_SID == "oracle" ]]
then
        echo "**** WARNING, it looks like you have not set the SID"
        echo "     continuing, but it probably will not work :( "
	_PressEnter
fi


if [ ! -n "$LOGIN_USER" ]
then
        SUPER="as sysdba"
else
        SUPER=""
fi

TF1=t1.tmp
# get DBname and DBID, we will need these later
sqlplus -S $LOGIN_USER/$LOGIN_PASS $SUPER << EOF
spool $TF1
set head off
set colsep ,
select dbid, name from v\$database;
spool off
EOF

DBINFO=`sort $TF1 | tail -1`
DBID=`echo $DBINFO | cut -d"," -f 1`
DBNAME=`echo $DBINFO | cut -d"," -f 2`
echo "Processing for DB $DBNAME with ID $DBID"



if [ $GETSNAPIDS -eq 1 ]
then
	echo "Available snap ids for database $DB_SID are:"

sqlplus -S $LOGIN_USER/$LOGIN_PASS $SUPER << EOF1
set head on
set wrap off
set trun off
set linesize 2999
set pagesize 2999
set colsep ,
select DBID||','||INSTANCE_NUMBER||','||SNAP_ID||','||STARTUP_TIME||','
||BEGIN_INTERVAL_TIME||','||END_INTERVAL_TIME 
 from dba_hist_snapshot where DBID = $DBID
order by begin_interval_time;
EOF1

	# OK,  got all snaps, done
	exit 0
fi


# if we get this far we should expect a start and end snap id

if (( $# < 1 ))
then
        echo "usage: $0 [-u user -p pass] [-s] snap_start snap_end"
	echo "-s  get snap ids only, then exit"
        exit 1
fi

SNAP_START=$1
SNAP_END=$2


# the following script automates the calling of AWR creation using
# the values passed in as input
sqlplus -S $LOGIN_USER/$LOGIN_PASS $SUPER << EOF2
@?/rdbms/admin/awrrpt
html
1
$SNAP_START
$SNAP_END

EOF2

if [ $HTMLOUTPUT -eq 1 ]
then
	# put out the report name to the output stream so we can nab it
	RPT=`ls -rt awrrpt*.html | tail -1`
	# The following line is for PnS Dashboard users
	echo "<h2><a href='getlog_viassh.pl?`uname -n`,`pwd`,$RPT'>Click here for report $RPT</a></h2>"
fi

exit 0