Oracle expdp and impdp - Some useful tips

Introduction

Many of you may be using Oracle Data Pump Export (expdp) and Data Pump Import (impdp) for logical backups of objects/schemas as well as the full databases for say, performing a database platform migration. I have some useful tips for you in an Oracle 11g R1 database.

1) How to run expdp as sysdba

There are two ways of running it using sysdba. Either by giving the option in the command line, or giving the option when it prompts for the username. Note the escape characters in the command line

i) expdp \"/ as sysdba\" parfile=myparfile.lst
ii) expdp parfile=myparfile.lst

Export: Release 11.1.0.7.0 - 64bit Production on Thursday, 15 May, 2011 13:11:57

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Username: / as sysdba

2) How to use nohup

nohup lets one run a operating system command in the background. The process won't be terminated even if the operating system (os) user has logged out. Using nohup for expdp can give errors unless a parfile (parameter file) is used.

The below is an example of using nohup to perform a full database export with the parfile option.

The parfile (fulldb_export.par) has the following contents

dumpfile=DATA_PUMP_DIR:mydb_full_%U.dmp
LOGFILE=DATA_PUMP_DIR:mydb_full_dump.log
parallel=4
filesize=40G
full=Y

nohup expdp \"/ as sysdba\" parfile=fulldb_export.par &

3) How to exclude schemas

Get the syntax right when you want to exclude a particular schema during the export or import process. In one wants to exclude the system schema, one has to use the EXCLUDE=SCHEMA:"in\('SYSTEM'\)" option.

4) How to exclude multiple schemas

If one wants to exclude multiple schemas during an export or import process, for example, SYSTEM and FIN schemas, one has to use the EXCLUDE=SCHEMA:"in\('SYSTEM','FIN'\)" option

5) Avoid filling up the $ORACLE_HOME location

By default, the DATA_PUMP_DIR (in dba_directories) points to $ORACLE_HOME/rdbms/log directory. If this location does not have much free space left, the $ORACLE_HOME can become 100% full in the case of large dump files, causing problems to the normal database operations. To avoid this issue, consider creating a symbolic link from $ORACLE_HOME/rdbms/log to a location (for e.g /lot_of_freespace) where you have lot of free space available.

cd $ORACLE_HOME/rdbms
mv log log.old
ln -s /lot_of_freespace log

6) Consider COMPRESSION option to reduce the export dump file sizes

Oracle 11g introduced the concept of compressing contents in the export dump file. I am impressed with the compression algorithm because in the event of an import operation (impdp), Oracle decompresses automatically (No additional options required). This is similar to the RMAN backup compression technique that is introduced in Oracle 10g. I have noticed good data compression ratios when used with the expdp command (Almost 1:10 to 1:15). However, it is not well documented in the Utilities guide whether COMPRESSION feature requires a separate license from Oracle (Oracle Advanced Compression)

The option for expdp is COMPRESSION=ALL. It is not applicable for impdp.