|
Wannabe, It gets pretty involved, but the basic algorithm for exporting/importing a large database is:
I've attached a UNIX shell script I first wrote back in the 1990s for doing this kind of thing. It is called "expimp.sh" and it is intended to perform a single stream of EXP->IMP (i.e. an entire schema, or a table, or a partition) by means of a UNIX FIFO or "pipe" in between. Doing this with a "pipe" is usually orders of magnitude faster than performing EXP->dmpfile->copy-file->IMP. No warranties implied or offered - caveat emptor... Hope this helps! Tim Gorman consultant -> Evergreen Database Technologies, Inc. postal => P.O. Box 630791, Highlands Ranch CO 80163-0791 website => http://www.EvDBT.com/ email => Tim@xxxxxxxxx mobile => +1-303-885-4526 fax => +1-303-484-3608 Lost Data? => http://www.ora600.be for DUDE "Technical skill is mastery of complexity, while creativity is mastery of simplicity" - E. Christoper Zeeman, 1977 Oracle Dba Wannabe wrote:
|
#!/bin/ksh
#=============================================================================
# File: expimp.sh
# Date: 15jan97
# Author: Tim Gorman (Evergreen Database Technologies, Inc)
# Description:
#
# Shell script to perform a specified export/import using the EXP and
# IMP utilities, connected via UNIX "named pipe".
#
# NOTE: Be sure that the security-protected password file
# "$HOME/.unpwd" has username, password, and TNS-connect-string
# stored for use by both the source and target databases in the
# format:
#
# username/password@TNS-connect-string
#
# without any white-space padding at the beginning or ending
# of the line...
#
# Modifications:
#=============================================================================
_Pgm=expimp
_ExpUn=system # Oracle account that the EXP utility is connecting to
_ImpUn=system # Oracle account that the IMP utility is connecting to
#
#-----------------------------------------------------------------------------
# Usage:
# - one command-line parameter provided means EXP/IMP by entire schema
# - two command-line parameters provided mean EXP/IMP by individual
# table
# - three command-line parameters provided mean EXP/IMP by individual
# table partition or subpartition
#-----------------------------------------------------------------------------
case $# in
5) _Mode=partition; _Targ=$3_$4_$5 ;;
4) _Mode=table; _Targ=$3_$4 ;;
3) _Mode=user; _Targ=$3 ;;
*) echo "Usage: \"${_Pgm}.sh ExpTNS ImpTNS SchemaName [ TableName
[ PartitionName] ]\""
exit 1
;;
esac
#
#-----------------------------------------------------------------------------
# Save command-line parameters to program variables...
#-----------------------------------------------------------------------------
_ExpTNS=$1
_ImpTNS=$2
_Own=$3
_Tbl=$4
_Prt=$5
#
#-----------------------------------------------------------------------------
# Create shell variables for important filenames...
#-----------------------------------------------------------------------------
_ExpPar=/tmp/${_Pgm}_exp_${_Targ}_$$.par
_ExpLog=/tmp/${_Pgm}_exp_${_Targ}_$$.log
_ExpOut=/tmp/${_Pgm}_exp_${_Targ}_$$.out
_ExpErr=/tmp/${_Pgm}_exp_${_Targ}_$$.err
_ImpPar=/tmp/${_Pgm}_imp_${_Targ}_$$.par
_ImpLog=/tmp/${_Pgm}_imp_${_Targ}_$$.log
_ImpOut=/tmp/${_Pgm}_imp_${_Targ}_$$.out
_ImpErr=/tmp/${_Pgm}_imp_${_Targ}_$$.err
_Pipe=/tmp/${_Pgm}_${_Targ}_$$.pipe
#
#-----------------------------------------------------------------------------
# Verify the two TNS-strings provided on the command-line...
#-----------------------------------------------------------------------------
${ORACLE_HOME}/bin/tnsping ${_ExpTNS} > /dev/null 2>&1
if (( $? != 0 ))
then
echo "${_Pgm}.sh: \"tnsping ${_ExpTNS}\" failed; aborting..."
exit 1
fi
${ORACLE_HOME}/bin/tnsping ${_ImpTNS} > /dev/null 2>&1
if (( $? != 0 ))
then
echo "${_Pgm}.sh: \"tnsping ${_ImpTNS}\" failed; aborting..."
exit 1
fi
#
#-----------------------------------------------------------------------------
# Retrieve the password for the specified user for this database from the
# file ".unpwd", which should reside in the user's $HOME directory. This file
# should also have 600 permissions...
#-----------------------------------------------------------------------------
if [ ! -r ${HOME}/.unpwd ]
then
echo "${_Pgm}.sh: Password file \"${HOME}/.unpwd\" not found or not
readable; aborting..."
exit 1
fi
#
_ExpPwd="`grep -i "^${_ExpUn}/" ${HOME}/.unpwd|grep -i "@${_ExpTNS}$"|awk -F/
'{print $2}'|awk -F'@' '{print $1}'`"
if [[ "~${_ExpPwd}~" = "~~" ]]
then
echo "${_Pgm}.sh: Password file \"${HOME}/.unpwd\" does not have
\"${_ExpUn}\" password for \"${_ExpTNS}\"; aborting..."
exit 1
fi
#
_ImpPwd="`grep -i "^${_ImpUn}/" ${HOME}/.unpwd|grep -i "@${_ImpTNS}$"|awk -F/
'{print $2}'|awk -F'@' '{print $1}'`"
if [[ "~${_ImpPwd}~" = "~~" ]]
then
echo "${_Pgm}.sh: Password file \"${HOME}/.unpwd\" does not have
\"${_ImpUn}\" password for \"${_ImpTNS}\"; aborting..."
exit 1
fi
#
#-----------------------------------------------------------------------------
# Create a protected parameter file for the EXP utility...
#-----------------------------------------------------------------------------
echo "rows=y" > ${_ExpPar}
chmod 600 ${_ExpPar}
echo "userid=${_ExpUn}/${_ExpPwd}@${_ExpTNS}" >> ${_ExpPar}
echo "file=${_Pipe}" >> ${_ExpPar}
echo "log=${_ExpLog}" >> ${_ExpPar}
echo "buffer=134217728" >> ${_ExpPar} # 128Mb
echo "compress=n" >> ${_ExpPar}
echo "direct=y" >> ${_ExpPar}
echo "grants=n" >> ${_ExpPar}
echo "indexes=n" >> ${_ExpPar}
echo "record=n" >> ${_ExpPar}
echo "triggers=n" >> ${_ExpPar}
echo "constraints=n" >> ${_ExpPar}
case "${_Mode}" in
partition) echo "tables=${_Own}.${_Tbl}:${_Prt}" >> ${_ExpPar} ;;
table) echo "tables=${_Own}.${_Tbl}" >> ${_ExpPar} ;;
user) echo "owner=${_Own}" >> ${_ExpPar} ;;
esac
#
#-----------------------------------------------------------------------------
# Create a protected parameter file for the IMP utility...
#-----------------------------------------------------------------------------
echo "rows=y" > ${_ImpPar}
chmod 600 ${_ImpPar}
echo "userid=${_ImpUn}/${_ImpPwd}@${_ImpTNS}" >> ${_ImpPar}
echo "file=${_Pipe}" >> ${_ImpPar}
echo "log=${_ImpLog}" >> ${_ImpPar}
echo "buffer=134217728" >> ${_ImpPar} # 128Mb
echo "ignore=y" >> ${_ImpPar}
echo "commit=y" >> ${_ImpPar}
echo "indexes=n" >> ${_ImpPar}
echo "grants=n" >> ${_ImpPar}
echo "constraints=n" >> ${_ImpPar}
echo "fromuser=${_Own}" >> ${_ImpPar}
echo "touser=${_Own}" >> ${_ImpPar}
case "${_Mode}" in
partition) echo "tables=${_Tbl}:${_Prt}" >> ${_ImpPar} ;;
table) echo "tables=${_Tbl}" >> ${_ImpPar} ;;
esac
#
#-----------------------------------------------------------------------------
# Create the UNIX "named pipe" to act as the method of
# interprocess communication...
#-----------------------------------------------------------------------------
mknod ${_Pipe} p
if (( $? != 0 ))
then
echo "${_Pgm}.sh: \"mknod ${_Pipe} p\" failed; aborting..."
exit 1
fi
chmod 600 ${_Pipe}
if (( $? != 0 ))
then
echo "${_Pgm}.sh: \"chmod 600 ${_Pipe}\" failed; aborting..."
exit 1
fi
#
#-----------------------------------------------------------------------------
# Start the EXP utility in "background" and the IMP utility in "foreground".
# Both will complete around the same time...
#-----------------------------------------------------------------------------
echo "`date`: EXP started in \"background\"..."
${ORACLE_HOME}/bin/exp parfile=${_ExpPar} > ${_ExpOut} 2> ${_ExpErr} &
echo "`date`: IMP started in \"foreground\"..."
${ORACLE_HOME}/bin/imp parfile=${_ImpPar} > ${_ImpOut} 2> ${_ImpErr}
echo "`date`: EXP and IMP completed..."
#
#-----------------------------------------------------------------------------
# Check for possible issues in the ".log" files from EXP and IMP...
#-----------------------------------------------------------------------------
integer _ExitStatus=0
if [ ! -f ${_ExpLog} ] # check the log file from the EXP utility...
then
#
echo "Log file from EXP (\"${_ExpLog}\") not found"
integer _ExitStatus=${_ExitStatus}+1
#
else
#
if grep "ORA-" ${_ExpLog} > /dev/null 2>&1
then
echo "Log file from EXP (\"${_ExpLog}\") shows \"ORA-\"
errors..."
integer _ExitStatus=${_ExitStatus}+1
fi
#
if ! grep "Export terminated" ${_ExpLog} > /dev/null 2>&1
then
echo "Log file from EXP (\"${_ExpLog}\") shows EXP utility did
not complete"
integer _ExitStatus=${_ExitStatus}+1
else
if ! grep "Export terminated successfully without warnings"
${_ExpLog} \
> /dev/null 2>&1
then
echo "Log file from EXP (\"${_ExpLog}\") shows warnings
and/or errors"
integer _ExitStatus=${_ExitStatus}+1
fi
fi
#
fi
#
if [ ! -f ${_ImpLog} ] # check the log file from the IMP utility...
then
#
echo "Log file from IMP (\"${_ImpLog}\") not found"
integer _ExitStatus=${_ExitStatus}+1
#
else
#
if grep "ORA-" ${_ImpLog} > /dev/null 2>&1
then
echo "Log file from IMP (\"${_ImpLog}\") shows \"ORA-\"
errors..."
integer _ExitStatus=${_ExitStatus}+1
fi
#
if ! grep "Import terminated" ${_ImpLog} > /dev/null 2>&1
then
echo "Log file from IMP (\"${_ImpLog}\") shows IMP utility did
not complete"
integer _ExitStatus=${_ExitStatus}+1
else
if ! grep "Import terminated successfully without warnings"
${_ImpLog} \
> /dev/null 2>&1
then
echo "Log file from IMP (\"${_ImpLog}\") shows warnings
and/or errors"
integer _ExitStatus=${_ExitStatus}+1
fi
fi
#
fi
#
#-----------------------------------------------------------------------------
# Clean up the temporary files, as well as any files containing usernames
# and/or passwords, and then exit...
#-----------------------------------------------------------------------------
rm -f ${_Pipe} ${_ExpPar} ${_ImpPar}
exit ${_ExitStatus}
