MASTER NOTE ON ORACLE DATAPUMP

Master Note for Data Pump (Doc ID 1264715.1)

By Ken Robinson on November 8, 2010 1:47 PM

Concepts/Definitions

Oracle Data Pump technology enables very high-speed movement of data and metadata from one database to another. Oracle Data Pump is made up of three distinct parts:

• The command-line clients, expdp and impdp
• The DBMS_DATAPUMP PL/SQL package (also known as the Data Pump API)
• The DBMS_METADATA PL/SQL package (also known as the Metadata API)

The Data Pump is a server based utility vs. the traditional Export / Import which are client based utilities. The Oracle Data Pump is not compatible with the Export / Import functionality.

The Oracle Data Pump can also use NETWORK_LINK functionality to move data and metadata from a remote database without a dumpfile using the network technology between the two databases.

Diagnosing

This Master Note is not intended to be a complete diagnostic guide with Data Pump. However, a few key articles are included below:

Setup / Configuration / Syntax

Users need certain privileges to run Data Pump. See Note 351598.1 for details on minimum requirements for this utility. All messages regarding work in progress, completed, and errors encountered can be written to a log file using the Data Pump LOGFILE parameter. This log file should be your first place to check for problems with the Data Pump.

You can also include the undocumented parameter METRICS=y to include additional logging information about number of objects and the time it took to process them in the log file.

Error messages that are report in the log file do not automatically indicate failures in Data Pump job. Some are reported as warning messages and informational. For example, messages about objects already existing and being skipped. In that case, you may need to adjust the IMPDP command parameters to recreate those objects or append data to those existing objects.

Other resources:

Note 266875.1 Export/Import DataPump Parameter DIRECTORY - How to Specify a Directory
Note 430221.1 How To Reload Datapump Utility EXPDP/IMPDP

Search the Oracle Knowledge Base using keywords "how to datapump" for a variety of How To articles to help with parameter syntax for commonly required Data Pump jobs.

In some cases, the performance of the EXPDP or IMPDP utilities may be slower than the traditional EXP / IMP utilities.

Refer to Note 286496.1 for pointers on tracing long running Data Pump jobs to investigate where bottlenecks or performance issues are occurring. The Data Pump can parallelize certain operations. However, this does not always mean the job will be faster than the traditional EXP / IMP tools. Refer to Note 365459.1 for more details on performance improvements and restrictions on parallelism with Data Pump.

Characterset and Compatibility Issues

Unlike previous tools, the Data Pump uses the characterset of the source database to ensure proper conversion to the target database. There can still be issues with character loss in the conversion process.

Note 227332.1 NLS considerations in Import/Export - Frequently Asked Questions
Note 457526.1 Possible data corruption with Impdp if importing NLS_CHARACTERSET is different from exporting NLS_CHARACTERSET
Note 436240.1 ORA-2375 ORA-12899 ORA-2372 Errors While Datapump Import Done From Single Byte Characterset to Multi Byte Characterset Database
Note 553337.1 Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions [Video]
Note 864582.1 Examples using Data pump VERSION parameter and its relationship to database compatible parameter

Performance Issues

The Data Pump may be performing slower than expected. When investigating performance issues like this, it is important to eliminate factors outside the Data Pump utility first. Is overall performance on the database slow? If so this could be the root issue for the slow Data Pump job. Is overall performance impacted currently on the server? Again the slow Data Pump job could be "victim" of issues with CPU or memory on the server.

Determine if there are bottlenecks with CPU utilization and/or memory usage on the Operating System. In some cases, stress for these resources can lead to paging issue and that can be the root issue of the performance issues with Data Pump.

Investigating this area is different from platform to platform. Check with your OS Administrator for assistance on analysis at the server level.

At the database level, investigate performance information using tools like AWR or ADDM. Refer to Note 748642.1 for pointers on generating and use the AWR tool. Note 842884.1 provides high level tips and hints about interpreting data in the AWR report. Focus on "top waits", and I/O information.  Note 22908.1 discusses latch contention.

If the performance issues are narrowed down to the Data Pump utility, refer to articles below:

Note 552424.1 Export/Import DataPump Parameter ACCESS_METHOD - How to Enforce a Method of Loading and Unloading Data ?
Note 457526.1 Possible data corruption with Impdp if importing NLS_CHARACTERSET is different from exporting NLS_CHARACTERSET
Note 227332.1 NLS considerations in Import/Export - Frequently Asked Questions
Note 436240.1 ORA-2375 ORA-12899 ORA-2372 Errors While Datapump Import Done From Single Byte Characterset to Multi Byte Characterset Database
Note 553337.1 Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions [Video]
Note 864582.1 Examples using Data pump VERSION parameter and its relationship to database compatible parameter

Common Issues

Bug 7362589 indicates with 11g performance can be very slow when exporting a small proportion of objects relative to the number in the database.
Bug 8845859 indicates issues with 11g performance on partition exports. See Note 1050907.1

Note 812864.1 discusses problems prior to 10.2.0.4 with REMAP_SCHEMA

Data Pump is slower when using TABLE_EXISTS_ACTION=TRUNCATE on 10g with Index Organized Table (IOT) objects exist. This is expected behavior. See Note 780784.1 for more details.

Prior to 10.2.0.4, Data Pump export loses global indexes for local domain indexes. Bug 5152232 is documented in Note 781759.1.

There is some confusion about parallel operations with indexes and Data Pump. Note 402511.1 documents how this works more completely.

Exporting Flashback archive tables will throw ORA-1426 errors. See Note 742739.1.