Wednesday, January 12, 2005

exp/imp Korn Shell scripts

Today, I am releasing two scripts that will allow the people here to export a schema and re-import it into another database. Both are Korn Shell scripts so obviously they are for Unix or variants.
=======================================================================
export.sh -
Usage: export.sh -p [dba_user_password] -d [tns_entry_of_db_2_exp] -f [export_file_name.dmp] -s [schema_nameschema_name,shema_name<,...>]

These parameters need to be in the order as shown. The -s option can either be a space or comma (no spaces) list.
========================================================================
import.sh -
Usage: import.sh -p [dba_user_password] -d [tns_entry_of_db_2_exp] -f [export_file_name.dmp]
========================================================================

In both scripts the -f option can have the full path to another directory, but will automatically change the path to the predefined variable using the filename supplied.

In both scripts if any option is omitted, the user will be prompted for the value.

Here are the scripts!:

export.sh
=======
#!/usr/bin/ksh
# export.sh - Korn Shell script to export (an) Oracle schema(s).

# January 6, 2005
# Author - John Baughman
# Hardcoded values: copy_dir, pipefile, dba
dba=PSG

copy_dir=/dbcopy/
# Check the dump directory exists
if [[ ! -d "${copy_dir}" ]]then
echo
echo "${copy_dir} does not exist !"
echo
exit 1
fi

pipefile=~/backup/backup_pipe
# Check we have created the named pipe file, or else there is no point
# continuing
if [[ ! -p ${pipefile} ]]then
echo
echo "Create the named pipe file ${pipefile} using "
echo " $ mknod p ${pipefile}"
echo
exit 1
fi

# Loop through the command line parameters
# -p - Currently the PSG user password.
# -d - The Oracle SID of the database to export. This matches the TNS entry.
# -s - The schema to import
# If either/all of these don't exist, prompt for them.
# Now, check the parameters...
while getopts ":p:d:f:s" opt; do
case $opt in
p ) psg_password=$OPTARG ;;
d ) sid=$OPTARG ;;
f ) raw_name=${OPTARG##/*/} ;;
s ) shift $((OPTIND))
raw_schema=$@ ;;
\? ) print "usage: export.sh -p password -d tns_entry -s schema[...]"
exit 1 ;;
esac
done
# Get the missing ${dba} user password

while [[ -z ${psg_password} ]]; do
read psg_password?"Enter ${dba} Password: "
done

# Get the missing SID
while [[ -z ${sid} ]]; do
read sid?"Enter Oracle SID: "
done

# Get the missing "raw" name
while [[ -z ${raw_name} ]]; do
read raw_name?"Enter the export dump file name: "
done

while [[ -z ${raw_schema} ]]; do
print "Enter the schema(s)"
read raw_schema?"(if more than one schema, they can either be space or comma delimited): "
done

# Fix up the raw schema list
schema_list=""
raw_schema=$(print $raw_schema tr "[a-z]" "[A-Z]")
for name in $raw_schema; do
if [[ -z $schema_list ]]; then
schema_list="${name}"
else
schema_list="${schema_list},${name}"
fi
done
raw_schema=${schema_list}
schema_list="(${schema_list})"

# Fix up the export file name here...
export_file=${copy_dir}${raw_name}.Z

# Let's go!!!
print ""
print "*******************************************************"
print "* Exporting ${raw_schema} schema(s) at `date`"
print "*******************************************************"
print

compress < ${pipefile} > ${export_file} &
exp ${dba}/${psg_password}@${sid} direct=y log=${copy_dir}${raw_name}.exp.log statistics=none buffer=1000000 feedback=10000 file=${pipefile} owner=${schema_list}

print
print "*******************************************************"
print "* Export Completed at `date`"
print "*******************************************************"
print

exit 0


import.sh
=======
#!/usr/bin/ksh
# import.sh - Korn Shell script to import (an) Oracle schema(s).

# January 6, 2005
# Author - John Baughman
# Hardcoded values: copy_dir, pipefile, dba

dba=PSG

copy_dir=/dbcopy/
# Check the dump directory exists
if [[ ! -d "${copy_dir}" ]]then
echo
echo "${copy_dir} does not exist !"
echo
exit 1
fi

pipefile=~/backup/backup_pipe
# Check we have created the named pipe file, or else there is no point
# continuing
if [[ ! -p ${pipefile} ]]then
echo
echo "Create the named pipe file ${pipefile} using "
echo " $ mknod p ${pipefile}"
echo
exit 1
fi

# Loop through the command line parameters
# -p - Currently the PSG user password.
# -d - The Oracle SID of the database to export. This matches the TNS entry.
# -f - The export file to import.
# If either/all of these don't exist, prompt for them.
# Now, check the parameters...
while getopts ":p:d:f:" opt; do
case $opt in
p ) psg_password=$OPTARG ;;
d ) sid=$OPTARG ;;
f ) raw_name=${OPTARG##/*/} ;;
\? ) print "usage: export.sh -p password -d tns_entry -f dump_file.dmp"
exit 1 ;;
esac
done

# Get the missing ${dba} user password
while [[ -z ${psg_password} ]]; do
read psg_password?"Enter ${dba} Password: "
done

# Get the missing SID
while [[ -z ${sid} ]]; do
read sid?"Enter Oracle SID: "
done

# Get the missing "raw" name
while [[ -z ${raw_name} ]]; do
read raw_name?"Enter the export dump file name: "
done

# Fix up the export file name here...
export_file=${copy_dir}${raw_name}.Z

# Let's go!!!
print
print "*******************************************************"
print "* Importing ${export_file} at `date`"
print "*******************************************************"
print

uncompress < ${export_file} > ${pipefile} imp ${dba}/${psg_password}@${sid} log=${copy_dir}${raw_name}.imp.log full=y statistics=none buffer=1000000 feedback=10000 file=${pipefile} ignore=y

print
print "*******************************************************"
print "* Import Completed at `date`"
print "*******************************************************"
print

exit 0

Sunday, January 09, 2005

Yes, the name says it all...

According to the Oracle reference: ORA-06950 = No Error

What a fantastic error message. Makes absolutely NO sense what so ever. I mean, why bother setting an error if there is none?

Hopefully, future posts will be more informative. Here's where I'll dump my Oracle knowledge for all to see (if you really care).

-John