If you ever encounter problems with Oracle Data Pump, you can use this recipe to get valuable tracing.

Over the years, I’ve helped many customers with Data Pump issues. The more information you have about a problem, the sooner you can come up with a solution. Here’s my list of things to collect when tracing a Data Pump issue.

Daniel’s Tracing Recipe

1. AWR

• Be sure you have a proper license to use AWR.

• Set the snapshot interval to 15 minutes and create a new snapshot:

  exec dbms_workload_repository.modify_snapshot_settings(null, 15);
  exec dbms_workload_repository.create_snapshot;
  

• If you are on Multitenant, do so in the root container and in the PDB.

2. SQL Trace

• Depending on the nature of the problem, you can enable SQL trace of the Data Pump processes:

  alter system set events 'sql_trace {process: pname = dw | process: pname = dm} level=8';
  

  • You can change the trace level as required.
  • dm is the Data Pump control process, dw are worker processes.

• If you already know the SQL ID causing problems, you can enable tracing for just that SQL:

  alter system set events 'sql_trace[SQL: <sql-id>]';
  

  • Replace <sql-id> with the offending SQL ID.

3. Start Data Pump

• Start the Data Pump job that you want to trace:

  expdp ... metrics=yes logtime=all trace=<trace-setting>
  impdp ... metrics=yes logtime=all trace=<trace-setting>
  

  • metrics=yes and logtime=all enable diagnostic information in the log file.
  • trace is a hexadecimal number that translates into a bitmask that enables different kinds of Data Pump functional tracing.
  • Normally, trace=1FF0300 is a good setting. However, for transportable tablespaces, use trace=3FF0300 instead.
  • You can find more information about the trace parameter in My Oracle Support, Export/Import DataPump Parameter TRACE – How to Diagnose Oracle Data Pump (Doc ID 286496.1).

4. AWR

• Be sure you have a proper license to use AWR.

• When the Data Pump job completes or after you stopped it, reset the snapshot interval to the original value and create a new AWR snapshot:

  exec dbms_workload_repository.modify_snapshot_settings(null, <original-value>);
  exec dbms_workload_repository.create_snapshot;
  

• Create an AWR report spanning the entire period:

  @?/rdbms/admin/awrrpt
  

  • If needed, you can later on create AWR reports for a shorter period.

• If you are on Multitenant, do so in the root container and in the PDB.

Get the Information

Collect the following information:

1. The Data Pump log file.
2. AWR reports – on CDB and PDB level
3. Data Pump trace files
   • Stored in the database trace directory
   • Control process file name: *dm*
   • Worker process file names: *dw*

This should be a great starting point for diagnosing your Data Pump problem.

What Else

• Remember, you can use the Data Pump Log Analyzer to quickly generate an overview and to dig into the details.

• Regarding Data Pump parameters metrics=yes and logtime=all. You should always have those in your Data Pump jobs. They add very useful information at no extra cost. In Oracle, we are discussing whether these should be default in a coming version of Data Pump.

Leave a comment and let me know your favorite way of tracing Oracle Data Pump.