#!/bin/ksh

#=================================
#Setting up all the Valiables:
#=================================
export HOME=/home/oracle
export ORACLE_BASE=/apps/oracle
export ORACLE_HOME=/apps/oracle/product/8.1.6
export SYBASE=/home/oracle/current
export DBA_CONTACTS="boss@boss.com"

#=======================================
# Getting SID lis from Sybase server
# get_sids calls get_sid() function
#=======================================
get_sids()
{
${SYBASE}/bin/isql -U sa -S <Server Name> << eof | egrep -v 'Password|name|\-\-\-\-\-' > /tmp/sids_list
`cat ${HOME}/.passwd`
use ora_monitor_dev
go
set nocount on
go
select server_name from server_list where port=1521
go
eof
}

#=============================================================================================================
# Checking all the server are available or not based on the online status it creates a final list of database
# check_avail calls check_avail()
#=============================================================================================================
check_avail()
{
$ORACLE_HOME/bin/svrmgrl  << eof
connect system/`cat $HOME/.passwd`@${db}
spool /tmp/${db}.out
select * from v\$database;
exit
eof

avail=`grep ORA- /tmp/${db}.out`
# Oracle is not available
if [ "$avail" != "" ]
then
echo ${db} >> /tmp/sids_down_list
else
echo ${db} >> /tmp/sids_up_list
fi
}

#==========================================================================================
# Getting all the Database space information for each database in the final list
# It is also creating spool file based on the database name and creating a insert scripts
# Here is the script: cat /tmp/$db_inserts.sql >> /tmp/inserts.sql  [Many to one file]
# get_space calls get_space() function
#===========================================================================================
get_space()
{
$ORACLE_HOME/bin/sqlplus -s system/`cat $HOME/.passwd`@$db << eof
set linesize 100
set head off
set heading off
set echo off
set feedback off
set pagesize 0
set verify off
set termout off
set pause off
set timing off
set linesize 300
spool /tmp/$db_inserts.sql

select 'insert into database_space_usage values (''$db'''||','||''''||dd.tablespace_name||''''||',0,'||
       round((sum(distinct(dd.bytes||'.'||lpad(dd.file_id,10,'0'))) -
          sum(distinct('.'||lpad(dd.file_id,10,'0'))))/(1024*1024))||','||
       round((sum(distinct(dd.bytes||'.'||lpad(dd.file_id,10,'0'))) -
          sum(distinct('.'||lpad(dd.file_id,10,'0'))))/(1024*1024))||','||0||','||
       round((sum(distinct(dd.bytes||'.'||lpad(dd.file_id,10,'0'))) -
          sum(distinct('.'||lpad(dd.file_id,10,'0'))))/(1024*1024))||','||
       (round((sum(distinct(dd.bytes||'.'||lpad(dd.file_id,10,'0'))) -
          sum(distinct('.'||lpad(dd.file_id,10,'0'))))/(1024*1024)) -
       round(sum(nvl(df.bytes,0))/(1024*1024)))||','||
       (round((sum(distinct(dd.bytes||'.'||lpad(dd.file_id,10,'0'))) -
          sum(distinct('.'||lpad(dd.file_id,10,'0'))))/(1024*1024)) -
       round(sum(nvl(df.bytes,0))/(1024*1024)))||','||round(sum(nvl(df.bytes,0))/(1024*1024))||','||
       (100 - round(trunc(10000*sum(nvl(df.bytes,0)) /
            (sum(distinct(dd.bytes||'.'||lpad(dd.file_id,10,'0'))) -
              sum(distinct('.'||lpad(dd.file_id,10,'0')))))/100)||','||''''||to_char(sysdate,'DD-MON-YY HH24:MM:SS')||''''||')')
       ||CHR(10)||'go'
from
dba_free_space df,
dba_data_files dd
where dd.tablespace_name = df.tablespace_name (+)
and dd.file_id = df.file_id (+)
group by dd.tablespace_name;

eof
}

#=================================================================
# Creating blank file
#=================================================================
> /tmp/sids_list
> /tmp/sids_up_list
> /tmp/sids_down_list
> /tmp/inserts.sql

#=================================================================
# Executing get_sids() function which is generating sids_list file
#=================================================================
get_sids

#==================================================================================================
# Opening sids_list file and checkinh the database is up or not if up It creates sids_up_list file
#==================================================================================================
for db in `cat /tmp/sids_list`
do
check_avail
done

#==================================================================================================================
# Opening sids_up_list file and getting the Tablespace Space information and generating <database_name>_inserts.sql
# files and finally makeing inserts.sql file
#==================================================================================================================
for db in `cat /tmp/sids_up_list`
do
echo $db
get_space

cat /tmp/$db_inserts.sql >> /tmp/inserts.sql
rm /tmp/$db_inserts.sql
done

#=========================================================================
# Append go command at the end of inserts.sql file
#=========================================================================
echo 'go' >> /tmp/inserts.sql

#===============================================================================================
# Connecting Sybase database and executing inserts.sql file also generating the insert out file
#===============================================================================================
$SYBASE/bin/isql -S <Server Name> -D ora_monitor_dev -U sa -e -i /tmp/inserts.sql -o /tmp/inserts.sql.out << eof
`cat $HOME/.passwd`
eof

#======================================================================================================
# Checking inserts.sql.out file for any errors.If error found then it send a email along with the file
# for future action
#======================================================================================================
Error_Count=`cat /tmp/inserts.sql.out | egrep -i "Msg|Level|State|error" | wc -l`

if [ $Error_Count -ne 0 ]
then
echo "RUN IS FAILED"
mailx -s "ORA MONITOR RUN IS FAILED " $DBA_CONTACTS < /tmp/inserts.sql.out
mailx -s "ORACLE Servers DOWN!!! Please Investigate " $DBA_CONTACTS < /tmp/sids_down_list
else
echo "RUN IS SUCCESSFULL"
mailx -s "ORACLE Servers DOWN!!! Please Investigate " $DBA_CONTACTS < /tmp/sids_down_list
fi

#==============================================================================
# Deleting all the files at the end
#==============================================================================
rm /tmp/inserts.sql
rm /tmp/sids_list
rm /tmp/sids_up_list
rm /tmp/sids_down_list