Title: Generating Remote or Distributed Database Reports

Author: Ratan Makker, a DBA for Wipro Ltd., in New Delhi, India.

This shell program is written to generate the database report using SQL*PLUS with TNS connect strings. This will get the
Oracle_SID from local as well as remote m/c, run sqlplus with connect strings to get the output.

The password should be the same for all the SID. If it is not, that SID will not show in your report.

Three different reports will be generated name sqlMC_NAME.rpt

# Assosiated Program - parab.sql

The objective of writing this program is to avoid jumping from one machine to another and avoiding unnecessary work in
generating the report.

The TNS string name for all the Databases need to be same as SID.

##############################
MC_LIST="abc mno xyz"
############################
# Run the loop for each machine
###########################
for MC_NAME in $MC_LIST
do
SUMMARY=sql${MC_NAME}.rpt
rm -f $SUMMARY
############################
# Get the list of Instances from /etc/oratab file
############################
ORATAB=/etc/oratab
rm olist
remsh $MC_NAME cat $ORATAB | while read LINE
do    case $LINE in        \#*)            ;;      #comment-line in oratab
                        *)#       Proceed only if third field is 'Y'.
                        if [ "`echo $LINE | awk -F: '{print $3}' -`" = "Y" ] ;
                        then
                        O_SID=`echo $LINE | awk -F: '{print $1}' -`
                        echo "$O_SID" >>olist
                        fi
                        esac
                        done
###########################
I_LIST=`cat olist |sort`
MC_DATE=`remsh $MC_NAME date`
## Heading
echo $MC_NAME $MC_DATE>$SUMMARY
echo "------------------------------------------------------------------------------------"     >> $SUMMARY
echo "Instance  Creation        Logmode      Processes     SGA        Freemem        " >>$SUMMARY
echo "-----------------------------------------------------------------------------------"      >> $SUMMARY
##########################
# Run sqlplus with tnsname for all the SID
#########################
        for I_NAME in $I_LIST
        do
                sqlplus -s system@$I_NAME @parab>$SUMMARY
############################
# remove para.lst file as it is printing the old stuff for down or incorrect
# password SID
###########################
                        rm para.lst
                        fi
        done
done
######################
# End of Shell Program
######################
# Start of parab.sql program
##########################
set pages 100
set verify off
set head off
set feedback off
spool para
column Process format A10
column Value format A5
col c new_value SGA noprint
col d new_value FREE noprint
col TOT_SGA format 999,999,999
col FREE_SGA format 999,999,999
select bytes d from v$sgastat where name in ( 'free memory' );
select sum(bytes) c from v$sgastat ;
select  a.name INSTANCE, a.created , a.log_mode, b.Value  Process, &SGA TOT_SGA,
  &FREE FREE_SGA from v$database a,  V$PARAMETER b ,  dual  where b.name in
( 'processes'  )
/

##########################
# End of SQL program
##########################