#!/bin/sh
#
# oracle: Oracle BB external script test
#
# version 1.0
# version 2.0 - Now uses new whos_on_db.sh.v2 script which uses SVRMGR
# version 3.0 - All pieces are now in this one script - modified extensively
# version 3.1 - properly uses $CAT instead of cat
# version 3.2 - properly uses $MACHINE instead of `uname -n` in bb-hosts check
# version 3.3 - properly uses $THIS_HOST instead of $MACHINE due to fqdn using comma in name - thanks Craig Cook
# version 3.4 - works in VCS clustered environment
# version 3.5 - fixed bug that appeared after "clusterizing" this 
# version 3.6 - try to autosense where the oratab is - /etc or /var/opt/oracle
# version 3.6 - Fix bad path line in lsnrctl call
#
# BIG BROTHER / XXXXXXXXXXXXXXXX status
#
# Written by Paul A. Luzzi
#  on Sept 19, 1999
#
# The template script has whatever copyright you want
# Do whatever you want with it
# I take no responsability for it, use at your own risks

########################################
#
# NOTE
# Version 1.0 and 2.0 of this have been tested with BB 1.2b on :
# 
#   RedHat Linux 5.2
#   SCO UnixWare7
#   SCO OpenServer 5.4
#   HP-UX 10.20, 11.0
#
# Version 3.0 of this has been tested with BB 1.2b, 1.4h2, 1.5, 1.5a on :
#
#   RedHat Linux 5.2
#   SCO UnixWare7
#
# As of version 3 of this oracle extension script, I now try to accomodate
#  the newer versions of BigBrother where you can run as a restricted user.
#  This script will attempt to work with earlier "root" run versions of BB
#  as well as the newer "bb" run versions.  It should sense who it is running
#  as and set variable accordingly, and of course execute the proper commands
#  based on these results.   The only condition is that the user that BB
#  is running as, if other than root, needs to be a member of the dba group.
#
# A final note : The easiest implementation of BB in a large environment,
#  is to setup the BBDISPLAY, BBNET, and BBPAGER on another host(s), and 
#  allow all of this (B) to run as user root on these client machines.  Since
#  there is no direct access, and no web servers needed on these machines, and
#  these extension scripts will never be accessible by anyone other than the
#  sys admin, it remains safe.  These client machines send only data back to 
#  the BBDISPLAY server, so these client machines are "secured" from external
#  access.  This would allow "safe" running as root on the client boxes, and
#  is certainly easier to setup/rollout.  However, that being said, it always
#  a good idea to keep an eye on security, so the choice is yours, the script
#  will run either way.
#
########################################

########################################
# INSTALLATION
#  step 1  - update the EXT section of the runbb.sh script to incl this
#  step 2  - The AWK scripts need to use field NF for the PS command
#  step 3  - update the $BBHOME/etc/bbsys.local file for any new variables
#  step 4  - for releases newer than 1.3 where this seems to run as the bb
#            user, bb needs to be a member of the oracle dba group
#  ...
########################################

##################################
# CONFIGURE IT HERE
##################################
#
# see below for variable declarations
#

##################################
# Start of script
##################################

if test ! "$BBHOME"
then
	echo "template: BBHOME is not set"
	exit 1
fi

if test ! -d "$BBHOME"
then
	echo "template: BBHOME is invalid"
	exit 1
fi

if test ! "$BBTMP"                      # GET DEFINITIONS IF NEEDED
then
	echo "template: The BB environment is not set"
	exit 1
fi

#####
#####  Start of custom section added by Paul A. Luzzi
#####
WHICH_NODE=`if [ -s /opt/VRTSvcs/bin/hastatus ]
  then
    /opt/VRTSvcs/bin/hastatus -summary | grep "^B" | egrep "ONLINE|PARTIAL" | awk '{print $3}' 
   else
    echo "$THIS_HOST"
  fi `
HASTATUS=`if [ -s /opt/VRTSvcs/bin/hastatus ]
  then
    /opt/VRTSvcs/bin/hastatus -summary | grep "^B" | grep "$THIS_HOST" | egrep "ONLINE|PARTIAL"
  fi `

#####
#####  Get Status proc - used to get all responses
#####
get_status() 
{

  #####
  #####  Setup any and all variables before beginning.
  #####
  MY_USER_ID=` id | $AWK -F= '{print $2}' | $AWK -F"(" '{print $1}' `
  ORACLE_USER=` $GREP orac /etc/passwd | $AWK -F\: '{print $1}' `  
  ORACLE_HOME=` $GREP orac /etc/passwd | $AWK -F\: '{print $6}' `/product/8.1.6
  ORACLE_BASE=$ORACLE_HOME
  ORACLE_PATH=$ORACLE_HOME:$ORACLE_HOME/db_mgmt:/bin:/usr/bin:/usr/lbin:/usr/local/bin
  ORACLE_TERM=386u
  ORA_CSM_MODE=line
  PATH=$PATH:$ORACLE_PATH:$BBHOME/ext
  LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/lib
  if [ -s /etc/oratab ]
    then
      ORATAB=/etc/oratab
    else 
      ORATAB=/var/opt/oracle/oratab
    fi
  DB_LIST=` $CAT $ORATAB | \
              $GREP -v "^#" | \
                while read ORATABLINE 
                  do
                    echo $ORATABLINE | \
                      $AWK -F: '{ if ( $3 == "Y" ) print $1 " "}' 
                  done `
  export ORACLE_USER ORACLE_HOME DB_LIST ORACLE_BASE ORACLE_PATH ORACLE_TERM ORA_CSM_MODE LD_LIBRARY_PATH MY_USER_ID
  echo "<BR>Instances installed : $DB_LIST"
  echo ""

  #####
  #####  First we will check that the oracle database is up and running
  #####    Probably use the parse_dbs.sh shell script for this so that
  #####    we can handle multiple databases running on the same server
  #####
  #####  Output to be returned is a "green light" for the db_process (7 of 'em)
  #####
  #####  Notes : we will run thru all procs and echo each individually
  #####
  echo "<P><DIV ALIGN=\"CENTER\"><HR>" 
  echo "<B>============== Oracle Instance Processes ==============</B>"
  echo "<HR></DIV>" 
  echo "<BLOCKQUOTE>" 
  ORA_PROCS=`$PS | $GREP ora_ | $GREP -v grep | $AWK -F" " '{print $NF}' `
  export ORA_PROCS
  if [ ! -z "$ORA_PROCS" ]
    then
      COLOR="green"
      for EACH_PROC in $ORA_PROCS
      do
        echo "Oracle process $EACH_PROC up and running."
      done
     else
      COLOR="red"
      echo "No Oracle processes running !"
    fi
  echo "</BLOCKQUOTE>"

  #####
  #####  Second we will check that the oracle network listener is up and running
  #####
  #####  Output to be returned is a "green light" for the listener process
  #####
  #####  Notes : dont know if more than one, or MTS will cause problems
  #####
  echo "<P><DIV ALIGN=\"CENTER\"><HR>" 
  echo "<B>============== Oracle Listener ==============</B>"
  echo "<HR></DIV>" 
  echo "<BLOCKQUOTE>" 
  ORA_LISTENER_PROC=`$PS | $GREP tnslsnr | $GREP -v grep `
  if [ ! -z "$ORA_LISTENER_PROC" ]
    then
      ORA_LISTENER="up"
      if [ "$MY_USER_ID" = "0" ]
        then
          su - $ORACLE_USER -c "lsnrctl status"
         else
          $ORACLE_HOME/bin/lsnrctl status
        fi
     else
      COLOR="red"
      ORA_LISTENER=""
      if [ "$MY_USER_ID" = "0" ]
        then
          su - $ORACLE_USER -c "lsnrctl status | $GREP \"No listener\" | $UNIQ"
         else
          lsnrctl status | $GREP "No listener" | $UNIQ
        fi
    fi
  echo "</BLOCKQUOTE>"

  #####
  #####  Third we will check what users are currently logged into the database
  #####
  #####  Output to be returned will be the list of users, and where they are
  #####    logged in from.  
  #####
  #####  Notes : handle multiple dbs on the same server have not been tested
  #####          but should work.
  #####
  echo "<P><DIV ALIGN=\"CENTER\"><HR>" 
  echo "<B>============== Oracle Users ==============</B>"
  echo "<HR></DIV>" 
  echo "<BLOCKQUOTE>" 
  if [ -z "$ORA_PROCS" -o -z "$ORA_LISTENER" ]
    then
      /bin/false
      echo "No Oracle users currently logged on."
     else
      echo ""
      echo "Oracle users currently logged on :"
      for EACH_SID in $DB_LIST
      do 
        ORACLE_SID=`echo $EACH_SID | $AWK -F" " '{print $1}' `
        export ORACLE_SID
        echo ""
        echo "USERNAME                       OSUSER                         MACHINE                                                          TERMINAL"
        if [ "$MY_USER_ID" = "0" ]
          then
            su - $ORACLE_USER -c "$BBHOME/ext/whos_on_db.sh.v2 $ORACLE_SID" | $EGREP -v "SVRMGR|Copyright|Release|^\$" 
           else
            `$ORACLE_HOME/bin/svrmgrl << EOF > $BBTMP/$THIS_HOST.oracle.log
              connect internal
              select username, osuser, machine, terminal from v\\$session ;
              exit ;
EOF `
            $CAT $BBTMP/$THIS_HOST.oracle.log | $EGREP -v "SVRMGR|Copyright|Release|^\$"
          fi
      done
    fi

  echo "</BLOCKQUOTE>"
  export COLOR

#####
#####  End of get_status proc
#####
}

touch $BBTMP/$THIS_HOST.oracle_new
$GREP $THIS_HOST $BBHOSTS | $GREP "oracle" | 
while read line
  do
    if [ ! -z "$line" ]
      then
        echo "<BR><DIV ALIGN=\"CENTER\"><HR></DIV>" > $BBTMP/$THIS_HOST.oracle_new
        echo "Status for Oracle on $THIS_HOST " >> $BBTMP/$THIS_HOST.oracle_new

        if [ "$WHICH_NODE" = "$THIS_HOST" ]
          then
            #####
            #####  We are running on the primary node.
            #####
            get_status >> $BBTMP/$THIS_HOST.oracle_new
           else
            #####
            #####  We are running on the backup node.
            #####
            COLOR="clear"
            export COLOR
            echo "Not running on this node - see the active node." >> $BBTMP/$THIS_HOST.oracle_new
            echo "<DIR><DIR>" >> $BBTMP/$THIS_HOST.oracle_new
            set >> $BBTMP/$THIS_HOST.oracle_new
            echo "</DIR></DIR>" >> $BBTMP/$THIS_HOST.oracle_new
          fi

        $BB $BBDISP "status $THIS_HOST.oracle $COLOR `date` `$CAT $BBTMP/$THIS_HOST.oracle_new` "
      else
        COLOR="clear"
        $BB $BBDISP "status $THIS_HOST.oracle $COLOR `date` `$CAT $BBTMP/$THIS_HOST.oracle_new` "
      fi
    done

#####
#####  End of custom section added by Paul A. Luzzi
#####
  
##############################################
# end of script
##############################################