This document guides you through the process of using Data Pump Export and Import utilities, or the original Export and Import utilities, to move data from one database to another.
This document covers the following topics:
- When to Use Data Pump Export/Import Versus Original Export/Import
- Export and Import Requirements
- Upgrade the Database Using Export/Import
Oracle Database Utilities for detailed information about Data Pump and the Export and Import utilities
The Data Pump Export (expdp) and Import (impdp) utilities have a similar look and feel to the original Export (exp) and Import (imp) utilities, but they are completely separate. The Data Pump Export and Import utilities, first released in Oracle Database 10g Release 1 (10.1), are high performance replacements for the original Export and Import utilities. When upgrading from Oracle Database 10g Release 1 (10.1) or higher, Oracle recommends the Data Pump Export and Import utilities in most cases because these utilities provide greatly enhanced performance compared to the original Export and Import utilities.
Data Pump Export and Import offer the following advantages:
- Much greater data and metadata filtering capability than was provided by the original Export utility. Data Pump supports filtering the metadata that is exported and imported based upon objects and object types, using INCLUDE and EXCLUDE parameters.
- Different modes for unloading/loading portions of the database including: full database mode, schema mode, table mode, tablespace mode, and transportable tablespace mode. (See the “Data Pump Export Modes” and “Data Pump Import Modes” sections in Oracle Database Utilities)
- Allow you to specify how partitioned tables should be handled during import operations, using the PARTITION_OPTIONS parameter.
- Support for the full range of data types.
Oracle Database Utilities for an overview of Data Pump Export and Import
The original Export/Import utilities are required for the following types of database upgrades and downgrades:
- If you are upgrading from a release prior to Oracle Database 10g Release 1 (10.1), then you must use the original Export/Import utilities to move the data. Likewise, if you are downgrading your database to a release prior to Oracle Database 10g Release 1 (10.1), then you must use the original Export/Import utilities.
- If you must downgrade to a release prior to Oracle Database 10g Release 1 (10.1), then the original Export utility continues to provide support to ensure backward compatibility.
- If you are moving tables of XMLType or tables containing XMLType columns (schema and non-schema-based) from Oracle Database 11g Release 1 (11.1) to Oracle Database 10g Release 2 (10.2).
Oracle Database Utilities for information on moving a database from one platform to another
Dump files generated by the Data Pump Export utility are not compatible with dump files generated by the original Export utility. Therefore, files generated by the original Export (exp) utility cannot be imported with the Data Pump Import (impdp) utility, and vice versa.
Dump files created by one of the Export utilities can be imported into all future releases of Oracle Database. For example, an Oracle8 original Export dump file can be imported by the Oracle8i, Oracle9i, Oracle Database 10g, and Oracle Datab ase 11g original Import utility.
Table 7-1 shows which releases to use when exporting data from releases earlier than Oracle Database 11g Release 2 (11.2) and then importing that data into Oracle Database 11g Release 2 (11.2).
|Export From||Import To||Export Version to Use||Import Version to Use|
|Release 11.1||Release 11.2||Data Pump Export Release 11.1||Data Pump Import Release 11.2|
|Release 10.2||Release 11.2||Data Pump Export Release 10.2||Data Pump Import Release 11.2|
|Release 10.1||Release 11.2||Data Pump Export Release 10.1||Data Pump Import Release 11.2|
|Release 9.2||Release 11.2||Original Export Release 9.2||Original Import Release 11.2|
|Release 8.1.7||Release 11.2||Original Export Release 8.1.7||Original Import Release 11.2|
|Release 8.0.6||Release 11.2||Original Export Release 8.0.6||Original Import Release 11.2|
|Release 7.3.4||Release 11.2||Original Export Release 7.3.4||Original Import Release 11.2|
The Oracle Database release to which you downgrade can be one release older, at the most. For example, if the current database is Oracle Database 11g Release 1 (11.1), then Oracle Database 10g Release 2 (10.2) is the only release supported for the downgrade.
To obtain a downward compatible dump file:
- With Data Pump Export, use the current version’s Data Pump Export utility, but set the
VERSIONparameter to the target older version.
Data Pump Import cannot read dump file sets created by a database version that is newer than the current database version, unless those dump file sets were created with the
VERSIONparameter set to the version of the target database. Therefore, the best way to perform a downgrade is to perform your Data Pump export with the
VERSIONparameter set to the version of the target database.
Oracle Database Utilities for more information about using the
- With original Export, run an older version of Export (exp) to produce a dump file that is compatible with the database version to which you want to downgrade. Then, run the target’s original Import utility.
Original Export dump files are not downward compatible with the Import utilities of previous Oracle Database releases. That is, exported data cannot be imported by the Import utilities of previous Oracle Database releases. For example, an Oracle9i Export dump file cannot be imported by an Oracle8i Import utility, and an Oracle Database 10g export dump file cannot be imported by an Oracle9i Import utility. The following tables provide specific examples.
Oracle Database Utilities for more information about using different releases and versions of Export
Table 7-2 shows which releases to use when exporting data from Oracle Database 11g Release 2 (11.2) and then importing that data into earlier releases. Major release downgrades are supported to 11.1, 10.2, and 10.1.
|Export From||Import To||Export Version to Use||Import Version to Use|
|Release 11.2||Release 11.1||Data Pump Export Release 11.2 with
||Data Pump Import Release 11.1|
|Release 11.1||Release 10.2||Data Pump Export Release 11.1 with
||Data Pump Import Release 10.2|
|Release 10.2||Release 10.1||Data Pump Export Release 10.2 with
||Data Pump Import Release 10.1|
When using the original Export utility, if the source database is newer than the target database, then you must run the
catexp.sql script supplied with the previous (older) release for the export to be successful. Once the export is done, then run the
catexp.sqlscript from the newer release to restore the export views. Perform the steps as follows:
- Run the older
CATEXP.SQLscript on the database to be exported.
- Use the older Export utility to create the dump file.
- Use the older Import utility to import to the target database.
- Run the newer
CATEXP.SQLscript on the exported database.
When you export data to a previous release, data that is incompatible with the previous release either is not exported at all or is exported with the loss of some features.
In general, if you must export data to a previous release, then first remove as many incompatibilities with the previous release as possible before you export the data.
To upgrade a database using the Export/Import utilities, complete the following steps:
- Export data from the current database using the Export utility shipped with the current database. See the current Oracle Database Utilities documentation for information about using the Export utility on the current database.
To ensure a consistent export, make sure the current database is not available for updates during and after the export. If the current database is available to users for updates after the export, then, prior to making the current database available, put procedures in place to copy the changes made in the current database to the new database after the import is complete.
- Install the new Oracle Database software. Installation is operating system specific. Installation steps for Oracle Database are covered in your operating system-specific Oracle documentation.
- If the new database has the same name as the current database, then shut down the current database before creating the new database.
- Create the new database.
Oracle Database Administrator’s Guide for information about creating a database
- Start SQL*Plus in the new Oracle Database environment.
- Connect to the database instance as a user with
- Start an Oracle Database instance using
- Optionally, you can change the storage parameters from the source database.
You can pre-create tablespaces, users, and tables in the new database to improve space usage by changing storage parameters. When you pre-create tables using SQL*Plus, either run the database in the original database compatibility mode or make allowances for the specific data definition conversions that occur during import. When items have been pre-created, specify one of the following options:
TABLE_EXISTS_ACTION=APPENDfor Data Pump Import
IGNORE=Yfor original Import
If the new database is created on the same computer as the source database, and you do not want to overwrite the source database data files, then you must pre-create the tablespaces and specify one of the following options when you import:
REUSE_DATAFILES=Nfor Data Pump Import
Optionally, consider using the
REMAP_TABLEoptions so that references to the old names in the dump file set are remapped to new, non-colliding names.
DESTROY=Nfor original Import.
- Use the Import utility of the new database to import the objects exported from the current database. Include one of the following parameters to save the informational and error messages from the import session to a file:
LOGFILEparameter for Data Pump Import
LOGparameter for original Import
Oracle Database Utilities for a complete description of the Import utility.
- After the import, check the import log file for information about which imports of which objects completed successfully and, if there were failures, which failed.
Oracle Database Utilities and the Oracle Database
README.docfile for error handling information.
- Use further Import scenarios (see Oracle Database Utilities) or SQL scripts that create the database’s objects to clean up incomplete imports (or possibly to start an entirely new import).
If a Data Pump Export or Import job encounters a fatal error, then the job can be restarted after the condition inducing the failure is corrected. The job then continues automatically from the point of failure.
- If changes are made to the current database after the export, then make sure those changes are propagated to the new database prior to making it available to users. See Step 1 for more information.
- Complete the procedures described in Chapter 4, “After Upgrading to the New Release”.
Importing a Full Database Using a Network Link
As an alternative to the procedure in “Upgrade the Database Using Export/Import”, you can use the Data Pump Import utility with a database link to do a full database import from a source database to a destination database without intermediate dump files. Follow these steps:
- Ensure that the exporting user at the source database has the
This user must be specified when you create the database link.
- Ensure that the importing user at the destination database has the
- Create and test a database link between the source and destination databases.
- Run the following command, where
import_useris the username for the importing user, and
db_linkis the name of the database link owned by the exporting user:
IMPDP import_user/password NETWORK_LINK=db_link FULL=Y;
- A log file for the import operation is written to the DATA_PUMP_DIR directory. You can discover the location of this directory by running the following command:
SQL> select * from dba_directories where DIRECTORY_NAME like 'DATA_PUMP_DIR';
XML objects are not exported from the source database.
Note 466181.1 on OracleMetalink (
https://metalink.oracle.com/) for more information on Data Pump Imports using a database link
Copyright © 2002, 2009, Oracle and/or its affiliates. All rights reserved.