Tag: trace

How to Trace Oracle Data Pump

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>

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.

How to Trace Oracle Data Pump

Data Pump is hanging!

If you ever think the above, the answer is probably:

No, it’s not, it’s working…

It might be working in a suboptimal way, but nevertheless, it’s working. Here’s how you can tell what’s going on.

My Ultimate Tracing Guide

I use this approach every time I work with Oracle Data Pump and need to see what happens underneath the hood.

Before Data Pump

  • I always ensure dictionary and fixed objects statistics are current before starting Data Pump:

    begin
   dbms_stats.gather_schema_stats('SYS');
   dbms_stats.gather_schema_stats('SYSTEM');
   dbms_stats.gather_fixed_objects_stats;
end;
/
    • I usually don’t use gather_dictionary_stats. I prefer gathering schema stats instead. I’ve seen a few edge cases with gather_dictionary_stats over the years, but mostly it works fine. But you know, old dog and new tricks…
    • You should always follow this advice even if you are not tracing a specific problem.

  • I change the AWR snapshot interval to 15 minutes to get better granularity and manually create an AWR snapshot:

    begin
   dbms_workload_repository.modify_snapshot_settings(null, 15);
   dbms_workload_repository.create_snapshot;
end;
/

  • Optionally, if I need to diagnose a performance problem, I enable SQL trace for Data Pump processes:

    alter system set events 'sql_trace {process: pname = dw | process: pname = dm} level=8';
    • Or a specific SQL ID:
    alter system set events 'sql_trace[SQL:  ]';

Starting Data Pump

  • I add diagnostic information (metrics and logtime) to the log file and turn on tracing:
    expdp ... metrics=yes logtime=all trace=1FF0300
impdp ... metrics=yes logtime=all trace=1FF0300
    • 1FF0300 turns on tracing for more or less everything in Data Pump, but there are other trace levels.

After Data Pump

  • I create a new AWR snapshot:
    begin
   dbms_workload_repository.create_snapshot;
end;
/
    • You might also want to reset the AWR snap interval to the previous setting.
  • Plus, an AWR report spanning the entire period.
    @?/rdbms/admin/awrrpt
  • Data Pump writes the tracing into the process trace files. You can find them in the database trace directory:
    • Control process trace files named *dm*.trc.
    • Worker process trace files named *dw*.trc.

Happy Debugging

Normally, the above information is enough to figure out the underlying problem. If you supply the above to Oracle Support when you open the next case, they’ll be super excited.

Further Reading