Home -> Mailing Lists -> Oracle-L -> Re: Export Import Large Database - 9i

Re: Export Import Large Database - 9i

From: Tim Gorman <tim_at_evdbt.com>
Date: Sat, 16 Jan 2010 09:31:45 -0700
Message-ID: <4B51E9F1.7010902_at_evdbt.com> 




  


Wannabe,



It gets pretty involved, but the basic algorithm for
exporting/importing a large database is:


    
  
  1. EXP FULL=Y ROWS=N from the source-db then IMP FULL=Y ROWS=N
INDEXES=N CONSTRAINTS=N into the target-db
    2. 
  
      
    
    • The idea is to lay down the tablespaces and table definitions
in the target-db.  Depending on similarities (or the lack thereof)
between the two dbs, you may or may not be able to create tablespaces
using IMP, but the goal is to import tables DDL and the DDL for other
objects (but not indexes and constraints) using IMP
      • 
    
    • Don't forget to disable all DML triggers created in target-db
before going on to the next step...
      
    
      • 
  
    
  
  2. Run as many concurrent exp/imp streams as you can between the
source-db and target-db to copy the table data across
    3. 
  
      
    
    • Goal is to keep as many streams running concurrently as
possible.  For smaller tables, one exp/imp stream per table.  For
larger tables, one exp/imp stream per partition or one exp/imp stream
per section of the table specified using EXP QUERY=
      
    
      • 
  
    
  
  3. Create indexes and constraints using previously-obtained EXP
FULL=Y ROWS=N in step #1
    4. 
  
      
    
    • Usually I just extract the DDL using IMP INDEXFILES= and create
SQL*Plus scripts for this, so I can break the task up into many
concurrently running jobs for speed...
      
    
      • 
  
    


That is just the general idea; as you can imagine, there are a lot of
details - some specific to your environment, some generic to the task. 
You're going to need to do a lot of UNIX shell-scripting, as well as
the techniques of SQL-generating-SQL, not to mention some dynamite
editing skills.  If you don't have those skills, find them or learn
them.



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_at_EvDBT.com
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:


  
  

  
Hi,

  
Was wondering if anyone could share their experience of having
to export import VLDB's in a 9i environment. For certains reason the
database that I need to migrate cannot be upgraded - which would open
up a lot more options. I'm hoping people out there could help with
their personal experiences, if any, with respect to what they found
helped the best during this process. I'd appreciate any input, thanks

  
Thanks 



  











#!/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_at_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 "_at_${_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 "_at_${_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}_at_${_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}_at_${_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}


--
http://www.freelists.org/webpage/oracle-l


Received on Sat Jan 16 2010 - 10:31:45 CST












Original text of this message