# $Header: emdb/sysman/admin/scripts/db/reorg/reorganize.pl /st_emdbsa_11.2/4 2009/03/21 05:46:43 rasundar Exp $
#
# reorganize.pl
# 
# Copyright (c) 2002, 2009, Oracle and/or its affiliates. All rights reserved. 
#
#    NAME
#      reorganize.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)
#    rasundar    03/18/09 - remove hardcoded password in test code
#    mahessub    01/20/09 - Copy 10.2.0.5GC on top of 11.2 DB Control
#    vgoli       11/21/08 - fix bug 7569063: extra check for ORA errors
#    mahessub    05/20/08 - Merge 11GC CM Functionality
#    rpattabh    01/03/08 - Project 25671: Masking integration with Clone
#    xshen       08/07/07 - bug 6326606
#    pkaliren    06/26/07 - Adding runSynchScriptFile
#    szhu        10/10/03 - Turn off tracing 
#    szhu        11/08/02 - Handle spool file
#    szhu        10/06/02 - szhu_reorg_job
#    szhu        10/06/02 - filter DB credentials
#    szhu        10/04/02 - Creation
# 

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

use strict;
use File::Temp qw/ tempfile tempdir /;
use vars qw/ $userID $role /;

# Global variables
$userID = "";
$role = "";

#A temporary solution and will be removed after EMD supports this setting
#in logging.xml.
#$ENV{EMAGENT_PERL_TRACE_LEVEL} = 0;  #DEBUG level.

# Set the source DB credential
# setSrcDBCredential(userName, password)
sub setSrcDBCredential
{
  EMD_PERL_DEBUG("reorganize.setSrcDBCredential(): *** START ***");
  my ($userName, $password, $my_role, $tns) = @_;
	if(!$tns){
			$userID = $userName."/".$password;
	}
	else{
			$userID = $userName."/".$password."\@${tns}";
	}

  EMD_PERL_DEBUG("reorganize.setSrcDBCredential(): User Name: $userName");

  if($my_role)
  {
    $role = $my_role;
    EMD_PERL_DEBUG("reorganize.setSrcDBCredential(): role: $role");
  }
  
  EMD_PERL_DEBUG("reorganize.setSrcDBCredential(): *** END ***");
}

# Filter out DB credential from sql script, which is written to emd_perl.trc
# filterDBCredential(sqlScript)
sub filterDBCredential
{
  my ($sqlScript) = @_;

  my $position1 = index($sqlScript, "CONNECT");
  my $position2 = index($sqlScript, "AS");
  if($position2 == -1)
  {
     $position2 = index($sqlScript, "\@");
  }
  my $replacedLength = $position2 - $position1 - 9;
  substr($sqlScript, $position1 + 8, $replacedLength) = "username/password(Hiden intentionally)";

  return $sqlScript;
}

# Parse the output string to detect ORA- errors
# parseOutput(output)
sub parseOutput
{
  EMD_PERL_DEBUG("reorganize.parseOutput(): *** START ***");

  my ($output) = @_;
  
  if ($output eq "")
  {
    EMD_PERL_ERROR("reorganize.parseOutput(): Output file is empty!");
  }

  # reorg backend error
  if ($output =~ /errorExitOraError!/)
  {
    EMD_PERL_ERROR("reorganize.parseOutput(): errorExitOraError! See output log.");
    exit(1);
  }
  
  elsif ($output =~ /errorExit!/)
  {
    EMD_PERL_ERROR("reorganize.parseOutput(): errorExit! See output log.");
    exit(1);
  }

  # sqlplus errors
  elsif ($output =~ /SP2-[0-9]+/)
  {
    EMD_PERL_DEBUG("reorganize.parseOutput(): SP2- ERROR! Exit! See output log.");
    exit(1);
  }

  # ORA errors
  elsif ($output =~ /ORA-[0-9]+/)
  {
    EMD_PERL_DEBUG("reorganize.parseOutput(): ORA- ERROR! Exit! See output log.");
    exit(1);
  }

  # successful
  else
  {
    EMD_PERL_DEBUG("reorganize.parseOutput(): No Error found.");        
  }  
  
  EMD_PERL_DEBUG("reorganize.parseOutput(): *** END ***");
}

# Run given sql script on target DB
# runSqlScript(sqlScript)
sub runSqlScript
{
  EMD_PERL_DEBUG("reorganize.runSqlScript(): *** START ***");
  
  my ($sqlScript) = @_[0];
  
  my $sqlScript_debug = &filterDBCredential($sqlScript);
  EMD_PERL_DEBUG("reorganize.runSqlScript(): SQL:\n$sqlScript_debug");

  open(SQL_SCRIPT, "|$ENV{ORACLE_HOME}/bin/sqlplus /nolog")
    || die "Cannot open pipe for SQL_SCRIPT";
  print SQL_SCRIPT $sqlScript;
  close SQL_SCRIPT || die "Bad SQL_SCRIPT";
  
  EMD_PERL_DEBUG("reorganize.runSqlScript(): *** END ***");
}

# Run reorganize sql script file
# Call &set_env(oracleHome, oracleSid) before calling this method.
# runReOrgScriptFile(oracle_home, oracle_sid, sqlScriptFileName, spoolFileName)
sub runReOrgScriptFile
{
  EMD_PERL_DEBUG("reorganize.runReOrgScriptFile(): *** START ***");

  &set_env_var(@_[0], @_[1]);

  my ($sqlScriptFileName) = @_[2];
  my ($spoolFileName) = @_[3];
  
  EMD_PERL_DEBUG("reorganize.runReOrgScriptFile(): sql script file: $sqlScriptFileName");
  EMD_PERL_DEBUG("reorganize.runReOrgScriptFile(): spool file: $spoolFileName");
  
  my $sql_string = "";
  if($role eq "")
  {
    $sql_string .= "CONNECT $userID;\n";
  }
  else
  {
    $sql_string .= "CONNECT $userID AS $role;\n";
  }
  $sql_string .= "\@$sqlScriptFileName;\n";
  $sql_string .= "EXIT;\n";

  &runSqlScript($sql_string);

  #Open the spool file to parse execution error
  #A temp solution
  #my $lastDatIndex = rindex($sqlScriptFileName, ".");
  #my $spoolFileName = substr($sqlScriptFileName, 0, $lastDatIndex + 1);
  #$spoolFileName .= "log";
  #EMD_PERL_DEBUG("reorganize.runReOrgScriptFile(): spool file:\n$spoolFileName");
  
  open (OUT_PUT, "$spoolFileName") || die "Unable to open spool file for OUT_PUT\n";
  my @output_content = <OUT_PUT>;
  my $output_string = "@output_content";
  close OUT_PUT;
  
  EMD_PERL_DEBUG("reorganize.runReOrgScriptFile(): OUT_PUT:\n$output_string");

  &removeFile($spoolFileName);
  
  &parseOutput($output_string);
  
  EMD_PERL_DEBUG("reorganize.runReOrgScriptFile(): *** END ***");
}
1;

# Run reorganize sql script file
# Call &set_env(oracleHome, oracleSid) before calling this method.
# runSynchScriptFile(oracle_home, oracle_sid, sqlScriptFileName, spoolFileName, dbLinkUser, dbLinkPwd)
sub runSynchScriptFile
{
  EMD_PERL_DEBUG("reorganize.runSynchScriptFile(): *** START ***");

  &set_env_var(@_[0], @_[1]);

  my ($sqlScriptFileName) = @_[2];
  my ($spoolFileName) = @_[3];
  my ($dbLinkUser)  = @_[4];
  my ($dbLinkPwd)  = @_[5];

  EMD_PERL_DEBUG("reorganize.runSynchScriptFile(): sql script file: $sqlScriptFileName");
  EMD_PERL_DEBUG("reorganize.runSynchScriptFile(): spool file: $spoolFileName");

  my $sql_string = "";

  if($role eq "")
  {
    $sql_string .= "CONNECT $userID;\n";
  }
  else
  {
    $sql_string .= "CONNECT $userID AS $role;\n";
  }
  if($dbLinkUser eq "")
  {
    $sql_string .= "\@$sqlScriptFileName;\n";
  }
  else
  {
    $sql_string .= "\@$sqlScriptFileName $dbLinkUser $dbLinkPwd ;\n";
  }
  $sql_string .= "EXIT;\n";
  &runSqlScript($sql_string);

  #Open the spool file to parse execution error
  #A temp solution
  #my $lastDatIndex = rindex($sqlScriptFileName, ".");
  #my $spoolFileName = substr($sqlScriptFileName, 0, $lastDatIndex + 1);
  #$spoolFileName .= "log";
  #EMD_PERL_DEBUG("reorganize.runSynchScriptFile(): spool file:\n$spoolFileName");

  open (OUT_PUT, "$spoolFileName") || die "Unable to open spool file for OUT_PUT\n";
  my @output_content = <OUT_PUT>;
  my $output_string = "@output_content";
  close OUT_PUT;

  EMD_PERL_DEBUG("reorganize.runSynchScriptFile(): OUT_PUT:\n$output_string");

  &removeFile($spoolFileName);

  &parseOutput($output_string);

  EMD_PERL_DEBUG("reorganize.runSynchScriptFile(): *** END ***");
}
1;

#Tests:
sub main_reorg
{
  $ENV{EMAGENT_PERL_TRACE_LEVEL} = 0;
  my $oracle_home = "/private1/oracle_kits/v901";
  my $oracle_sid = "v901";
  my $sqlScriptFileName = "/private2/backup/test.sql";
  my $spoolFileName = "/private2/backup/test.log";
  #### fill in db password as the second parameter below for testing purposes
  setSrcDBCredential("system", "", "0", "0");
  ### fill in sys password as the second parameter below for testing as sys user
  #setSrcDBCredential("sys", "", "sysdba", "0");
  runReOrgScriptFile($oracle_home, $oracle_sid, $sqlScriptFileName, $spoolFileName);
}

#main_reorg();