The DataPump Index work-around

As I mentioned in a previous post, I did not like the way Data Pump does indexes. Apparently, many other folks feel the same way.

UPDATES: We are working on a new version which we hope to have ready in the next couple days. While it does not do anything fancy like Data Pump does, it does successfully parallelize those operations that current (10.2.0.3) limited to one worker process. Additionally, I have communicated with the fine folks working on Data Pump, and am very excited with the work they are doing to improve it.

Here is the script I am using to run indexes in parallel (the formatting sucks - thanks Blogger!):
#!/bin/ksh
####################
## Charles Schultz 19-Dec-2007
## Copied from http://sysadmintalk.com/showthread.php?threadid=762
## Assume paresh = Parallel Execution Shell
##
## Additionally, the original script was written for bourne shell. I have switched it to
## use kshell since that is what we use; I have left as much of the original code in tact,
## and attempted to comform to the style.
##
## Still could use some cleaning up. The error reporting is not stellar, but I believe it
## is passable.
####################

SETX=""
# SETX="set -x" # Uncomment this line to set -x
$SETX

# OLD SYNTAX: paresh command_file parallel_count
# NEW SYNTAX: paresh command_file parallel_count [$ORACLE_SID]

## GLOBAL VARIABLES -- Added by Charles Schultz 19-Dec-2007
LOCAL_BIN="/u01/app/oracle/local/bin" # Directory where dbhome and oraenv are located
TMP="/var/tmp" # Customizable TEMP directory
OUTPUT="paresh_output"
SLEEP_SEC=.1 # Duration of pause between get_shell requests
shell_status=0
egrep="grep -E"
DRYRUN=${DRYRUN:-"N"} # Did not want to mess with command-line parameters,
# so I made this either a hard-coded or environment var
# DRYRUN="Y"

#-------------------------------------------------------------
# message
# Establish a timestamp and echo the message to the screen.
# Tee the output (append) to a unique log file.
#-------------------------------------------------------------
#
message()
{
$SETX
timestamp=`date +"%D %T"`
echo "$timestamp $*" | tee -a $logfile
return
}
#-------------------------------------------------------------
# run_sql
# Added by Charles Schultz 19-Dec-2007
# Takes SQL as a parameter and runs it via sqlplus
# Connects "/ as sysdba"; avoids having to deal with passwords,
# but could be a security risk if not careful about the input file
#-------------------------------------------------------------
#
run_sql()
{
$SETX
shell_status=0
$SQLPLUS /nolog <<> $SQLLOGTMP 2>&1
connect / as sysdba
set echo on
set timing on
@$SQLFILE
exit
EOS

errors=`$egrep "ORA-|SP2-" $SQLLOGTMP`
if [ "$errors" != "" ]
then
cat $SQLLOGTMP >> $SLAVE_ERROR_LOG
shell_status=1
fi
cat $SQLLOGTMP >> $SQLLOG
return
}
#-------------------------------------------------------------
# get_shell
# This function is responsible for establishing the next
# command to be processed. Since multiple processes might
# be requesting a command at the same time, it has a built-
# in locking mechanism.
#-------------------------------------------------------------
# MODIFICATIONS Charles Schultz 19-Dec-2007
#
# Workfile will have SQL DDL statements, and thus span more than
# one line. I have adapted get_shell to read a chunk of lines
# up to either the CREATE or DECLARE statement. This is specific
# for DDL generated by 10.2.0.3 IMPDP index metadata.
#
# Also, updated the worker thread to go against sqlplus instead
# of executing the command "as is".
#-------------------------------------------------------------
#
get_shell()
{
$SETX
echo "`date` $1 Shell Request $$" >> $lklogfile
while : # until a command or end
do
next_shell="" # initialize command
if [ ! -s ${workfile} ] # if empty file (end)
then #
break # no more commands
fi #
if [ ! -f $lockfile ] # is there a lock?
then # not yet...
echo $$ > $lockfile # make one
echo "`date` $1 Lock Obtained $$" >> $lklogfile
if [ "$$" = "`cat $lockfile`" ]
then # we created it last
## START -- Added by Charles 19-Dec-2007
## Get the next line number of the last statement we are interested in
## FOR ALTER DDL (ie, Constraints)
# chunk_end=`$egrep -n "ALTER" $workfile|head -2|sed 's/:.*//'`
## FOR Data Pump Index DDL
chunk_end=`$egrep -n "CREATE|DECLARE" $workfile|head -2|sed 's/:.*//'`

if [ "$chunk_end" = "" ]
then
break # No more chunks found, exit
fi

line_num=${chunk_end##* } # if two line numbers found, line_num != chunk_end
# grab the last number as the line number

if [ $line_num = $chunk_end ] # if only one line found, run everything else
then
## Run whatever is left in the workfile
next_shell=`cat $workfile`
echo "" > $workfile
else # else get the next chunk
line_num=$((line_num-1))
next_shell=`head -${line_num} $workfile` # Get chunk of work
sed -e 1,${line_num}d $workfile > ${workfile}.tmp # Chop off chunk of work
mv ${workfile}.tmp $workfile
fi
## END -- Added by Charles 19-Dec-2007

rm -f $lockfile # turn off lock
echo "`date` $1 Shell Issued " >> $lklogfile
return # done, command in
else # variable "next_shell"
echo "`date` $1 Lock FAULTED $$" >> $lklogfile
fi # double check faulted
# else # locked by other
# echo "`date` $1 Lock Wait $$" >> $lklogfile
fi
sleep $SLEEP_SEC # brief pause
done # try again
return # only if no commands
}
#-------------------------------------------------------------
# paresh_slave
# This code is executed by each of the slaves. It basically
# requests a command, executes it, and returns the status.
#-------------------------------------------------------------
# Modified by Charles Schultz 19-Dec-2007
# Passes next_shell to run_sql
#-------------------------------------------------------------
#
paresh_slave()
{
$SETX
export SQLFILE="$OUTPUT/paresh_${1}.sql" # Sql file for this slave
SLAVE_ERROR_LOG="$OUTPUT/paresh_${1}_sql.errors" # Error log file for this slave
echo "" > $SLAVE_ERROR_LOG
SQLLOG="$OUTPUT/paresh_${1}_sql.log" # Sql log file for this slave
echo "" > $SQLLOG
SQLLOGTMP="$OUTPUT/paresh_${1}_sql.log.tmp" # Temp Sql log file for this slave
shell_count=0 # Commands done by this slave
get_shell $1 # get next command to execute
while test "$next_shell" != ""
do # got a command
shell_count=`expr $shell_count + 1`
message "Slave $1: Running sql: $next_shell"
echo "set sqlblanklines on" > $SQLFILE # This was added to avoid errors with blank lines in source
echo "$next_shell" >> $SQLFILE
shell_status=0
if [ "$DRYRUN" = "Y" ]
then
message "NOTE: This is a DRYRUN; no actual work will be done"
else
run_sql # execute command
fi
# shell_status=$? # get exit status
if [ "$shell_status" -gt 0 ]
then # then message
message "Slave $1: ERROR IN SQLPLUS status=$shell_status"
echo "Slave $1: ERROR IN Shell SQLPLUS status=$shell_status" >> $errfile
fi
# message "Slave $1: Finished Shell"
get_shell $1 # get next command
done # all done
message "Slave $1: Done (Executed $shell_count Shells)"
return # slave complete
}
##############################################################
# paresh_driver
# This code is executed by the top level process only. It
# parses the arguments and spawns the appropriate number
# of slaves. Note that the slaves run this same shell file,
# but the slaves execute different code, based on the
# exported variable PARESH.
#-------------------------------------------------------------
#
paresh_driver()
{
$SETX
rm -f $lklogfile # start a new log file
if [ "$1" = "" ] # first argument?
then # no?
master_file="master.list" # default value
else # yes?
if [ ! -f "$1" ] # does file exist?
then # no?
echo "$0: Unable to find File $1"
exit 1 # quit
else # yes?
master_file="$1" # use specified filename
fi
fi
if [ "$2" = "" ] # Second Argument?
then # no?
parallel_count=4
else # Yes?
if [ "$2" -lt 1 ] # Less than 1?
then # Yes?
echo "$0: Parallel Process Count Must be > 0"
exit 1 # quit
else # no?
parallel_count=$2 # Use Specified Count
fi
fi

## Added by Charles 13-Dec-2007
export PATH="$LOCAL_BIN:$PATH" # Setup Oracle Environment
export ORACLE_SID=${3:-$ORACLE_SID} # Set ORACLE_SID
export ORAENV_ASK=NO;. $LOCAL_BIN/oraenv
export SQLPLUS="$ORACLE_HOME/bin/sqlplus"
if [ ! -e $SQLPLUS ]
then
echo "Oracle Home $ORACLE_HOME not valid for Oracle SID $ORACLE_SID - exiting"
exit 1
fi

mkdir -p $OUTPUT
## Added by Charles 13-Dec-2007

message "------------------------------"
message "Master Process ID: $PARESH"
message "Processing File: $master_file"
message "Parallel Count: $parallel_count"
message "Log File: $logfile"
message "Working Output Directory: $OUTPUT"
message "------------------------------"
cp $master_file $workfile # make a copy of commands file
while test $parallel_count -gt 0
do
if [ ! -s $workfile ]
then
message "All Work Completed - Stopped Spawning at $parallel_count"
break # Quit spawning
fi
$0 $parallel_count &
message "Spawned Slave $parallel_count [pid $!]"
parallel_count=`expr $parallel_count - 1`
done
wait
message "All Done"
return
}
#-------------------------------------------------------------
# main
# This is the main section of the program. Because this shell
# file calls itself, it uses a variable to establish whether or
# not it is in Driver Mode or Slave Mode.
#-------------------------------------------------------------
#
if [ "$PARESH" != "" ] # If variable is set
then # then slave mode
workfile=$TMP/paresh.work.$PARESH # Work file with parent pid
lockfile=$TMP/paresh.lock.$PARESH # Lock file with parent pid
lklogfile=$TMP/paresh.lklog.$PARESH
logfile=$TMP/paresh.log.$PARESH # Log File with parent pid
errfile=$TMP/paresh.err.$PARESH # Error File with parent pid
paresh_slave $* # Execute Slave Code
else
PARESH="$$"; export PARESH # Establish Parent pid
workfile=$TMP/paresh.work.$PARESH # Work File with parent pid
lockfile=$TMP/paresh.lock.$PARESH # Lock File with parent pid
lklogfile=$TMP/paresh.lklog.$PARESH
logfile=$TMP/paresh.log.$PARESH # Log File with parent pid
errfile=$TMP/paresh.err.$PARESH # Error File with parent pid
rm -f $errfile # remove error file
paresh_driver $* # execute Driver Code
rm -f $workfile # remove work file
rm -f $lklogfile # remove lock log file
if [ -f $errfile ] # Is there was an error
then
message "*************************************************"
message "FINAL ERROR SUMMARY. Errors logged in $errfile"
cat $errfile | tee -a $logfile
message "*************************************************"
exit 1
fi
fi
exit