#  $Header: dgDatabases.pl 15-aug-2006.11:23:43 sjconnol Exp $
#
# Copyright (c) 2002, 2006, Oracle. All rights reserved.  
#
#    NAME
#      dgDatabases.pl
#
#    DESCRIPTION
#      This metric test determines metric information for a Data Guard configuration. For each database in the configuration, this test determines the Data Not Received, Data Not Applied, and Status.
#
#    OUTPUT:
#	For 9.2:
#	DB Object Name, Data Not Received (logs), Data Not Applied (logs), Status
#
#	For 10.1:
#	DB Object Name, Data Not Received (logs), Data Not Received (MB),
#	Data Not Applied (logs), Data Not Applied (MB), Status
#
#	For 10.2:
#	DB Object Name, Failover Time (secs), LAG Time (secs), Apply Rate (KB/sec)
#	Potential Data Loss (secs), Status 
#
#    NOTES
#      DBI handles automatic error checking.
#
#    MODIFIED   (MM/DD/YY)
#      sjconnol  08/15/06 - 11g support
#      ngade     05/09/06 - fix bug 5226727 
#      ngade     05/09/06 - fix bug 5203762 
#      ngade     04/11/06 - fix bug 5082046 for 9i db 
#      gallison  08/12/05 - Fix bug 4547507 
#      sjconnol  06/24/05 - Move get_dg_dbres_status to dgutil.pl
#      gallison  09/22/04 - 10.2 support 
#      gallison  06/01/04 - Fix bug 3653330 
#      gallison  05/14/04 - Support for non-broker metrics
#      gallison  03/12/04 - Fix bug 3361521 
#      gallison  10/13/03 - Fix version 
#      pbantis   06/25/03 - Handle login role
#      gallison  06/04/03 - Use stdinvars
#      pbantis   12/04/02 - Trace errors by RaiseError
#      pbantis   11/19/02 - Show size in megabytes
#      pbantis   11/12/02 - pbantis_update021101
#      pbantis   11/01/02 - 10g support
#      pbantis   09/26/02 - Target version support
#      pbantis   07/15/02 - Exit if metric not applicable
#      xxu       06/24/02 - remove /usr/local/bin/perl
#      pbantis   06/06/02 - Data Not Applied = Received - Applied.
#      pbantis   05/16/02 - Handle 10g dbs.
#      gallison  04/29/02 - gallison_dgm
#      pbantis   03/14/02 - Creation
#

use strict;
use DBI;
use DBI qw(:sql_types);

require "$ENV{EMDROOT}/sysman/admin/scripts/emd_common.pl";
require "$ENV{EMDROOT}/sysman/admin/scripts/db/dg/dgutil.pl";

# RaiseError
$SIG{__DIE__} = \&handleError;
# PrintError
$SIG{__WARN__} = \&handleWarning;

# Trace the target name.
EMD_PERL_DEBUG("Database target=$ENV{EM_TARGET_NAME}");

my $db_version = $ENV{VersionCategory};
verify_dbversion_92($db_version);

# Connect to the instance.
my %stdinArgs = get_stdinvars();
my $username = $stdinArgs{EM_TARGET_USERNAME};
my $password = $stdinArgs{EM_TARGET_PASSWORD};
my $role = $ENV{EM_TARGET_ROLE};
my $mode = 0;
if($role =~ /SYSDBA/i)
{
  $mode = 2;
}
elsif($role =~ /SYSOPER/i)
{
  $mode = 4;
}

my $address = $ENV{EM_TARGET_ADDRESS};
my $lda = DBI->connect('dbi:Oracle:', "$username@".$address, "$password", {ora_session_mode => $mode, PrintError => 0, RaiseError => 1});

# Register this test once we're connected.
register_metric_call($lda);

# Make a few more checks before continuing.
verifyPrimary($lda);
my $broker = verifyBroker($lda); 

# evaluate metric for broker or non-broker cases (non-broker 10.1 and up)
if($broker) 
{
  doEval($lda, $db_version);
}
elsif($db_version !~ /9iR2/i)
{
  getStatusNoBroker($lda, $db_version, 1);
}

$lda->disconnect;

exit 0;

# Parameters
# lda - The login identifier (Login Data Area).
# db_version - The database version 9iR2 or 10gR1.
sub doEval
{
  my ($lda, $db_version) = @_;

  # 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;
  my $potential_data_loss;
  my $current_log = 0;
  EMD_PERL_DEBUG("Process the primary...");
  foreach $site_obj (@site_list)
  {
    # Parse out the default state (to determine if PRIMARY or STANDBY).
    $site_start_pos = -1;
    $default_state = get_dg_token($site_obj, "default_state", $site_start_pos);
    if ($default_state eq "PRIMARY")
    {
      processSiteObject($lda, $db_version, $site_obj, $default_state, $potential_data_loss, $current_log);
      last; # break out of the for loop
    }
    else
    {
      next;
    }
  } # foreach site

  # Process the standbys.
  EMD_PERL_DEBUG("Process the standbys...");
  foreach $site_obj (@site_list)
  {
    # Parse out the default state (to determine if PRIMARY or STANDBY).
    $site_start_pos = -1;
    $default_state = get_dg_token($site_obj, "default_state", $site_start_pos);
    if ($default_state eq "STANDBY")
    {
      processSiteObject($lda, $db_version, $site_obj, $default_state, $potential_data_loss, $current_log);
    }
    else
    {
      next;
    }
  } # foreach site
}

# Process a site object.
# Parameters
# lda - The login identifier (Login Data Area).
# db_version - The database version 9iR2 or 10g.
# site_obj - The site content object.
# default_state - The default state of the site object.
# potential_data_loss (10g) - IN/OUT- The PotentialDataLoss property value.
# current_log (9i) - IN/OUT - The primary current log.
# Return result list.
sub processSiteObject
{
  my ($lda, $db_version, $site_obj, $default_state, $potential_data_loss, $current_log) = @_;
  my $site_start_pos = -1;

  # 9i - Parse out the site name.
  my $site_name;
  if ($db_version eq "9iR2")
  {
    $site_name = get_dg_token($site_obj, "name", $site_start_pos);
    EMD_PERL_DEBUG("site_name=$site_name");
  }

  # Parse out the site id.
  my $site_id = get_dg_token($site_obj, "site_id", $site_start_pos);
  EMD_PERL_DEBUG("site_id=$site_id");

  # Retrieve the site DBRESOURCE_ID property.
  my $dbres_id = get_dg_property($lda, $site_id, "DBRESOURCE_ID");
  EMD_PERL_DEBUG("dbres_id=$dbres_id");

  # 10g - Retrieve the db resource DBDISPLAYNAME property.
  my $dbres_display_name;
  if (isDB10gOrHigher($db_version))
  {
    # Use NLS version of getdbdisplayname
    $dbres_display_name = get_dbdisplayname($lda, $dbres_id);
    if ($dbres_display_name eq "")
    {
      $dbres_display_name = get_dg_token($site_obj, "name", $site_start_pos);
    }
    EMD_PERL_DEBUG("dbres_display_name=$dbres_display_name");
  }

  # Retrieve the db resource ENABLED property.
  my $dbres_enabled = get_dg_property($lda, $dbres_id, "ENABLED");
  EMD_PERL_DEBUG("dbres_enabled=$dbres_enabled");
  # The db resource must be enabled.
  if (!($dbres_enabled =~ /YES/i))
  {
    EMD_PERL_DEBUG("The db resource must be enabled.");
    return;
  }
  # Retrieve the db resource INTENDED_STATE property.
  my $dbres_state = get_dg_property($lda, $dbres_id, "INTENDED_STATE");
  EMD_PERL_DEBUG("dbres_state=$dbres_state");
  # The db resource intended state must not be offline.
  if (($dbres_state =~ /OFFLINE/i))
  {
    EMD_PERL_DEBUG("The db resource must not be offline.");
    return;
  }

  # Retrieve the db resource status.
  my $dbres_status = "";
  my $dbres_status_text = "";
  my $ignore_error = "ORA-16820";
  get_dg_dbres_status($lda, $dbres_id, $dbres_status, $dbres_status_text, $ignore_error);

  my $dnr_log = "";
  my $dnr_size = "";
  my $dna_log = "";
  my $dna_size = "";
  # If this is the (10gR1 or 9iR2) primary db resource...
  if ($default_state eq "PRIMARY")
  {
    if ($db_version =~ /^10gR1/i)
    {
      # potential_data_loss
      get10gPrimaryInfo($lda, $dbres_id, $_[4]);
    }
    if ($db_version =~ /^9iR2/i)
    {
      # current_log
      get9iPrimaryInfo($lda, $dbres_id, $_[5]);
    }
  }
  # If this is a standby db resource...
  else
  {
    if ($db_version =~ /^10gR1/i)
    {
      get10gStandbyInfo($lda, $site_id, $dbres_id, $potential_data_loss, $dnr_log, $dnr_size, $dna_log, $dna_size);
    }
    if ($db_version =~ /^9iR2/i)
    {
      get9iStandbyInfo($lda, $dbres_id, $current_log, $dnr_log, $dna_log);
    }
  }

  # Send the results back.
  my $result;
  if (isDB102OrHigher($db_version))
  {
    $result = "em_result=$dbres_display_name|$dbres_status $dbres_status_text\n";
  }
  elsif ($db_version =~ /^10gR1/i)
  {
    $result = "em_result=$dbres_display_name|$dnr_log|$dnr_size|$dna_log|$dna_size|$dbres_status $dbres_status_text\n"; 
  }
  elsif ($db_version =~ /^9iR2/i)
  { 
    $result = "em_result=$site_name|$dnr_log|$dna_log|$dbres_status $dbres_status_text\n";
  }
  print $result;
  EMD_PERL_DEBUG("$result");

}

# 10g - Retrieve the PotentialDataLoss property value.
# Parameters
# lda - The login identifier (Login Data Area).
# dbres_id - The db resource id.
# potential_data_loss - OUT- The PotentialDataLoss property value.
sub get10gPrimaryInfo
{
  my ($lda, $dbres_id, $potential_data_loss) = @_;

  # Retrieve the db resource PotentialDataLoss property.
  # potential_data_loss
  $_[2] = get_dg_property($lda, $dbres_id, "PotentialDataLoss");
  EMD_PERL_DEBUG("potential_data_loss=$_[2]");
}

# 10g - Determine the dnr (logs), dnr (MB), dna (logs), and dna (MB).
# Parameters
# lda - The login identifier (Login Data Area).
# site_id - The site id.
# dbres_id - The db resource id.
# potential_data_loss - The PotentialDataLoss property value.
# dnr_log - OUT - Data not received (logs).
# dnr_size - OUT - Data not received (MB).
# dna_log - OUT - Data not applied (logs).
# dna_size - OUT - Data not applied (MB).
sub get10gStandbyInfo
{
  my ($lda, $site_id, $dbres_id, $potential_data_loss, $dnr_log, $dnr_size, $dna_log, $dna_size) = @_;

  # Calculate the data not received.
  # dnr_log, dnr_size
  getDataNotReceived($site_id, $potential_data_loss, $_[4], $_[5]);

  # Calculate the data not applied.
  # Retrieve the db resource RedoNotApplied property.
  # Format: "#logs,KBs"
  my $redo_not_applied = get_dg_property($lda, $dbres_id, "RedoNotApplied");
  EMD_PERL_DEBUG("redo_not_applied=$redo_not_applied");

  # Make sure the broker did not return any errors (ORACLE not available...)
  if (($redo_not_applied =~ /ERROR/i))
  {
    return;
  }
  
  my @sby_logs = split(/,/, $redo_not_applied);

  # Data Not Applied (logs)
  # dnr_log
  $_[6] = $sby_logs[0];
  EMD_PERL_DEBUG("dna (logs)=$_[6]");

  # Data Not Applied (MB)
  # dnr_size
  $_[7] = $sby_logs[1]/1024;
  EMD_PERL_DEBUG("dna (MB)=$_[7]");
}

# 10g - Parse out the dnr (logs) and dnr (MB) from the PotentialDataLoss property value.
# Parameters
# site_obj - The site content object.
# potential_data_loss - The PotentialDataLoss property value.
# dnr_log - OUT - Data not received (logs).
# dnr_size - OUT - Data not received (MB).
sub getDataNotReceived
{
  my ($site_id, $potential_data_loss, $dnr_log, $dnr_size) = @_;

  # Format: "<standby1>=#logs,KBs,<standby2>=#logs,KBs,..."
  my @token_list = split(/,/, $potential_data_loss);
  while (my $token = shift(@token_list))
  {
    # <standby1>=#logs
    my @sub_list = split(/=/, $token);
    my $standby_id = shift(@sub_list);
    if ($standby_id == $site_id)
    {
      # Data Not Received (logs)
      # dnr_log
      $_[2] = shift(@sub_list);
      EMD_PERL_DEBUG("dnr (logs)=$_[2]");

      # Data Not Received (MB)
      # dnr_size
      $_[3] = shift(@token_list)/1024;
      EMD_PERL_DEBUG("dnr (MB)=$_[3]");
    }
    else
    {
      # Skip over the KBs for non-matching standby.
      shift(@token_list);
    }
  }
}

# 9i - Retrieve the current log number from the PrimaryLogSeqNumbers property.
# Parameters
# lda - The login identifier (Login Data Area).
# dbres_id - The db resource id.
# current_log - OUT- The primary current log number.
sub get9iPrimaryInfo
{
  my ($lda, $dbres_id, $current_log) = @_;

  # Retrieve the db resource PrimaryLogSeqNumbers property.
  # Format: "current log#,last locally archived log#,standby1=last received log#,standby2=last received log#,..."
  my $primary_log_seq_numbers = get_dg_property($lda, $dbres_id, "PrimaryLogSeqNumbers");
  EMD_PERL_DEBUG("primary_log_seq_numbers=$primary_log_seq_numbers");

  my @primary_logs = split(/,/, $primary_log_seq_numbers);
  # current_log
  $_[2] = $primary_logs[0];
}

# 9i - Determine the dnr (logs), and dna (logs).
# Parameters
# lda - The login identifier (Login Data Area).
# dbres_id - The db resource id.
# curent_log - The primary current log number.
# dnr_log - OUT - Data not received (logs).
# dna_log - OUT - Data not applied (logs).
sub get9iStandbyInfo
{
  my ($lda, $dbres_id, $current_log, $dnr_log, $dna_log) = @_;

  # Retrieve the db resource SbyLogSeqNumbers property.
  # Format: "last received log#,last applied log#"
  my $sby_log_seq_numbers = get_dg_property($lda, $dbres_id, "SbyLogSeqNumbers");
  EMD_PERL_DEBUG("sby_log_seq_numbers=$sby_log_seq_numbers");

  my @sby_logs = split(/,/, $sby_log_seq_numbers);

  # Data Not Received (logs)
  # dnr_log = current - received
  $_[3] = $current_log - $sby_logs[0];
  EMD_PERL_DEBUG("dnr (logs)=$_[3]");

  # Data Not Applied (logs)
  # dna_log = received - applied
  $_[4] = $sby_logs[0] - $sby_logs[1];
  EMD_PERL_DEBUG("dna (logs)=$_[4]");
}

sub get_dbdisplayname
{
    my ($lda, $object_id) = @_;     
    my($indoc);
    my($outdoc);
    my($pos);    
    $indoc = "<DO_MONITOR><PROPERTY name=\"dbdisplayname\" object_id=\"$object_id\"/></DO_MONITOR>";
    $outdoc = get_dg_document($lda, $indoc);
    $pos = -1;
    my $retstr;
    ## First one is value_type=
    $retstr = get_dg_token($outdoc, "value", $pos);
    ## Second one is value=
    $retstr = get_dg_token($outdoc, "value", $pos);
    debug("dgcreate.get_dbdisplayname: $retstr");
    return $retstr;
}