#  $Header: dbresp.pl 11-jun-2003.12:08:37 ychan Exp $
#
# Copyright (c) 2001, 2003, Oracle Corporation.  All rights reserved.  
#
#    NAME
#      dbresp.pl - <one-line expansion of the name>
#
#    DESCRIPTION
#      It connects to a database, executes a user-defined SQL
#      and reports success/failure and response time.
#
#    NOTES
#      <other useful comments, qualifications, etc.>
#
#    MODIFIED   (MM/DD/YY)
#      ychan     06/11/03 - remove comment
#      ychan     05/14/03 - Support role
#      aaitghez  05/27/03 - review comments
#      aaitghez  05/19/03 - remove password from environment
#      ychan     09/18/02 - ychan_move_dbscript
#      kdas      09/03/02 - fix bug 2523069
#      swexler   06/27/02 - fix login time - bug 2370560
#      xxu       06/24/02 - remove /usr/local/bin/perl
#      jsoule    03/13/02 - add transient error column.
#      jsoule    02/15/02 - remove avgResp result and simplify.
#      jsoule    01/24/02 - return nulls for times when database is down..
#      glavash   10/10/01 - add statname query for speed
#      glavash   08/17/01 - remove hardcoded reference to OREO
#      aaitghez  08/07/01 - adding database registration call.
#      njagathe  07/27/01 - Exit with 0 on failure to connect
#      qsong     07/15/01 -
#      aaitghez  06/28/01 - removing setting ORACLE_HOME.
#      aaitghez  06/22/01 - naming convention.
#      aaitghez  06/22/01 - adding requried lib.
#      aaitghez  06/22/01 - set oracle_home at start of script.
#      glavash   06/06/01 - fix logontime
#      xxu       05/25/01 - sync with the new tcl version
#      xxu       05/21/01 - cut over from tcl
#      xxu       05/21/01 - Creation
#

use strict;
use Oraperl;
use Time::HiRes;
use DBI;
use DBI qw(:sql_types);

require "emd_common.pl";

my %stdinArgs = get_stdinvars();

my $username = $stdinArgs{"EM_TARGET_USERNAME"};
my $password = $stdinArgs{"EM_TARGET_PASSWORD"};

my $address = $ENV{EM_TARGET_ADDRESS};
my $role = $ENV{EM_TARGET_ROLE};

my $mode = 0;
if($role =~ /SYSDBA/i)
{
  $mode = 2;
}
elsif($role =~ /SYSOPER/i)
{
  $mode = 4;
}

my $start_time = Time::HiRes::time;

my $lda;
unless ( $lda = DBI->connect('dbi:Oracle:', "$username@".$address, "$password", 
                            {ora_session_mode => $mode,
                             PrintError => 0,
                             RaiseError => 0}) ) 
{ 
  if( index($ora_errstr, "01017",0)!=-1)
  {
    die "$ora_errstr\n";
  }
  else
  {
      print "em_result=|0||$ora_errstr\n";
  }
  exit 0; 
}

register_metric_call($lda);

# sysdba and > 8i database

my $state = "OPEN";
if ($mode == 2)
{
  # query the v$instance for db status
  my $sql = "SELECT status, LPAD(version, 10, '0') FROM v\$instance";  
  my $cur = &ora_open ($lda, $sql) || warn "ora_open ($lda, $sql): $ora_errno: $ora_errstr\n";
  my @fetch_row = &ora_fetch($cur);
  &ora_close($cur)  || warn "ora_close($cur): $ora_errno: $ora_errstr\n";
  $state = $fetch_row[0];  
  my $version = $fetch_row[1];  

  if ($state =~ /MOUNTED/i)
  {      
    if ($version gt "09.0.0.0.0")
    {
       # if db is mounted, query database_role from v$database
       my $sqld = "SELECT database_role FROM v\$database";  
       my $curd = &ora_open ($lda, $sqld) || warn "ora_open ($lda, $sqld): $ora_errno: $ora_errstr\n";
       my @fetch_rowd = &ora_fetch($curd);
       &ora_close($curd)  || warn "ora_close($curd): $ora_errno: $ora_errstr\n";
       my $dbrole = $fetch_rowd[0];
       if (!($dbrole =~ /STANDBY/i))
       {
         $state = "";
       }  
    }
    else
    {
       $state = "";
    }
  }   
}

my $end_time = Time::HiRes::time;

&ora_logoff($lda) || warn "ora_logoff($lda): $ora_errno: $ora_errstr\n";

# the end_time and start_time returned are in terms of seconds, 
# the logon_time should be in miliseconds
my $logon_time = ($end_time - $start_time) * 1000;

print "em_result=$logon_time|1|$state|\n";

exit 0;