DB connection report

# APAC Instance Connection Script
06,21,36,51 * * * * /opt/oracle/DBAENGG/scripts/ofr_dbalert_apac.ksh

pbpmccdbaora2:/opt/oracle/DBATOOLS/sql/general>>more /opt/oracle/DBAENGG/scripts/ofr_dbalert_apac.ksh
#!/usr/bin/ksh
. $HOME/.setup_PMCGRD20.ksh
export MAILLIST="Equities_APAC_Oracle_Support@jpmchase.com"
export MAILLIST1="ib.core.svcs.oracle.mbox@jpmorgan.com"
ORALOGIN=dbsnmp
ORAPASSWD=umagellan1#
GRIDLOGIN=/
SCRIPTDIR=`dirname "$0"`
FILENAME=`basename "$0"`
ValidDB=0
InValidDB=0
BlackOut=0
ExcptDB=0
LOG_FILE=/opt/oracle/DBAENGG/log/ofr_dbalert_apac.txt
rm $LOG_FILE /opt/oracle/DBAENGG/scripts/sqlnet.log
echo " -----------------------------------------------------------------------------------------------------------------"> $LOG_FILE
echo "| Connection Status For APAC Oracle Databases   `date '+%Y/%m/%d %H:%M:%S'` EST  |">> $LOG_FILE
echo " -----------------------------------------------------------------------------------------------------------------">> $LOG_FILE
for ORATABLN in `cat /opt/oracle/DBAENGG/dbmap/oratab.apac`
do
  case $ORATABLN in
        \#*)            ;;      #comment-line
          *)
        unset DB_NAME ORACLE_HOST
     if [ "`echo $ORATABLN | awk -F: '{print $NF}' -`" = "Y" ] ; then
            DB_NAME=`echo $ORATABLN | awk -F: '{print $1}' -`
            ORACLE_HOST=`echo $ORATABLN | awk -F: '{print $2}' -`
V_DB_NAME=$(sqlplus -s -L $ORALOGIN/$ORAPASSWD@$DB_NAME <<EOF01A
set heading off feedback off;
set newpage none;
set pages 0;
set echo off;
set term off;
set verify off;
select upper(instance_name)  from gv\$instance where upper(instance_name)=upper('$DB_NAME');
exit;
EOF01A)
V_DB_NAME=`echo -e $V_DB_NAME`
echo $V_DB_NAME $DB_NAME
if [ "${V_DB_NAME}" = "${DB_NAME}" ]; then
  ValidDB=`expr $ValidDB + 1`
else
#echo entering blackout check
BNPROD_DB_NAME=$(sqlplus -s -L $GRIDLOGIN as sysdba<<EOF03A
set heading off feedback off;
set newpage none;
set pages 0;
set echo off;
set term off;
set verify off;
select distinct('$DB_NAME') from
sysman.MGMT_TARGETS a ,
sysman.MGMT_BLACKOUT_WINDOWS b
where lower(TARGET_TYPE)='oracle_database' and
START_TIME <= b.end_time
and a.TARGET_GUID=b.TARGET_GUID
and target_name like '%$DB_NAME%'
and b.status=4;
exit;
EOF03A)
BNPROD_DB_NAME=`echo -e $BNPROD_DB_NAME`
if [ "${BNPROD_DB_NAME}" = "${DB_NAME}" ]; then
   BlackOut=`expr $BlackOut + 1`
else
  echo '| '$DB_NAME':' 'Connection Failure *** |'  >>  $LOG_FILE
  echo $V_DB_NAME  >>  $LOG_FILE
  InValidDB=`expr $InValidDB + 1`
fi
fi
     fi;;
  esac
done
echo " -------------------------------------------------------------------------">> $LOG_FILE
echo "| Total Success:" $ValidDB '                                             |' >>  $LOG_FILE
echo "| Total Failure:" $InValidDB '                                                  |' >>  $LOG_FILE
echo "| Total BlackOut:" $BlackOut '                                              |' >>  $LOG_FILE
echo "| Total Excluded:" $ExcptDB '                                             |' >>  $LOG_FILE
echo " -------------------------------------------------------------------------">> $LOG_FILE
echo "Script: /opt/oracle/DBAENGG/scripts/ofr_dbalert_apac.ksh" >> $LOG_FILE
InValidDB=`echo -e $InValidDB`
if [ $InValidDB -gt 0 ]; then
 echo
mail -s "APAC: Oracle INSTANCE Connection Report" $MAILLIST1 -c $MAILLIST < $LOG_FILE
fi
exit 0

pbpmccdbaora2:/opt/oracle/DBATOOLS/sql/general>>cat /opt/oracle/DBAENGG/dbmap/oratab.apac
CVPROD:Y
NEOTKP:N
NEOKRP:N
NEOKRP-KRLNEOP2:N
KRNEOP:Y
HKNEO:Y
CERDCORP:Y
NEOHKP:Y
SGNEOP:Y
INNEOP:Y
NEOTWP:Y
TWNEOP:Y