Some Data Pump issues: DBMS_DATAPUMP Import via NETWORK_LINK fails + STATUS parameter giving bad performance

One of my dear Oracle ACS colleagues (Danke Thomas!) highlighted this issue to me as one of his lead customers hit this pitfall a week ago. .

DBMS_DATAPUMP Import Over NETWORK_LINK fails with ORA-39126 / ORA-31600

Symptoms are:

KUPW$WORKER.CONFIGURE_METADATA_UNLOAD [ESTIMATE_PHASE]
ORA-31600: invalid input value IN ('VIEWS_AS_TABLES/TABLE_DATA') for parameter VALUE in function SET_FILTER

This can be cured with the patch for bug 19501000 –  but this patch can conflict with:Bug 18793246  EXPDP slow showing base object lookup during datapump export causes full table scan per object and therefore may require a merge patchpatch 21253883 is the one to go with in this case. The merge is only needed on top of 12.1.0.2.

The fixes for:
Bug 18793246 – BASE OBJECT LOOKUP DURING DATAPUMP EXPORT CAUSES FULL TABLE SCAN PER OBJECT
Bug 19501000 – ORA-39126 RUNNING IMPDP USING NETWORK_LINK FROM 12C CONNECTING TO 10GR2
are both included from Oracle Database 12.2.0.1 onward.

 

Data Pump is giving bad performance in Oracle 12.1.0.2 when the STATUS parameter option is used on command line

Another issue Roy recently came across is the one below.

Symptoms are:

It looks like the routines we are using to get status are significantly slower in 12c than in 11g. On 11.2.0.4 a STATUS call of expdp/impdp runs in 0.2-0.3 seconds, but in 12.1.0.2 it takes 0.8-1.6 seconds. As a result the client falls behind on 12.1.0.2; it is taking about 0.5-0.8 seconds to put out each line in the logfile because it is getting the status each time. With over 9000 tables in a test that half a second really adds up. The result in this test case was that the data pump job completed in 35 minutes, but it took another 30-35 minutes to finish putting out messages on the client (the log file was already complete) and return control to the command line. This happens only when you use STATUS on the command line.

Recommendation is:

In Oracle 11g and 12.1, the STATUS parameter on the expdp/impdp command line this has been an issue.. This got tracked under Bug 21123545 which then was identified as a duplicate of Bug 20236523 – DATAPUMP EXPORT SLOW USING CONTENT=METADATA_ONLY .

From Oracle Database 12.2.0.1 onward, this is fixed and should not cause any issue anymore.

–Mike

Share this: