#!/usr/local/bin/perl
# 
# $Header: emdb/sysman/admin/scripts/db/dg/dgCollector.pl /st_emdbsa_11.2/2 2009/02/24 09:50:38 ngade Exp $
#
# dgCollector.pl
# 
# Copyright (c) 2008, 2009, Oracle and/or its affiliates.All rights reserved. 
#
#    NAME
#      dgCollector.pl - <one-line expansion of the name>
#
#    DESCRIPTION
#      <short description of component this file declares/defines>
#
#    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
#      sjconnol   08/15/06 - Creation
#    gallison    12/10/08 - Fix 11.2
#    gallison    10/24/08 - Get RTQ, non-broker primary status
#    gallison    09/10/08 - Do not collect perf metrics
#    rimmidi     06/26/08 - Code slap from 11GC to 10.2.0.5GC
#    rimmidi     06/26/08 - 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}");

# It should work for only 9.2 and higher
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});

my $dbRole = getDBRole($lda);
my $dbName = getNameForVersion();
my $dbID = get_db_id($lda);
my $usingBroker = "NO";

## The same table rows are updated separately by this metric run on both the prmy
## and stby. When run on a primary, rows for all databases in the config are
## returned, including status data but without the metric column data. 
## When run on a stby, only the metric column data is returned. 

if ($dbRole =~ /STANDBY/i){
  ## Standby collection works for broker and non-broker.
  EMD_PERL_DEBUG("Running on a standby");
  
  ## Don't think this is needed from the stby
  ##my $prmy_db_name = getPrimaryDBName($lda, $db_version);  
  
  ## Skip retrieving the performance metrics since this can be very slow on a rac standby.
  ## We can get them from the dataguard_sperf instead
  
  #my($fot,$lag,$pdl,$blocks) = getMetrics102($lda, $db_version);
  my $lag = "";
  my $pdl = "";
  my $rtq = "";
  # Get the Real-time Query (RTQ) value for 11.1+ physical standbys
  if (isDB11gOrHigher($db_version) && ($dbRole =~ /PHYSICAL STANDBY/i)){
      $rtq = getRTQ($lda);
  }
  
  ## Fill in the role from the stby collection since
  ## it cannot be determined on the prmy.
  ## Determine db ID of primary.
  my($prmy_db_id) = get_prmy_db_id($lda, $dbRole);

  # Try to get the primary status. We need to override the primary status if it is down since it would
  # not be collecting/updating any status at all. 
  my $status = "";
  my $status_text = "";  
  my $dbres_id = 0; 
  get_dg_dbres_status($lda, $dbres_id, $status, $status_text);
  
  my $result;
  ## Allow the list of primary down status to go through. 
  ## Error ORA-16625: cannot reach the database
  if($status_text =~ /16625/){  
    $result = "em_result=S|S|||${dbName}|${dbID}||${prmy_db_id}|${dbRole}|${rtq}|||${status} ${status_text}|${lag}|${pdl}\n";
  }else {
    $result = "em_result=S|S|||${dbName}|${dbID}||${prmy_db_id}|${dbRole}|${rtq}||||${lag}|${pdl}\n";
  }
    

  EMD_PERL_DEBUG("$result");
  print $result;
}
else{
  EMD_PERL_DEBUG("Running on a primary");
  my $prot_mode = get_prot_mode($lda);
  my ($fsfo_mode, $fsfo_target);
  if (isDB102OrHigher($db_version)){ 
    ($fsfo_mode, $fsfo_target) = get_fsfo_status($lda);
    if($fsfo_mode !~ /DISABLED/i){
      $fsfo_mode = "${fsfo_mode} target=${fsfo_target}";
    }
  }

  ## Collect all config data for broker and non-broker
  if(verifyBroker($lda)){
    $usingBroker = "YES";
    EMD_PERL_DEBUG("Running on a broker config");
    ## Status for all databases is collected on the primary
    getAllStatus($prot_mode, $fsfo_mode);
  }
  else{
    EMD_PERL_DEBUG("Running on a non-broker config");
    my $stby_list;

    ## 9.2 non-broker case is not support.
    if ($db_version eq "9iR2"){ 
      my $result = "em_result=P|S|NO|YES|${dbName}||${dbName}|${dbID}|||||||\n";
      EMD_PERL_DEBUG("$result");
      print $result;
    }
    else
    {
      ## This just gets the status for the standbys
      EMD_PERL_DEBUG("getting non-broker status...");
      my $ret = getStatusNoBroker($lda, $db_version, 0);
      my @status_nb = split(/\n/, $ret);
      my($line);
      my $pstatus = "";
      foreach $line(@status_nb){
        my($name, $status) = split(/\|+/, $line);
        if($status =~ /^Normal/i){
          $status = "Normal";
        }
        $pstatus = $status;
        ## Don't put role in here; that is filled in by stby collection (above).
        ## (Note: The table loader relies on not have the role filled in for
        ##  standby rows uploaded from the primary collection.)
        ##  Use primary db ID for prmy_db_id column.
        my $result = "em_result=P|S|${usingBroker}|YES|${name}||${dbName}|${dbID}|||||${status}||\n";
        EMD_PERL_DEBUG("$result");
        print $result;

        ## Collect the standbys in a list for use in prmy result below
        if(!$stby_list){
          $stby_list = "${name}";
        }
        else{
          $stby_list = "${stby_list},${name}";
        }
      }

      ## Print one row for the primary (w/o status)
      my $result = "em_result=P|P|${usingBroker}||${dbName}|${dbID}|||${dbRole}|${stby_list}|${prot_mode}|${fsfo_mode}|${pstatus}||\n";
      
      EMD_PERL_DEBUG("$result");
      print $result;
      }
  }
}

$lda->disconnect;

exit 0;

## Get the status for all databases in the DG config
sub getAllStatus
{
  my($prot_mode, $fsfo_mode) = @_;
  
  ## Use this as the primary db name for each stby below.
  my $prmy_db_name = $dbName;
  my $prmy_db_id = $dbID;

  # 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 $stby_list;

  # 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")
    {
      ## For 10g+ stby, use the broker site name for db_unique_name
      ## (For 9.2, use <host>_<sid>)
      my($db_name) = get_dg_token($site_obj, "name", $site_start_pos);
      if ($db_version eq "9iR2"){
	$site_start_pos = -1;
        my($host_t) = get_dg_token($site_obj, "host", $site_start_pos);        
	$site_start_pos = -1;
        my($sid_t) = get_dg_token($site_obj, "sid", $site_start_pos);        
        $db_name = "${host_t}_${sid_t}";
      }
      my ($dbres_status, $dbres_status_text) = processSiteObject($lda, $db_version, $site_obj);
      chomp($dbres_status_text);
      ## Lag, PDL columns are collected separately on stby.
      ## (Note: The table loader relies on not have the role filled in for
      ##  standby rows uploaded from the primary collection.)
      my $result = "em_result=P|S|${usingBroker}|YES|${db_name}||${prmy_db_name}|${prmy_db_id}|||||${dbres_status} ${dbres_status_text}||\n";
      EMD_PERL_DEBUG("$result");
      print $result;

      ## Collect the standbys in a list for use in prmy result below
      if(!$stby_list){
        $stby_list = "${db_name}";
      }
      else{
        $stby_list = "${stby_list},${db_name}";
      }
    }
  } # foreach site

  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")
    {
      my ($dbres_status, $dbres_status_text) = processSiteObject($lda, $db_version, $site_obj);
      chomp($dbres_status_text);
      ## Lag & PDL columns are blank (they are collected separately on stby)
      my $result = "em_result=P|P|${usingBroker}||${dbName}|${dbID}|||${dbRole}|${stby_list}|${prot_mode}|${fsfo_mode}|${dbres_status} ${dbres_status_text}||\n";
      EMD_PERL_DEBUG("$result");
      print $result;
      last; # break out of the for loop
    }
  } # foreach site
}

# Process a site object.
sub processSiteObject
{
  my ($lda, $db_version, $site_obj) = @_;
  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");

  # 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 role property.
  #my $db_role = "";
  #my $broker_role = get_dg_token($site_obj, "role", $site_start_pos);
  #EMD_PERL_DEBUG("site_obj = $site_obj");
  #EMD_PERL_DEBUG("broker_role=$broker_role");
  #if (($broker_role =~ /PHYSICAL/i)){
  #  $db_role = "PHYSICAL STANDBY";
  #}
  #elsif (($broker_role =~ /LOGICAL/i)){
  #  $db_role = "LOGICAL STANDBY";
  #}
  #elsif (($broker_role =~ /LOGICAL/i)){
  #  $db_role = "LOGICAL STANDBY";
  #}

  # Retrieve the db resource status.
  my $status = "";
  my $status_text = "";
  get_dg_dbres_status($lda, $dbres_id, $status, $status_text);

  ## Don't need status_text if Normal
  if($status =~ /Normal/i){
    $status_text = "";
  }

  #EMD_PERL_DEBUG("$status $status_text");

  return($status, $status_text);
}

sub getNameForVersion
{
  if (!($db_version eq "9iR2"))
  {
    return get_db_unique_name($lda);
  }

  ## For 9.2, substitute <host>_<sid> for db_unique_name
  my $sql = "select instance_name, host_name from v\$instance";
  my $dbcur = $lda->prepare($sql);
  $dbcur->execute;

  my @row = $dbcur->fetchrow_array();
  my $sid = $row[0];
  my $host = $row[1];
  return ("${host}_${sid}");
}

sub getRTQ
{
  my ($lda) = @_;

  my $sql = "select distinct('YES') RTQ from gv\$database d, gv\$managed_standby m where d.database_role='PHYSICAL STANDBY' and d.open_mode like 'READ ONLY%' and m.process like 'MRP%' and d.inst_id = m.thread#";
  my $dbcur = $lda->prepare($sql);
  $dbcur->execute;

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