Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\scripts\osm\empexecuteSQL.pl

# $Header: empexecuteSQL.pl 20-aug-2007.08:14:29 shnavane Exp $
#
# Copyright (c) 2002, 2007, Oracle. All rights reserved.  
#
#    DESCRIPTION
#      ECM script to execute a SQL script
#
#      For each database
#       -   Execute the SQL specified
#
#    USAGE
#      perl empexecuteSQL.pl <options>
#
#    options
#          -oh <ORACLE_HOME>
#          -os <List of SIDs(separated by ampersand(&)) running out 
#               the ORACLE_HOME>
#          -sql Absolute path to the SQL script
#
#    NOTES
#      <other useful comments,qualifications,etc>
#
#    MODIFIED     (MM/DD/YY)
#       shnavane   09/02/07 - Backport shnavane_bug-6024894 from main
#       vsriram    07/25/07 - Bug 5501067 - Handle the return values
#       shnavane   05/18/07 - Fix bug #6024894
#       bguhatha   06/07/07 - bug-5971223 
#       bguhatha   06/22/07 - Backport bguhatha_bug-5971223 from main
#       rsah       03/09/06 - Fix for bug 5064490.\. 
#       milshah    11/11/05 - change statusf to always right shift return code 
#       shgangul   09/21/05 - Remove warnings 
#       shgangul   07/14/05 - support extra unknown parameters, -asm <asm 
#                             instances> 
#       shgangul   06/08/05 - Execute the sql script directly
#       shgangul   04/29/05 - Spool sql errors and parse them 
#       mningomb   04/13/05 - mningomb_dbpatch
#       shgangul   04/08/05 - Creation
#

# --- Set up necessary variables for proper running of this environment ---
use strict;
use FileHandle;
use File::Basename();
use File::Path();
use File::Find();
use File::Spec();
use File::stat;
use User::pwent;
use XML::Parser;

my $scriptName   = File::Basename::basename($0);
my $osmScriptDir = File::Basename::dirname($0);
my $ecmCommon    = File::Spec->catfile($osmScriptDir, 'ecmCommon.pl');
#my $db_instance = File::Spec->catfile($ENV{EMDROOT}, "sysman", "admin", "scripts", "db", "db_instance.pl");
my $db_state = File::Spec->catfile($ENV{EMDROOT}, "sysman", "admin", "scripts", "db", "dbstate.pl");

my %elements;
my $seen = 0;
my $root;

require "$ecmCommon";
require "$db_state";
#require "$db_instance";

# ------ Initialize global variables -------------------------------------
use constant COPYRIGHT => "Copyright \251 2002, 2006, Oracle. All rights reserved.";
use constant VERSION   => '10.2.0.0.0';
use constant PTYPE_PATCH        => 'patch';
use constant PTYPE_PATCHSET     => 'patchset';
use constant TTYPE_HOST         => 'host';
use constant TTYPE_DB           => 'oracle_database';
use constant TTYPE_EMD          => 'oracle_emd';
use constant TTYPE_IAS          => 'oracle_ias';
use constant DEPOTROOT          => 'EMStagedPatches';
use constant S_DELAY     => '-delay';
use constant S_FORCE     => '-force';
use constant S_HELP      => '-help';
use constant S_JRE       => '-jre';
use constant S_JDK       => '-jdk';
use constant S_LOCAL     => '-local';
use constant S_MINIMIZEDOWNTIME => '-minimize_downtime';
use constant S_NOBUGSUPERSET    => '-no_bug_superset';
use constant S_NOINVENTORY      => '-no_inventory';
use constant S_OH               => '-oh';
use constant S_PATCHID          => '-patchid';
use constant S_RETRY            => '-retry';
use constant S_SILENT           => '-silent';
use constant S_VERSION          => '-version';
use constant S_VERBOSE          => '-verbose';
use constant S_INVPTRLOC        => '-invPtrLoc';
use constant S_ASM              => '-asm';
use constant S_EMPTY            => '';
use constant S_SPACE            => ' ';
use constant S_QUOTE            => '"';
use constant S_APOSTROPHE       => "'";
use constant KB                 => 1024;
use constant B_TRUE             => 1;
use constant B_FALSE            => 0;
use constant E_SUCCESS          => 0;
use constant E_FAIL             => 1 * (1 << 8);
use constant E_NO_ENV           => 2 * (1 << 8);
use constant E_NO_OPATCHPL      => 3 * (1 << 8);
use constant E_NO_INVENTORY     => 4 * (1 << 8);
use constant E_NO_COMMAND       => 5 * (1 << 8);
use constant E_INV_COMMAND      => 6 * (1 << 8);
use constant E_INV_ARG          => 7 * (1 << 8);
use constant E_TOO_MANY         => 8 * (1 << 8);
use constant E_MISSING_ARG      => 9 * (1 << 8);
use constant E_NOT_ENOUGH_SPACE => 10 * (1 << 8);
my $action          = S_EMPTY;     # ARGV[0]
my $ORACLE_HOME     = S_EMPTY;     # -oh /private/OraHome1
my $ORACLE_SID      = S_EMPTY;     # 
my @ORACLE_SIDs     = ();          # -os mjgdb817,msdfsjk,sdnms23
my @ORACLE_ASMs     = ();          # -os mjgdb817,msdfsjk,sdnms23
my $tns_admin       = S_EMPTY;     # Location of listener config file
my $sqlscript        = S_EMPTY;     # Location of sql script to execute
my $listener_list   = S_EMPTY;     # List of comma separated listeners
my @db_listeners    = ();          # List of listeners
my $shared          = 0;           # Whether shared or not
my $rac             = 0;           # Whether RAC setup or not
my $shutdown_type   = '15';        # -st 1=instance,2=listener,4=agent,8=startup
my $shutdown_sids   = S_EMPTY;     # -ss mjgdb817
my $shutdown_lsnrs  = S_EMPTY;     # -sl LISTENER
my $patch_id        = S_EMPTY;     #  -i 1390304
my $patch_size      = '0';         #  -s 5237
my $patch_file      = S_EMPTY;     #  -f p1390304.zip
my $patch_index     = '1';         #  -x 1..n
my $patch_count     = '1';         #  -c n
my $patch_lock      = 'PATCH.lck';
my $dbsnmp_lock     = 'DBSNMP.lck';
my $lsnr_lock       = 'LSNR.lck';
my $db_lock         = 'DB.lck';
my $db_name         = S_EMPTY;
my @db_instances    = ();
my $OS              = $^O;         # OS type (solaris|linux)
my $PERL            = $^X;         # Perl executable
my $PERL5LIB        = S_EMPTY;
my $EMDROOT         = S_EMPTY;
my $JAVA_HOME       = S_EMPTY;
my $OUILOC          = S_EMPTY;
my $isRAC           = B_FALSE;     # true if RAC patch
my $db_shutdown     = B_FALSE;     # true if database shutdown required
my $lsnr_shutdown   = B_FALSE;     # true if listener shutdown required
my $dbsnmp_shutdown = B_FALSE;     # true if Oracle agent shutdown required
my $isProduct       = B_FALSE;     # true if RTM release
my $isDebug         = B_FALSE;     # true if debugging
my $isHelp          = B_FALSE;     # true if help request
my $isLogging       = B_FALSE;     # true if logging output
my $isHeader        = B_FALSE;     # true if header printed
my $local_opt       = S_EMPTY;     # if -local specified
my $inv_loc         = S_EMPTY;     # invPtrLoc option for apply
my $isShared        = 0;           # if it is a shared home
my $Shared          = S_EMPTY;     # sharedMove string
my $INVPTRLOCFILE   = 'oraInst.loc';
my $INVPTRLOCFILEPATH = S_EMPTY;

my $file;
my $item;
my @lit;
my $count;
my @loc;
my $script_loc;
my $m_sql_script;
my $m_cpu_name;
my $output_string;
my @tokens;
my $str;
my $len;

# --------------------- Command-line arguments --------------------------
use constant CTYPE_HELP     => 'help';
use constant CTYPE_APPLY    => 'applyPatch';
use constant CTYPE_CHECK    => 'checkTarget';
use constant CTYPE_EXPAND   => 'expandPatch';
use constant CTYPE_SHOW     => 'showResults';
use constant CTYPE_SHUTDOWN => 'shutdown';
use constant CTYPE_STARTUP  => 'startup';
my $CT     = '-c';                 #  -c count
my $CF     = '-cf';                # -cf
my $DP     = '-d';                 #  -d directory
my $PF     = '-f';                 #  -f patchfile
my $DB     = '-g';                 #  -g debug
my $HP     = '-h';                 #  -h
my $ID     = '-i';                 #  -i patchid
my $OH     = '-oh';                # -oh ORACLE_HOME
my $SID    = '-os';                # -os ORACLE_SID
my $PT     = '-p';                 #  -p patchtype
my $QT     = '-q';                 #  -q
my $SI     = '-s';                 #  -s size
my $SL     = '-sl';                # -sl shutownlisteners
my $SS     = '-ss';                # -ss shutdownsids
my $ST     = '-st';                # -st shutdownttype
my $TT     = '-t';                 #  -t targettype
my $PI     = '-x';                 #  -x index
my $VE     = '-v';                 #  -v
my $SHOME  = '-sharedMove';        #  -sharedMove, an unary operator
my %OPTV   = ();
my %UNARYS = ();
my %DEFS   = ();
my %ARGS   = ();
my %CMDS   = ();

# --------------------- OSD platform-specific ---------------------------
my $DF           = '/usr/bin/df';
my $dfOpt        = '-k';
my $ECHO         = '/usr/bin/echo';             # obsolete
my $EGREP        = '/usr/bin/egrep';            # obsolete
my $EMUNZIP      = 'emunzip';
my $EMZIP        = 'emzip';
my $LS           = '/usr/bin/ls';
my $AWK          = '/usr/bin/awk';              # obsolete
my $NULL_DEVICE  = '/dev/null';
my $PS           = '/usr/bin/ps';               # obsolete
my $LSNRCTL      = 'lsnrctl';                   # obsolete
my $SHELL        = '/bin/sh';
my $SQLPLUS      = 'sqlplus';                   # obsolete
my $SRVCTL       = 'srvctl';                    # obsolete
my $TAR          = '/usr/bin/tar';
my $tarOpt       = 'xvf';
my $UNZIP        = '/usr/bin/unzip';
my $unzipOpt     = '-o';
my $ZIP          = '/usr/bin/zip';
my $zipOpt       = S_EMPTY;
my $oratab       = '/var/opt/oracle/oratab';    # obsolete
my $BAT_SUFFIX   = S_EMPTY;
my $EXE_SUFFIX   = S_EMPTY;
my $CLASSPATHSEP = ':';
my $PATHSEP      = ':';
my $FILESEP      = '/';
my $DEF_PATH     =
  '/bin:/sbin:/usr/bin:/usr/sbin:/etc:/usr/etc:/usr/ccs/bin:/usr/ucb';
my $DEF_LD_LIBRARY_PATH = '/usr/lib';
my $DEF_JAVA_HOME       = '/usr/local/packages/jdk1.3.1';

# --------------------- Subroutines -------------------------------------

# setupOSD()
#
# Setup OSD commands
#
sub setupOSD
{
    if (onWindows())
    {
        $ECHO                = 'echo';
        $LS                  = 'dir';
        $NULL_DEVICE         = 'NUL';
        $SHELL               = 'cmd.exe /c';
        $TAR                 = 'tar.exe';
        $UNZIP               = 'unzip.exe';
        $ZIP                 = 'zip.exe';
        $BAT_SUFFIX          = '.bat';
        $EXE_SUFFIX          = '.exe';
        $CLASSPATHSEP        = ';';
        $PATHSEP             = ';';
        $FILESEP             = '\\';
        $DEF_PATH            = $ENV{'PATH'};
        $DEF_LD_LIBRARY_PATH = $ENV{'PATH'};
        $DEF_JAVA_HOME       = $ENV{'JAVA_HOME'};
        $ENV{'DIRCMD'}       = '';
        $ENV{'COPYCMD'}      = '';
    }
    else
    {
        if (!equalsIgnoreCase('solaris', $OS)
            && !equalsIgnoreCase('linux', $OS))
        {
            $dfOpt = '-Pk';
        }
        if (!equalsIgnoreCase('solaris', $OS))
        {
            $oratab = '/etc/oratab';
        }
        if (equalsIgnoreCase('hpux', $OS))
        {
           $DF =  '/usr/bin/bdf';
           $dfOpt = '';
        }
        if (equalsIgnoreCase('linux', $OS))
        {
            $DF    = '/bin/df';
            $ECHO  = '/bin/echo';
            $EGREP = '/bin/egrep';
            $LS    = '/bin/ls';
            $AWK   = '/bin/awk';
            $PS    = '/bin/ps';
            $TAR   = '/bin/tar';
        }
        elsif (equalsIgnoreCase('darwin', $OS))
        {
            $DF    = '/bin/df';
            $ECHO  = '/bin/echo';
            $LS    = '/bin/ls';
            $PS    = '/bin/ps';
        }
    }
}

#
# logf(<message>)
#
# Display the message with timestamp
#
#
sub logf($) 
{
    my ($msg) = @_;

printf("\n%s - %s\n", scalar(localtime()), $msg);
}

# isRunning(<proc_match>)
#
# Return true if process is running
#
sub isRunning($)
{
    my ($proc_match) = @_;

return (system("$PS -e -o args | $EGREP -s \'$proc_match\'") == E_SUCCESS);
}

# initOptions()
#
# Setup known options for parsing
#
sub initOptions
{
    $CMDS{lc(CTYPE_HELP)}   = '1';
    $CMDS{lc(CTYPE_APPLY)}  = '1';
    $CMDS{lc(CTYPE_CHECK)}  = '1';
    $CMDS{lc(CTYPE_EXPAND)} = '1';
    $CMDS{lc(CTYPE_SHOW)}   = '1';

#$CMDS{lc(CTYPE_SHUTDOWN)} = '1';
    #$CMDS{lc(CTYPE_STARTUP)}  = '1';

$OPTV{$CT}   = 'count';
    $DEFS{$CT}   = '1';              # -c
    $OPTV{$DP}   = 'directory';
    $DEFS{$DP}   = DEPOTROOT;        # -d
    $OPTV{$PF}   = 'patchfile';      # -f
    $OPTV{$DB}   = 'debug';
    $DEFS{$DB}   = '0';              # -g
    $OPTV{$HP}   = 'help';
    $UNARYS{$HP} = '1';              # -h
    $OPTV{$ID}   = 'patchid';        # -i
    $OPTV{$OH}   = 'ORACLE_HOME';    # -oh
    $OPTV{$SID}  = 'ORACLE_SID';
    $DEFS{$SID}  = 'none';           # -os
    $OPTV{$PT}   = 'patchtype';
    $DEFS{$PT}   = PTYPE_PATCH;      # -p
    $OPTV{$SI}   = 'size';
    $DEFS{$SI}   = '0';              # -s
    $OPTV{$SL}   = 'listeners';
    $DEFS{$SL}   = ' ';              # -sl
    $OPTV{$SS}   = 'ids';
    $DEFS{$SS}   = ' ';              # -ss
    $OPTV{$ST}   = 'shutdowntype';
    $DEFS{$ST}   = '15';             # -st
    $OPTV{$TT}   = 'targettype';
    $DEFS{$TT}   = TTYPE_DB;         # -t
    $OPTV{$PI}   = 'index';
    $DEFS{$PI}   = '1';              # -x
    $OPTV{$VE}   = 'version';
    $UNARYS{$VE} = '1';              # -v
    $OPTV{$CF}   = 'checkfree';
    $UNARYS{$CF} = '1';              # -cf
    $OPTV{$QT}   = 'quiet';
    $UNARYS{$QT} = '1';              # -q
    $OPTV{$SHOME} = 'sharedMove';    # -sharedMove
    $UNARYS{$SHOME} = '1';           # -sharedMove
    $DEFS{$SHOME} = '0';             # default is not-shared
}

# parseArgs()
#
# Store all the arguments in hashed table
#
sub parseArgs
{

# Initialize the parameters;

my $opt      = S_EMPTY;
    my $argcount = scalar(@ARGV);

logf("Arguments: @ARGV");

my $i = 0;
    while ($i < $argcount)
    {
        $opt = $ARGV[$i];
        if ($opt eq "-oh")
        {
            $i ++;
            $ORACLE_HOME = $ARGV[$i];
        }
        elsif ($opt eq "-os")
        {
            $i ++;
            # Parse the SID list and store it in SID array
            my $sidlist = $ARGV[$i];
            chomp $sidlist;
            @ORACLE_SIDs = split(/&/, $sidlist);

# Get a list of uniq SIDs
            my @tempsids = ();
            for(my $sidi = 0; $sidi < scalar(@ORACLE_SIDs); $sidi++)
            {
                my $uniq = 1;
                for(my $sidj = 0; $sidj < scalar(@tempsids); $sidj++)
                {
                    if ($ORACLE_SIDs[$sidi] eq $tempsids[$sidj])
                    {
                        $uniq = 0;
                        last;
                    }
                }

if ($uniq == 1)
                {
                    push(@tempsids, $ORACLE_SIDs[$sidi]);
                }
            }
            @ORACLE_SIDs = @tempsids;
        }
        elsif ($opt eq "-sql")
        {
            $i ++;
            $sqlscript = $ARGV[$i];
        }
        elsif ($opt eq S_ASM)
        {
            $i ++;
            # Parse the ASM list and store it in ASM array
            my $asmlist = $ARGV[$i];
            chomp $asmlist;
            @ORACLE_ASMs = split(/&/, $asmlist);

# Get a list of uniq ASMs
            my @tempasms = ();
            for(my $asmi = 0; $asmi < scalar(@ORACLE_ASMs); $asmi++)
            {
                my $uniq = 1;
                for(my $asmj = 0; $asmj < scalar(@tempasms); $asmj++)
                {
                    if ($ORACLE_ASMs[$asmi] eq $tempasms[$asmj])
                    {
                        $uniq = 0;
                        last;
                    }
                }

if ($uniq == 1)
                {
                    push(@tempasms, $ORACLE_ASMs[$asmi]);
                }
            }
            @ORACLE_ASMs = @tempasms;
        }
        else
        {
            printHeader($0, $#ARGV) if (!$isHeader);
            #abort($action, statusf(E_INV_ARG), "Invalid argument: $opt");
            printf("Following argument not supported: $opt");
        }
        
        $i ++; # increment the count
    }

if ($ORACLE_HOME eq S_EMPTY)
    {
        abort($action, statusf(E_INV_ARG), "Oracle Home not set");
    }

if ($sqlscript eq S_EMPTY)
    {
        abort($action, statusf(E_INV_ARG), "SQL script not specified");
    }

if (! -f $sqlscript  && $sqlscript ne 'defaultSqlScript')
    {
        abort($action, statusf(E_INV_ARG), "SQL script does not exist");
    }
    
    if (scalar(@ORACLE_SIDs) == 0)
    {
        abort($action, statusf(E_INV_ARG), "No SIDs are specified");
    }
}

# isDBRunning(<sid>)
#
# Return true if db is running
#
sub isDBRunning($)
{
    my ($sid) = @_;

my $old_sid   = $ENV{'ORACLE_SID'};
    my @lines     = ();
    my $isRunning = B_FALSE;

$ENV{'ORACLE_SID'} = $sid;
    chomp(@lines =
          `$ECHO "SET pagesize 0\\nSET tab off\\nSELECT 1 FROM sys.dual;\\n" | $SQLPLUS -S '/ as sysdba'`
          );
    $ENV{'ORACLE_SID'} = $old_sid;

if ($#lines >= 0)
    {
        $lines[0] =~ s/ //g;
        $isRunning = B_TRUE if (isEqual('1', $lines[0]));
    }

return $isRunning;
}

# createLock(<lock_file>,<lock_id>)
#
# Place a marker in the depot to indicate patching state
#
sub createLock($$)
{
    my ($lock_file, $lock_id) = @_;

my $lock_path = File::Basename::dirname($lock_file);

File::Path::mkpath($lock_path, 0, 0775) if (!-d "$lock_path");
    open(OUTPUT, "> $lock_file")
      or abort($action, statusf($?),
               "Could not open lockfile $lock_file to write: $!");
    printf(OUTPUT "$lock_id\n");
    close(OUTPUT);
}

# isLocked(<lock_file>)
#
# Test a marker in the depot to indicate patching state
#
sub isLocked($)
{
    my ($lock_file) = @_;

return (-f $lock_file);
}

# findLocks(<path>,<mask>)
#
# Return list of lock files
#
sub findLocks($$)
{
    my ($path, $mask) = @_;

my @locks   = ();
    my $pattern = File::Spec->catfile($path, $mask);

if (onWindows())
    {
        $pattern =~ s~/~\\~g;
        chomp(@locks = `DIR /B $pattern 2> $NULL_DEVICE`);
        for (my $i = 0 ; $i <= $#locks ; $i++)
        {
            $locks[$i] = File::Spec->catfile($path, $locks[$i]);
        }
    }
    else
    {
        chomp(@locks = `$LS -1 $pattern 2> $NULL_DEVICE`);
    }

return @locks;
}

# getDB_NAME(<lock_file>)
#
# Find db_name for use in shutting down RAC instances
#
sub getDB_NAME($)
{
    my ($lock_file) = @_;

my @lines = ();

if (!isEmpty($lock_file))
    {
        open(INPUT, "< $lock_file");
        while (<INPUT>)
        {
            chomp;
            $db_name = $_ if (!isEmpty($_));
        }
        close(INPUT);

return;
    }

chomp(@lines =
          `$ECHO "SET pagesize 0\\nSET tab off\\nSELECT SYS_CONTEXT(\'USERENV\',\'DB_NAME\') FROM sys.dual;\\n" | $SQLPLUS -S '/ as sysdba'`
          );

if ($#lines >= 0)
    {
        $lines[0] =~ s/ //g;
        $db_name = $lines[0];
    }
}

# getDB_LISTENERS()
#
# Populate db_listeners for use in shutting down all Oracle listeners
#
sub getDB_LISTENERS()
{

my $lsnr  = S_EMPTY;
    my @lines = ();

# Listener config file
    my $list_config_file = File::Spec->catfile($tns_admin, "listener.ora");
    print "Geting teh listenres  file=$list_config_file\n";
    if (! -e $list_config_file )
    {
        return;
    }

# Parse the listener.ora file to get the list of listeners
    local *LISTDATA;
    open(LISTDATA, "<$list_config_file")
      or abort($action, statusf($?),
               "Could not open listerner config file \'$list_config_file\'");
    my @ldata = <LISTDATA>;
    close(LISTDATA);

foreach my $line (@ldata)
    {
        chomp $line;
        print "Reading line=$line\n";
        if ($line =~ m#^LISTENER(.*)=(\s*)$#)
        {
        print "Special Reading line=$line\n";
            my @linewords = split(/ /, $line);
            if ($linewords[0] eq "") {next;}
            if ($linewords[0] =~ m#^\(#) {next;}
            if ($linewords[0] =~ m#^SID_LIST_#) {next;}
            push (@db_listeners, $linewords[0]);
        }
    }

logf ("List of listeners detected: @db_listeners");
}

# createInitOra(<sid>,<initora>)
#
# Create an init$ORACLE_SID.ora file for use in startup
#
sub createInitOra($$)
{
    my ($sid, $initora) = @_;

my $name  = S_EMPTY;
    my $value = S_EMPTY;
    my @lines = ();

open(OUTPUT, "> $initora")
      or abort($action, statusf($?),
               "Could not open initfile $initora to write: $!");

printf(OUTPUT "# $initora generated on %s\n", scalar(localtime()));

chomp(@lines =
          `$ECHO "SET pagesize 0\\nSET tab off\\nSET linesize 512\\nSELECT CONCAT(CONCAT(name,'='),value) FROM V\\\$PARAMETER WHERE isdefault='FALSE' ORDER BY name;\\n" | $SQLPLUS -S '/ as sysdba'`
          );

for (my $i = 0 ; $i <= $#lines ; $i++)
    {
        $lines[$i] =~ s/ //g;
        next if (!$lines[$i] =~ m/=/);

($name, $value) = split (/=/, $lines[$i], 2);
        next if (!defined $value);

if ($value =~ m/,/)
        {
            $value =~ s/,/","/g;
            $value = '"' . $value . '"';
        }
        elsif ($value =~ m/=/)
        {
            $value = '"' . $value . '"';
        }
        if ($name =~ m/control_files/i)
        {
            $value = '(' . $value . ')';
        }
        else
        {
            $value = '""' if (isEmpty($value));
        }

printf(OUTPUT "%s=%s\n", $name, $value);
    }
    printf(OUTPUT "# end of generated file\n");
    close(OUTPUT);
}

# shutdownLSNR(<name>)
#
# Shutdown database listener <name> if running
# Create shutdown marker to indicate what we did
#
sub shutdownLSNR($)
{
    my ($name) = @_;

$! = E_SUCCESS;
    logf("Stopping Oracle database listener ${name}...");
    echodo("$LSNRCTL stop $name");
    if ($! != E_SUCCESS)
    {
      logf("Could not shutdown database listener: $!");
    }
}

# shutdownDBSNMP()
#
# Shutdown Oracle Agent if running
# Create shutdown marker to indicate what we did
#
sub shutdownDBSNMP
{
    my $DBSNMP   = 'dbsnmp';
    my $AGENTCTL = "$LSNRCTL dbsnmp_stop";
    my $status   = E_SUCCESS;

if (-f File::Spec->catfile($ORACLE_HOME, 'bin', 'agentctl'))
    {
        $DBSNMP = File::Spec->catfile($ORACLE_HOME, 'bin', $DBSNMP);
        $AGENTCTL =
          File::Spec->catfile($ORACLE_HOME, 'bin', 'agentctl') . ' stop';
    }

if (isRunning("^${DBSNMP}\$"))
    {
        printf(STDOUT "Stopping Oracle agent...\n");
        $status = echodo("$AGENTCTL");

$status == E_SUCCESS
          or abort($action, statusf($?),
                   "Could not shutdown Oracle agent: $!");

createLock($dbsnmp_lock, 'AGENT');
    }
}

# executesqlSID(<sid>, <script location>)
#
# Shutdown database instance if running
# Create shutdown marker to indicate what we did
#
sub executesqlSID($$)
{
    my ($sid, $sqlpscript) = @_;
    my $retval = 0;

our $oracle_home=$ORACLE_HOME;
    our $oracle_sid=$sid;
    our $db_name="";
    our $username="";
    our $password="";
    our $role="sysdba";
    our $arrayElem="";
    our @SQLCMDS=();
    our $IGNORE_SQL_ERROR=0;

# Spool name OH/<sid>_sql.lst
    my $spool_name = $sid . "_sql.lst";
    my $spool_file = File::Spec->catfile($ORACLE_HOME, $spool_name);

open(script_txt_handle, $sqlscript) || abort($action, statusf(E_INV_ARG), "Could not read the SQL script ($sqlscript)");
    close script_txt_handle;
    open(spool_out, ">$spool_file") || abort($action, statusf(E_INV_ARG), "Could not open file ($sqlscript) to write SQL script output");
    close spool_out;

# Start spooling
    $arrayElem = "\@$sqlscript\n";
    @SQLCMDS=(@SQLCMDS,$arrayElem);

my $connStr = constructConnectStr($username, $password, "", $role);
    $retval = executeSQLPlus($connStr, $spool_file);
    logf ("SQL script $sqlscript returned with ErrorCode=$retval");

# Parse the SQL out for errors
    open(spool_out, "$spool_file") || abort($action, statusf(E_INV_ARG), "Could not open file ($sqlscript) to write SQL script output");
    my @spool_lines = <spool_out>;
    logf("Output for execution of SQL script: $sqlscript");
    my $spool_errors = "";
    foreach my $spool_line (@spool_lines)
    {
        chomp $spool_line;
        print "$spool_line\n";
        if ($spool_line =~ m#^ORA-[0-9]+.*#)
        {
            my @errors = split (/: /, $spool_line);
            my $ora_error = $errors[0];
            chomp $ora_error;
            if ( !($ora_error eq "ORA-29558") && 
                 !($ora_error eq "ORA-06512")
               )
            {
                $spool_errors = $spool_errors . $spool_line . "\n";
                $retval = E_FAIL;
            }
        }
    }
    logf("End of output for execution of SQL script: $sqlscript");
    if ($retval != 0)
    {
        logf ("Error detected: \n$spool_errors");
    }

return $retval;
}

# setShutdownTypes()
#
# Set up requested shutdown/startup types
#
sub setShutdownTypes
{
    my $sid   = S_EMPTY;
    my $lsnr  = S_EMPTY;
    my $count = 0;

if (equalsIgnoreCase(CTYPE_SHUTDOWN, $action))
    {
        $db_shutdown     = B_TRUE if ($shutdown_type & 1);
        $lsnr_shutdown   = B_TRUE if (($shutdown_type & 2) && !$isRAC);
        $dbsnmp_shutdown = B_TRUE if ($shutdown_type & 4);
    }
    elsif (equalsIgnoreCase(CTYPE_STARTUP, $action) && ($shutdown_type & 8))
    {
        $db_shutdown     = B_TRUE if ($shutdown_type & 1);
        $lsnr_shutdown   = B_TRUE if (($shutdown_type & 2) && !$isRAC);
        $dbsnmp_shutdown = B_TRUE if ($shutdown_type & 4);
    }

if ($db_shutdown && !isEmpty($shutdown_sids))
    {
        $count = $#db_instances;
        $shutdown_sids =~ s/ //g;
        LOOP: foreach $sid (split (/,/, $shutdown_sids))
        {
            next LOOP if (isEmpty($sid));
            for (my $i = 0 ; $i <= $count ; $i++)
            {
                next LOOP if (isEqual($sid, $db_instances[$i]));
            }
            $count += 1;
            $db_instances[$count] = $sid;
        }
    }

if ($lsnr_shutdown && !isEmpty($shutdown_lsnrs))
    {
        $count = $#db_listeners;
        $shutdown_lsnrs =~ s/ //g;
        LOOP: foreach $lsnr (split (/,/, $shutdown_lsnrs))
        {
            next LOOP if (isEmpty($lsnr));
            for (my $i = 0 ; $i <= $count ; $i++)
            {
                next LOOP if (isEqual($lsnr, $db_listeners[$i]));
            }
            $count += 1;
            $db_listeners[$count] = $lsnr;
        }
    }
}

# startupDBSNMP()
#
# Start Oracle agent if it was running
# Remove shutdown marker to indicate the current state
#
sub startupDBSNMP
{
    my $DBSNMP   = 'dbsnmp';
    my $AGENTCTL = "$LSNRCTL dbsnmp_start";
    my $status   = E_SUCCESS;

if (isLocked($dbsnmp_lock))
    {
        if (!isRunning("^${DBSNMP}\$"))
        {
            $! = E_SUCCESS;
            printf(STDOUT "Starting Oracle agent...\n");
            $status = echodo("$AGENTCTL");

$status == E_SUCCESS
              or abort($action, statusf($?),
                       "Could not startup Oracle agent: $!");
        }
        unlink($dbsnmp_lock);
    }
}

# addPATH(<new_path>)
#
# Add the directory at the beginning of PATH
#
sub addPATH($)
{
    my ($new_path) = @_;

my $PATH = $ENV{'PATH'};

if (onWindows())
    {
        $new_path =~ s~/~\\~g;
    }

if (isEmpty($PATH))
    {
        $PATH = $DEF_PATH;
    }
    else
    {
        if (onWindows())
        {
            $new_path =~ s~\\~\\\\~g;
        }
        $PATH =~ s~(^|${PATHSEP})${new_path}(${PATHSEP}|$)~${PATHSEP}~g;
        $PATH =~ s~(^${PATHSEP}|${PATHSEP}$)~~g;
        if (onWindows())
        {
            $new_path =~ s~\\\\~\\~g;
        }
    }
    $ENV{'PATH'} = "${new_path}${PATHSEP}${PATH}";
}

# addLDPATH(<NEW_LDPATH>)
#
# Add the directory at the beginning of LD_LIBRARY_PATH
#
sub addLDPATH($)
{
    my ($new_ldpath) = @_;

my $LD_LIBRARY_PATH = $ENV{'LD_LIBRARY_PATH'};

if (onWindows())
    {
        addPATH($new_ldpath);
        return;
    }

if (isEmpty($LD_LIBRARY_PATH))
    {
        $LD_LIBRARY_PATH = $DEF_LD_LIBRARY_PATH;
    }
    else
    {
        $LD_LIBRARY_PATH =~
          s~(^|${PATHSEP})${new_ldpath}(${PATHSEP}|$)~${PATHSEP}~g;
        $LD_LIBRARY_PATH =~ s~(^${PATHSEP}|${PATHSEP}$)~~g;
    }
    $ENV{'LD_LIBRARY_PATH'} = "${new_ldpath}${PATHSEP}${LD_LIBRARY_PATH}";
}

# oraenv(<oracle_home>,<oracle_sid>)
#
# Set ORACLE_HOME environment
#
sub oraenv($$)
{
    ($ORACLE_HOME, $ORACLE_SID) = @_;

$ORACLE_HOME = trim($ORACLE_HOME);
    $ORACLE_SID  = trim($ORACLE_SID);
    @db_instances = split (/,/, $ORACLE_SID);
    $ORACLE_SID = trim($db_instances[0]);
    $db_name = $ORACLE_SID;
    $LSNRCTL = File::Spec->catfile($ORACLE_HOME, 'bin', $LSNRCTL);
    $SQLPLUS = File::Spec->catfile($ORACLE_HOME, 'bin', "$SQLPLUS$EXE_SUFFIX");
    $SRVCTL  = File::Spec->catfile($ORACLE_HOME, 'bin', $SRVCTL);
    if (-f $SRVCTL)
    {
        $isRAC = B_TRUE;
    }
    my %oldenv = %ENV;
    if (!onWindows())
    {
        %ENV = ();

#   my ($login,$passwd,$uid,$gid,$quota,$comment,$gcos,$dir,$shell,$expire) = getpwuid($>);
        my $pw = getpw($>);
        $ENV{'HOME'}    = $pw->dir;
        $ENV{'LOGNAME'} = $pw->name;
        $ENV{'USER'}    = $pw->name;
        $ENV{'LC_ALL'}  = 'C';
    }
    $ENV{'EMDROOT'}     = $EMDROOT;
    $ENV{'PERL5LIB'}    = $PERL5LIB;
    $ENV{'JAVA_HOME'}   = $JAVA_HOME;
    $ENV{'PWD'}         = $oldenv{'PWD'} if (defined $oldenv{'PWD'});
    $ENV{'SHELL'}       = $SHELL;
    $ENV{'TZ'}          = $oldenv{'TZ'} if (defined $oldenv{'TZ'});
    $ENV{'ORACLE_HOME'} = $ORACLE_HOME;
    $ENV{'ORACLE_SID'}  = $ORACLE_SID;
    $ENV{'TNS_ADMIN'}   = File::Spec->catfile($ORACLE_HOME, 'network', 'admin');

$ENV{'PATH'} = S_EMPTY;
    addPATH(File::Spec->catfile($JAVA_HOME,   'bin'));
    addPATH(File::Spec->catfile($ORACLE_HOME, 'bin'));

$ENV{'LD_LIBRARY_PATH'} = S_EMPTY;
    addLDPATH(File::Spec->catfile($ORACLE_HOME, 'network', 'lib'));
    addLDPATH(File::Spec->catfile($ORACLE_HOME, 'lib'));

#printf(STDOUT "PATH = %s\nLD_LIBRARY_PATH = %s\n",$ENV{'PATH'},$ENV{'LD_LIBRARY_PATH'});
}

# emdenv()
#
# Set EMD environment
#
sub emdenv
{
    $EMDROOT = $ENV{'EMDROOT'};
    if (isEmpty($EMDROOT))
    {
        abort($action, statusf(E_INV_ARG), "EMDROOT not set");
        # ($EMDROOT = $osmScriptDir) =~ s/.sysman.admin.scripts.osm$//;
    }
    $JAVA_HOME = $ENV{'JAVA_HOME'};
    if (isEmpty($JAVA_HOME))
    {
        $JAVA_HOME = $DEF_JAVA_HOME;
    }
    $UNZIP = File::Spec->catfile($EMDROOT, 'bin', "unzip$EXE_SUFFIX");
    if (!-x $UNZIP)
    {
        $UNZIP = File::Spec->catfile($EMDROOT, 'bin', "$EMUNZIP$BAT_SUFFIX");
    }
    if (!-x $UNZIP)
    {
        $UNZIP = 'unzip';
    }
    $ZIP = File::Spec->catfile($EMDROOT, 'bin', "zip$EXE_SUFFIX");
    if (!-x $ZIP)
    {
        $ZIP = File::Spec->catfile($EMDROOT, 'bin', "$EMZIP$BAT_SUFFIX");
    }
    if (!-x $ZIP)
    {
        $ZIP = 'zip';
    }
    $OUILOC   = $ENV{'OUILOC'};
    $PERL5LIB = getPERL5LIB();
}

# usage()
#
# Display a usage help page
#
sub usage
{
    if (defined $ARGS{$VE})
    {
        printf(STDOUT "\n%s Version %s\n%s\n\n", $scriptName, VERSION, COPYRIGHT);
        cleanAndExit(0);
    }

# Start of "here doc".
    print STDOUT <<_EOM_
    USAGE
      perl empexecuteSQL.pl <options>

options
          -oh <ORACLE_HOME>
          -os <List of SIDs(separated by ampersand(&)) running out 
               the ORACLE_HOME>
          -sql Absolute path to the SQL script

Examples:

empexecuteSQL.pl -oh /private/OraHome1 -os smcore1&smcore2
                -sql /private/OraHome1/apply.sql

_EOM_
      ;

}

# statusf(<status>)
#
# Returns the termination status of command
#
sub statusf($)
{
    my ($status) = @_;

$status = E_FAIL if (!defined $status);
    $status = ($status >> 8);
    return $status;
}

# cleanAndExit(<status>)
#
# Clean up and terminate with status
#
sub cleanAndExit($)
{
    my ($status) = @_;

exit statusf($status);
}

# subroutines to parse the patch metadata file
# The following routines parse an XML file to construct a node tree
# Based on the parser utility functions on www.xml.com
# http://www.xml.com/pub/a/98/09/xml-perl.html

sub COUNT () {0;}
sub MINLEV () {1;}
sub SEEN  () {2;}
sub CHARS () {3;}
sub EMPTY () {4;}
sub PTAB  () {5;}
sub KTAB  () {6;}
sub ATAB  () {7;}
sub VTAB  () {8;}

sub new
{
    my $class = shift;
    my $self = [0, undef, 0, 0, 1, {}, {}, {},{}];
    
    bless $self, $class;
}

# The start handler subroutine. This creates the node tree, and does the  parsing of the XML file.
# The first 2 parameters passed to a start handler are the parser object and the name of the element just started. 
# After shifting these two things off the list, we lookup what information we've got for that element in the %elements hashtable.
# If there's no object there, we create one, and set the SEEN field for it.
# The $seen variable gets incremented for each new element type we see. 
# Refer to http://www.xml.com/pub/a/98/09/xml-perl.html for more explanation

sub start_handler
{
    my $p = shift;
    my $el = shift;
    
    my $elinf = $elements{$el};

if (not defined($elinf))
    {
        $elements{$el} = $elinf = new;
        $elinf->[SEEN] = $seen++; 
    }
    
    $elinf->[COUNT]++;

my $partab = $elinf->[PTAB];

my $parent = $p->current_element;
    if (defined($parent))
    {
        # store parent info
        $partab->{$parent}++;
        my $pinf = $elements{$parent};

# Increment our slot in parent's child table
        $pinf->[KTAB]->{$el}++;
        $pinf->[EMPTY] = 0;
    }
    else
    {
        $root = $el;
    }

# Deal with attributes

my $atab = $elinf->[ATAB];
    my $vtab = $elinf->[VTAB];

my $flag = "0";
    while (@_)
    {
                                                                                       
       # Read the attribute name and its value, and store the value in a comma seperated
        # list asscoiated with the attribute name
        my $att = shift;

my $val = shift;
        $atab->{$att} = $atab->{$att}.",".$val; # store the value in a comma separated list.
        if($att eq "defaultValue")
        {
          $flag = "1";
        }

}
    # If no default value provided for attribute, put '_' as default value.
    if( $flag eq "0")
    {
       $atab->{"defaultValue"} = $atab->{"defaultValue"}.","."_";
    }

}  # End start_handler

# subroutine to set the level of each node in the tree
sub set_minlev
{
    my ($el, $lev) = @_;

my $elinfo = $elements{$el};
    if (! defined($elinfo->[MINLEV]) or $elinfo->[MINLEV] > $lev)
    {
        my $newlev = $lev + 1;

$elinfo->[MINLEV] = $lev;
        foreach (keys %{$elinfo->[KTAB]})
        {
            set_minlev($_, $newlev);
        }
    }
}  # End set_minlev

sub bystruct
{
    my $refa = $elements{$a};
    my $refb = $elements{$b};

$refa->[MINLEV] <=> $refb->[MINLEV]
        or $refa->[SEEN] <=> $refb->[SEEN];
}  # End bystruct

#subroutine to analyze the parsed metadata file 
sub sql_file 
{

my $parser = new XML::Parser(ErrorContext => 2); # create an instance of the XML parser

# Associate start handler
  $parser->setHandlers(Start => \&start_handler
                    );

# parse the file
  $parser->parsefile($file);

# set the level of the parsed tree root to 0
  set_minlev($root, 0);

my $el;

# loop over all the nodes of the parsed tree
  foreach $el (sort bystruct keys %elements)
  {
    my $ref = $elements{$el};

# for 'parameter' nodes, look at the script_loc attribute value, and find the corresponding value in the 'assignment' attribute.
    if($el eq "proc:Parameter")
   {
        my @list = %{$ref->[ATAB]};
        my $pos = 0;
        my $size = @list;
        # for every item in the list of attributes of the 'parameter node', look for the one whose attribute value is script_loc. 
        # Mark its position in the variable 'pos'.
        foreach $item (@list)
        {
           @lit = split(/,/, $ref->[ATAB]->{$item});
           for ($count=@lit; $count>0; $count--)
           {
                 if($lit[$count] eq "script_loc")
                 {
                      $pos = $count;
                      last;

}
           }
        }
        # Now look at the attribute values for the attribute 'assignment' 
        @loc = split(/,/, $ref->[ATAB]->{"assignment"});
        # The desired SQL script location is the value at the location 'pos'.
        $script_loc = $loc[$pos];
     }

# for 'Argument' nodes, look at the sql_script and cpu_name attribute values
     if($el eq "proc:Argument")
     {
        my @list = %{$ref->[ATAB]};
        my $pos1 = 0;
        my $pos2 = 0;
        my $size = @list;
        foreach $item (@list)
        {

@lit = split(/,/, $ref->[ATAB]->{$item});
           # Mark the positions of 'sql_script' and 'cpuname' attributes in the list of attributes.
           for ($count=@lit; $count>0; $count--)
           {
                 if($lit[$count] eq "sql_script")
                 {
                      $pos1 = $count;
                 }
                 if($lit[$count] eq "cpuname")
                 {
                      $pos2 = $count;
                 }
           }
        }
        # read the default values assigned to sql_script and cpuname
        @loc = split(/,/, $ref->[ATAB]->{"defaultValue"});
        $m_sql_script = $loc[$pos1];
        $m_cpu_name = $loc[$pos2];
     }

}

$output_string =  $script_loc."/".$m_sql_script;
   @tokens = split(/\//, $output_string);
   $str = "";
   # substitute A$oracle_home by $oracle_home, and A$cpuname by the default name of the cpu.
   foreach $item (@tokens)
   {
     if($item eq 'A$oracle_home' )
     {
       $str = $str.'$oracle_home'.'/';
     }
     elsif($item eq 'A$cpuname')
     {
       $str = $str.$m_cpu_name.'/';
     }
     else
     {
      $str = $str.$item.'/';
     }

}
   # Remove the last '/'
   $len = length($str);
   $len = $len - 1;
   $str = substr($str,0,$len);
   return $str
}

# --------------------- Main program ----------------------------------
setOutputAutoflush();

parseArgs();

#
# Check for help requested
#
if ($isHelp)
{
    usage();
    cleanAndExit(0);
}

#
# Set the EMDROOT environment
#
setupOSD();
emdenv();

# If the user chose to apply the default sql script, look at the patch metadata file to find
# the sql script and its location
if($sqlscript eq 'defaultSqlScript')
{
  # find the patch metadata file
  $file = findFile($ORACLE_HOME, 'patchmd.xml');

# Log an error if the patch metadata file is not found
  if (isEmpty($file))
  {
    logf("Error: Patch metadata file not found");
  }
  # Parse the metadata file, extract the sql script location out of it
  $sqlscript =  sql_file();

# Now we need to replace the token '$oracle_home' in the sql script location
  # with the actual value of the oracle home. Hence split the string on '/', 
  # and replace the token. Note that '/' is used in the patch metadata file
  # irrespective of whether it is a linux patch or a windows patch
  @tokens = split(/\//, $sqlscript);
  $str = "";

foreach $item (@tokens)
  {
     if($item eq '$oracle_home' )
     {
       $str = $str.$ORACLE_HOME.'/';
     }
     else
     {
       $str = $str.$item.'/';
     }

}

# Remove the last '/'
   $len = length($str);
   $len = $len - 1;
   $str = substr($str,0,$len);
   $sqlscript = $str;

# Try to find the sql script. If not found, log an error
   if (! -f $sqlscript)
   {
     logf("Error: SQL script to execute not found");
   }

}

#
# Set ORACLE_HOME, ORACLE_SID environment and invoke SQL
#
my $overall_sql_result = 0; # Overall result over sids
for(my $i=0; $i < scalar(@ORACLE_SIDs); $i++)
{
    my $oracle_sid = $ORACLE_SIDs[$i];
    my $retval = 0;
    logf ("Executing SQL script ($sqlscript) on database [$ORACLE_HOME (OH), $oracle_sid (SID)]");
    oraenv($ORACLE_HOME, $oracle_sid);
    $retval = executesqlSID($oracle_sid, $sqlscript);
    if ($retval != 0)
    {
        logf("Error: SQL script failed on database [$ORACLE_HOME (OH), $oracle_sid (SID)]");
        $overall_sql_result = $retval;
    }
}

cleanAndExit($overall_sql_result);

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de