#!/usr/local/bin/perl
# 
# dgutil.pl
# 
# Copyright (c) 2002, 2009, Oracle and/or its affiliates.All rights reserved. 
#
#    NAME
#      dgutil.pl
#
#    DESCRIPTION
#      Data Guard utility routines. Used only for 10.1 DG remote ops/jobs. 
#      Used for 10.1 OMS -> 10.2 Agent compatibility.
#
#    NOTES
#      <other useful comments, qualifications, etc.>
#
#    MODIFIED   (MM/DD/YY)
#    ngade       01/30/09 - Code slap 10.2.0.5.0 -> 11.2 round 2
#    ngade       08/03/08 - Code Slap 10.2.0.5GC -> 11.2SA
#    gallison    12/10/08 - Fix 11.2
#    sjconnol    11/20/08 - Fix getPrimarySite for pre-11.1 targets
#    sjconnol    09/18/08 - Don't return 0 for lag/pdl/fot if there is no data
#    gallison    11/12/08 - Add 11.2 support for getMetrics102
#    rimmidi     05/04/08 - Code slap from 11GC to 10.2.0.5
#    kganapat    11/28/07 - XbranchMerge kganapat_filter_ora_errs from main
#    ngade       05/25/07 - fix bug 5951219
#    ngade       11/01/07 - fix bug 6524798
#    kganapat    09/17/07 - Call filterOraError subroutine before die
#    sjconnol    07/31/07 - Add get_db_id
#    sjconnol    04/12/07 - Change get_fsfo_status
#    sjconnol    09/12/06 - Fix isDB102OrHigher
#    sjconnol    08/15/06 - Add new routines for 11g
#    ngade       07/05/06 - fix bug 5374868 
#    ngade       05/18/06 - fix bug 5226727 
#    ngade       05/09/06 - fix bug 5203762 
#    sjconnol    06/24/05 - Move get_dg_dbres_status here from dgDatabases.pl
#    sjconnol    06/17/05 - Bug 4439779: replace content from dgutil_core.pl
#    sjconnol    06/02/05 - Bug 4407481: Move content to dgutil_core.pl
#    sjconnol    05/12/05 - Bug 4366174: Put back executeSQLPlus methods
#    sjconnol    10/12/04 - Remove startObserver
#    pbantis     05/17/04 - Remove executeSQLPlus methods. 
#    gallison    05/14/04 - Support for non-broker metrics
#    sjconnol    05/07/04 - Use observer logfile
#    sjconnol    04/22/04 - Add startObserver
#    sjconnol    03/16/04 - Remove getLDA
#    sjconnol    11/18/03 - Return val from executeSQLPlus
#    sjconnol    11/10/03 - Bug 3193231
#    gallison    10/13/03 - Fix 10i to 10g 
#    gallison    06/04/03 - Add dgError sub
#    sjconnol    02/26/03 - Add SIGCHLD handler; redo SQPLUS error handling
#    sjconnol    02/10/03 - Null-out username & password if not passed-in
#    pbantis     01/31/03 - Change size of buffer in get_dg_property().
#    sjconnol    01/09/03 - Add getLDA, waitForDG
#    sjconnol    12/18/02 - Make use of connect desc and db creds optional
#    pbantis     12/04/02 - Add handleError/Warning subroutines
#    pbantis     11/20/02 - Add flush
#    sjconnol    11/16/02 - misc changes
#    pbantis     11/12/02 - pbantis_update021101
#    pbantis     11/11/02 - Added more methods.
#    sjconnol    10/29/02 - Change SQLPLUS failure error message
#    sjconnol    10/17/02 - Add additional routines
#    sjconnol    08/01/02 - Creation
# 
use File::Spec;
use DBI;
use DBI qw(:sql_types);
use POSIX "sys_wait_h";
require "emd_common.pl";
require "flush.pl";
require "$ENV{EMDROOT}/sysman/admin/scripts/db/db_common.pl";
use vars qw/ $TEST_101 /;

## Only include dbstate if &executeSQLPlus is not defined, which
##  means this is likely a 10.1.0.X OMS -> 10.2 Agent
##  (executeSQLPlus is pulled in from the OMS side when running from 
##   10.2 OMS, but
##   must be obtained from from the Agent side when running from 10.1.0.X OMS.)
## 10.2 OMS -> 10.2 Agent: executeSQLPlus pulled from ha_dbstate on OMS side
## 10.2 OMS -> 10.1.0.X Agent: same
## 10.1.0.X OMS -> 10.2 Agent: executeSQLPlus pulled from dbstate.pl on Agent side
## 10.1.0.X OMS -> 10.1.0.X Agent: executeSQLPlus pulled from dgutil.pl on Agent side
if($TEST_101 && !defined(&executeSQLPlus)){
  debug("dgutil: including dbstate.pl");  
  require "$ENV{EMDROOT}/sysman/admin/scripts/db/dbstate.pl";
}
else{
  debug("dgutil: not including dbstate.pl");  
}

## Handle DBI sql errors
sub handleError{
  dgError("dgutil.handleError()");
  if(defined($DBI::errstr)){
    dgError("SQL Error: $DBI::errstr");
    die(filterOraError("SQL Error: $DBI::errstr\n", $DBI::err));
  }
  else{
    dgError("$_[0]");
    die("$_[0]\n");
  }
}

## Handle DBI sql warnings
sub handleWarning{
  debug("dgutil.handleWarning()");
  if(defined($DBI::errstr)){
    debug("SQL Warning: $DBI::errstr");
  }
  else{
    debug("$_[0]");
  }
}

# Execute a Broker command.
# Parameters
# lda - The login identifier (Login Data Area).
# input_document - The Broker XML input document.
# Returns the Broker XML output document.
sub get_dg_document
{
  my ($lda, $input_document) = @_;

  my $do_control_raw = q{
    begin
      sys.dbms_drs.do_control_raw(:indoc, :outdoc, :rid, :piece, :context);
    end;
  };

  my $request_id = 0;
  my $output_document = "";
  my $request_id = "";
  my $piece_id = 1;
  my $context = "";

  my $dbcur = $lda->prepare($do_control_raw);

  $dbcur->bind_param(":indoc", $input_document, SQL_BINARY);
  $dbcur->bind_param_inout(":outdoc", \$output_document, 4096, SQL_BINARY);
  $dbcur->bind_param_inout(":rid", \$request_id, 16);
  $dbcur->bind_param(":piece", $piece_id);
  $dbcur->bind_param(":context", $context);

  $dbcur->execute;

  return $output_document;
}

# Retrieves a Data Guard property value.
# Parameters
# lda - The login identifier (Login Data Area).
# object_id - The id of the object.
# property_name - The property name.
# Returns the property value.
sub get_dg_property
{
  my ($lda, $object_id, $property_name) = @_;

  my $get_property_obj = q{
    begin
      :prop_value := sys.dbms_drs.get_property_obj(:id, :prop_name);
    end;
  };

  my $property_value = "";

  my $dbcur = $lda->prepare($get_property_obj);

  $dbcur->bind_param(":id", $object_id);
  $dbcur->bind_param(":prop_name", $property_name);
  $dbcur->bind_param_inout(":prop_value", \$property_value, 1024);

  $dbcur->execute;

  return $property_value;
}

# Given a name, get it's value from a Data Guard XML output document.
# Parameters
# document - The Data Guard output document.
# token_name - The token name.
# start_pos - IN/OUT - The starting search position.
# Returns the token value.
sub get_dg_token
{
  my ($document, $token_name, $start_pos) = @_;

  my $token_value = "";
  if (($start_pos = index($document, $token_name, $start_pos)) > -1)
  {
    # Skip over token_name + '="'
    $start_pos = $start_pos + length($token_name) + 2;

    my $end_pos = -1;
    my $length = 0;
    if (($end_pos = index($document, "\"", $start_pos)) > -1)
    {
      $length = $end_pos - $start_pos;
      $token_value = substr($document, $start_pos, $length);
      $start_pos = $end_pos + 1;
    }
  }
  $_[2] = $start_pos;
  return $token_value;
}

# Map the Data Guard Broker status to a status used by the UI.
# Parameters
# status - IN/OUT - The status of the object.
# Returns the token value.
sub map_dg_status
{
  my ($status) = @_;

  if ($status =~ /SUCCESS/i)
  {
    $_[0] = "Normal";
  }
  elsif ($status =~ /WARNING/i)
  {
    $_[0] = "Warning";
  }
  elsif ($status =~ /FAILURE/i)
  {
    $_[0] = "Error";
  }

}

# Get a database initialization parameter.
# Parameters
# lda - The login identifier (Login Data Area).
# param_name - The parameter name.
# Returns the parameter value.
sub get_init_param
{
  my ($lda, $param_name) = @_;

  my $sql = "select value from v\$parameter where name='$param_name'";
  my $dbcur = $lda->prepare($sql);
  $dbcur->execute;

  my @row = $dbcur->fetchrow_array();
  my $param_value = $row[0];
  return $param_value;
}

# Verify the database role. If not primary, exit.
sub verify_role
{
  my ($db_role) = @_;  

  EMD_PERL_DEBUG("Database role=$db_role");
  if (!($db_role =~ /Primary/i))
  {
    EMD_PERL_INFO("In order to run a Data Guard metric, the database must be in the primary role with at least one standby.");
    exit 0;
  }
}

# Get the database version from v$version.
# Parameters
# lda - The login identifier (Login Data Area).
# Returns the db version such as "92" for 9iR2, "100" for 10iR1.
# Obsolete method (for now - 10/01/02).
sub get_dbversion
{
  my ($lda) = @_;

  # Get the database banner.
  my $sql = "select banner from v\$version";
  my $dbcur = $lda->prepare($sql);
  $dbcur->execute;

  my @row = $dbcur->fetchrow_array();
  my $db_version_string = $row[0];
  #EMD_PERL_DEBUG("Database version string=$db_version_string");

  # Parse out the version number.
  my(@db_version_tokens, @text_tokens, $major_version);
  @db_version_tokens = split(/\./, $db_version_string);
  @text_tokens = split(/\ /, $db_version_tokens[0]);
  $major_version = pop(@text_tokens);

  my $db_version = $major_version . $db_version_tokens[1];
  return $db_version;
}

# Verify that the database version is 9iR2 or higher
# If it's not the correct version, then exit the program.
# Parameters
# db_version - The database version (9iR2, 10gR1, etc.).
sub verify_dbversion_92
{
  my ($db_version) = @_;  

  EMD_PERL_DEBUG("Database version=$db_version");
  if (!isDB92OrHigher($db_version))
  {
    EMD_PERL_INFO("In order to run this Data Guard metric, the database version must be 9.2 or higher.");
    exit 0;
  }
}

# Verify that the database version is 10gR1 or higher
# If it's not the correct version, then exit the program.
# Parameters
# db_version - The database version (9iR2, 10gR1, etc.).
sub verify_dbversion_10g
{
  my ($db_version) = @_;  

  EMD_PERL_DEBUG("Database version=$db_version");
  if (!isDB10gOrHigher($db_version))
  {
    EMD_PERL_INFO("In order to run this Data Guard metric, the database version must be 10.1 or higher.");
    exit 0;
  }
}

# Verify that the database version is 10.2 or higher
# If it's not the correct version, then exit the program.
# Parameters
# db_version - The database version (9iR2, 10gR1, etc.).
sub verify_dbversion_102
{
  my ($db_version) = @_;  

  EMD_PERL_DEBUG("Database version=$db_version");
  if (!isDB102OrHigher($db_version))
  {
    EMD_PERL_INFO("In order to run this Data Guard metric, the database version must be 10.2 or higher.");
    exit 0;
  }
}

# Return the initialization parameter 'dg_broker_start'.
# Caller now decides how to handle it.
# Return Parameter
#  TRUE/FALSE
sub verify_broker
{
  my ($lda) = @_;
  my $dg_broker_start = get_init_param($lda, "dg_broker_start");
  EMD_PERL_DEBUG("dg_broker_start=$dg_broker_start");
  return $dg_broker_start;
}

# Verify that the Data Guard Configuration is enabled.
# If it's not, then exit the program.
# Parameters
# lda - The login identifier (Login Data Area).
sub verify_config_enabled
{
  my ($lda) = @_;

  my $drc_enabled = get_dg_property($lda, "0", "ENABLED");
  EMD_PERL_DEBUG("drc_enabled=$drc_enabled");
  # The configuration must be enabled.
  if ($drc_enabled =~ /YES/i)
  {
    EMD_PERL_DEBUG("broker config enabled");
    return 1;
  }
  else
  {
    EMD_PERL_DEBUG("broker config disabled");
    return 0;
  }
}

# Verify broker is running and config is enabled
# Parameters
# lda - The login identifier (Login Data Area).
sub verifyBroker
{
  my ($lda,$database_role) = @_;
  
  my $broker = verify_broker($lda);

  # The configuration must be enabled (if using the broker)
  if ($broker =~ /TRUE/i)
  {
    return(verify_config_enabled($lda));
  }

  # broker not running
  return 0;
}

sub waitForDG{
  my($lda) = $_[0];
  debug("dgutil.waitForDG: *** START ***");
  my($WAIT_FOR_DATAGUARD_MASK) = 1 | 8 | 16 | 256 | 1024 | 2048;

  my($MAX_TRIES) = 180;
  my($sql)= "SELECT FLAGS FROM X\$RFMP";

  my($count) = 0;
						
  while ($count < $MAX_TRIES){
    my $dbcur = $lda->prepare($sql);
    $dbcur->execute;
    my @row = $dbcur->fetchrow_array();
    if(!defined(@row) || !defined($row[0])){
      debug("dgutil.waitForDG: can't determine Data Guard state");
      return 0;
    }

    my($value) = $row[0];
    if (($value & $WAIT_FOR_DATAGUARD_MASK) == 1){
      debug("dgutil.waitForDG: Waited $count seconds for Data Guard to start");
      return 1;
    }
    ## Wait 1 second between retries
    sleep(1);
    $count++;
  }

  debug("dgutil.waitForDG: Timed out after $count seconds waiting for Data Guard to start");
  return 0;
}

# Get the db resource status.
# Parameters
# lda - The login identifier (Login Data Area). 
# dbres_id - The database resource id.
# dbres_status - OUT - The database resource status.
# dbres_status_text - OUT - The database resource status text.
sub get_dg_dbres_status
{
  my ($lda, $dbres_id, $dbres_status, $dbres_status_text, $ignore_error) = @_;

  # Retrieve the db resource STATUS property.
  my $indoc = "<DO_CONTROL><DO_COMMAND type=\"GetStatus\" object_id=\"$dbres_id\"/></DO_CONTROL>";
  my $dbres_big_status = get_dg_document($lda, $indoc);
  # EMD_PERL_DEBUG("dbres_big_status=$dbres_big_status");

  my @dbres_status_tokens = split(/[><]+/, $dbres_big_status);
  # Token 0 is "", token 1 is "RESULT ", token 2 is "MESSAGE "
  $dbres_status = $dbres_status_tokens[3];
  my @temp_tokens = split(/\s+/, $dbres_status);
  $dbres_status = $temp_tokens[0];
  EMD_PERL_DEBUG("dbres_status=$dbres_status.");

  my $success_status_text = "ORA-00000: normal, successful completion";
  my $success_status = "SUCCESS";

  if ($dbres_status eq $success_status)
  {
    $dbres_status_text = $success_status_text;
  }
  else
  {
    # Parse out the error from the big status.
    my $dbres_status_token;
    my $token_count = 0;
    my $status = "@dbres_status_tokens";
    if(defined($ignore_error) && $status =~ /$ignore_error/i)
    { 
      $dbres_status_text = $success_status_text;
      $dbres_status = $success_status; 
    } 
    else
    { 
      foreach $dbres_status_token (@dbres_status_tokens)
      {
        #EMD_PERL_DEBUG("dbres_status_token=$dbres_status_token.");
        if ($dbres_status_token eq "ERROR_TEXT ")
        {
          $dbres_status_text = $dbres_status_tokens[$token_count + 1];
          last;
        }
        $token_count++;
      }
    }
  }
  EMD_PERL_DEBUG("dbres_status_text=$dbres_status_text");
  map_dg_status($dbres_status);

  # Pass the values back.
  $_[2] = $dbres_status;
  $_[3] = $dbres_status_text;
}

# Get PRIMARY DB_UNIQUE_NAME
sub getPrimaryDBName{
  my ($lda, $db_version) = @_;
  ## For 11g, get primary db name directly from v$database
  if (isDB11gOrHigher($db_version))
  {
    EMD_PERL_DEBUG("11g database: getting prmy db_unique_name from v\$database");
    return get_prmy_db_unique_name($lda);
  }
  # Pre-11g, only evaluate metric if broker is in use
  else
  {
    my $broker = verifyBroker($lda);
    if ($broker)
    {
      EMD_PERL_DEBUG("Pre-11g database: getting prmy db_unique_name from broker");
      my $site_obj = getPrimarySite($lda);
      if($site_obj){
        return getSiteValue($lda, $site_obj, "name");
      }
    }
  }
  EMD_PERL_DEBUG("Pre-11g database: cannot get prmy db_unique_name from broker");
  return "";
}

# Get PRIMARY_DB_UNIQUE_NAME from v$database
# Parameters
# lda - The login identifier (Login Data Area).
# Returns the parameter value.
sub get_prmy_db_unique_name
{
  my ($lda) = @_;

  my $sql = "select primary_db_unique_name from v\$database";
  my $dbcur = $lda->prepare($sql);
  $dbcur->execute;

  my @row = $dbcur->fetchrow_array();
  my $param_value = $row[0];
  return $param_value;
}

# Get DB_UNIQUE_NAME from v$database
# Parameters
# lda - The login identifier (Login Data Area).
# Returns the parameter value.
sub get_db_unique_name
{
  my ($lda) = @_;

  my $sql = "select db_unique_name from v\$database";
  my $dbcur = $lda->prepare($sql);
  $dbcur->execute;

  my @row = $dbcur->fetchrow_array();
  my $param_value = $row[0];
  return $param_value;
}

# Get DBID from v$database
# Parameters
# lda - The login identifier (Login Data Area).
# Returns the parameter value.
sub get_db_id
{
  my ($lda) = @_;

  my $sql = "select dbid from v\$database";
  my $dbcur = $lda->prepare($sql);
  $dbcur->execute;

  my @row = $dbcur->fetchrow_array();
  my $param_value = $row[0];
  return $param_value;
}

# Get DBID from v$database
# Parameters
# lda - The login identifier (Login Data Area).
# Returns the parameter value.
sub get_prmy_db_id
{
  my ($lda, $role) = @_;
  my($prmy_db_id);

  ## For all database types exception logical, prmy id is the same
  my $sql = "select dbid from v\$database";

  if ($role =~ /LOGICAL/i){  
    $sql = "select source_dbid from dba_logmnr_session";
  }

  EMD_PERL_DEBUG("Running $sql");
  my $dbcur = $lda->prepare($sql);
  $dbcur->execute;
  
  my @row = $dbcur->fetchrow_array();
  my $param_value = $row[0];
  return $param_value;
}
 

# Get PROTECTION_MODE from v$database
# Parameters
# lda - The login identifier (Login Data Area).
# Returns the parameter value.
sub get_prot_mode
{
  my ($lda) = @_;

  my $sql = "select protection_mode from v\$database";
  my $dbcur = $lda->prepare($sql);
  $dbcur->execute;

  my @row = $dbcur->fetchrow_array();
  my $param_value = $row[0];
  return $param_value;
}

# Get FS_FAILOVER_STATUS from v$database
# Parameters
# lda - The login identifier (Login Data Area).
# Returns the parameter value.
sub get_fsfo_status
{
  my ($lda) = @_;

  my $sql = "select fs_failover_status, fs_failover_current_target from v\$database";
  my $dbcur = $lda->prepare($sql);
  $dbcur->execute;

  my @row = $dbcur->fetchrow_array();
  my $status = $row[0];
  my $target = $row[1];
  return ($status, $target);
}

# verifyPrimary - Verify this is primary db
sub verifyPrimary
{
  my($lda) = @_;
  my $database_role = getDBRole($lda);
  if ($database_role !~ /PRIMARY/i)
  {
    EMD_PERL_INFO("This metric will only run against primary databases.");
    $lda->disconnect;
    exit 0;
  }
}

# verifyStandby - Verify this is standby db
sub verifyStandby
{
  my($lda) = @_;
  my $database_role = getDBRole($lda);
  if ($database_role !~ /STANDBY/i)
  {
    EMD_PERL_INFO("This metric will only run against standby databases.");
    $lda->disconnect;
    exit 0;
  }
}

# getDBRole - returns database role
# Parameters
# lda - The login identifier (Login Data Area).
sub getDBRole
{
  my ($lda) = @_;
  
  # Ensure that the 'DATABASE_ROLE' is PRIMARY.
  my $sql = "select DATABASE_ROLE from v\$database";
  my $dbcur = $lda->prepare($sql);
  $dbcur->execute;
  my @row = $dbcur->fetchrow_array();
  my $database_role = $row[0];
  EMD_PERL_DEBUG("database role = $database_role");  
  return $database_role;
}

# Return the primary site in a broker config
# Parameters
# lda - The login identifier (Login Data Area).
sub getPrimarySite
{
  my ($lda) = @_;

  # Retrieve the configuration object content.
  my $indoc = "<DO_CONFIGURE_DRC><GET_DRC/></DO_CONFIGURE_DRC>";
  my $drc_obj = get_dg_document($lda, $indoc);

  # Retrieve the list of sites.
  my (@site_list);
  my ($site_id, $site_obj);
  my $drc_start_pos = -1;
  my $i = 0;
  while (($site_id = get_dg_token($drc_obj, "site_id", $drc_start_pos)) ne "")
  {
    EMD_PERL_DEBUG("site_id=$site_id");
    # Retrieve the site object content.
    $indoc = "<DO_CONFIGURE_SITE><GET_SITE site_id=\"$site_id\"/></DO_CONFIGURE_SITE>";
    $site_obj = get_dg_document($lda, $indoc);
    $site_list[$i] = $site_obj;
    $i++;
  }

  # Process the primary site first.
  my $default_state;
  my $site_start_pos = -1;
  EMD_PERL_DEBUG("Finding the primary site");
  foreach $site_obj (@site_list)
  {
    # Parse out the default state (to determine if PRIMARY or STANDBY).
    $default_state = get_dg_token($site_obj, "default_state", $site_start_pos);
    if ($default_state eq "PRIMARY")
    {
      return $site_obj;
    }
    else
    {
      $site_start_pos = -1;
      next;
    }
  } # foreach site
  return undef;
}

# Extract a parameter value from a broker site object.
# Parameters
# lda - The login identifier (Login Data Area).
# site_obj - The site content object.
# param - The site param to get
# Return value of param
sub getSiteValue
{
  my ($lda, $site_obj, $param) = @_;
  my $site_start_pos = -1;

  my $retval = get_dg_token($site_obj, $param, $site_start_pos);
  EMD_PERL_DEBUG("retval=$retval");
  return $retval
}

# Get NON BROKER DG status
# lda - The login identifier (Login Data Area).
# db_version - The database version 9iR2 or 10g 
sub getStatusNoBroker
{
  my ($lda, $db_version, $doprint) = @_;
  my $sql = "select error,db_unique_name from v\$archive_dest where target='STANDBY'";
  my $dbcur = $lda->prepare($sql);
  $dbcur->execute;
  my @row;
  my $ret;
  while ( @row = $dbcur->fetchrow_array() ) {
      my $dbres_status_text = $row[0];
      my $db_unique_name = $row[1];
      my $dr_status = "Error";
      
      if ($dbres_status_text eq "")
      {
        EMD_PERL_DEBUG("No primary errors found. Returning normal");
        $dbres_status_text = "ORA-00000: normal, successful completion";
        $dr_status = "Normal";
      }

      my $result;
      if (isDB102OrHigher($db_version))
      {
        $result = "$db_unique_name|$dr_status $dbres_status_text\n";
      }
      else
      {
        $result = "$db_unique_name|||||$dr_status $dbres_status_text\n";
      }
      EMD_PERL_DEBUG("$result");
      if($doprint){
        print "em_result=$result";
      }
      else{
        $ret = $ret.$result;
      }
  }
  return $ret;
}

sub getMetrics102
{
  my ($lda, $db_version) = @_;

  if($db_version && !isDB102OrHigher($db_version)){
    EMD_PERL_DEBUG("getMetrics102: returning due to db version < 10.2");
    return;
  }

  if($db_version && isDB11gR2OrHigher($db_version)){
    return getMetrics112($lda,$db_version);
  }

  my $database_role = getDBRole($lda);

  EMD_PERL_DEBUG("Getting perf metrics for a $database_role with version $db_version");
  my $sql = "SELECT SUM(DECODE(name, 'apply finish time', value, null)) FOT,  SUM(DECODE(name, 'apply lag', value, null)) LAG, SUM(DECODE(name, 'transport lag', value, null)) PDL  from (SELECT name, extract(day from p.val) *86400 + extract(hour from p.val) *3600 + extract(minute from p.val) *60 + extract(second from p.val) value from (SELECT name,to_dsinterval(value) val from v\$dataguard_stats) p )";
  my $dbcur = $lda->prepare($sql);
  $dbcur->execute;
  my @row = $dbcur->fetchrow_array();
  my $fot = $row[0];
  my $lag = $row[1];
  my $pdl = $row[2]; 

  $sql = "SELECT * from (select SUM(DECODE(name, 'estimated startup time', value, 0)) from v\$dataguard_stats),(select value from v\$dataguard_stats where name='standby has been open')";
  $dbcur = $lda->prepare($sql);
  $dbcur->execute;
  @row = $dbcur->fetchrow_array();
  my $t1 = $row[0];
  my $bounce = $row[1];

  if ($bounce eq "Y")
  {
    $fot = $fot + $t1;
  }
  
  EMD_PERL_DEBUG("Getting apply time");
  
  my $blocks;
  if ($database_role !~ /LOGICAL STANDBY/i)
  {
    $sql = "SELECT s.value*512/1000 KB_bytes from v\$sysstat s where s.name='redo blocks read for recovery'";
  }
  else
  {
    $sql = "SELECT t.value/1000 from v\$logmnr_stats t where t.name like 'bytes of redo%'";  
  }
  EMD_PERL_DEBUG("Getting blocks");
  $dbcur = $lda->prepare($sql);
  $dbcur->execute;
  @row = $dbcur->fetchrow_array();
  $blocks = $row[0];
  
  return($fot,$lag,$pdl,$blocks);
}


sub getMetrics112
{
  my ($lda, $db_version) = @_;

  if($db_version && !isDB11gR2OrHigher($db_version)){
    EMD_PERL_DEBUG("getMetrics112: returning due to db version < 11.2");
    return;
  }

  my $database_role = getDBRole($lda);

  EMD_PERL_DEBUG("Getting perf metrics for a $database_role");

  my $sql = "SELECT SUM(DECODE(name, 'apply finish time', value, 0)) FOT,  SUM(DECODE(name, 'transport lag', value, 0)) PDL  from (SELECT name, extract(day from p.val) *86400 + extract(hour from p.val) *3600 + extract(minute from p.val) *60 + extract(second from p.val) value from (SELECT name,to_dsinterval(value) val from v\$dataguard_stats) p )";
  my $dbcur = $lda->prepare($sql);
  $dbcur->execute;
  my @row = $dbcur->fetchrow_array();
  my $fot = $row[0];
  my $pdl = $row[1]; 

  $sql = "SELECT * from (select SUM(DECODE(name, 'apply lag', value, 0)) from v\$dataguard_stats),(select SUM(DECODE(name, 'estimated startup time', value, 0)) from v\$dataguard_stats)";
  $dbcur = $lda->prepare($sql);
  $dbcur->execute;
  @row = $dbcur->fetchrow_array();
  my $lag = $row[0];
  my $t1 = $row[1];

  $sql = "select value from v\$dataguard_stats where name='standby has been open'";
  $dbcur = $lda->prepare($sql);
  $dbcur->execute;
  @row = $dbcur->fetchrow_array();
  my $bounce = $row[0];

  if ($bounce eq "Y")
  {
    $fot = $fot + $t1;
  }
  
  EMD_PERL_DEBUG("Getting apply time");
  
  my $blocks;
  if ($database_role !~ /LOGICAL STANDBY/i)
  {
    $sql = "SELECT s.value*512/1000 KB_bytes from v\$sysstat s where s.name='redo blocks read for recovery'";
  }
  else
  {
    $sql = "SELECT t.value/1000 from v\$logmnr_stats t where t.name like 'bytes of redo%'";  
  }
  EMD_PERL_DEBUG("Getting blocks");
  $dbcur = $lda->prepare($sql);
  $dbcur->execute;
  @row = $dbcur->fetchrow_array();
  $blocks = $row[0];
  
  return($fot,$lag,$pdl,$blocks);
}

sub isDB92OrHigher{
  my($dbver) = @_;
  EMD_PERL_DEBUG("isDB92OrHigher: $dbver");

  if(!defined($dbver)){
    return 0;
  }

  if(($dbver =~ /^1[0-9]/) || ($dbver =~ /^(92|9iR2)/i)){
    EMD_PERL_DEBUG("DB is 9.2 or higher");
    return 1;
  }
  return 0;
}

sub isDB10gOrHigher{
  my($dbver) = @_;
  EMD_PERL_DEBUG("isDB10gOrHigher: $dbver");

  if(!defined($dbver)){
    return 0;
  }

  if($dbver =~ /^1[0-9]/){
    EMD_PERL_DEBUG("DB is 10g or higher");
    return 1;
  }
  return 0;
}

sub isDB102OrHigher{
  my($dbver) = @_;
  EMD_PERL_DEBUG("isDB102OrHigher: $dbver");

  if(!defined($dbver)){
    return 0;
  }

  if($dbver =~ /^(10gR2|1[1-9])/){
    EMD_PERL_DEBUG("DB is 10.2 or higher");
    return 1;
  }
  return 0;
}

sub isDB11gOrHigher{
  my($dbver) = @_;
  EMD_PERL_DEBUG("isDB11gOrHigher: $dbver");
  
  if(!defined($dbver)){
    return 0;
  }

  if($dbver =~ /^1[1-9]/){
    EMD_PERL_DEBUG("DB is 11g or higher");
    return 1;
  }
  return 0;
}

sub isDB11gR2OrHigher{
  my($dbver) = @_;
  EMD_PERL_DEBUG("isDB11gR2OrHigher: $dbver");
  
  if(!defined($dbver)){
    return 0;
  }

  if($dbver =~ /^(11gR2)/){
    EMD_PERL_DEBUG("DB is 11gR2 or higher");
    return 1;
  }
  return 0;
}


sub printDebug{
  print "$_[0]\n";
	flush(STDOUT);
  debug($_[0]);
}

sub debug{
  EMD_PERL_DEBUG($_[0]);
}

sub dgError{
  EMD_PERL_ERROR($_[0]);
}

1;