#!/usr/local/bin/perl
# 
# $Header: db_instance.pl 31-may-2007.11:39:08 kdas Exp $
#
# db_instance.pl
# 
# Copyright (c) 2002, 2007, Oracle. All rights reserved.  
#
#    NAME
#      db_instance.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)
#    kdas        05/31/07 - fix for 6079211
#    xshi        05/15/07 - bug5591354 handle & in password
#    kdas        04/09/07 - fix for 5937948
#    rgiroux     02/07/07 - XbranchMerge rgiroux_bug-5768229 from main
#    kdas        01/30/07 - XbranchMerge kdas_bug-5768229 from st_emdbsa_11.1
#    kdas        06/17/05 - fix for 4434018 
#    kdas        02/15/05 - fix for 4175468
#    kdas        11/01/04 - fix for 3980057 
#    kdas        10/22/04 - support for 3088641 
#    hying       06/03/04 - no open db with resetlogs option for standby 
#    hying       03/23/04 - Open db with resetlogs 
#    ychan       10/23/03 - Fix bug 3173824 
#    kdas        09/15/03 - fix for 3139381 
#    kdas        08/26/03 - fix for 3113830 
#    kdas        07/03/03 - fix for 3033825
#    kdas        05/22/03 - bugfix
#    kdas        04/03/03 - fix for 2835853
#    kdas        12/06/02 - fix for 2695040
#    kdas        09/24/02 - cleanup
#    ychan       09/18/02 - ychan_move_dbscript
#    kdas        08/28/02 - remove debug stmt
#    kdas        08/14/02 - fix for library path
#    kdas        07/19/02 - fix for exitcode
#    kdas        07/16/02 - Creation
# 
use File::Temp qw/ tempfile tempdir /;
use FileHandle;
use IPC::Open2;
use vars qw/ $OS $NT $S $TEMP $CP $MV $PS $DF $DELIMITER/;
require "$ENV{EMDROOT}/sysman/admin/scripts/db/db_common.pl";

#Wrapper function to change database state using sqlplus.
sub change_db_state
{
    $oracle_home=fixOracleHome($oracle_home);
    &set_env_var($oracle_home, $oracle_sid);

    
    $retval=executeSQLPlusScriptUniDirPipe();
    
    if (!$NT)
    {
    system("emctl reload agent dynamicproperties $target_name:$target_type");
    }
}

#This method assumes 2 array variables - @sqlListParseError and @sqlList.
#@sqlListParseError should contain sqls that donot support sqlerror eg. startup#and shutdown
#@sqlList should contain any other sql
#These should no longer be needed once sqlplus supports sqlerror in 10i.
sub executeSQLPlusScript
{

    $|=1;
    $pid = open2(\*RDRFH, \*SQL_WRITER, "$ENV{ORACLE_HOME}/bin/sqlplus /nolog");
    $old_fh = select(SQL_WRITER);
    $| = 1;
    select($old_fh);  

    # handle special characters in password: & add escape
    my $password_copy = $password;
    if ($password_copy =~ m/&/)
    {
        my $e = &find_escape($password_copy);
        print SQL_WRITER "set escape $e\n";
        $password_copy =~ s/&/$e&/g;
    }
if ($password)
{    
    if ($role)
    {
	print SQL_WRITER "connect $username/\"$password_copy\" as $role\n";
    }
    else
    {
	print SQL_WRITER "connect $username/\"$password_copy\"\n";
    }
}
else
{
     if ($role)
    {
	print SQL_WRITER "connect $username/$password as $role\n";
    }
    else
    {
	print SQL_WRITER "connect $username/$password \n";
    }
}
    if($? !=0)
    {
	exit 100;
    }
    $arrayIndex = 0;
    while($arrayIndex <@sqlListParseError)
{
    print SQL_WRITER "$sqlListParseError[$arrayIndex]";
    print SQL_WRITER "prompt EOTMARKER;\n";
    
    $markerIndex =0;
    $fullBuf ="";
    do {
	$sysret = sysread RDRFH, $buf, 100;
	$fullBuf=$fullBuf.$buf;
	$markerIndex = index($fullBuf, "EOTMARKER",0);
    } while (($sysret != 0) &&($markerIndex==-1));
    $computedBuf =substr $fullBuf,0, $markerIndex;
    print "$computedBuf\n";
    
#ignore ORA-01109 error: error trying to close not open database
#ignore ORA-32006 :deprecated parameter warning for instance startup
 #ignore ORA-01507: error trying to unmount started database during shutdown  
    $errorIndex = -2;
     while ($errorIndex>0 || $errorIndex==-2) {
	$errorIgnore1Index = index($computedBuf, "ORA-01109",0);
	$errorIgnore2Index = index($computedBuf, "ORA-32006",0);
	$errorIgnore3Index = index($computedBuf, "ORA-01507",0);
	$errorIndex = index($computedBuf, "ORA-",0);
	if($errorIndex!=-1)
	{
	    if($errorIndex == $errorIgnore1Index || ($errorIndex == $errorIgnore2Index) || ($errorIndex == $errorIgnore3Index))
	    {
		$remainingStr=substr($computedBuf,$errorIndex+1);
		$computedBuf =$remainingStr;
#ignore this error, continue looking for more ORA errors
		$errorIndex=-2;

	    }
	    else
#valid ORA error
	    {
		last;
	    }

	}

    } ;

    if($errorIndex !=-1)
{
    print SQL_WRITER "exit;\n";
    close SQL_WRITER;
    exit 100;
}


$arrayIndex++;
}
$arrayIndex = 0;
if($arrayIndex <@sqlList)
{
    print SQL_WRITER "whenever sqlerror exit sql.sqlcode;\n";
}
while ($arrayIndex <@sqlList)
{
    print SQL_WRITER "$sqlList[$arrayIndex]";
    $arrayIndex++;
}
$arrayIndex = 0;
if($arrayIndex <@sqlListNoError)
{
    print SQL_WRITER "whenever sqlerror continue;\n";
}
while ($arrayIndex <@sqlListNoError)
{
    print SQL_WRITER "$sqlListNoError[$arrayIndex]";
    $arrayIndex++;
}
print SQL_WRITER "exit;\n";
close SQL_WRITER;
$toExitWithError = 0;
if($? !=0)
{ 
    $toExitWithError = 1;
}
$fullBuf ="";
do {
    $sysret = sysread RDRFH, $buf, 100;
    $fullBuf=$fullBuf.$buf;
} while ($sysret != 0);
$errorIndex = index($fullBuf, "ORA-",0);
print "$fullBuf\n";
if($? !=0)
{ 
    $toExitWithError = 1;
}
if($toExitWithError ==1)
{
    exit 100; 
}
if($errorIndex !=-1)
{
    exit 100;
}



}

sub startupWithInitParameters
{
    &set_env_var($oracle_home, $oracle_sid);
    my $dir = tempdir( CLEANUP => 1);
    (my $fh, my $filename) = tempfile( DIR => $dir );

    if($NT)
    {
	$TEMP = "C:\\TEMP";
	&mkDir($TEMP);
	$mytime = time();
	$filename = "$TEMP\\dbinstora.$mytime";
    }

    open (PFILE_WRITER , "+>$filename");
    print PFILE_WRITER "$initParameters";
    close (PFILE_WRITER);
    close $fh;

    $arrayElem = "shutdown $shutdownMode;\n";
    @sqlListParseError = (@sqlListParseError,$arrayElem);
    $arrayElem = "startup  $startupMode pfile=$filename;\n";
    @sqlListParseError = (@sqlListParseError,$arrayElem);

    $retval=executeSQLPlusScriptUniDirPipe();

    if($NT)
    {
	&removeFile($filename);
    }
}


sub startupWithInitParametersAndChangeArchiveLogMode
{
    &set_env_var($oracle_home, $oracle_sid);
    my $dir = tempdir( CLEANUP => 1);
    (my $fh, my $filename) = tempfile( DIR => $dir );
    if($NT)
    {
	$TEMP = "C:\\TEMP";
	&mkDir($TEMP);
	$mytime = time();
	$filename = "$TEMP\\dbinstarch.$mytime";
    }
    open (PFILE_WRITER , "+>$filename");
    print PFILE_WRITER "$initParameters";
    close (PFILE_WRITER);
    close $fh;

    $arrayElem = "shutdown $shutdownMode;\n";
    @sqlListParseError = (@sqlListParseError,$arrayElem);
    $arrayElem = "startup  mount pfile=$filename;\n";
    @sqlListParseError = (@sqlListParseError,$arrayElem);
    $arrayElem = "alter database $archiveLogMode;\n";
    @sqlList = (@sqlList,$arrayElem);
    $arrayElem = "alter database open;\n";
    @sqlList = (@sqlList,$arrayElem);

   $retval=executeSQLPlusScriptUniDirPipe();

    system("emctl reload agent dynamicproperties $target_name:$target_type");

    if($NT)
    {
	&removeFile($filename);
    }
}



sub get_db_status
{
    &set_env_var($oracle_home, $oracle_sid);
    my $dir = tempdir( CLEANUP => 1);
    (my $fh, my $filename) = tempfile( DIR => $dir );

    if($NT)
    {
	$TEMP = "C:\\TEMP";
	&mkDir($TEMP);
	$mytime = time();
	$filename = "$TEMP\\dbinstance.$mytime";
    }
    open(SQL_WRITER, "|$ENV{ORACLE_HOME}/bin/sqlplus /nolog >$filename")
	|| die "Can not open pipe for DB_STATUS";
    print SQL_WRITER "whenever sqlerror exit sql.sqlcode\n";

    # handle special characters in password: & add escape
    my $password_copy = $password;
    if ($password_copy =~ m/&/)
    {
        my $e = &find_escape($password_copy);
       print SQL_WRITER "set escape $e\n";
        $password_copy =~ s/&/$e&/g;
    }
if($password)
{
    if ($role)
    {
	print SQL_WRITER "connect $username/\"$password_copy\" as $role\n";
    }
    else
    {
	print SQL_WRITER "connect $username/\"$password_copy\"\n";
    }
}
else
{
     if ($role)
    {
	print SQL_WRITER "connect $username/$password as $role\n";
    }
    else
    {
	print SQL_WRITER "connect $username/$password \n";
    }
}
    print SQL_WRITER "whenever sqlerror continue\n";
    print SQL_WRITER "select status from v\$instance;\n";
    print SQL_WRITER "exit;\n";
    close SQL_WRITER;

    open (DB_STATUS, "$filename") || die "Unable to open tmp file\n";
    while ($_=<DB_STATUS>)
    {
	$fullOutput=$fullOutput.$_;
	if (/OPEN/)
	{
	    close DB_STATUS;
	    print "OPEN";
	    if($NT)
	    {
		&removeFile($filename);
	    }
	    return;
	}
	if (/MOUNTED/)
	{
	    close DB_STATUS;
	    print "MOUNTED";
	    if($NT)
	    {
		&removeFile($filename);
	    }
	    return;
	}
	if (/STARTED/)
	{
	    close DB_STATUS;
	    print "STARTED";
	    if($NT)
	    {
		&removeFile($filename);
	    }
	    return;
	}
    }

    close DB_STATUS;
    close $fh;
    
    if($? !=0)
{
    print "$fullOutput\n";
    if($NT)
    {
	&removeFile($filename);
    }
    exit 100;
}
print "SHUTDOWN";
if($NT)
{
    &removeFile($filename);
}
return;
}

sub get_db_open_resetlogs_option
{
    &set_env_var($oracle_home, $oracle_sid);
    my $dir = tempdir( CLEANUP => 1);
    (my $fh, my $filename) = tempfile( DIR => $dir );

    if($NT)
    {
	$TEMP = "C:\\TEMP";
	&mkDir($TEMP);
	$mytime = time();
	$filename = "$TEMP\\dbinstance.$mytime";
    }
    open(SQL_WRITER, "|$ENV{ORACLE_HOME}/bin/sqlplus /nolog >$filename")
	|| die "Can not open pipe for DB_STATUS";
    print SQL_WRITER "whenever sqlerror exit sql.sqlcode\n";

    # handle special characters in password: & add escape
    my $password_copy = $password;
    if ($password_copy =~ m/&/)
    {
        my $e = &find_escape($password_copy);
        print SQL_WRITER "set escape $e\n";
        $password_copy =~ s/&/$e&/g;
    }
if ($password)
{
    if ($role)
    {
	print SQL_WRITER "connect $username/\"$password_copy\" as $role\n";
    }
    else
    {
	print SQL_WRITER "connect $username/\"$password_copy\"\n";
    }
}
else
{
     if ($role)
    {
	print SQL_WRITER "connect $username/$password as $role\n";
    }
    else
    {
	print SQL_WRITER "connect $username/$password \n";
    }
}
    print SQL_WRITER "whenever sqlerror continue\n";
    print SQL_WRITER "select controlfile_type, open_resetlogs from v\$database;\n";
    print SQL_WRITER "exit;\n";
    close SQL_WRITER;

    open (DB_OPTION, "$filename") || die "Unable to open tmp file\n";
    while (<DB_OPTION>)
    {
        ## return "NOT ALLOWED" if controlfile_type is STANDBY
	if (/NOT ALLOWED/ || /STANDBY/)
	{
	    close DB_OPTION;
	    print "NOT ALLOWED";
	    if($NT)
	    {
		&removeFile($filename);
	    }
	    return;
	}
	if (/REQUIRED/)
	{
	    close DB_OPTION;
	    print "REQUIRED";
	    if($NT)
	    {
		&removeFile($filename);
	    }
	    return;
	}
	if (/ALLOWED/)
	{
	    close DB_OPTION;
	    print "ALLOWED";
	    if($NT)
	    {
		&removeFile($filename);
	    }
	    return;
	}
    }

    close DB_OPTION;
    close $fh;
    
    if($? !=0)
    {
        print "sql error: $?\n";
        if($NT)
        {
            &removeFile($filename);
        }
        exit 100;
    }
    print "NOT ALLOWED";
    if($NT)
    {
        &removeFile($filename);
    }
    return;
}


# This version of executeSQLPlusScript doesnot support parsinf og intermediate #error messages
sub executeSQLPlusScriptUniDirPipe
{
    my $dir = tempdir( CLEANUP => 1);
    (my $fh, my $filename) = tempfile( DIR => $dir );
    
    if($NT)
    {
	$TEMP = "C:\\TEMP";
	&mkDir($TEMP);
	$mytime = time();
	$filename = "$TEMP\\dbinstance.$mytime";
    }
    open(SQL_WRITER, "|$ENV{ORACLE_HOME}/bin/sqlplus /nolog >$filename")
	|| die "Can not open pipe for DB_STATUS";

    # handle special characters in password: & add escape
    my $password_copy = $password;
    if ($password_copy =~ m/&/)
    {
        my $e = &find_escape($password_copy);
        print SQL_WRITER "set escape $e\n";
        $password_copy =~ s/&/$e&/g;
    }
if ($password)
{
    if ($role)
    {
	print SQL_WRITER "connect $username/\"$password_copy\" as $role\n";
    }
    else
    {
	print SQL_WRITER "connect $username/\"$password_copy\"\n";
    }
}
else
{
     if ($role)
    {
	print SQL_WRITER "connect $username/$password as $role\n";
    }
    else
    {
	print SQL_WRITER "connect $username/$password \n";
    }
}
    $arrayIndex = 0;
    while ($arrayIndex <@shutdownSqlList)
{
    print SQL_WRITER "$shutdownSqlList[$arrayIndex]";
    $arrayIndex++;
}
    print SQL_WRITER "whenever sqlerror exit sql.sqlcode;\n";
    $arrayIndex = 0;
    while ($arrayIndex <@sqlListParseError)
{
    print SQL_WRITER "$sqlListParseError[$arrayIndex]";
    $arrayIndex++;
}
$arrayIndex = 0;
while ($arrayIndex <@sqlList)
{
    print SQL_WRITER "$sqlList[$arrayIndex]";
    $arrayIndex++;
}
$arrayIndex = 0;
if($arrayIndex <@sqlListNoError)
{
    print SQL_WRITER "whenever sqlerror continue;\n";
}
while ($arrayIndex <@sqlListNoError)
{
    print SQL_WRITER "$sqlListNoError[$arrayIndex]";
    $arrayIndex++;
}
print SQL_WRITER "exit;\n";
close SQL_WRITER;

$toExitWithError = 0;
if($? !=0)
{
    $toExitWithError = 1;
}
open (DB_STATUS, "$filename") || die "Unable to open tmp file\n";
while (<DB_STATUS>)
{
    print;
}
close DB_STATUS;
if($toExitWithError ==1)
{
    if($NT)
    {
	&removeFile($filename);
    }
    exit 100; 
}
if($NT)
{
    &removeFile($filename);
}

}

sub fixOracleHome($oracle_home)
{
    $len = length $oracle_home;
    $last = substr $oracle_home, $len-1, 1;
    if(($last eq "/") || ($last eq "\\"))
{
	$oracle_home=substr $oracle_home, 0,$len-1;
}
    return $oracle_home;
}

# Get a special char that is not included in the password as escape char
# Called in three places: db_instance.pl, ha_dbstate_o.pl and rman_o,pl
# Should be put in a common file like db_common.pl
sub find_escape()
{
  my ($password_str) = @_;
  my @specialchars = ('!', '~', '#', '_', '`');
  my $escape = q/!/;
  for my $char (@specialchars)
  {
    $escape = $char;
    if ( $password_str !~ m/$char/)
    {
      last;
    }
  }
  return $escape;
}

1;