#!/usr/local/bin/perl
# 
# dbstate.pl
# 
# Copyright (c) 2004, 2005, Oracle. All rights reserved.  
#
#    NAME
#      dbstate.pl
#
#    DESCRIPTION
#      Database startup and shutdown functions for both single-instance and rac 
#      databases.
#
#      *** NOTE ***: any modifications/fixes to this file must replicated to
#                    OMS-side file at: $EMDROOT/sysman/webapps/em/WEB-INF/perl/ha_dbstate_o.pl
#                    This file is used for startup/shutdown job support.
#
#    NOTES
#      <other useful comments, qualifications, etc.>
#
#    MODIFIED   (MM/DD/YY)
#    vkapur      09/23/05 - bug 4618693 
#    vkapur      07/25/05 - 4471901 - support open read only 
#    sjconnol    07/12/05 - Bug 4440066
#    kramarat    05/19/05 - Fix strict compilation issue - bug 4377718 
#    kramarat    05/05/05 - Turn on use strict and synch with ha_dbstate_o.pl 
#    vkapur      03/16/05 - bug 4162728 
#    vkapur      02/22/05 - bug 4201722: don't pass connect string to srvctl 
#    vkapur      10/25/04 - fix db_role typo 
#    vkapur      10/07/04 - fix change_db_state 
#    pbantis     09/30/04 - Handle os authentication. 
#    vkapur      09/17/04 - fix get_srvctl_status 
#    pbantis     09/01/04 - Don't use CHLD signal handler.
#    vkapur      07/01/04 - add srvctl status 
#    vkapur      06/17/04 - add changeCWDForSrvctlIfNecessary, has_ec
#    vkapur      06/02/04 - rac-ify get_db_state, change_db_state 
#    pbantis     05/18/04 - Change restart to return if shutdown failure.
#    pbantis     05/14/04 - Fix booleans. 
#    pbantis     05/10/04 - Added get_db_state(), change_db_state(), cleanup.
#    vkapur      03/31/04 - misc fixes
#    vkapur      03/03/04 - add rac startup/shutdown 
#    pbantis     02/21/04 - pbantis_rec040217 
#    pbantis     02/16/04 - Creation
# 
use strict;
use File::Spec;
use POSIX "sys_wait_h";
use Cwd;

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

use vars qw(@SQLCMDS $SQLPID $NT);

use vars qw($DBSTATE_SUCCESS_CODE $DBSTATE_ERROR_CODE $DBSTATE_RETURN_CODE $IGNORE_SQL_ERROR $IGNORE_SQL_SHUTDOWN_ERROR $IGNORE_SQLPLUS_ERROR $orgCWD);

use vars qw($TARGET_TYPE_ORACLE_DATABASE $TARGET_TYPE_RAC_DATABASE $SHUTDOWN_NORMAL $SHUTDOWN_IMMEDIATE $STARTUP_NOMOUNT $STARTUP_MOUNT $STARTUP_OPEN $DBROLE_NORMAL $DBROLE_SYSDBA $SQL_SHUTDOWN $STATE_UNKNOWN $STATE_SHUTDOWN $STATE_STARTED $STATE_MOUNTED $STATE_OPEN $SRVCTL_VERS_10 $SRVCTL_VERS_9_2 $SRVCTL_VERS_9_0 $SRVCTL_VERS_8 $srvctlVers $OPEN_MODE_READ_ONLY $RESTRICTED_MODE);
                                                                                
$DBSTATE_SUCCESS_CODE = 0;
# TBD - Should this be -1?
$DBSTATE_ERROR_CODE = 1;
$DBSTATE_RETURN_CODE = $DBSTATE_SUCCESS_CODE;

# Ignore SQL errors for all commands.
$IGNORE_SQL_ERROR = 0;
# Ignore SQL errors for the shutdown command.
$IGNORE_SQL_SHUTDOWN_ERROR = 1;

# Continue when SQLPLUS errors.
$IGNORE_SQLPLUS_ERROR = 1;

$TARGET_TYPE_ORACLE_DATABASE = "oracle_database";
$TARGET_TYPE_RAC_DATABASE = "rac_database";

$SHUTDOWN_NORMAL = "normal";
$SHUTDOWN_IMMEDIATE = "immediate";

$STARTUP_NOMOUNT = "nomount";
$STARTUP_MOUNT = "mount";
$STARTUP_OPEN = "open";

$DBROLE_NORMAL = "normal";
$DBROLE_SYSDBA = "SYSDBA";

$SQL_SHUTDOWN = "shutdown";

$STATE_UNKNOWN = "UNKNOWN";
$STATE_SHUTDOWN = "SHUTDOWN";
$STATE_STARTED = "STARTED";
$STATE_MOUNTED = "MOUNTED";
$STATE_OPEN = "OPEN";
$OPEN_MODE_READ_ONLY = "READ ONLY";
$RESTRICTED_MODE = "RESTRICT";

$SRVCTL_VERS_10 = "10";
$SRVCTL_VERS_9_2 = "9.2";
$SRVCTL_VERS_9_0 = "9.0";
$SRVCTL_VERS_8 = "8";

sub printError
{
  no strict 'subs';
  print "$_[0]\n";
  flush(STDOUT);
  EMD_PERL_ERROR($_[0]);
}

sub reaper
{
  EMD_PERL_DEBUG("dbstate.reaper: SIGCHLD received for $SQLPID");
  my $retpid = waitpid($SQLPID, WNOHANG);
                                                                                
  EMD_PERL_DEBUG("dbstate.reaper: retpid=$retpid");
  if($retpid > 0)
  {
    EMD_PERL_DEBUG("dbstate.reaper: status available for $SQLPID");
    if($? >> 8)
    {
      EMD_PERL_ERROR("dbstate.reaper: SQLPLUS proc $SQLPID failed; see job output for error messages");    
      ## There isn't likely to be anything in $! in this case;
      ##  any error is simply in the SQLPLUS output in stdout
      $DBSTATE_RETURN_CODE = $DBSTATE_ERROR_CODE;
      if($IGNORE_SQLPLUS_ERROR)
      {
        EMD_PERL_DEBUG("dbstate.reaper: Continuing after SQLPLUS error");
      }
      else
      {
        exit($DBSTATE_RETURN_CODE);
      }
    }
  }
}

#
# Retrieve the database status.
# (select status from v$instance)
#
# targetType The target type.
#   Values: oracle_database, rac_database
# oracleHome The Oracle Home of the target database.
#   (oracle_database or rac_database)
# localSID: SID on which to operate (local SID in case of RAC)
# instanceList A list of instanceNames to retrieve the status of or NULL to retrieve
#              the status of all instances (RAC-only)
#   (oracle_database or rac_database)
# dbUsername The username to log onto the database with.
#   (oracle_database or rac_database)
# dbPassword The password for the username.
#   (oracle_database or rac_database)
# dbRole The role of the database user.
#   (oracle_database or rac_database)
#   Values: normal, sysdba
# tns The TNS descriptor. Optional.
#   (oracle_database or rac_database)
# isDB10i True if the database version is 10g or higher? Optional.
#   (oracle_database or rac_database)
#
# Return value: 
# (1) $DBSTATE_SUCCESS_CODE with the database state of SHUTDOWN, STARTED, MOUNTED, or OPEN. 
# (2) $DBSTATE_ERROR_CODE with the database state of UNKNOWN and the output of 
# the SQLPLUS session written to standard output.
#
sub get_db_state
{
  my($targetType, $oracleHome, $localSID, $dbName, $dbUsername, $dbPassword, $dbRole, $tns, $instanceList, $isDB10i) = @_;

  EMD_PERL_DEBUG("dbstate:get_db_state targetType=$targetType");
  EMD_PERL_DEBUG("dbstate:get_db_state oracleHome=$oracleHome");
  EMD_PERL_DEBUG("dbstate:get_db_state localSID=$localSID");
  EMD_PERL_DEBUG("dbstate:get_db_state dbName=$dbName");
  EMD_PERL_DEBUG("dbstate:get_db_state instanceList=$instanceList");
  EMD_PERL_DEBUG("dbstate:get_db_state dbUsername=$dbUsername");
  EMD_PERL_DEBUG("dbstate:get_db_state dbRole=$dbRole");
  EMD_PERL_DEBUG("dbstate:get_db_state tns=$tns");
  EMD_PERL_DEBUG("dbstate:get_db_state instanceList=$instanceList");
  EMD_PERL_DEBUG("dbstate:get_db_state isDB10i=$isDB10i");

  # set ORACLE_HOME and ORACLE_SID in env.
  &set_db_var($oracleHome, $localSID, $isDB10i);

  # construct db connection string
  my $connStr = &constructConnectStr($dbUsername, $dbPassword, $tns, $dbRole);  

  # for now, call get_srvctl_status() for RAC case since sqlplus output
  # cannot reliably be retrieved for RAC
  my $errCode;
  my $initState;
  if ($targetType =~ /$TARGET_TYPE_ORACLE_DATABASE/i) {
    ($errCode, $initState) = &get_db_state_conn($targetType, $connStr);
    ##  return(&get_db_state_conn($targetType, $connStr));
  }
  elsif ($targetType =~ /$TARGET_TYPE_RAC_DATABASE/i) {
    ($errCode, $initState) = &get_srvctl_status($dbName, $instanceList);
    ## return(&get_srvctl_status($dbName, $instanceList));
  }
  EMD_PERL_DEBUG("dbstate:get_db_state errCode=$errCode");
  EMD_PERL_DEBUG("dbstate:get_db_state initState=$initState");
  return($errCode, $initState);
}

#
# Retrieve the database status.
# (select status from v$instance)
#
# Callers should call the following method(s) before calling this routine:
#   &set_db_var();
#
# targetType The target type.
#   Values: oracle_database, rac_database
# connectStr The database connect string.
#   See &constructConnectStr() for mode details.
#
# Return value (oracle_database):
# (1) $DBSTATE_SUCCESS_CODE with the database state of SHUTDOWN, STARTED, MOUNTED, or OPEN. 
# or (2) $DBSTATE_ERROR_CODE with the database state of UNKNOWN and the output of 
# the SQLPLUS session written to standard output.
#
# Return value (rac_database):
# $DBSTATE_SUCCESS_CODE or $DBSTATE_ERROR_CODE 
# In both cases, the results of the SQLPLUS session are written to standard output.
# In the case of success, the expected form is:
#    INSTANCE_NAME      STATUS
#    ----------------   ------------
#    racinst1           OPEN
#    racinst2           MOUNT
#    racinst3           STARTED
# Note that if an instance is shutdown, it will NOT appear in the output.
# 
sub get_db_state_conn
{
  my($targetType, $connectStr) = @_;

  EMD_PERL_DEBUG("dbstate:get_db_state_conn targetType=$targetType");

  my $outputFile = create_temp_file();
  if ($targetType =~ /$TARGET_TYPE_ORACLE_DATABASE/i)
  {
    # single-instance
    @SQLCMDS = "select status from v\$instance";
    $DBSTATE_RETURN_CODE = &executeSQLPlus($connectStr, $outputFile);
  }
  elsif ($targetType =~ /$TARGET_TYPE_RAC_DATABASE/i)
  {
    # RAC
    @SQLCMDS = "select instance_name,status from gv\$instance";
    $DBSTATE_RETURN_CODE = &executeSQLPlus($connectStr); # output should be sent to stdout
  }
  else
  {
    EMD_PERL_ERROR("dbstate:get_db_state_conn invalid targetType=$targetType");
    $DBSTATE_RETURN_CODE = $DBSTATE_ERROR_CODE;
  }

  my $dbState = $STATE_UNKNOWN;

  # parse SQLplus output for single-instance; RAC output will be parsed by caller
  if ($targetType =~ /$TARGET_TYPE_ORACLE_DATABASE/i) {

  # Clear out SQLCMDS.
  @SQLCMDS = ("");
  
  # Command did not get executed
  my $fileSize = getFileSize($outputFile);
  if ($fileSize == 0 || $fileSize == -1)
  {
    $DBSTATE_RETURN_CODE = $DBSTATE_ERROR_CODE;
  }
  else
  {
    open (DB_STATUS, "$outputFile")
      || ((printError("ERROR: Unable to open the temporary file in get_db_state()")) && (return $DBSTATE_ERROR_CODE));
    while ($_ = <DB_STATUS>)
    {
      if (/$STATE_OPEN/i)
      {
        $dbState = $STATE_OPEN;


        EMD_PERL_DEBUG("dbstate:get_db_state_conn: database is in open mode, determining if it is read only");

        # get open mode 
        my $openModeFile = create_temp_file();
        @SQLCMDS = "select open_mode from v\$database";
        $DBSTATE_RETURN_CODE = &executeSQLPlus($connectStr, $openModeFile);
        my $fileSize2 = getFileSize($openModeFile);

        if (!($fileSize2 == 0 || $fileSize2 == -1))
        {
            open (DB_OPEN_MODE, "$openModeFile") 
              || ((printError("ERROR: Unable to open the temporary file in get_db_state()")));
            while ($_ = <DB_OPEN_MODE>) 
            {
                if (/$OPEN_MODE_READ_ONLY/i)
                {
                    EMD_PERL_DEBUG("dbstate:get_db_state_conn: database is in open read only mode");
                    $dbState = "$dbState $OPEN_MODE_READ_ONLY";
                }
            }
        }
        else {
            $DBSTATE_RETURN_CODE = $DBSTATE_ERROR_CODE;
        }
	unlink($openModeFile);

        $DBSTATE_RETURN_CODE = $DBSTATE_SUCCESS_CODE;
        last;
      }
      if (/$STATE_MOUNTED/i)
      {
        $dbState = $STATE_MOUNTED;
        $DBSTATE_RETURN_CODE = $DBSTATE_SUCCESS_CODE;
        last;
      }
      if (/$STATE_STARTED/i)
      {
        $dbState = $STATE_STARTED;
        $DBSTATE_RETURN_CODE = $DBSTATE_SUCCESS_CODE;
        last;
      }
      # ORA-01034: ORACLE not available
      if (/ORA-01034/i)
      {
        $dbState = $STATE_SHUTDOWN;
        $DBSTATE_RETURN_CODE = $DBSTATE_SUCCESS_CODE;
        last;
      }
    }

   # Clear out SQLCMDS.
    @SQLCMDS = ("");

    # get restricted mode
    if ($dbState =~ /$STATE_OPEN/i || $dbState =~ /$STATE_MOUNTED/i || $dbState =~ /$STATE_STARTED/i) 
    {
        my $restrictModeFile = create_temp_file();
        @SQLCMDS = "select logins from v\$instance";
        $DBSTATE_RETURN_CODE = &executeSQLPlus($connectStr, $restrictModeFile);
        my $fileSize3 = getFileSize($restrictModeFile);

        if (!($fileSize3 == 0 || $fileSize3 == -1))
        {
            open (DB_RESTRICT, "$restrictModeFile") 
              || ((printError("ERROR: Unable to open the temporary file in get_db_state()")));
            while ($_ = <DB_RESTRICT>) 
            {
                if (/$RESTRICTED_MODE/i)
                {
                    EMD_PERL_DEBUG("dbstate:get_db_state_conn: database is in restricted mode");
                    $dbState = "$dbState $RESTRICTED_MODE";
                }
            }
        }
        else {
            $DBSTATE_RETURN_CODE = $DBSTATE_ERROR_CODE;
        }
	unlink($restrictModeFile);
    }

    if ($DBSTATE_RETURN_CODE != $DBSTATE_SUCCESS_CODE)
    {
      # Write the contents of the output file to standard output.
      seek(DB_STATUS, 0, 0);
      while ($_ = <DB_STATUS>)
      {
        print "$_";
      }
    }
    close DB_STATUS;
  }
  unlink($outputFile);
  }
  else {
      # rac
      $DBSTATE_RETURN_CODE = $DBSTATE_SUCCESS_CODE;
  }

  EMD_PERL_DEBUG("dbstate.get_db_state_conn: return with code=$DBSTATE_RETURN_CODE, dbState=$dbState");
  return($DBSTATE_RETURN_CODE, $dbState);
}

# Retrieve the status of a RAC database through SRVCTL
#
# connStr The database connect string
# instanceList A list of instanceNames to retrieve the status of or NULL to retrieve
#              the status of all instances
#
# Returns output from srvctl status database -S 1 command.  For 10g (r1 + r2) 
# this returns both config and status data; 
#
# For pre-10g, config and status output will come from seperate commands, in a 
# different format.  The caller needs to handle the version in its parser.
#
sub get_srvctl_status
{
    my ($dbName, $instanceList) = @_;

    EMD_PERL_DEBUG("dbstate:get_srvctl_status dbName=$dbName");
    EMD_PERL_DEBUG("dbstate:get_srvctl_status instanceList=$instanceList");
    EMD_PERL_DEBUG("dbstate:get_srvctl_status version=$srvctlVers");

    my $srvctl_cmd;
    if ($srvctlVers =~ $SRVCTL_VERS_10 || !$srvctlVers) {
	# For 10g, status returns both config and status data
	$srvctl_cmd = constructSRVCTLCmd($dbName, $instanceList, "", "status", "-S 1");
	return (&executeSRVCTL($srvctl_cmd));
    }
    else {
	# For pre-10g, need to run config, then status

	# note: srvctl config database does not take instanceList
	$srvctl_cmd = constructSRVCTLCmd($dbName, $instanceList, "", "config");
	my $returnCode = &executeSRVCTL($srvctl_cmd);	

	if ($returnCode != $DBSTATE_SUCCESS_CODE) {
	    return $returnCode;
	}

	print "#@=endconfig\n";  # marker for end of config output

	$srvctl_cmd = constructSRVCTLCmd($dbName, $instanceList, "", "status", "-S 1");
	return (&executeSRVCTL($srvctl_cmd));	
    }
}

#
# Change the state of the database from the current state to the desired state.
#
# targetType The target type.
#   Values: oracle_database, rac_database
# connectStr The database connect string.
#   See &constructConnectStr() for mode details.
# instanceList A list of SID(s) to change the state of or NULL to change the state of all instances.
#   (rac_database)
# desiredState The desired state of the database. UNKNOWN, SHUTDOWN, STARTED, MOUNTED, OPEN
#
# Callers should call the following method(s) before calling this routine:
#   &set_db_var();
#
# Possible values for current/desired states:
#   Current State   Desired State
#   UNKNOWN         UNKNOWN
#   SHUTDOWN        SHUTDOWN
#   STARTED         STARTED
#   MOUNTED         MOUNTED
#   OPEN            OPEN
#
# Note for rac_database:
#   ALL instances of the database will be brought up, regardless of whether 
#   they were up before.  Further, all instances will be brought up to 
#   identical states ($desiredState).
#
sub change_db_state
{
  my($targetType, $connStr, $instanceList, $desiredState, $dbName) = @_;

  EMD_PERL_DEBUG("dbstate:change_db_state targetType=$targetType");
  EMD_PERL_DEBUG("dbstate:change_db_state instanceList=$instanceList");
  EMD_PERL_DEBUG("dbstate:change_db_state desiredState=$desiredState");
  EMD_PERL_DEBUG("dbstate:change_db_state dbName=$dbName");

  # If the desired state is unknown, then there's not much we can do.
  if ($desiredState eq $STATE_UNKNOWN) 
  {
    return;
  }

  # Get the current state.
  my($errCode, $currState) = &get_db_state_conn($targetType, $connStr); 
  EMD_PERL_DEBUG("dbstate:change_db_state currState=$currState");

  # If the desired state is the same as the current state, then we've achieved our goal.
  if ($desiredState eq $currState)
  {
     EMD_PERL_DEBUG("dbstate:change_db_state: Current state is same as desired state");
     return;
  }

  # Let's change the state from the current state to the desired state.

  # oracle_database:
  # SQL is generated as follows:
  #   1) Current state is shutdown --> issue a "startup <target_state>"
  #   2) Current state is < desired state --> issue "alter database <next_state>" 
  #   3) Current state is > desired state --> issue a "shutdown immediate", then #1
  if ($targetType =~ /$TARGET_TYPE_ORACLE_DATABASE/i) {
  if ($desiredState eq $STATE_SHUTDOWN)
  {
    push(@SQLCMDS, "shutdown immediate");
  }
  elsif ($desiredState eq $STATE_STARTED)
  {
    if ($currState eq $STATE_SHUTDOWN)
    {
      push(@SQLCMDS, "startup nomount");
    }
    else
    {
      push(@SQLCMDS, "shutdown immediate");
      push(@SQLCMDS, "startup nomount");
    }
  }
  elsif ($desiredState eq $STATE_MOUNTED)
  {
    if ($currState eq $STATE_SHUTDOWN)
    {
      push(@SQLCMDS, "startup mount");
    }
    elsif ($currState eq $STATE_STARTED)
    {
      push(@SQLCMDS, "alter database mount");
    }
    else
    {
      push(@SQLCMDS, "shutdown immediate");
      push(@SQLCMDS, "startup mount");
    }
  }
  elsif ($desiredState =~ /$STATE_OPEN/i)
  {
    my $openState ="$STATE_OPEN";
    if ($desiredState =~ /$OPEN_MODE_READ_ONLY/i) {
        # so that we don't include restricted option here:
        $openState = "$openState $OPEN_MODE_READ_ONLY";
    }

    if ($currState eq $STATE_SHUTDOWN)
    {
      push(@SQLCMDS, "startup $openState");
    }
    elsif ($currState eq $STATE_STARTED)
    {
      push(@SQLCMDS, "alter database mount");
      push(@SQLCMDS, "alter database $openState");
    }
    elsif ($currState eq $STATE_MOUNTED)
    {
      push(@SQLCMDS, "alter database $openState");
    }
    else
    {
      # may be reached if desiredState=open read only and currState=open
      push(@SQLCMDS, "shutdown immediate");
      push(@SQLCMDS, "startup $openState");
    }

    if ($desiredState =~ /$RESTRICTED_MODE/i)
    {
        # enable restricted mode 
        push(@SQLCMDS, "alter system enable restricted session");
    }
  }

  return(&executeSQLPlus($connStr));
  }

  # rac_database:
  # SRVCTL is issued as follows:
  #   1) "srvctl stop database" followed by "srvctl start database -o <target_state>"
  # Note the end result of this is that ALL instances of the database will be brought
  # up, regardless of whether they were up before.  Further, all instances will be 
  # brought up to identical states (= $desiredState)
  elsif ($targetType =~ /$TARGET_TYPE_RAC_DATABASE/i) {

      # shutdown immediate specified instances (or all if instanceList null)
      my $srvctl_cmd = constructSRVCTLCmd($dbName, $instanceList, "immediate", "stop");
      &executeSRVCTL($srvctl_cmd, $connStr);      

      my $startCmd = $desiredState;
      if ($desiredState =~ /$STATE_OPEN/i) {
          $startCmd = $STATE_OPEN; 
          if ($desiredState =~ /$OPEN_MODE_READ_ONLY/i) {
              # because we need to move restrict to front of cmd for srvctl
              $startCmd = "$STATE_OPEN $OPEN_MODE_READ_ONLY";
          }
      }
      elsif ($desiredState =~ /$STATE_MOUNTED/i) {
          $startCmd = $STARTUP_MOUNT;
      }
      elsif ($desiredState =~ /$STATE_STARTED/i) {
          $startCmd = $STARTUP_NOMOUNT;
      }

      if ($desiredState =~ /$RESTRICTED_MODE/i) {
          # add restrict to start command
          $startCmd = "$RESTRICTED_MODE $startCmd";
      }

      # startup specified instances (or all) to desiredState
      $srvctl_cmd = constructSRVCTLCmd($dbName, $instanceList, $startCmd, "start");      
      &executeSRVCTL($srvctl_cmd, $connStr);      
  }
}

#
# Shutdown the database.
#
# targetType The target type.
#   Values: oracle_database, rac_database
#   Uses SQLPLUS for oracle_database target type
#   Uses SRVCTL for rac_database target type
# oracleHome The Oracle Home of the target database.
#   (oracle_database or rac_database)
# localSID: SID on which to operate (local SID in case of RAC)
#   (oracle_database or rac_database)
# dbName The database name (db_unique_name)
#   (rac_database)
# dbUsername The username to log onto the database with.
#   (oracle_database or rac_database)
# dbPassword The password for the username.
#   (oracle_database or rac_database)
# dbRole The role of the database user.
#   (oracle_database or rac_database)
#   Values: normal, sysdba
# tns The TNS descriptor. Optional.
#   (oracle_database or rac_database)
# instanceList A list of SID(s) to shutdown or NULL to shutdown all instances.
#   (rac_database)
# isDB10i True if the database version is 10g or higher? Optional.
#   (oracle_database or rac_database)
# shutdownOption - The shutdown command option for the SQLPLUS SHUTDOWN or SRVCTL STOP command.  
#   (oracle_database or rac_database)
#   Values: normal, immediate, transactional, abort
#   Default: immediate
# restoreDBState boolean (0/1), indicates that the database should be
#   restored to original running state if operation fails
# initState_p If specified, and restoreDBState=1, db will be restored to this state.  Not required.
# preShutdownSql An array containing SQLPLUS script to run before shutting down the database. Optional.
#   (oracle_database)
#   Do NOT add semicolons (;) to the end of the statements.
#
# Return value: 
# $DBSTATE_SUCCESS_CODE or $DBSTATE_ERROR_CODE with the output of the 
# SQLPLUS session written to standard output.
#
sub shutdown_db
{
  my($targetType, $oracleHome, $localSID, $dbName, $dbUsername, $dbPassword, $dbRole, $tns, $instanceList, $isDB10i, $shutdownOption, $restoreDBState, $initState_p, @preShutdownSql) = @_;

  EMD_PERL_DEBUG("dbstate:shutdown_db targetType=$targetType");
  EMD_PERL_DEBUG("dbstate:shutdown_db oracleHome=$oracleHome");
  EMD_PERL_DEBUG("dbstate:shutdown_db localSID=$localSID");
  EMD_PERL_DEBUG("dbstate:shutdown_db dbName=$dbName");
  EMD_PERL_DEBUG("dbstate:shutdown_db dbUsername=$dbUsername");
  EMD_PERL_DEBUG("dbstate:shutdown_db dbRole=$dbRole");
  EMD_PERL_DEBUG("dbstate:shutdown_db tns=$tns");
  EMD_PERL_DEBUG("dbstate:shutdown_db instanceList=$instanceList");
  EMD_PERL_DEBUG("dbstate:shutdown_db isDB10i=$isDB10i");
  EMD_PERL_DEBUG("dbstate:shutdown_db shutdownOption=$shutdownOption");
  EMD_PERL_DEBUG("dbstate:shutdown_db restoreDBState=$restoreDBState");
  EMD_PERL_DEBUG("dbstate:shutdown_db initState_p=$initState_p");
  EMD_PERL_DEBUG("dbstate:shutdown_db preShutdownSql=@preShutdownSql");

  # set ORACLE_HOME and ORACLE_SID in env.
  &set_db_var($oracleHome, $localSID, $isDB10i);

  # construct db connection string
  my $connStr = &constructConnectStr($dbUsername, $dbPassword, $tns, $dbRole);  
  my $returnCode = $DBSTATE_SUCCESS_CODE;
  my ($errCode, $initState);

  if ($restoreDBState)
  {
    # get current state, for later use in case of failure
    # use oracle_database targetType for both single-inst and RAC to get status 
    # for local instance in case of RAC
    if (!$initState_p) {
       ($errCode, $initState) = &get_db_state_conn($TARGET_TYPE_ORACLE_DATABASE, $connStr, $instanceList);  
    }
    else {
	$initState = $initState_p;
    }

    # Note:On failure, all specified instances for a RAC db will be returned
    #      to initState (regardless of whether local instance is in $instanceList or not)
    EMD_PERL_DEBUG("dbstate:shutdown_db initState=$initState");
  }

  if (@preShutdownSql)
  {
    @SQLCMDS = (@preShutdownSql);
  }
  if (!defined($shutdownOption) || $shutdownOption eq "")
  {
    $shutdownOption = $SHUTDOWN_IMMEDIATE;
  }

  # determine target db type (single-inst/RAC) and shut down accordingly
  if ($targetType =~ /$TARGET_TYPE_ORACLE_DATABASE/i)
  {
    ### shutdown single-instance database through sqlplus
    my $shutdownCmd = "shutdown $shutdownOption";
    if (@preShutdownSql)
    {
      push(@SQLCMDS, $shutdownCmd);
    }
    else
    {
      @SQLCMDS = ($shutdownCmd);
    }
    EMD_PERL_DEBUG("dbstate:shutdown_db SQLCMDS=@SQLCMDS");

    $returnCode = &executeSQLPlus($connStr);
  }
  elsif ($targetType =~ /$TARGET_TYPE_RAC_DATABASE/i)
  {
      ### shutdown RAC database through srvctl

      my $srvctl_cmd = constructSRVCTLCmd($dbName, $instanceList, $shutdownOption, "stop");

     
    # execute preShutdown SQLplus:
    if (@preShutdownSql)
    {
      $returnCode = &executeSQLPlus($connStr);
    }

    # execute SRVCTL stop cmd (only if SQL execution was successful)
    if ($returnCode == $DBSTATE_SUCCESS_CODE) {
       $returnCode = &executeSRVCTL($srvctl_cmd, $connStr);
    }
  }
  else
  {
    EMD_PERL_ERROR("dbstate:shutdown_db invalid targetType=$targetType");
    return($DBSTATE_ERROR_CODE);
  }

  # if operation fails and if restoreDBState flag is true, attempt to restore original state
  if (($returnCode != $DBSTATE_SUCCESS_CODE) && $restoreDBState)
    {
      &change_db_state($targetType, $connStr, $instanceList, $initState, $dbName);
    }

  return ($returnCode);
}

#
# Startup the database.
#
# targetType The target type.
#   Values: oracle_database, rac_database
#   Uses SQLPLUS for oracle_database target type
#   Uses SRVCTL for rac_database target type
# oracleHome The Oracle Home of the target database.
#   (oracle_database or rac_database)
# localSID: SID on which to operate (local SID in case of RAC)
#   (oracle_database or rac_database)
# dbName The database name (db_unique_name)
#   (rac_database)
# dbUsername The username to log onto the database with.
#   (oracle_database or rac_database)
# dbPassword The password for the username.
#   (oracle_database or rac_database)
# dbRole The role of the database user.
#   (oracle_database or rac_database)
#   Values: normal, sysdba
# tns The TNS descriptor. Optional.
#   (oracle_database or rac_database)
# instanceList A list of SID(s) to shutdown or NULL to shutdown all instances.
#   (rac_database)
# isDB10i True if the database version is 10g or higher? Optional.
#   (oracle_database or rac_database)
# startupOption - The startup command option for the SQLPLUS STARTUP, SQLPLUS ALTER DATABASE, 
#   or SRVCTL START command. Optionally append PFILE=filename. Optional.
#   (oracle_database or rac_database)
#   Values: nomount, mount, open,
#   force nomount, force mount, force open,
#   restrict nomount, restrict mount, restrict open
#
#   For SQLPLUS ALTER DATABASE (used when sqlRunState is nomount or mount)
#   open, open read write, open read only,
#   open resetlogs, open noresetlogs,
#   open read write resetlogs, open read write noresetlogs
#
#   To change the state of the database, for example, from nomount to mount,
#   pass in mount as the startupOption. This method will determine that the database
#   is not shutdown and therefore execute 'alter database mount', instead of 'startup mount'.
#
# sqlRunState What the state of the database should be in when running the postStartupSql.
#   Optionally append PFILE=filename. Optional.
#   (oracle_database)
#   values: nomount, mount, open
# bounceAfterPostSQL boolean (0/1), if non-empty, indicates that database should be
#   restarted after running postStartupSql scripts
# restoreDBState boolean (0/1), indicates that the database should be
#   restored to original running state if operation fails
# initState_p If specified, and restoreDBState=1, db will be restored to this state.  Not required.
# postStartupSql An array containing SQLPLUS script to run when in the sqlRunState. Optional.
#   (oracle_database)
#   Do NOT add semicolons (;) to the end of the statements.
#
# Return value: 
# $DBSTATE_SUCCESS_CODE or $DBSTATE_ERROR_CODE with the output of the 
# SQLPLUS session written to standard output.
#
sub startup_db
{
  my($targetType, $oracleHome, $localSID, $dbName, $dbUsername, $dbPassword, $dbRole, $tns, $instanceList, $isDB10i, $startupOption, $sqlRunState, $restoreDBState, $initState_p, $bounceAfterPostSQL, @postStartupSql) = @_;

  EMD_PERL_DEBUG("dbstate:startup_db targetType=$targetType");
  EMD_PERL_DEBUG("dbstate:startup_db oracleHome=$oracleHome");
  EMD_PERL_DEBUG("dbstate:startup_db localSID=$localSID");
  EMD_PERL_DEBUG("dbstate:startup_db dbName=$dbName");
  EMD_PERL_DEBUG("dbstate:startup_db dbUsername=$dbUsername");
  EMD_PERL_DEBUG("dbstate:startup_db dbRole=$dbRole");
  EMD_PERL_DEBUG("dbstate:startup_db tns=$tns");
  EMD_PERL_DEBUG("dbstate:startup_db instanceList=$instanceList");
  EMD_PERL_DEBUG("dbstate:startup_db isDB10i=$isDB10i");
  EMD_PERL_DEBUG("dbstate:startup_db startupOption=$startupOption");
  EMD_PERL_DEBUG("dbstate:startup_db sqlRunState=$sqlRunState");
  EMD_PERL_DEBUG("dbstate:startup_db restoreDBState=$restoreDBState");
  EMD_PERL_DEBUG("dbstate:startup_db initState_p=$initState_p");
  EMD_PERL_DEBUG("dbstate:startup_db bounceAfterPostSQL=$bounceAfterPostSQL");
  EMD_PERL_DEBUG("dbstate:startup_db postStartupSql=@postStartupSql");

  # set ORACLE_HOME and ORACLE_SID in env.  
  &set_db_var($oracleHome, $localSID, $isDB10i); 

  if ($isDB10i) {
      # null TNS if DB 10i (or higher). 
      # Cannot use listener to connect when db is down (for >= 10i db)
      $tns = "";
  }

  if (!defined($startupOption) || $startupOption eq "")
  {
    $startupOption = $STARTUP_OPEN;
  }

  # construct db connection string
  my $connStr = &constructConnectStr($dbUsername, $dbPassword, $tns, $dbRole);
  my $returnCode = $DBSTATE_SUCCESS_CODE;

  # get current state, for later use in case of failure and
  # to also determine whether to do a 'startup' or 'alter database' command.
  # use oracle_database targetType for both single-inst and RAC to get status 
  # for local instance in case of RAC
  my($errCode, $curState, $initState);
 ($errCode, $curState) = &get_db_state_conn($TARGET_TYPE_ORACLE_DATABASE, $connStr, $instanceList);
  EMD_PERL_DEBUG("dbstate:startup_db curState=$curState");
                                                                                
  if (!$initState_p) {
    $initState = $curState;
  }
  else {
    $initState = $initState_p;
  }
  EMD_PERL_DEBUG("dbstate:startup_db initState=$initState");

  # add postStartupSql to SQL cmd stack:
  # for RAC, the postStartupSql will be run on the local instance 
  #          Also the local inst will be brought up to $startupOption state; SRVCTL will 
  #          bring up the rest of the instances up later (and no-op on the started inst).
  if (@postStartupSql)
  {
    if ($sqlRunState =~ /$STARTUP_NOMOUNT|$STARTUP_MOUNT|$STARTUP_OPEN/i)
    {
      @SQLCMDS = ("startup $sqlRunState");  ## This assumes instance is currently DOWN... 
      push(@SQLCMDS, @postStartupSql);	  
	  
      if ($bounceAfterPostSQL) 
      {
        push(@SQLCMDS, "shutdown immediate");
      }
      else
      {
        # no bounce, just alter database

        if ($startupOption =~ /$sqlRunState/i)
        {
          # startupOption == sqlRunState
          # not an error, just a no-op
          # note that special options in startupOptions will be ignored
          EMD_PERL_DEBUG("dbstate:startup_db startupOption and sqlRunState are at same level, no alter database performed.");
        }
        else
        {
          # This assumes startupOption state is > sqlRunState --> caller gets around it by setting bounceAfterPostSql
          # If the caller does try sqlRunState > startupOption, caller will get errors from SQLplus
          push(@SQLCMDS, "alter database $startupOption");   
        }
      }
    }
    else
    {
      EMD_PERL_ERROR("dbstate:startup_db invalid sqlRunState=$sqlRunState");
      return($DBSTATE_ERROR_CODE);
    }
  }
  
  # determine target db type (single-inst/RAC) and startup accordingly
  if ($targetType =~ /$TARGET_TYPE_ORACLE_DATABASE/i)
  {
    ### startup single-instance database through sqlplus
    if (@postStartupSql)
    {
      if ($bounceAfterPostSQL) 
      {
        push(@SQLCMDS, "startup $startupOption");
      }
    }
    else
    {
      # no SQL to run after startup:
      if (($curState eq $STATE_UNKNOWN) || ($curState eq $STATE_SHUTDOWN))
      {
        @SQLCMDS = ("startup $startupOption");
      }
      else
      {
        @SQLCMDS = ("alter database $startupOption");
      }
    }

    EMD_PERL_DEBUG("dbstate:startup_db SQLCMDS=@SQLCMDS");
      
    $returnCode = &executeSQLPlus($connStr);
  }
  elsif ($targetType =~ /$TARGET_TYPE_RAC_DATABASE/i)
  {
      ### startup RAC database through srvctl

      my $srvctl_cmd = constructSRVCTLCmd($dbName, $instanceList, $startupOption, "start");

      
    # execute postStartup SQL:
    if (@postStartupSql)
    {
      $returnCode = &executeSQLPlus($connStr);
    }

    # execute SRVCTL start cmd (only if SQL execution was successful)
    if ($returnCode == $DBSTATE_SUCCESS_CODE) {
      $returnCode = &executeSRVCTL($srvctl_cmd, $connStr);
    }
  }
  else
  {
    EMD_PERL_ERROR("dbstate:startup_db invalid targetType=$targetType");
    return($DBSTATE_ERROR_CODE);
  }

  # if operation fails and if restoreDBState flag is true, attempt to restore original state
  if (($returnCode != $DBSTATE_SUCCESS_CODE) && $restoreDBState)
  {
    &change_db_state($targetType, $connStr, $instanceList, $initState, $dbName);
  }

  return($returnCode);
}

#
# Restart the database
#
# targetType The target type.
#   Values: oracle_database, rac_database
#   Uses SQLPLUS for oracle_database target type
#   Uses SRVCTL for rac_database target type
# oracleHome The Oracle Home of the target database.
#   (oracle_database or rac_database)
# localSID: SID on which to operate (local SID in case of RAC)
#   (oracle_database or rac_database)
# dbName The database name.
#   (rac_database)
# dbUsername The username to log onto the database with.
#   (oracle_database or rac_database)
# dbPassword The password for the username.
#   (oracle_database or rac_database)
# dbRole The role of the database user.
#   (oracle_database or rac_database)
#   Values: normal, sysdba
# tns The TNS descriptor. Optional.
#   (oracle_database or rac_database)
# instanceList A list of SID(s) to shutdown or NULL to shutdown all instances.
#   (rac_database)
# isDB10i True if the database version is 10g or higher? Optional.
#   (oracle_database or rac_database)
# restoreDBState boolean (0/1), indicates that the database should be
#   restored to original running state if operation fails.
# shutdownOption - The shutdown command option for the SQLPLUS SHUTDOWN or SRVCTL STOP command. Optional.
#   (oracle_database or rac_database)
#   Values: normal, immediate, transactional, abort
#   Default: immediate
# preShutdownSql A reference to an array containing SQLPLUS script to run before shutting down the database. Optional.
#   (oracle_database)
#   Do NOT add semicolons (;) to the end of the statements.
# startupOption - The startup command option for the SQLPLUS STARTUP, SQLPLUS ALTER DATABASE, 
#   or SRVCTL START command. Optionally append PFILE=filename. Optional.  
#   (oracle_database or rac_database)
#   Values: nomount, mount, open,
#   force nomount, force mount, force open,
#   restrict nomount, restrict mount, restrict open
#   For SQLPLUS ALTER DATABASE (used when sqlRunState is nomount or mount)
#   open, open read write, open read only,
#   open resetlogs, open noresetlogs,
#   open read write resetlogs, open read write noresetlogs
# postStartupSql A reference to an array containing SQLPLUS script to run when in the sqlRunState. Optional.
#   (oracle_database)
#   Do NOT add semicolons (;) to the end of the statements.
# sqlRunState What the state of the database should be in when running the SQL.
#   Optionally append PFILE=filename. Optional.
#   (oracle_database)
#   values: nomount, mount, open
# bounceAfterPostSQL boolean (0/1), if non-empty, indicates that database should be
#   restarted after running postStartupSql scripts
#
# Return value: 
# $DBSTATE_SUCCESS_CODE or $DBSTATE_ERROR_CODE with the output of the 
# SQLPLUS session written to standard output.
#
sub restart_db
{
  my($targetType, $oracleHome, $localSID, $dbName, $dbUsername, $dbPassword, $dbRole, $tns, $instanceList, $isDB10i, $shutdownOption, $preShutdownSql, $startupOption, $postStartupSql, $sqlRunState, $restoreDBState, $bounceAfterPostSQL) = @_;

  EMD_PERL_DEBUG("dbstate:restart_db targetType=$targetType");
  EMD_PERL_DEBUG("dbstate:restart_db oracleHome=$oracleHome");
  EMD_PERL_DEBUG("dbstate:restart_db localSID=$localSID");
  EMD_PERL_DEBUG("dbstate:restart_db dbName=$dbName");
  EMD_PERL_DEBUG("dbstate:restart_db dbUsername=$dbUsername");
  EMD_PERL_DEBUG("dbstate:restart_db dbRole=$dbRole");
  EMD_PERL_DEBUG("dbstate:restart_db tns=$tns");
  EMD_PERL_DEBUG("dbstate:restart_db instanceList=$instanceList");
  EMD_PERL_DEBUG("dbstate:restart_db isDB10i=$isDB10i");
  EMD_PERL_DEBUG("dbstate:restart_db shutdownOption=$shutdownOption");
  EMD_PERL_DEBUG("dbstate:restart_db preShutdownSql=@$preShutdownSql");
  EMD_PERL_DEBUG("dbstate:restart_db startupOption=$startupOption");
  EMD_PERL_DEBUG("dbstate:restart_db postStartupSql=@$postStartupSql");
  EMD_PERL_DEBUG("dbstate:restart_db sqlRunState=$sqlRunState");
  EMD_PERL_DEBUG("dbstate:restart_db restoreDBState=$restoreDBState");
  EMD_PERL_DEBUG("dbstate:restart_db bounceAfterPostSQL=$bounceAfterPostSQL");

  my $errCode;
  my $initState;
  if ($restoreDBState)
  {
    # get current state, for later use in case of failure
    # The database should be returned to this state in case of failure during
    # shutdown OR startup

    # use oracle_database targetType for both single-inst and RAC to get status 
    # for local instance in case of RAC
    ($errCode, $initState) = &get_db_state($TARGET_TYPE_ORACLE_DATABASE, $oracleHome, $localSID, $dbName, $dbUsername, $dbPassword, $dbRole, $tns, $instanceList, $isDB10i);  
  }

  EMD_PERL_DEBUG("dbstate:restart_db restart_db errCode returned=$errCode");
  EMD_PERL_DEBUG("dbstate:restart_db restart_db initState returned=$initState");

  # Dereference preShutdownSql and postStartupSql variables.
  my $result = &shutdown_db($targetType, $oracleHome, $localSID, $dbName, $dbUsername, $dbPassword, $dbRole, $tns, $instanceList, $isDB10i, $shutdownOption, $restoreDBState, $initState, @$preShutdownSql);

  # only do startup if shutdown did not receive errors
  if ($result == $DBSTATE_SUCCESS_CODE)
  {
    $result = &startup_db($targetType, $oracleHome, $localSID, $dbName, $dbUsername, $dbPassword, $dbRole, $tns, $instanceList, $isDB10i, $startupOption, $sqlRunState, $restoreDBState, $initState, $bounceAfterPostSQL, @$postStartupSql);
  }

  return($result);
}

# Retrieve version of SRVCTL software
#
# oracleHome $OH of target database
#
# Returns 'srvctl config -V'
#
sub get_srvctl_version_dbhome
{
    my ($oracleHome) = @_;

    $ENV{ORACLE_HOME} = $oracleHome;

    EMD_PERL_DEBUG("dbstate:get_srvctl_version oracleHome=$oracleHome");

    my $srvctl_cmd = "$oracleHome/bin/srvctl config -V";
    return (&executeSRVCTL($srvctl_cmd));
}

#
# Execute the SQL commands as SYSDBA user.
#
# dbUsername The username to log onto the database with.
#   (oracle_database)
# dbPassword The password for the username.
#   (oracle_database)
# tns The TNS descriptor. Optional.
#   (oracle_database)
# outputFile Redirect output to the named file. Optional.
#   (oracle_database)
#
# Return value: 
# $DBSTATE_SUCCESS_CODE or $DBSTATE_ERROR_CODE with the output of the 
# SQLPLUS session written to standard output or to the file passed in.
#
sub executeSQLPlusSYSDBA
{
  my($dbUsername, $dbPassword, $tns, $outputFile) = @_;
  my $connStr = &constructConnectStr($dbUsername, $dbPassword, $tns, $DBROLE_SYSDBA);  
  return(&executeSQLPlus($connStr, $outputFile));
}

#
# Execute the SQL commands using the database connect string.
#
# connectStr The database connection string. See &constructConnectStr for details.
#   (oracle_database)
# outputFile Redirect the output from SQLPLUS to the named file. Optional. 
#   Otherwise redirect the output to standard output.
#   (oracle_database)
#
# Return value: 
# $DBSTATE_SUCCESS_CODE or $DBSTATE_ERROR_CODE with the output of the 
# SQLPLUS session written to standard output or to the file passed in.
#
sub executeSQLPlus
{
#  local($SIG{'CHLD'}) = \&reaper;

  my($connStr, $outputFile) = @_;

  EMD_PERL_DEBUG("dbstate.executeSQLPlus");

  # Redirect the output to a file.
  if (defined($outputFile) && ($outputFile ne "")){
    $SQLPID = open(SQL, "|$ENV{ORACLE_HOME}/bin/sqlplus /nolog > $outputFile");
  }
  # Redirect the output to standard output.
  else{
    $SQLPID = open(SQL, "|$ENV{ORACLE_HOME}/bin/sqlplus /nolog");
  }
  if(!$SQLPID){
    printError("ERROR: could not run SQLPLUS");
    return($DBSTATE_ERROR_CODE);
  }
  EMD_PERL_DEBUG("dbstate.executeSQLPlus: SQLPID=$SQLPID");

  my($old_fh) = select(SQL);
  $| = 1;
  select($old_fh);  

  ## Cause exit on any ORA errors unless IGNORE_SQL_ERROR is set
  if($IGNORE_SQL_ERROR){
    print SQL "whenever sqlerror continue\n";
    EMD_PERL_DEBUG("dbstate.executeSQLPlus: whenever sqlerror continue");
  }
  else{
    print SQL "whenever sqlerror exit sql.sqlcode\n";
    EMD_PERL_DEBUG("dbstate.executeSQLPlus: whenever sqlerror exit sql.sqlcode");
  }

  print SQL "connect $connStr\n";
  print SQL "set echo on\n";

  EMD_PERL_DEBUG("dbstate.executeSQLPlus: Connected");

  # Run thru all commands (@SQLCMDS is global set by caller)
  if(defined(@SQLCMDS)){
    my($cmd);
    foreach $cmd (@SQLCMDS)
    {
      ## Turn off error exit for shutdown, as ORA
      ##  errors are expected in some cases (e.g., shutdown of standby)
      if(!$IGNORE_SQL_ERROR)
      {
        if($IGNORE_SQL_SHUTDOWN_ERROR && ($cmd =~ /$SQL_SHUTDOWN/i))
        {
          print SQL "whenever sqlerror continue\n";
          print SQL "${cmd};\n";
          print SQL "whenever sqlerror exit sql.sqlcode\n";
          EMD_PERL_DEBUG("dbstate.executeSQLPlus: whenever sqlerror continue");
          EMD_PERL_DEBUG("dbstate.executeSQLPlus: ${cmd}");
          EMD_PERL_DEBUG("dbstate.executeSQLPlus: whenever sqlerror exit sql.sqlcode");
          next;
        }
      }
        
      print SQL "${cmd};\n";
      EMD_PERL_DEBUG("dbstate.executeSQLPlus: ${cmd}");
    }
  }
    
  close(SQL);

  ## For NT, signal handling not operable, so must determine if
  ##  if error occurred here.
  ## if($NT && ($? >> 8)){

  ## Use following code for all platforms instead of signal handler.
  ## In signal handler, waitpid() always returns a pid of -1 on Linux
  ## in conjuction with the open() call.
  if($?){
    ##  SQLPLUS error is in stdout
    EMD_PERL_ERROR("dbstate.executeSQLPlus: SQLPLUS error; see job output for error messages");    
    $DBSTATE_RETURN_CODE = $DBSTATE_ERROR_CODE;
    if($IGNORE_SQLPLUS_ERROR){
      EMD_PERL_DEBUG("dbstate.executeSQLPlus: Continuing after SQLPLUS error");
    }
    else{
      exit($DBSTATE_RETURN_CODE);
    }
  }

  ## Normally returns 0, 
  ##  unless IGNORE_SQLPLUS_ERROR is on and SQLPLUS exited on error
  EMD_PERL_DEBUG("dbstate.executeSQLPlus: return with code=$DBSTATE_RETURN_CODE");
  return $DBSTATE_RETURN_CODE;
}

## Execute SRVCTL command
# Pipe command to tmp file first, to avoid exposing $connStr credentials
# Print output to stdout
# Grep output for error codes to ensure correct error reporting
##
sub executeSRVCTL
{
  my ($srvctl_cmd, $connStr) = @_;

  # append db connection string
  # bug 4201722: don't pass connect string to srvctl
  #  if ($connStr)
  #  {
  #    $srvctl_cmd= $srvctl_cmd." -c \"$connStr\"";
  #  }

  # create temp file for password piping
  my $dir = tempdir(CLEANUP => 1);
  
  my ($srvctl_fh, $srvctl_filename);  
  if (!$NT)
  {
    ($srvctl_fh, $srvctl_filename) = tempfile( DIR => $dir );
  }
  else 
  {
    $srvctl_filename = "$dir\\"."rac.srvctl.$$";  ## create unique file name
  }
    
  open(SRVCTL_WRITER, "|$srvctl_cmd > $srvctl_filename") || die "dbstate.executeSRVCTL: Can not open pipe for srvctl command";
    
  close SRVCTL_WRITER;
    
  my $isErr = 0;
  if ($? != 0) {
    $isErr = 1;
  }
    
  open (SRVCTL_STATUS, "$srvctl_filename") || die "dbstate.executeSRVCTL: Unable to open temp file: $srvctl_filename";
    
  my $result ="";
  while ($_ = <SRVCTL_STATUS>)
  {
    #EMD_PERL_DEBUG("$_");
    #print "$_";
    $result= $result."$_";
  }
    
  close SRVCTL_STATUS;
    
  if (!$NT) {
    close $srvctl_fh;  # need to explicity close tmp file in NT
  }
    
  removeFile ($srvctl_filename);

  print "$result\n";
  EMD_PERL_DEBUG("srvctl result = $result");
    
  if ($isErr) {
    return ($? >> 8);
  }
    
  return has_ec($result);    
}

# Constructs a SRVCTL cmd to perform a database shutdown or startup (RAC only)
#
# dbName The database name (db_unique_name)
# instanceList A list of SID(s) to shutdown or NULL to shutdown all instances.
# srvctlOption '-o' Options for startup/shutdown (see comments for startup_db/
#              shutdown_db for details) 
# opCmd SRVCTL command verb: {start, stop, status, config}
# extraOptions Any extra command-line options (e.g., "-S 1")
#
# Returns string of form:
#    1) srvctl $opCmd database -d $dbName -o $srvctlOption $extraOptions
# or 2) srvctl $opCmd database -d $dbName -o $srvctlOption -i $instanceList $extraOptions
# or 3) srvctl $opCmd database -d $dbName $extraOptions
sub constructSRVCTLCmd()
{
    my($dbName, $instanceList, $srvctlOption, $opCmd, $extraOptions) = @_;    

    EMD_PERL_DEBUG("dbstate:constructSRVCTLCmd dbName=$dbName");    
    EMD_PERL_DEBUG("dbstate:constructSRVCTLCmd instanceList=$instanceList");    
    EMD_PERL_DEBUG("dbstate:constructSRVCTLCmd srvctlOption=$srvctlOption");    
    EMD_PERL_DEBUG("dbstate:constructSRVCTLCmd opCmd=$opCmd");    
    EMD_PERL_DEBUG("dbstate:constructSRVCTLCmd extraOptions=$extraOptions");    

    my $srvctl_cmd;

    if (!defined($instanceList) || $instanceList eq "")
    {
	# all instances
	$srvctl_cmd = "$ENV{ORACLE_HOME}/bin/srvctl $opCmd database -d $dbName";
    }
    else 
    {
	# specify instanceList
	$srvctl_cmd = "$ENV{ORACLE_HOME}/bin/srvctl $opCmd instance -d $dbName -i \"$instanceList\"";
    }
    
    # append '-o' options
    if ($srvctlOption && !($srvctlOption eq "")) {
	# note: do not use \" here because of the way it is called from startup/shutdown jobs
	$srvctl_cmd = $srvctl_cmd." -o $srvctlOption";
    }

    # append extra options
    if ($extraOptions && !($extraOptions eq "")) {
	$srvctl_cmd = $srvctl_cmd." $extraOptions";
    }    

    EMD_PERL_DEBUG("dbstate:constructSRVCTLCmd srvctl_cmd=$srvctl_cmd");
    return $srvctl_cmd;
}

sub set_db_var()
{
  my($target_home, $target_sid, $is10i) = @_;

  if (defined($is10i) && $is10i)
  {
    set_env_var($target_home, $target_sid, "TRUE");
  }
  else
  {
    set_env_var($target_home, $target_sid, "FALSE");
  }
}

## Construct the database connection string.  Possible return formats:
#   $dbUsername/$dbPassword@$tns as $dbRole
#   $dbUsername/$dbPassword as $dbRole
#   $dbUsername/$dbPassword@$tns
#   $dbUsername/$dbPassword
#   / as $dbRole
##
sub constructConnectStr
{
  my($dbUsername, $dbPassword, $tns, $dbRole) = @_;

  my $connStr;
    
  if(!$dbUsername && !$dbPassword){
    # assume OS authentication if user/pass are null
    EMD_PERL_DEBUG("dbstate.constructConnectStr: No db credentials, assuming OS authentication");
    ## Ensure these params are empty strings
    $dbUsername = "";
    $dbPassword = "";

    # Need to preserve TNS for RAC full connect descriptors:
    # $tns = ""; 
  }
  if (!($dbRole =~ /$DBROLE_SYSDBA/i)){
    # assume OS authentication if the db role is NOT SYSDBA (needed by backup/recovery)
    ## Ensure these params are empty strings
    $dbUsername = "";
    $dbPassword = "";
    $dbRole = $DBROLE_SYSDBA;
  }
  
  if ($dbRole =~ /$DBROLE_NORMAL/i || $dbRole eq '') {
    # no $dbRole supplied
    if(defined($tns) && $tns ne ""){
        $connStr = "${dbUsername}/${dbPassword}\@${tns}";
    }
    else{
        $connStr = "${dbUsername}/${dbPassword}";
    }
  }
  else{
    # $dbRole supplied
    if(defined($tns) && $tns ne ""){
        $connStr = "${dbUsername}/${dbPassword}\@${tns} as $dbRole"; 
    }
    else{
        $connStr = "${dbUsername}/${dbPassword} as $dbRole";
    }
  }

  return $connStr;
}


# Purpose: find following error codes in input string:
#          "PRKP"
#          "PRKH"
#          "PRKO"
#          "CRS"
#          "ORA"
sub has_ec
{
    my($in_str) = $_[0];

#    print "error code detector: $in_str\n";

    if ($in_str =~ /PRKP-/ || $in_str =~ /PRKH-/ || $in_str =~ /PRKO-/ || $in_str =~ /CRS-/ || $in_str =~ /ORA-/) {
	return(-1);  # error mesg
    }
    else {
	return(0);
    }
    
}

## The following method is written to workaround bug 3469118.
## For pre-10g NT rac, if PATH env doesn't include $oracleHome/bin, 
## the srvctl will return error when invoked not within $oracleHome/bin. 
## The workaround for this bug is to change the current dir to $oracleHome/bin 
## before calling srvctl and set it back when finished
##
# Return the original current dir if it's changed
# Otherwise return ""
sub changeCWDForSrvctlIfNecessary
{
    my ($oracleHome, $srvctlVersion) = @_;
    my $orgDIR = "";
    
    if ($NT && $srvctlVersion eq '9.2')
    {    
        # This is a NT pre-10g srvctl
        $orgDIR = getcwd;
        chdir "$oracleHome/bin";
    }
    
    $orgDIR;
}

## Determine if the specified database in a Data Guard
## broker configuration.
sub isDGBrokerConfig
{
  @SQLCMDS = ("");
  my ($user, $pw, $tns, $ohome, $sid) = @_;

  ## If $ohome and $sid are defined, this is OS auth; connect
  ##  descriptor not needed
  if($ohome && $sid){
    &set_db_var($ohome, $sid, 1); 
  }

  my $dir = tempdir(CLEANUP => 1);
  my ($tempfh, $tempfile);  
  if (!$NT)
  {
    ($tempfh, $tempfile) = tempfile( DIR => $dir );
  }
  else 
  {
    $tempfile = "$dir\\"."dgtest.$$";  ## create unique file name
  }

  my $sql = "set echo off;\n";
  $sql .= "set serveroutput on;\n";
  $sql .= "variable result number;\n";
  $sql .= "BEGIN\n";
  $sql .= "  select count(*) INTO :result from x\$vinst;\n";
  $sql .= "  dbms_output.put_line('result='||:result);\n";
  $sql .= "END;\n";
  $sql .= "/\n";
  $sql .= "EXIT;\n";

  push(@SQLCMDS, $sql);

  executeSQLPlusSYSDBA($user, $pw, $tns, $tempfile);

  open (DGOUTPUT, "$tempfile") || die "dbstate.isDGBrokerConfig: Unable to open temp file: $tempfile";
    
  my @output;
  my $result = 0;
  while (<DGOUTPUT>){
    if($_ =~ /=/){
      @output = split /=/, $_;
      $result = $output[1];
      last;
    }
  }

  close(DGOUTPUT);
  removeFile ($tempfile);

  if($result > 0){
    EMD_PERL_DEBUG("dbstate.isDGBrokerConfig: is broker config ");
    return "1";
  }
  EMD_PERL_DEBUG("dbstate.isDGBrokerConfig: not broker config ");
  return "0";
}

## Determine if the specified database is in a Data Guard
## configuration, NOT managed by DG broker.
sub isDGNonBrokerConfig
{
  @SQLCMDS = ("");
  my ($user, $pw, $tns, $ohome, $sid) = @_;

  ## If $ohome and $sid are defined, this is OS auth; connect
  ##  descriptor not needed
  if($ohome && $sid){
    &set_db_var($ohome, $sid, 1); 
  }

  my $dir = tempdir(CLEANUP => 1);
  my ($tempfh, $tempfile);  
  if (!$NT)
  {
    ($tempfh, $tempfile) = tempfile( DIR => $dir );
  }
  else 
  {
    $tempfile = "$dir\\"."dgtest.$$";  ## create unique file name
  }

  my $sql = "set echo off;\n";
  $sql .= "set serveroutput on;\n";
  $sql .= "variable result number;\n";
  $sql .= "BEGIN\n";
  $sql .= "  select case when (((select count(*) from v\$archive_dest where target='STANDBY') > 0) OR (database_role like '\%STANDBY')) and ((select count(*) from x\$vinst) = 0) then '1' else '0' end INTO :result from v\$database;\n";
  $sql .= "  dbms_output.put_line('result='||:result);\n";
  $sql .= "END;\n";
  $sql .= "/\n";
  
  push(@SQLCMDS, $sql);
  
  executeSQLPlusSYSDBA($user, $pw, $tns, $tempfile);
  
  open (DGOUTPUT, "$tempfile") || die "dbstate.isDGNonBrokerConfig: Unable to open temp file: $tempfile";
  
  my @output;
  my $result = 0;
  while (<DGOUTPUT>){
    if($_ =~ /=/){
      @output = split /=/, $_;
      $result = $output[1];
      last;
    }
  }

  close(DGOUTPUT);
  removeFile ($tempfile);
  
  if($result == 1){
    EMD_PERL_DEBUG("dbstate.isDGNonBrokerConfig: is non-broker config ");
    return "1";
  }
  EMD_PERL_DEBUG("dbstate.isDGNonBrokerConfig: not non-broker config ");
  return "0";
}

## Determine if the specified database is in a Data Guard
## configuration, broker or non-broker
sub isDGConfig{
  @SQLCMDS = ("");
  my ($user, $pw, $tns, $ohome, $sid) = @_;
  if(isDGBrokerConfig($user, $pw, $tns, $ohome, $sid) || isDGNonBrokerConfig($user, $pw, $tns, $ohome, $sid)){
    EMD_PERL_DEBUG("dbstate.isDGConfig: is DG config ");
    return "1";
  }

  EMD_PERL_DEBUG("dbstate.isDGConfig: not DG config ");
  return "0";
}
  
## Determine if the specified database is 
## participating in a fast-start failover configuration
sub isFSFOEnabled
{
  @SQLCMDS = ("");
  my ($user, $pw, $tns, $ohome, $sid) = @_;

  ## If $ohome and $sid are defined, this is OS auth; connect
  ##  descriptor not needed
  if($ohome && $sid){
    &set_db_var($ohome, $sid, 1); 
  }

  my $dir = tempdir(CLEANUP => 1);
  my ($tempfh, $tempfile);  
  if (!$NT)
  {
    ($tempfh, $tempfile) = tempfile( DIR => $dir );
  }
  else 
  {
    $tempfile = "$dir\\"."dgtest.$$";  ## create unique file name
  }

  my $sql = "set echo off;\n";
  $sql .= "set serveroutput on;\n";
  $sql .= "variable result varchar2(64);\n";
  $sql .= "BEGIN\n";
  $sql .= "  select FS_FAILOVER_STATUS INTO :result from v\$database;\n";
  $sql .= "  dbms_output.put_line('result='||:result);\n";
  $sql .= "END;\n";
  $sql .= "/\n";
  
  push(@SQLCMDS, $sql);
  
  executeSQLPlusSYSDBA($user, $pw, $tns, $tempfile);
  
  open (DGOUTPUT, "$tempfile") || die "dbstate.isFSFOEnabled: Unable to open temp file: $tempfile";
  
  my @output;
  my $result = 0;
  while (<DGOUTPUT>){
    if($_ =~ /=/){
      @output = split /=/, $_;
      $result = $output[1];
      last;
    }
  }

  close(DGOUTPUT);
  removeFile ($tempfile);
  
  if($result !~ /DISABLED/){
    EMD_PERL_DEBUG("dbstate.isFSFOEnabled: FSFO enabled");
    return "1";
  }
  EMD_PERL_DEBUG("dbstate.isFSFOEnabled: FSFO disabled");
  return "0";
}

1;