7 Moving Data Using Oracle Data Pump

When upgrading Oracle Database, you can use the Export and Import utilities in Oracle Data Pump to move data from one database to another.

This chapter contains the following topics:

About Data Pump Export and Import for Upgrading Oracle Database
Export and Import Requirements for Oracle Database Upgrades
Upgrading the Database Using Data Pump Export/Import

See Also:

Oracle Database Utilities for detailed information about Data Pump and the Export and Import utilities

About Data Pump Export and Import for Upgrading Oracle Database

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 your database, Oracle recommends the Data Pump Export and Import utilities because these utilities provide greatly enhanced performance compared to the original Export and Import utilities.

See Also:

"Oracle Data Pump Export and Import and Oracle Database Upgrade" for information on when to use this upgrade method
Oracle Database Utilities for a detailed list of Data Pump features that contribute to increased performance, as well as to enhanced ease-of-use

Data Pump Export and Import offer the following advantages:

Provides 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.
Supports 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)
Enables you to specify how partitioned tables should be handled during import operations, using the PARTITION_OPTIONS parameter.
Provides support for the full range of data types.

See Also:

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 earlier than 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 earlier than Oracle Database 10g Release 1 (10.1), then you must use the original Export/Import utilities.
If you must downgrade to a release earlier than 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).

See Also:

Oracle Database Utilities for information on moving a database from one platform to another

Export and Import Requirements for Oracle Database Upgrades

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.

About Export Dump Files and Upgrading

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 Database 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).

Table 7-1 Exporting Data From Releases Earlier Than 11.2 and Importing Into Release 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

Export and Import Requirements for Downgrades

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 VERSION parameter 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 VERSION parameter set to the version of the target database. Therefore, the best way to perform a downgrade is to use Data Pump Export with the VERSION parameter set to the version of the target database.

See Also:
Oracle Database Utilities for more information about using the VERSION parameter
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.

See Also:
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.

Table 7-2 Exporting Data From Release 11.1 and Importing Into Earlier Releases

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 `VERSION=11.1`	Data Pump Import Release 11.1
Release 11.1	Release 10.2	Data Pump Export Release 11.1 with `VERSION=10.2`	Data Pump Import Release 10.2
Release 10.2	Release 10.1	Data Pump Export Release 10.2 with `VERSION=10.1`	Data Pump Import Release 10.1

Note:

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.sql script from the newer release to restore the export views. Perform the steps as follows:

Run the older CATEXP.SQL script 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.SQL script on the exported database.

Export/Import Usage on Data Incompatible with a Previous Release

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.

Upgrading the Database Using Data Pump Export/Import

To upgrade a database using the Data Pump 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, before 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 provided in Oracle Database Installation Guide for your operating system.
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.

See Also:
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 SYSDBA privileges.
Start an Oracle Database instance using STARTUP.
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=APPEND for Data Pump Import
- IGNORE=Y for original Import
Note:
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=N for Data Pump Import
  
  Optionally, consider using the REMAP_DATAFILE, REMAP_TABLESPACE and REMAP_TABLE options so that references to the old names in the dump file set are remapped to new, non-colliding names.
- DESTROY=N for 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:
- The LOGFILE parameter for Data Pump Import
- The LOG parameter for original Import
See Also:
Oracle Database Utilities for a complete description of the Data Pump 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.

See Also:
Oracle Database Utilities and the Oracle Database README.doc file 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).

Note:
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 before making it available to users. See Step 1 for more information.
Complete the procedures described in Chapter 4, "Post-Upgrade Tasks for Oracle Database".

Importing a Full Database Using a Network Link

As an alternative to the procedure in "Upgrading the Database Using Data Pump Export/Import", you can use the Data Pump Import utility with a database link to perform a full database import from a source database to a destination database without intermediate dump files.

Note:

In order to avoid interoperability errors, ensure that the appropriate patchset has been applied to the database being upgraded. See My Oracle Support at https://support.oracle.com to obtain the latest patchsets.

You may also refer to support note ID 4511371.8, which discusses ORA-6544 and ORA-4052 errors.

Follow these steps to use a network link with impdp:

Ensure that the exporting user at the source database has the EXP_FULL_DATABASE role.

This user must be specified when you create the database link.
Ensure that the importing user at the destination database has the IMP_FULL_DATABASE role.
Create and test a database link between the source and destination databases.
Run the following command, where import_user is the username for the importing user, and db_link is 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';
```
Note:
XML objects are not exported from the source database.

Note:

The import operation re-creates users on the new destination server, and the creation date for dba_users shows the actual import date. The expiration date is updated to be creation_date + password_life_time. The parameters for dba_users on the new server are different than the dba_users parameters on the source server.