# $Header: export.pl 19-jul-2005.10:03:22 rgiroux Exp $ # # export.pl # # Copyright (c) 2002, 2005, Oracle. All rights reserved. # # NAME # export.pl - # # DESCRIPTION # # # NOTES # # # MODIFIED (MM/DD/YY) # rgiroux 07/19/05 - # rgiroux 07/19/05 - escape wildcard # rgiroux 07/06/05 - implement %d wildcard # rgiroux 06/10/05 - make repeat_job a large parameter # ngade 06/09/05 - change for expdp # rgiroux 06/03/05 - add error messages # rgiroux 05/27/05 - implement repeat_jobs # rgiroux 05/04/05 - fix for bug 4345325; don't allow nonprived users # to turn on tracing # rgiroux 02/28/05 - fix for bug 4193463; use tns connector and agent # oracle home/sid when using DBI on NT # ngade 06/29/04 - add set_env_var for expdp # ngade 04/01/04 - tts support and cleanup # ngade 09/19/03 - nt fix -bug 3134969 # npamnani 09/18/03 - fix for bug 3134969 # rgiroux 04/20/03 - fixes for bugs 2800243, 2842012, 2901592, 2889763, 2867716, 2867730, 2806861 # ngade 02/21/03 - remove using perl from /usr/local/bin # ngade 01/31/03 - use start_datapump_job # ngade 01/17/03 - add support for db_10_or_higher # ngade 01/02/03 - remove read_datapump_output # ngade 11/13/02 - add 10i functionality # ngade 10/23/02 - remove checking for fileExecute permissions # ngade 09/23/02 - change support for lower-case users/tables # ngade 09/11/02 - update 10i jobsubmission # ngade 09/04/02 - support lower-case users/tables # ngade 08/29/02 - fix bug 2463124 # ngade 08/08/02 - cleanup tempfiles # rgiroux 08/02/02 - begin 10i support # ngade 06/26/02 - ngade_change_job_submission # ngade 06/26/02 - Creation # require "$ENV{EMDROOT}/sysman/admin/scripts/db/db_common.pl"; require "$ENV{EMDROOT}/sysman/admin/scripts/db/dataUtilities/datapump_job.pl"; use strict; ## global variables. use vars qw($oracle_home $oracle_sid $parse_errors $script $generate_log $log_file_name $smp_ignore $tns_connect $is_prived $repeat_job_script); ################################################### ## MAIN SCRIPT TO CALL EXPORT JOB. ################################################### sub export() { print_debug("Data Pump Export: Started..........\n"); my($user, $password, $role, $db_10_or_higher, $job_name) = @_; if(!defined($db_10_or_higher)) { $db_10_or_higher = "FALSE"; } &set_env_var($oracle_home, $oracle_sid, $db_10_or_higher); if($db_10_or_higher =~ /TRUE/i) { ## do 10i export if($repeat_job_script) { ## construct the replacement string for the %D date wildcard my($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(); my $wc = sprintf("%02d%02d%02d",$year%100,$mon+1,$mday); ## replace escaped %D with the date wildcard $repeat_job_script =~ s/"%D"/$wc/g; my $SQLPID = open(SQL, "|$ENV{ORACLE_HOME}/bin/sqlplus -s /nolog"); if(!$SQLPID) { print_error("ERROR: could not run SQLPLUS\n"); exit (1); } print_debug("SQLPID = $SQLPID"); my $userid = &set_userid($user, $password, $role); print SQL "connect $userid\n"; print SQL "$repeat_job_script\n"; print SQL "exit\n"; close(SQL); ## fall through here and start the datapump job ## the repeat_job_script defines the job but doesn't start it (abort_step => -1) ## in exactly the same was as a regular, one-time only job. that way we can use ## all the same logging and error handling as we always have. } ## fix for bug 4193463; for tns connect use agent home/sid or DBI will fail if($tns_connect) { $oracle_home = $ENV{ORACLE_HOME}; $oracle_sid = $ENV{ORACLE_SID}; &set_env_var($oracle_home, $oracle_sid, $db_10_or_higher); } &start_datapump_job($user, $password, $role, $job_name); } else { my $log_file = run_export(&set_userid($user, $password, $role)); if($parse_errors =~ /TRUE/i) { parse_log($log_file); } } } ################################################### ## SET DATABASE USERID ################################################### sub set_userid() { my($user, $password, $role) = @_; my $userid = $user."/".$password; $userid = ($role =~ /NORMAL/i)? $userid: "$userid as $role"; return $userid; } ################################################### ## PERFORM EXPORT OPERATION. ################################################### sub run_export { print_debug("Data Pump Export: Running Export Command Line\n"); my ($userid) = @_; # Create a temporary File (my $param_fh, my $param_filename) = create_temp_file(); ## construct the replacement string for the %D date wildcard my($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(); my $wc = sprintf("%02d%02d%02d",$year%100,$mon+1,$mday); ## replace escaped %D with the date wildcard $script =~ s/"%D"/$wc/g; ## replace non-escaped %D with the date wildcard ## (sending this over escaped was causing perl errors) $log_file_name =~ s/%D/$wc/g; open(PARAM_FILE, ">$param_filename"); print PARAM_FILE $script; close PARAM_FILE; my $exp_exe = "$ENV{ORACLE_HOME}/bin/exp"; my $command = "|$exp_exe PARFILE='$param_filename'"; if($generate_log =~ /FALSE/i) { # Create a temporary log File my($log_fh, $log_file) = create_temp_file(".log"); close $log_fh; $command = $command." "."LOG=$log_file"; $log_file_name = $log_file; } print_debug("Data Pump Export: Command: $command\n"); print_debug("Data Pump Export: generateLog: $generate_log\n"); print_debug("Data Pump Export: log_file_name: $log_file_name\n"); open(DATA_MGMT, $command)|| die "Couldn't execute: $exp_exe"; print DATA_MGMT "$userid\n"; print DATA_MGMT "exit"; close DATA_MGMT; close $param_fh; return $log_file_name; } ################################################### ## PARSE LOG FILE FOR ERRORS. ################################################### sub parse_log() { print_debug("Data Pump Export: Parsing logfile: $_[0]\n"); if (-e $_[0]) { open(LOGFILE, $_[0]) || die "Can't open :$!\n"; my @cont_arr = ; close LOGFILE; my $cont = "@cont_arr"; if(($cont !~ /ORA-[0-9]/) && ($cont !~ /EXP-[0-9]/)) { print_debug("Data Pump Export: No Errors\n"); } elsif($cont =~ /EXP-00000/) { print_debug("Data Pump Export: EXPORT ERROR[00000]\n"); exit(1); } elsif($smp_ignore =~ /TRUE/i) { print_debug("Data Pump Export: SMP_IGNORE=TRUE\n"); } else { print_debug("Data Pump Export: SMP_IGNORE = $smp_ignore\n"); exit(1); } } else { print_debug("Data Pump Export: File does not exist: $_[0]\n"); } } ################################################### ## cli EXPORT TO START AND RUN A EXPORT JOB. ################################################### sub expdp { my $user; my $password; my $role; my $db_10_or_higher; my $dmpfile_format10g; ($db_10_or_higher, $script, $oracle_home, $oracle_sid, $user, $password, $role, $smp_ignore, $generate_log, $parse_errors, $log_file_name, $dmpfile_format10g) = @_; &set_env_var($oracle_home, $oracle_sid, $db_10_or_higher); if($db_10_or_higher =~ /TRUE/i && $dmpfile_format10g =~ /TRUE/i) { ## do 10g cli export print_debug("Data Pump Export: Running expdp Command Line\n"); my $userid = &set_userid($user, $password, $role); my $exp_exe = "$ENV{ORACLE_HOME}/bin/expdp"; my $command = "|$exp_exe ". $script; open(DATA_MGMT, $command)|| die "Couldn't execute: $exp_exe"; print DATA_MGMT "$userid\n"; print DATA_MGMT "exit"; close DATA_MGMT; if($parse_errors =~ /TRUE/i) { &parse_log($log_file_name); } } else { ## call pre-10g export. ## for pre10g TTS always needs to be done in sysdba mode. &export($user, $password, $role); } } ################################################### ## TEST CASE FOR EXPORT. ## 1. Uncomment exptest(), modify any parameters. ## 2. set EMDROOT and run command ## 3. runperl export.pl ################################################### ## &exptest(); sub exptest() { $oracle_home = $ENV{ORACLE_HOME}; $oracle_sid = $ENV{ORACLE_SID}; $script = " owner=scott, rows=y, file=/tmp/expdat.dmp"; $smp_ignore="true"; $generate_log="false"; $parse_errors="true"; $log_file_name="/tmp/exp.log"; my $db_user="SYSMAN"; my $db_passwd="sysman"; my $db_role="normal"; &export($db_user, $db_passwd, $db_role); } 1;