#!/usr/local/bin/perl

#################################################################################################
#                                                                                               #
#  Name:        dbPing.pl                                                                       #
#  Author:      Randy Johnson                                                                   #
#  Description: This program does a simple connect and select from a database in a repeated     #
#               repeated fassion; just like TC/IP ping utility.                                 #
#  Input Params: -i ORACLE_SID                                                                  #
#                -u dba user account to use to login to the instance.                           #
#                -p passwrd                                                                     #
#                                                                                               #
#################################################################################################
#                                                                                               #
# MODIFICATION HISTORY:                                                                         #
#                                                                                               #
# Chg Date   Chg  By               Change Description                                           #
# ---------- ---- ---------------- ------------------------------------------------------------ #
#                                                                                               #
#################################################################################################


# Import Modules/Functions
#--------------------------#
use DBI;
#use DBD::Oracle qw(:ora_session_modes);     # Modes translate from numerics to constants (SYSDBA = 2, SYSOPER = 4)
use DBD::Oracle;
use File::Basename;
use Getopt::Long;
use Data::Dumper;
use Sys::Hostname;
use POSIX;

# -------------------------------------------#
#  Main Program                              #
# -------------------------------------------#
$dbi         = 'dbi:Oracle:';

# Process command line options
# --------------------------------
$Usage  = "\nUsage: $basename [-i ORACLE_SID] [-u DBA_USER] [-e] [-k] [-h] [-l LOG_LEVEL]\n\n";
$Usage .= "Where: ORACLE_SID = Target database name\n";
$Usage .= "       -i         = ORACLE_SID\n";
$Usage .= "       -u         = Authenticate as this user, eg. OPS\$ORACLE\n";
$Usage .= "       -p         = Password\n";

GetOptions ( 'i:s'   => \$instName,
             'u:s'   => \$secAdm,
             'p:s'   => \$secPwd);

if (not $secAdm) {
   print "No user specified.\n";
   print "$Usage\n";
   exit;
}

if (not $secPwd) {
   print "No password specified.\n";
   print "$Usage\n";
   exit;
}

if (not $instName) {
   print "No ORACLE_SID specified.\n";
   print "$Usage\n";
   exit;
}

# -------------------------------------------#
#  Main Program                              #
# -------------------------------------------#
eval {
   $dbh = DBI->connect($dbi . $instName, $secAdm, $secPwd, { RaiseError => 0, PrintError => 1, AutoCommit => 0 }); 
   $pingQry  = "SELECT INSTANCE_NAME, TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM V\$INSTANCE\n";
   while (1) {
      $pingCurs = $dbh->prepare($pingQry) or warn;
      $pingCurs->execute() || warn "Try again...\n" or warn;
      while (($INSTANCE_NAME, $PING_TIME) = $pingCurs->fetchrow) {
         @INSTANCE_NAME = split(//, $INSTANCE_NAME);
         $INSTANCE_NAME = join(' ', @INSTANCE_NAME);
         printf ">  %-15s %-10s\n\n", $INSTANCE_NAME, $PING_TIME;
      }
      sleep 1;
   } # End Main Program.
   if ($dbh) { $dbh->disconnect() };
};