#!/usr/local/bin/perl
# 
# $Header: initParameterFileUtl.pl 10-aug-2006.13:17:08 rsamaved Exp $
#
# initParameterFileUtl.pl
# 
# Copyright (c) 2002, 2006, Oracle. All rights reserved.  
#
#    NAME
#      initParameterFileUtl.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)
#    rsamaved    08/10/06 - check flash recovery size on windows
#    sxzhu       03/07/06 - fix bug 5232052: remove temp files 
#    jaronovi    01/24/06 - fixe system command quotes 
#    jstone      06/09/05 - 
#    jstone      05/31/05 - variable name typo rolse => role 
#    jstone      04/25/05 - execSQL_with_time_limit
#    xuliu       03/22/05 - fix 4067474 
#    xuliu       10/27/04 - hang solution 
#    xuliu       03/01/04 - bypass file existance checking in findInitParameterValueByName
#    xuliu       02/17/04 - fix 3450484 
#    xuliu       11/07/03 - pass dontTrimQuotes to the recursive call 
#    dkapoor     10/10/03 - add arg for unquoted param value 
#    xuliu       10/07/03 - pfile permssion (fix 3174326) 
#    lhan        09/24/03 - Fix discover bug 3136806 
#    xuliu       09/05/03 - robust sqlplus code 
#    xuliu       08/20/03 - dif way to get temp file 
#    xuliu       01/06/03 - logging
#    dkapoor     11/04/02 - set lib path
#    xuliu       09/27/02 - windows case in getParameterFile
#    xuliu       09/18/02 - refine findInitParameterValueByName()
#    xuliu       08/27/02 - convert spfile to pfile using sqlplus
#    xuliu       08/21/02 - xuliu_perf
#    xuliu       08/20/02 - Creation
# 

use strict;
use IO::File;
use POSIX qw(tmpnam);

my ($emdRoot,$hostName) = @ARGV;
require "$emdRoot/sysman/admin/scripts/semd_common.pl";
require "$emdRoot/sysman/admin/scripts/emd_common.pl";

# when a parameter is defined without specifying sid, the default sid is '*'
my $DEFAULT_PARAM_SID = "*";

# Hashtable contains all temporarily converted spfile
my %pfileOfSpfile;

# error message.  
my $INIT_PARAMETER_FILE_UTL_ERROR_MSG = "";

# cleanup
END {
    my $spfile;
    if (%pfileOfSpfile)
    {
        foreach my $spfile (keys %pfileOfSpfile)
        {
            unlink($pfileOfSpfile{$spfile});
        }
    }    
}


# Usage: discoverParameterValue($oracleHome, $sid, $param_name, $param_sid)
# Return the value of $param_sid.$param_name parameter in array
# If the returned value is empty, it means either there is no such parameter specified
# in the parameter files, or an error occurs during the discovery. 
# Use the function checkErrorMessage() after this call to check the details
sub discoverParameterValue
{
    my ($oracleHome, $sid, $param_name, $param_sid,$dontTrimQuotes) = @_;

    # clear the error message
    $INIT_PARAMETER_FILE_UTL_ERROR_MSG = "";

    my ($type, $paramFile) = getParameterFile($oracleHome, $sid);
    my @vals;
    
    if ($paramFile ne "")
    {
       @vals = retrieveParamValue($oracleHome, $sid, $type, $paramFile, $param_name, $param_sid,$dontTrimQuotes);
    }    
    
    if (!@vals)
    {
        @vals = ("");
    }     
    
    @vals;
}    

# This function returns the error message during last call of discoverParameterValue()
# If the returned value is not empty, it means some error occurs in last discoverParameterValue()
# If the returned value is empty, it means last call succeeded.
sub checkErrorMessageInParameterDiscovery
{
    $INIT_PARAMETER_FILE_UTL_ERROR_MSG;
}    

#################################################################################
#      The following functions are intended to be used inside this package      #
#################################################################################

# Usage: getParameterFile($oracleHome, $sid)
# Return ($type, $filePath) 
# $filePath is the parameter file the instance will use
# $type indicates if it's a spfile ($type == 0) or pfile ($type == 1)
# 
# It searchs in the following order
# Unix:
#   1.$oracleHome/dbs/spfile$sid.ora 
#   2.$oracleHome/dbs/spfile.ora 
#   3.$oracleHome/dbs/init$sid.ora 
# Windows
#   1.$oracleHome\database\spfile$sid.ora 
#   2.$oracleHome\database\spfile.ora 
#   3.$oracleHome\database\init$sid.ora 
sub getParameterFile
{
    my ($oracleHome, $sid) = @_;
    my ($type, $rst) = (0, "");

    my $dbDir = "dbs";
    
    if (get_osType() eq 'WIN')
    {
        # windows
        $dbDir = "database";
    }
    
    if (-e "$oracleHome/$dbDir")
    {
        if (-e "$oracleHome/$dbDir/spfile$sid.ora")
        {
            $rst = "$oracleHome/$dbDir/spfile$sid.ora";
        }
        elsif (-e "$oracleHome/$dbDir/spfile.ora")
        {
            $rst = "$oracleHome/$dbDir/spfile.ora";
        }
        elsif (-e "$oracleHome/$dbDir/init$sid.ora")
        {
            $rst = "$oracleHome/$dbDir/init$sid.ora";
            $type = 1;
        }
        else
        {
           $INIT_PARAMETER_FILE_UTL_ERROR_MSG = 
                "Cannot find any init parameter file for instance $sid in oracle home $oracleHome/$dbDir";
           
           EMD_PERL_ERROR("initParameterFileUtl::getParameterFile: $INIT_PARAMETER_FILE_UTL_ERROR_MSG"); 
        }
    }    
    else
    {
       $INIT_PARAMETER_FILE_UTL_ERROR_MSG = "$oracleHome/$dbDir doesn't exist";
       EMD_PERL_ERROR("initParameterFileUtl::getParameterFile: $INIT_PARAMETER_FILE_UTL_ERROR_MSG"); 
    }
            
    ($type, $rst);
}    
# Usage: retrieveParamValue($oracleHome, $sid, $type, $paramFile, $parameter, $param_sid)
# Logic:
#   if "$param_sid.$parameter" exists, return its values
#   elsif "*.$parameter" exists, return its values
# Return the values of the $param_sid.$parameter in an array
sub retrieveParamValue
{
    my ($oracleHome, $sid, $type, $paramFile, $parameter, $param_sid, $dontTrimQuotes, $ignore_param_sid_case) = @_;
    my %paramVals;
    my @paramFileStack = ($paramFile);
    ($parameter) = escapeRegExpSymbol($parameter);
    my $sidIFileProcessed = 0;
    my $sidSPFileProcessed = 0;
    
    my $rst = findInitParameterValueByName($oracleHome, $sid, $type, $paramFile, 
        $parameter, \%paramVals, \@paramFileStack, \$sidIFileProcessed, \$sidSPFileProcessed,$dontTrimQuotes);
    
    my @vals;
    
    if ($rst)
    {
        if (get_osType() eq 'WIN')
        {
            #we need to figure out the correct case of the sid for the init param
            foreach my $aSid (keys %paramVals)
            {
                if (uc($aSid) eq uc($param_sid))
                {
                    $param_sid = $aSid;
                    last;
                }
            }
        }    
        
        if (defined($paramVals{$param_sid}))
        {
            @vals = @{$paramVals{$param_sid}};
        }
        elsif (defined($paramVals{"*"}))
        {
	      @vals = @{$paramVals{"*"}};
        }
    }
    
    @vals;
}

#Usage: findInitParameterValueByName($oracleHome, $sid, $type, $paramFile, $parameter, 
#                  \%paramVals, \@paramFileStack, \$sidIFileProcessed, \$sidSPFileProcessed,$dontTrimQuotes)
#Put the parameter values in %paramVals as the following:
# %paramVals = ( 
#             "sid1" => [ "value" ],            # sid1.$parameter = value
#             "*" => ["rac921", "rac922"],      # *.$parameter = ('rac921', 'rac922') 
#                                               #   or $parameter = ('rac921', 'rac922') 
#           )  
# Return 1 if search completes
# Return 0 if error occurs. Error may be caused by: 
#   a. $paramFile not existing; 
#   b. failed to convert spfile to pfile; 
#   c. failed to open $paramFile
#   d. there is loop in the ifile/spfile link
sub findInitParameterValueByName
{
    my ($oracleHome, $sid, $type, $paramFile, $parameter, $paramVals, 
            $paramFileStack, $sidIFileProcessed, $sidSPFileProcessed,$dontTrimQuotes) = @_;

    my $asm = 0;
    if ( $paramFile =~ /^\+(.*)$/ )
    {
	    $asm = 1;
    }

    # Error a. $paramFile not existing; 
    #if (!$asm && (! -e $paramFile))
    #{
    #    $INIT_PARAMETER_FILE_UTL_ERROR_MSG = "$paramFile doesn't exist.";
    #    EMD_PERL_ERROR("initParameterFileUtl::findInitParameterValueByName: $INIT_PARAMETER_FILE_UTL_ERROR_MSG");
    #    return 0;
    #}
        
    if (!$type)
    {
        #convert spfile to pfile
        $paramFile = convertSPFileToPFile($oracleHome, $sid, $paramFile);
        
        # Error b. cannot convert spfile to pfile;  
        return 0 if ($paramFile eq "");
    }    
    
    if (open(PFILE, "<$paramFile"))
    {
        my @lines = <PFILE>;
        close(PFILE);
        
        foreach my $line (@lines)
        {
            $line = trimBlank(trimComments($line));
            if ($line =~ /^(.*)\.$parameter\s*=\s*(.*)$/i)
            {
                my $paraSid = ($1 eq "")? $DEFAULT_PARAM_SID : $1;
		        if($dontTrimQuotes)
		        {
                	$paramVals->{$paraSid} = [split(/,\s*/, trimBraces($2))];
		        }
		        else
		        {
                	$paramVals->{$paraSid} = [map {trimQuote($_)} split(/,\s*/, trimBraces($2))];
		        }
            }
            elsif ($line =~ /^$parameter\s*=\s*(.*)$/i)
            {
		        if($dontTrimQuotes)
		        {
                	$paramVals->{$DEFAULT_PARAM_SID} = [split(/,\s*/, trimBraces($1))];
		        }
		        else
		        {
                	$paramVals->{$DEFAULT_PARAM_SID} = [map {trimQuote($_)} split(/,\s*/, trimBraces($1))];
		        }
            }
            elsif ($line =~ /^(.*\.|)(ifile)\s*=\s*(.*)$/i || $line =~ /^(.*\.|)(spfile)\s*=\s*(.*)$/i)
            {
                my $paraSid = $1;
                my $childFile = getRealFilePath($oracleHome, $sid, trimQuote($3));
                my $childFileType = ($2 =~ /^ifile$/i);        		    
                
                # chomp the last . if any
                $paraSid =~ s/\.$//;
                $paraSid = $DEFAULT_PARAM_SID if ($paraSid eq "");
                
                if ($paraSid eq $sid || 
                        ($paraSid eq "*" && 
                            ((!$childFileType && !$$sidSPFileProcessed) 
                                ||($childFileType && !$$sidIFileProcessed)
                            )
                        )
                   )         
                {  
                    if ($paraSid eq $sid)
                    {
                        $$sidSPFileProcessed = 1 if (!$childFileType); #spfile
                        $$sidIFileProcessed = 1 if ($childFileType);
                    }
                                            
                    if (elementExists($paramFileStack, $childFile))
        		    {
     	    	        # Error d. there is loop in the ifile/spfile link
     	    	        $INIT_PARAMETER_FILE_UTL_ERROR_MSG = "A loop is found when following ifile/spfile links.";
     	    	        EMD_PERL_ERROR("initParameterFileUtl::findInitParameterValueByName: $INIT_PARAMETER_FILE_UTL_ERROR_MSG");
     	    	        return 0;
                    }

                    push @$paramFileStack, $childFile;
                    my $rst = findInitParameterValueByName($oracleHome, $sid, $childFileType, 
                        $childFile, $parameter, $paramVals, $paramFileStack, $sidIFileProcessed, $sidSPFileProcessed, $dontTrimQuotes);
    		        pop @$paramFileStack;
    		        
    		        # Error from the child call
    		        return 0 if (!$rst);
    		    }
		    }
		}
        
        # Success
        return 1;
    }

    # Error c. cannot open $paramFile
    if (!$asm)
    {
	$INIT_PARAMETER_FILE_UTL_ERROR_MSG = "Cannot open init parameter file $paramFile.";
	EMD_PERL_ERROR("initParameterFileUtl::findInitParameterValueByName: $INIT_PARAMETER_FILE_UTL_ERROR_MSG");
    }
    0; 
}

# Usages: getRealFilePath($oracleHome, $sid, $file)
# return the real file path specified by the value of ifile or spfile
sub getRealFilePath
{
   my ($oracleHome, $sid, $file) = @_;
   
   my $oldOH  = $ENV{ORACLE_HOME} if (defined $ENV{ORACLE_HOME});
   my $oldSID = $ENV{ORACLE_HOME} if (defined $ENV{ORACLE_SID});
   
   ($ENV{ORACLE_HOME}, $ENV{ORACLE_SID}) = ($oracleHome, $sid);
   
   # ? stands for ORACLE_HOME and @ for ORACLE_SID
   $file =~ s/\?/$oracleHome/g;
   $file =~ s/@/$sid/g;
    
   #windows environment variable, e.g. %ORACLE_HOME%    
   $file =~ s/%(\w+)%/$ENV{$1}/g;
   
   #unix environment variable, e.g. $ORACLE_HOME or ${ORACLE_SID}
   $file =~ s/\$(\w+)/$ENV{$1}/g;
   $file =~ s/\$\{(\w+)\}/$ENV{$1}/g;
   
   if (defined $oldOH)
   {
       $ENV{ORACLE_HOME} = $oldOH;
   }
   else
   {
       delete $ENV{ORACLE_HOME};
   }
   if (defined $oldSID)
   {
       $ENV{ORACLE_SID} = $oldSID;
   }
   else
   {
      delete $ENV{ORACLE_SID};
   }
   
   $file;
}

# Usages: elementExists(\@array, $elem)
# return 1 if $elem alread exists in @array
sub elementExists
{
   my ($aryRef, $elem) = @_;
   foreach my $e (@$aryRef)
   {
       if ($e eq $elem)
       {
           return 1;
       }
   }	
   return 0;
}

sub trimBraces
{
    my $t = $_[0];
    $t =~ s/^[(]|[)]$//g;
    $t;
}    

sub trimQuote 
{
    my $t = $_[0];
    $t =~ s/^['"]|['"]$//g;
    $t;
}

sub trimBlank
{
    my $t = $_[0];
    $t =~ s/^\s*|\s*$//g;
    $t;
}

sub trimComments
{
    my $t = $_[0];
    $t =~ s/#.*//g;
    $t;
}     

# Usage: escapeRegExpSymbol $str1 $str2 ...
# return the escaped $str1, $str2
sub escapeRegExpSymbol
{
    my @rst;
    
    foreach (@_)
    {
        s/\\/\\\\/g;
        s/\./\\\./g;
        s/\*/\\\*/g;
        s/\?/\\\?/g;
        s/\+/\\\+/g;
        s/\(/\\\(/g;
        s/\)/\\\)/g;
        s/\{/\\\{/g;
        s/\}/\\\}/g;
        s/\[/\\\[/g;
        s/\]/\\\]/g;
        s/\^/\\\^/g;
        s/\$/\\\$/g;
        s/\//\\\//g;
        push @rst, $_;
    } 
    
    @rst;
}

# Usage: system_with_time_limit($time_limit, $cmd)
# return 1 if the system command completes within the time limit.
# return 0 otherwise.
my $SYSTEM_WITH_TIME_LIMIT_MSG;
sub system_with_time_limit
{
    $SYSTEM_WITH_TIME_LIMIT_MSG = "";
    my ($time_limit, $cmd) = @_;
    my $result = 0;
    unless( defined($cmd) &&
            defined($time_limit) && 
            $time_limit =~ m/\d+/ ) { 
        return($result);
    }

    my $timeout_value = "timeout";

    # ignore the PIPE signal. or it will end the program
    local $SIG{PIPE} = sub {};
    local $SIG{ALRM} = sub { die "$timeout_value"; };

    my $system_cmd = sprintf( "system('%s')", $cmd );
    alarm($time_limit);  # set the time limit
    eval "$system_cmd";
    $SYSTEM_WITH_TIME_LIMIT_MSG = "$! $?";
    alarm(0);            # turn off the alarm
    
    $result = 1 unless ($@ =~ m/$timeout_value/) ;

    return($result);
}

# Usages: convertSPFileToPFile($oracleHome, $spfile)
# return the name of the tempory pfile. 
# Please don't try to delete the tempory pfile. The pfile will be cached and
# deleted at the end of the program.
sub convertSPFileToPFile
{
    my ($oracleHome, $sid, $spfile) = @_;
    
    my $convFailMsg = 
        "Failed to convert spfile $spfile to pfile using sqlplus";

    # if converted before, return it
    if (defined $pfileOfSpfile{$spfile})
    {
        if ("_SQLPLUS_HANG_" eq $pfileOfSpfile{$spfile})
        {
            my $convFailReason = 
                "sqlplus was hanging during the last run. ";
            $INIT_PARAMETER_FILE_UTL_ERROR_MSG = 
                "$convFailMsg: $convFailReason. ";
            return "";
        }
        else
        {
            return $pfileOfSpfile{$spfile};
        }
    }

    my $pfile = get_temp_file_name(0666);
    my $MAX_WAIT_SEC = 30;
    
    if ($pfile ne "")
    {
        my $outf = get_temp_file_name();
        my @SQL = ( "CREATE PFILE='$pfile' FROM SPFILE='$spfile';\n" );
        my $username = "";
        my $password = "";
        my $role     = "as sysdba";
        my $fra_error = "";
        
        if ( get_osType() eq 'WIN' ) {
            if ( flash_recovery_area_critical( $username, $password, $role,
                                               $oracleHome, $sid ) ) {
                # treat this also as sqlplus hang
                $pfileOfSpfile{$spfile}="_SQLPLUS_HANG_";
                my $convFailReason = "flash recovery area 97% full.";
                $INIT_PARAMETER_FILE_UTL_ERROR_MSG =
                    "$convFailMsg: $convFailReason";
                $fra_error = "full";
            }
        }

        if ( $fra_error eq "" ) {
        if ( !execSQL_with_time_limit( $username, $password, $role, 
                                          $oracleHome, $sid, $outf, 
                                          $MAX_WAIT_SEC, @SQL ) ) {
            if ( !length($INIT_PARAMETER_FILE_UTL_ERROR_MSG) ) {
                # timed out, sqlplus is hanging
                $pfileOfSpfile{$spfile}="_SQLPLUS_HANG_";
                my $convFailReason = 
                    "sqlplus failed to return after $MAX_WAIT_SEC seconds.";
                $INIT_PARAMETER_FILE_UTL_ERROR_MSG = 
                    "$convFailMsg: $convFailReason";
            }
        }
        elsif ( ! -s $pfile ) {
            # failed 
            $INIT_PARAMETER_FILE_UTL_ERROR_MSG = $convFailMsg;
            if (defined($SYSTEM_WITH_TIME_LIMIT_MSG)) {
                $INIT_PARAMETER_FILE_UTL_ERROR_MSG .=
                    ": $SYSTEM_WITH_TIME_LIMIT_MSG";
            }
        }
        }

        if ( length($INIT_PARAMETER_FILE_UTL_ERROR_MSG) ) {
            # more details may be in $outf
            if (open(OUTF, "<$outf")) {
                my @outputs = <OUTF>;
                close(OUTF);
                unlink($outf);
                my $output = join "", @outputs;
                $INIT_PARAMETER_FILE_UTL_ERROR_MSG .= 
                    " The output of sqlplus is \"$output\"";
            }    
            my $errMsg  = "initParameterFileUtl::convertSPFileToPFile: ";
               $errMsg .= $INIT_PARAMETER_FILE_UTL_ERROR_MSG;
            EMD_PERL_ERROR( $errMsg  );
        }
        else {
            # success
            # put the pfile in the hashtable
            $pfileOfSpfile{$spfile} = $pfile;

            #cleanup outf in case success
	    unlink($outf);

            return( $pfile );
        }    
                
        unlink($pfile);
    }    
    
    "";    
}   

#
# Optional Arg, $permission, 
# if $permission is provided, the method will call chmod($permission, $tmpfile) after
# temp file $tmpfile is created. 
#
# return a temp file name with the permission specified
# return empty if failed
#
sub get_temp_file_name
{
    my $perm = $_[0];

    my ($fh, $tmpfile);
    do { $tmpfile = tmpnam() }
      until $fh = IO::File->new($tmpfile, O_RDWR|O_CREAT|O_EXCL);
    close($fh);
    
    if (defined($perm) && $perm ne "")
    {
      if (!chmod($perm, $tmpfile))
      {
        $INIT_PARAMETER_FILE_UTL_ERROR_MSG = "initParameterFileUtl::get_temp_file_name: failed when calling chmod($perm, $tmpfile): $! $?. "; 
        $tmpfile = "";
      }  
    }

    if(get_osType() eq 'WIN')
    {
       # On Windows, we need to return the full path of the file which include
       # the drive (bug 3450484)
       $tmpfile = Win32::GetFullPathName($tmpfile);
    }
    
    $tmpfile;    
}     

#Checks if the database credentials valid
#Returns 1 if valid, 0 otherwise.
#If there is any error during connection, the credentials is not valid
sub isPasswordValid
{
    my ($username,$password,$oracleHome, $sid) = @_;
    my $retValue = 0;
        
    my $MAX_WAIT_SEC = 30;
    my $outf = get_temp_file_name();
    my $role = "";
    my @SQL = ();

    my $execSQL_success = 1;
    unless ( execSQL_with_time_limit( $username, $password, $role, 
                                      $oracleHome, $sid, $outf, 
                                      $MAX_WAIT_SEC, @SQL ) ) {
        my $DbgMsg;
        if ( length($INIT_PARAMETER_FILE_UTL_ERROR_MSG) ) {
            $DbgMsg  = "$INIT_PARAMETER_FILE_UTL_ERROR_MSG";
        }
        else {
           $DbgMsg  = "isPasswordValid TIMED OUT:";
           $DbgMsg .= " for [$username,$oracleHome, $sid]";      
        }
        EMD_PERL_DEBUG("$DbgMsg");      
        $execSQL_success = 0;
    }
        
    # check details in $outf (may include error text)
    if (open(OUTF, "<$outf"))
    {
        my @outputs = <OUTF>;
        close(OUTF);
        #unlink($outf);

        my $output = join "", @outputs;
        my $DbgMsg  = "isPasswordValid:";
           $DbgMsg .= " for [$username,$oracleHome, $sid] :$output";      
        EMD_PERL_DEBUG("$DbgMsg");      
        if ( $execSQL_success && ($output !~ /ORA-[0-9]*:/) ) {
            $retValue = 1;
        } 
    }    
 
    #always delete the temp file
    unlink($outf);  
        
    return $retValue;    
}

# execSQL_with_time_limit
# Executes the specified sql
# Returns 1 if execution completes within the specified time limit
# If failure is due to an error other than time out, then the
# INIT_PARAMETER_FILE_UTL_ERROR_MSG will be non-empty
# Otherwise, returns 0
sub execSQL_with_time_limit
{
    my ($username, $password, $role, 
        $oracleHome, $sid, $outf, $time_limit, @SQL) = @_;
    my $retValue = 0;
    $INIT_PARAMETER_FILE_UTL_ERROR_MSG = '';
        
    # save current environment settings
    my $oldOH      = $ENV{ORACLE_HOME};
    my $oldSID     = $ENV{ORACLE_SID};
    my $oldLibPath = get_complete_lib_path();
        
    # temporarily set the new environment
    $ENV{ORACLE_HOME} = $oracleHome;
    $ENV{ORACLE_SID}  = $sid;
    set_lib_path($oracleHome); # extends lib path for this oracleHome
        
    # construct the sql command file
    unless (defined($username)) { $username = ""; }
    unless (defined($password)) { $password = ""; }
    unless (defined($role))     { $role     = ""; }

    my $cmdf0 = get_temp_file_name();
    my $cmdf = ${cmdf0} . ".sql";

    if (open(CMDF, ">$cmdf")) {
        print CMDF "whenever sqlerror exit;\n";
        print CMDF "connect $username/$password $role\n";
        foreach my $cmd (@SQL) {
            print CMDF "$cmd";
        }
        print CMDF "exit;\n";
        close(CMDF);
    }
    else {
        $INIT_PARAMETER_FILE_UTL_ERROR_MSG = 
            "Cannot open temporary file $cmdf for write.";
        my $errMsg  = "initParameterFileUtl::execSQL_with_time_limit: ";
           $errMsg .= $INIT_PARAMETER_FILE_UTL_ERROR_MSG;
        # Error message is processed by the caller
    }

    # execute the sql command file
    unless ( length($INIT_PARAMETER_FILE_UTL_ERROR_MSG) ) {
        my $cmd = sprintf( '%s/bin/sqlplus /nolog @%s > %s 2>&1', 
                           $ENV{ORACLE_HOME}, $cmdf, $outf );
        $retValue = system_with_time_limit($time_limit, "$cmd") ;
    }
    unlink($cmdf);
    unlink($cmdf0);

    # remove redundant path entries while preserving order
    $oldLibPath = uniq_path( $oldLibPath );

    # restore the original environment
    $ENV{ORACLE_HOME} = $oldOH;
    $ENV{ORACLE_SID}  = $oldSID;
    set_complete_lib_path($oldLibPath);
        
    return( $retValue );    
}

# flash_recovery_area_critical
# Checks if flash recovery area is in critical state
# Returns 1 if flash recovery area is in critical state
# Otherwise, returns 0
sub flash_recovery_area_critical
{
    my ($username, $password, $role,
        $oracleHome, $sid) = @_;
    my $retValue = 0;
    $INIT_PARAMETER_FILE_UTL_ERROR_MSG = '';

    # save current environment settings
    my $oldOH      = $ENV{ORACLE_HOME};
    my $oldSID     = $ENV{ORACLE_SID};
    my $oldLibPath = get_complete_lib_path();

    # temporarily set the new environment
    $ENV{ORACLE_HOME} = $oracleHome;
    $ENV{ORACLE_SID}  = $sid;
    set_lib_path($oracleHome); # extends lib path for this oracleHome

    # construct the sql command file
    unless (defined($username)) { $username = ""; }
    unless (defined($password)) { $password = ""; }
    unless (defined($role))     { $role     = ""; }

    my $outf = get_temp_file_name();
    my $cmdf0 = get_temp_file_name();
    my $cmdf = ${cmdf0} . ".sql";

    if (open(CMDF, ">$cmdf")) {
        print CMDF "set echo off\n";
        print CMDF "set heading off\n";
        print CMDF "whenever sqlerror exit;\n";
        print CMDF "connect $username/$password $role\n";
        print CMDF "select space_used/space_limit*100 from v\$recovery_file_dest;\n";
        print CMDF "exit\n";
        close(CMDF);
    }
    else {
        $INIT_PARAMETER_FILE_UTL_ERROR_MSG =
            "Cannot open temporary file $cmdf for write.";
        my $errMsg  = "initParameterFileUtl::flash_recovery_area_critical: ";
           $errMsg .= $INIT_PARAMETER_FILE_UTL_ERROR_MSG;
        # Error message is processed by the caller
    }

    # execute the sql command file
    unless ( length($INIT_PARAMETER_FILE_UTL_ERROR_MSG) ) {
        my $cmd = sprintf( '%s/bin/sqlplus -s /nolog @%s > %s 2>&1',
                           $ENV{ORACLE_HOME}, $cmdf, $outf );
        my $system_cmd = sprintf( "system('%s')", $cmd );
        eval "$system_cmd";
        if (open(OUTF, "<$outf"))
        {
            my @lines = <OUTF>;
            close(OUTF);

            foreach my $line (@lines)
            {
                $line = trimBlank(trimComments($line));
                next if ($line eq "");
                if ( $line =~ /^\d*\.\d*$/ ||
                     $line =~ /^\d*$/ )
                {
                    my $critical_percent = 97;
                    my $percent = int($line);

                    if ($line > $critical_percent)
                    {
                        EMD_PERL_ERROR("initParameterFileUtl::flash_recovery_area_critical: Flash Recovery Area is in critical status - " . $line . "% full");
                        $retValue = 1;
                    }

                    last;
                }
            }
        }
    }
    unlink($cmdf);
    unlink($cmdf0);
    unlink($outf);

    # remove redundant path entries while preserving order
    $oldLibPath = uniq_path( $oldLibPath );

    # restore the original environment
    $ENV{ORACLE_HOME} = $oldOH;
    $ENV{ORACLE_SID}  = $oldSID;
    set_complete_lib_path($oldLibPath);

    return( $retValue );
}

1;