Export/Import DataPump Parameter TRACE - How to Diagnose Oracle Data Pump (Doc ID 286496.1)

In this Document

Purpose

Scope

Details

1. Introduction.

2. How to create a Data Pump trace file ?  Parameter: TRACE

3. How to start tracing the Data Pump job ?

4. How are Data Pump trace files named, and where to find them ?

5. How to get a detailed status report of a Data Pump job ?  Parameter: STATUS

6. How to get timing details on processed objects ? Parameter: METRICS

7. How to get SQL trace files of the Data Pump processes ?

8. How to get header details of Export Data Pump dumpfiles ?

9. How to get Data Definition Language (DDL) statements ? Parameter: SQLFILE

10. How to get the DDL both as SQL statements and as XML data ?

Additional Resources

References

---

 

APPLIES TO: 

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

PURPOSE

This document describes how to use the TRACE parameter with the Export Data Pump (expdp) and Import Data Pump (impdp) database utilities when diagnosing incorrect behavior and/or troubleshooting Data Pump errors.

SCOPE

The article is intended for users of the database utilities Export Data Pump (expdp) and Import Data Pump (impdp), and who need to troubleshoot the execution of the jobs that are generated by these utilities. These database utilities were introduced with Oracle10g. The article gives detailed information how to use the undocumented parameter TRACE.

DETAILS

1. Introduction.

In Oracle10g, we introduced the new database utilities Export Data Pump and Import Data Pump. 
Data Pump jobs use a master table, a master process, and worker processes to perform the work and keep track of the progress. For every Data Pump Export job and Data Pump Import job, a master process is created. The master process controls the entire job, including communicating with the clients, creating and controlling a pool of worker processes, and performing logging operations.
Example output when a full database Export Data Pump is running with parallelism 2:

The Data Pump processes will disappear when the Data Pump job completes or is (temporary) stopped.
Improved tracing capabilities have been implemented with these utilities. The tracing can be controlled with the TRACE parameter.

Data Pump is server based and not client based. This means that most Data Pump specific defects will be fixed on the server side (changes in packages in source and target database) and not on the client side (Export or Import Data Pump client). If a Data Pump defect that occurs during an import job is fixed in a later patchset (e.g. 10.2.0.4.0) and the target database is still on the base release (10.2.0.1.0) then the defect will still occur when importing with a 10.2.0.4.0 Import Data Pump client into this 10.2.0.1.0 target database. The same applies to export jobs. It is therefore recommended that both the source database and the target database have the latest patchset installed. For details, see also:
Note:553337.1 "Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions"

2. How to create a Data Pump trace file ?  Parameter: TRACE

Tracing can be enabled by specifying an 7 digit hexadecimal mask in the TRACE parameter of Export DataPump (expdp) or Import DataPump (impdp). The first three digits enable tracing for a specific Data Pump component, while the last four digits are usually: 0300.
Any leading zero's can be omitted, and the value specified for the TRACE parameter is not case sensitive.
Example:

Some rules to remember when specifying a value for the TRACE parameter:
- do not specify more than 7 hexadecimal digits;
- do not specify the typical leading 0x hexadecimal specification characters;
- do not convert the hexadecimal value to a decimal value;
- omit any leading zero's (not required though);
- values are not case sensitive.

When using the TRACE parameter, an error may occur if the Data Pump job is run with a non-privileged user, e.g.:

To resolve this problem: ensure that the user that connects to the database with the Export Data Pump or Import Data Pump utility, is a privileged user (i.e. a user who has the DBA role or the EXP_FULL_DATABASE resp. IMP_FULL_DATABASE role), e.g.:

For details, see also:
Note:351598.1 "Export/Import DataPump: The Minimum Requirements to Use Export DataPump and Import DataPump (System Privileges)"

Also ensure that the init.ora/spfile initialization parameter MAX_DUMP_FILE_SIZE is large enough to capture all the trace information (or set it to unlimited which is the default value), e.g.:

The majority of errors that occur during a Data Pump job, can be diagnosed by creating a trace file for the Master Control Process (MCP) and the Worker Process(es) only, e.g.:

Each Data Pump component can be specified explicitly in order to obtain tracing details of that component:

Combinations of tracing Data Pump components are possible, e.g.:

In order to trace all Data Pump components, level 1FF0300 can be specified:

Note: 
a/ For initial Data Pump tracing, the value 480300 is sufficient. When value 480300 is specified, we will trace the Master Control process (MCP) and the Worker process(es). When creating a Data Pump trace file, we recommend to use the value 480300 unless a different trace level is requested by Oracle Support.

b/ To help debugging performance issue related to PROCACT_INSTANCE, a new trace level is introduced in 12.2 DataPump Utility through Diagnostic Enhancement Bug 24661809 : DATA PUMP DOES NOT TRACK EXTENSIBLE CODE. 12.2 Data Pump Diagnostic Enhancement bug is backported to 11.2.0.4 and 12.1.0.2 and generic patches are available for download through My Oracle Support.   
    This enhancement basically opens up the 'black box' of procact_instance (actually all extensible features) so that timing can be understood. In any 11.2.0.4 and 12.1.0.2 environment patched with Patch 24661809, the enhancement can be enabled by setting new values for DP trace parameters to add callout tracing and/or the timing information in the DW trace for DPEX (Data Pump EXtensibility) facility. The new trace values are:

DPEX lines in the DW trace file will look like:

If there are issues with the calls as seen in the trace files, individual tags can be disabled by using 'exclude='<TAG-NAME> in the expdp command line in full mode. The TAG-NAMEs in the example would be MATVW, RULE and AQ.

3. How to start tracing the Data Pump job ?

Depending on how the Export or Import Data Pump job was started, there are several ways to activate tracing of the Data Pump processes.

3.1. Use the TRACE parameter upon the start of the job.
Start tracing by specifying the TRACE parameter and a trace level when the Export Data Pump or Import Data Pump is started. This is the standard way to activate Data Pump tracing.
Example:

3.2. Use the TRACE parameter when restarting a job.
If an Export or Import Data Pump  job was started without the TRACE parameter, it can be temporary stopped, and restarted with the TRACE parameter.
Example:

3.3. Use a database init.ora/spfile event to trace Data Pump.
Data Pump tracing can also be started with a line with EVENT 39089 in the initialization parameter file. This method should only be used to trace the Data Pump calls in an early state, e.g. if details are needed about the DBMS_DATAPUMP.OPEN API call. Trace level 0x300 will trace all Data Pump client processes.
Example when using init.ora initialization parameter file:

Example when using spfile initialization parameter file:

Alternatively, the event 39089 can also be enabled and disabled at system level using next commands:

Note: 
Only set this event 39089 if tracing in an early state of job execution is needed. For normal standard tracing, use the TRACE parameter as described in the sections 3.1. and 3.2. above. 
For details about setting events, see also: 
Note:160178.1 "How to set EVENTS in the SPFILE"

@ For Support:

4. How are Data Pump trace files named, and where to find them ?

Data Pump trace files are written to the directories specified by the init.ora/spfile initialization parameters BACKGROUND_DUMP_DEST and USER_DUMP_DEST.

4.1 Data Pump Master Control Process (MCP).
Format : <SID>_dm<number>_<process_id>.trc 
Example: ORCL_dm00_2896.trc   or:   ORCL_dm01_3422.trc (for second active Master Control Process) 
Location: BACKGROUND_DUMP_DEST   or   <ADR_HOME>/trace

4.2. Data Pump Worker Process trace file.
Format : <SID>_dw<number>_<process_id>.trc 
Example: ORCL_dw01_2936.trc   or:   ORCL_dw01_2844.trc  and  ORCL_dw02_2986.trc (if PARALLEL=2) 
Location: BACKGROUND_DUMP_DEST   or   <ADR_HOME>/trace

4.3. Data Pump Shadow Process trace file.
Format : <SID>_ora_<process_id>.trc 
Example: ORCL_ora_3020.trc 
Location: USER_DUMP_DEST   or   <ADR_HOME>/trace

Note that in Oracle10g the default location for the trace files is: $ORACLE_HOME/rdbms/log 
This location will be used if the initialization parameters BACKGROUND_DUMP_DEST and/or USER_DUMP_DEST are not set.

For Oracle11g, the trace files are written to the <ADR_HOME>/trace 
where <ADR_HOME> is: <ADR_BASE>/diag/<product_type>/<prod_id>/<instance_id> 
where <ADR_BASE> is specified by DIAGNOSTIC_DEST (defaults to: $ORACLE_HOME/log). 
E.g.: /oracle/diag/rdbms/ORCL/ORCL/trace 

For details, see also: 
Note:422893.1 "11g Understanding Automatic Diagnostic Repository"

5. How to get a detailed status report of a Data Pump job ?  Parameter: STATUS

If a Data Pump is started, a detailed status report of the job can be obtains with the STATUS parameter in Interactive Command mode.
Example:

Note that the status information is written only to your standard output device, not to the Data Pump log file.

6. How to get timing details on processed objects ? Parameter: METRICS

With the undocumented parameter METRICS additional information can be obtained about the number of objects that were processed and the time it took for processing them. Objects in this context are for example the exported system GRANT privileges, the imported tablespace quota GRANT statements, the exported CREATE TABLE statements. Note that the METRICS parameter does not create any additional trace files. Instead, the extra details are logged in the logfile of the Export Data Pump or Import Data Pump job. 
Example output:

7. How to get SQL trace files of the Data Pump processes ?

For troubleshooting specific situations, it may be required to create a SQL trace file for an Export Data Pump or Import Data Pump job. These SQL trace files can be created by setting Event 10046 for a specific process (usually the Worker process). Note that these SQL trace files can become very large, so ensure that there is enough free space in the directory that is specified by the init.ora/spfile initialization parameter BACKGROUND_DUMP_DEST.

Remarks:

• level 1: lowest level tracing - not always sufficient to determine cause of errors;
• level 4: useful when an error in Data Pump's worker or master process occurs;
• level 12: useful when there is an issue with Data Pump performance.

When creating a level 8 or 12 SQL trace file, it is required that the init.ora/spfile initialization parameter TIMED_STATISTICS is set to TRUE before the event is set and before the Data Pump job is started. The performance impact of setting this parameter temporary to TRUE is minimal. The SQL trace files that were created with level 8 or 12 as especially useful for investigating performance problems.
Example:

7.1. Create a standard SQL_TRACE file (level 1).
If the output of standard SQL_TRACE functionality is sufficient (i.e.: neither bind values nore waits details are needed), then this SQL tracing can be activated with the Data Pump parameter trace. To activate standard SQL tracing, use the value 1.
Example:

Note that this level of tracing is usually not sufficient for tracing Data Pump when an error occurs or when there is an issue with Data Pump performance. For tracing Data Pump when an error occurs use level 4, and when there is an issue with Data Pump performance use level 12 (see sections below).

7.2. Activate SQL_TRACE on specific Data Pump process with higher trace level.
If a specific Data Pump process needs to traced, and more SQL_TRACE details are required, and it is not required to trace the start of the job, then the Event 10046 with the desired level can also be set on the process that needs to be traced (usually the Worker process). 
Example:

In the example output above we see that the Data Pump Master process (DM00) has SID: 143 and serial#: 50 and the Data Pump Worker process (DW01) has SID: 150 and serial#: 17. These details can be used to activate SQL tracing in SQL*Plus with DBMS_SYSTEM.SET_EV, e.g.:

The example output of the query above also shows that the Data Pump Master process (DM00) has OS process Id: 17292 and the Data Pump Worker process (DW01) has OS process Id: 17294. With this information, it is also possible to use 'oradebug' in SQL*Plus to activate SQL tracing for those processes, e.g.:

Either DBMS_SYSTEM.SET_EV or 'oradebug' can be used to create a Data Pump trace file.

7.3. Place complete database in SQL_TRACE with specific level. 
It is possible that there is not enough time to activate tracing on a specific Data Pump process because an error occurs at an early stage of the job, or that the the Data Pump process needs to be traced from the beginning. In those cases, the Event 10046 with the desired level has to be set in SQL*Plus at database level, and the Data Pump job has to be started afterwards. When the job completes, unset the event again. 
Example:

Be careful though: the steps above will result in SQL tracing on all processes, so only use this method if no other database activity takes place (or hardly any other activity), and when the Data Pump job ends relatively quickly.

7.4. Analyze the SQL trace files and create a TKPROF output file.
If the SQL trace files were created with level 1 or 4 then we are usually interested in the statements (and their bind variables). Example scenario: Data Pump aborts with a specific error. When investigating those kind of errors, it makes sense to compress the complete trace file and upload the compressed file.

If the SQL trace files were created with level 8 or 12 then we are usually interested in the timing of the statements (and their wait events). Example scenario: there is an presumptive performance issue during a Data Pump job. These SQL trace files can become very large and the tkprof output after analyzing the files are in most cases more meaningful. When investigating those kind of errors, it makes sense to upload the tkprof output files only. 
Example:

For details about Event 10046 and tkprof, see also: 
Note:21154.1 "EVENT: 10046 "enable SQL statement tracing (including binds/waits)"
Note:32951.1 "Tkprof Interpretation"

8. How to get header details of Export Data Pump dumpfiles ?

Every export Data Pump dumpfile starts with a header (usually 4 kb in size) which contains details about the dumpfile. Starting with Oracle10g Release 2 (10.2.0.1.0), this header information can be extracted from the dumpfile by calling the procedure DBMS_DATAPUMP.GET_DUMPFILE_INFO. Header information can be extracted from any export dumpfile, even from dumpfiles created with the classic export client. Example output of a procedure that extracts information from a dumpfile header:

For more details and an example code of procedure SHOW_DUMPFILE_INFO, see: 
Note:462488.1 "How to Gather the Header Information and the Content of an Export Dumpfile ?"

Besides the DBMS_DATAPUMP.GET_DUMPFILE_INFO procedure, it is also possible to start an Import Data Pump job with TRACE=100300 in order to create a trace file with the dumpfile header details. The dumpfile header details are written to the Data Pump Master trace file: [SID]dm[number]_[PID].trc which can be found in the directory specified by the init.ora/spfile parameter BACKGROUND_DUMP_DEST. 
Example:

Example output of trace file: <sid>_dm00_1696.trc

For more details and an example, see: 
Note:462488.1 "How to Gather the Header Information and the Content of an Export Dumpfile ?"

9. How to get Data Definition Language (DDL) statements ? Parameter: SQLFILE

With the Import Data Pump parameter SQLFILE you can specify the name of a file into which all of the SQL DDL is written that Import would have executed, based on other parameters. The SQL is not actually executed, and the target system remains unchanged. The file is written to the directory object specified in the DIRECTORY parameter, unless another directory_object is explicitly specified here. Any existing file that has a name matching the one specified with this parameter is overwritten. 
Syntax: SQLFILE=[directory_object:]file_name 
Example:

Example output of sqlfile: <SQLFILE_NAME>.sql

For more details, see: 
Note:462488.1 "How to Gather the Header Information and the Content of an Export Dumpfile ?"

10. How to get the DDL both as SQL statements and as XML data ?

With the classic export dumpfiles, the Data Definition Language (DDL) statements were stored inside the export dumpfile as normal (ready-to-use) SQL statements. With Data Pump however, the data to recreate the DDL statements is stored in XML format. This XML data can be obtained together with the actual DDL statements by running an import DataPump job with the SQLFILE and TRACE parameter.

Example output of sqlfile: impdp_s.sql

Note: it is not recommended to perform an import Data Pump job with TRACE=2 unless explicitly requested by Oracle Support.

 

Additional Resources

Community: Database Utilities

Still have questions? Use the above community to search for similar discussions or start a new discussion on this subject.

REFERENCES

NOTE:30824.1 - Init.ora Parameter "TIMED_STATISTICS" Reference Note
NOTE:351598.1 - Export/Import DataPump: The Minimum Requirements to Use Export DataPump and Import DataPump (System Privileges)
NOTE:422893.1 - Understanding Automatic Diagnostic Repository

NOTE:553337.1 - Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions [Video]

BUG:5583370 - PRODUCT ENHANCEMENT: EXTEND PARAMETER TRACE TO PROVIDE TIMESTAMP IN DATAPUMP LOG
NOTE:453895.1 - Checklist For Slow Performance Of DataPump Export (expdp) And Import (impdp)
NOTE:462488.1 - How to Gather the Header Information and the Content of an Export Dumpfile ?
NOTE:1053432.1 - ORA-31631 During Export Using DataPump API
NOTE:30762.1 - Init.ora Parameter "MAX_DUMP_FILE_SIZE" Reference Note
NOTE:32951.1 - TKProf Interpretation (9i and below)
NOTE:336014.1 - How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ?
NOTE:341733.1 - Export/Import DataPump Parameters INCLUDE and EXCLUDE - How to Load and Unload Specific Objects
BUG:5152186 - EXPDP/IMPDP JOB WITH TRACE AND STARTED WITH NON-DBA USER FAILS: ORA-31631
NOTE:160178.1 - How To Set EVENTS In The SPFILE
NOTE:552424.1 - Export/Import DataPump Parameter ACCESS_METHOD - How to Enforce a Method of Loading and Unloading Data ?
NOTE:199081.1 - SQL_TRACE (10046), TKProf and Explain Plan - Overview Reference
NOTE:21154.1 - EVENT: 10046 "enable SQL statement tracing (including binds/waits)"