Export\Import: Database

Considerations

On the destination system you do not need to precreate the user accounts. They will be created upon importing with the same password and specs as on the source database.

Destination Database Prerequisites:

1. Is taking the database out of ArchiveLogMode temporarily to improve import speed warranted?
   Otherwise consider using: TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
2. All the tablespaces the user(s) access exist.
   Though they would be recreated in the import, it is better to pre-create with the ideal location and size.
3. All user tablespaces are sized adequately.
   You can use ts.createResizeCmds.sql
4. The users Temporary tablespace exists and is large enough.
5. All apps and processes on the destination schema have been stopped.
6. Kill any user sessions that may access schema to be refreshed.

Command Line

Export

expdp user_name/password 
  DIRECTORY=dir_obj_name 
  DUMPFILE=dump_file_name
  FULL=y|n
  JOB_NAME=job_name 
  [COMPRESSION=type]

expdp system/password DUMPFILE=db1.dmp DIRECTORY=datapump 
   FULL=y JOB_NAME=expDB LOGFILE=exportDatabase.log 

Import

impdp user_name/password 
  DIRECTORY=dir_obj_name
  DUMPFILE=dump_file_name
  FULL=y|n 
  JOB_NAME=job_name

impdp system/password DUMPFILE=db1.dmp DIRECTORY=datapump FULL=y 
   JOB_NAME=impDB LOGFILE=impDatabase.log 

Parameter File (.par)

Export

# expdp system/password PARFILE=expDatabase.par
COMPRESSION=ALL
DIRECTORY=datapump
DUMPFILE=db1.dmp
FULL=Y
JOB_NAME=expDB
LOGFILE=expDatabase.log

Minimal example.

More Practical Example

# expdp system PARFILE=xDB.par
CLUSTER=N
COMPRESSION=ALL
DIRECTORY=datapump
EXCLUDE=STATISTICS
FULL=Y
JOB_NAME=xDB
LOGFILE=xDB.log

# PARALLEL
PARALLEL=4
DUMPFILE=mydb.%U.dmp
FILESIZE=32g

Import

# impdp system/password PARFILE=impDatabase.par
DIRECTORY=datapump
DUMPFILE=db1.dmp
FULL=Y
JOB_NAME=impDB
LOGFILE=impDatabase.log
TABLE_EXISTS_ACTION=replace

Optimized Database Import

Though full database imports are supposed to exclude much of the schema listed here, you may find this provides for a cleaner import. Plus Oracle feels it cannot hurt!

#################################################################################
# Purpose:  Parameter file to import database from a Data Pump dump file.
# Version:  2.1
# Usage:    impdp system PARFILE=importDatabase.par
# Comments: Directory Object must exist before running.
#################################################################################

DIRECTORY=datapump
DUMPFILE=FULL_01.DMP,FULL_02.DMP
FULL=Y
JOB_NAME=ImportDatabase
LOGFILE=importDatabase.log

# Enterprise Edition Option
PARALLEL=2

EXCLUDE=SCHEMA:"='ANONYMOUS'"
EXCLUDE=SCHEMA:"='CTXSYS'"
EXCLUDE=SCHEMA:"='DBSNMP'"
EXCLUDE=SCHEMA:"='DIP'"
EXCLUDE=SCHEMA:"='DMSYS'"
EXCLUDE=SCHEMA:"='EXFSYS'"
EXCLUDE=SCHEMA:"='IMD'"
EXCLUDE=SCHEMA:"='MGMNT_VIEW'"
EXCLUDE=SCHEMA:"='ORACLE_OCM'"
EXCLUDE=SCHEMA:"='ORDPLUGINS'"
EXCLUDE=SCHEMA:"='OUTLN'"
EXCLUDE=SCHEMA:"='RESTORE'"
EXCLUDE=SCHEMA:"='SCOTT'"
EXCLUDE=SCHEMA:"='SITESCOPE'"
EXCLUDE=SCHEMA:"='SYS'"
EXCLUDE=SCHEMA:"='SYSTEM'"
EXCLUDE=SCHEMA:"='SYSMAN'"
EXCLUDE=SCHEMA:"='TSMSYS'"
EXCLUDE=SCHEMA:"='WKSYS'"
EXCLUDE=SCHEMA:"='WMSYS'"

# Custom Excludes
EXCLUDE=SCHEMA:"='X15'"

<- DataPump