Tuesday, January 08, 2008

My first chain blog [sic]

I usually delete emails that have anything to do with "being tagged", forward to x number of buddies and family members, etc. Irregardless of who they are from, I just marked them as spam and forget about them. So I get this email from Dan Norris and my finger is hovering over the kill button when I start to read a little of the preview. Hmm... a blog tag. Still, I am not overly excited, but it is from Dan, so I open it up and read it. Then I read the links (to his blog and his buddy Jake), then I keep reading more links. Interesting stuff.

Ok, so just this once I will indulge these silly chain mail things (well don't I sound mature). Actually, I was honored to be included in Dan's 8, and I am having a hard time coming up with 8 folks I would contact. But not being a big fan of such things anyway, I have no problems bending the rules a little.

And just for the record, I did enjoy this exercise, especially reading about other folks with whom I have communicated, and some I have never met.

1. I was awarded a "Citizenship Award" from the American Daughters of the Revolution. To this day, I have never taken the time to find out who that group (cult?) is. I was in high-school at the time, and when asked what I wanted to do with my life, I said I wanted to make video games. Yes, I was an outstanding citizen.

3. My beautiful wife originally was attracted to me when I had a monster beard and earlocks to boot. Make no doubt, the hirsute appearance has not the draw; she liked me despite that.







4. In boyscouts, our troupe was a drum and bugle corp. I played a contra bass which I thoroughly enjoyed even though I was not that great (and that is being generous). I enjoyed traveling around Illinois, even done to Missouri once, not to mention several parades in downtown Chicago.

2. I put a pitchfork through my foot when I was kid. It hurt. It was right after my dad said "Be careful with that thing, you could hurt yourself." I proved him right.

10. When I started blogging about Oracle stuff, I had only just heard about the late Lex de Haan. He seemed like a really impressive and well respected man, so I named my first blog in his honor. Ironically, blogger updated their software and I have not been able to go back to the old blog.

7. I am the oldest of 5 children, and I am proud of them all. One brother plays professional volleyball and travels a lot (even outside the states), another brother is helping special needs students at our old high school and working on his Education certification; one is taller than me, the other is bigger, and I am not a small person by any means. One sister was working at McGraw Hill until she switched to a smaller publisher that she enjoys much more, and my other sister, on a lark, picked up Muay Thai with the well-known Team Toro for kicks.

13. One of these is false.

6. Going to college at the University of Illinois in Champaign-Urbana (I know, it is called UIUC), I had the wonderful experience of trying out the grassroots of the WWW with Mosaic. I maintained a webpage that blatantly said "I am NOT the author of Peanuts", but I still received fan mail. I even had a teacher ask me to write a special comic strip for her classroom. I was touched, but on the other hand, I got to thinking that some people just want to believe something no matter what you tell them.

0. I bailed hay once, and we had roosters for a short time while I was growing up. To put this in perspective, I grew up in the Chicago 'burbs, 10 miles outside of downtown. There are no farms within 10 miles of downtown, at least none that are not called zoos. I bailed hay because my parents at one time thought we were going to move out to a farm (Minnesota), so we tried out farm-life for a week. Crazy idea that. A couple years later, we somehow brought home roosters as pets and kept them in the basement of our neighborhood bungalow. Another crazy idea that. They did not last long; one died and the other was called out because our neighbors did not like how it crowed before the sun came up.

1.6180339 I jumped out of a perfectly normal, safe, solid, airplane at over 14000 feet. Instead of a parachute strapped to my back, I had a another guy who was risking his life for a measly $200. Also, if you did not get the golden ratio hint, my first dbms was Sybase (in a dusty attic somewhere in my memory).


I am going to tag Job Miller (great excuse to start a blog!) and Ravi Gaur.

Wednesday, January 02, 2008

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