The DataPump expdp fails with the ORA-39126, ORA-31642, ORA-06512, ORA-06550, ORA-00201 errors.

Problem   : The datapump expdp fails with the following errors.

Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS []
ORA-31642: the following SQL statement fails:
BEGIN "DMSYS"."DBMS_DM_MODEL_EXP".SCHEMA_CALLOUT(:1,0,1,'11.02.00.02.00'); END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 1282
ORA-06550: line 1, column 8:
PLS-00201: identifier 'DMSYS.DBMS_DM_MODEL_EXP' must be declared
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPW$WORKER", line 8398
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x129b5cd68     19225  package body SYS.KUPW$WORKER
0x129b5cd68      8430  package body SYS.KUPW$WORKER
0x129b5cd68     12793  package body SYS.KUPW$WORKER
0x129b5cd68      2548  package body SYS.KUPW$WORKER
0x129b5cd68      9099  package body SYS.KUPW$WORKER
0x129b5cd68      1690  package body SYS.KUPW$WORKER
0xf2bbb830         2  anonymous block
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS []
ORA-31642: the following SQL statement fails:
BEGIN "DMSYS"."DBMS_DM_MODEL_EXP".SCHEMA_CALLOUT(:1,0,1,'11.02.00.02.00'); END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 1282
ORA-06550: line 1, column 8:
PLS-00201: identifier 'DMSYS.DBMS_DM_MODEL_EXP' must be declared
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPW$WORKER", line 8398
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x115de4448     19225  package body SYS.KUPW$WORKER
0x115de4448      8430  package body SYS.KUPW$WORKER
0x115de4448     12793  package body SYS.KUPW$WORKER
0x115de4448      2548  package body SYS.KUPW$WORKER
0x115de4448      9099  package body SYS.KUPW$WORKER
0x115de4448      1690  package body SYS.KUPW$WORKER
0x15e113630         2  anonymous block
Job "SYS"."EXP_JOB_201706061112" stopped due to fatal error at 11:13:11

Root Cause : The Data Mining schema DMSYS has been dropped in Oracle 11g. If it does, you should drop the DMSYS schema and its associated objects from the database. Because, Oracle migrated all DMSYS related object to SYS. 

Solution       : Check for existence of schema DMSYS and related object in the database after upgrading to Oracle 11g and drop them. Once dropped, try running DataPump expdp command.

SQL> SET LINES 220 PAGES 4999
SQL> SELECT * FROM SYS.exppkgact$ WHERE SCHEMA='DMSYS';
PACKAGE                        SCHEMA                              CLASS     level#
------------------------------ ------------------------------ ---------- ----------
dbms_dm_model_exp              dmsys                                   2       2000
dbms_dm_model_exp              dmsys                                   3       4000
dbms_dm_model_exp              dmsys                                   6       1000
SQL> DELETE FROM SYS.exppkgact$ WHERE SCHEMA = 'DMSYS';
3 ROWS deleted.
SQL> 
SQL> COLUMN db_link FOR a30
SQL> SELECT * FROM dba_synonyms WHERE table_owner='DMSYS';

OWNER                          synonym_name                   table_owner                    table_name                     db_link
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
PUBLIC                         cluster_rule_type              dmsys                          cluster_rule_type
PUBLIC                         cluster_type                   dmsys                          cluster_type
PUBLIC                         odm_attribute_importance_model dmsys                          odm_attribute_importance_model
PUBLIC                         odm_naive_bayes_model          dmsys                          odm_naive_bayes_model
SQL>DROP PUBLIC SYNONYM cluster_rule_type ;
SYNONYM dropped.
SQL>DROP PUBLIC SYNONYM cluster_type ;
SYNONYM dropped.
SQL>DROP PUBLIC SYNONYM odm_attribute_importance_model ;
SYNONYM dropped.
SQL>DROP PUBLIC SYNONYM odm_naive_bayes_model ;
SYNONYM dropped.
SQL> SELECT COUNT(*) FROM dba_objects WHERE OWNER='DMSYS';

  COUNT(*)
----------
         0
SQL>

Note             : You can create Dynamic SQL to perform the drop the above Public Synonyms.

References

  • Datapump Export Fails with ORA-39126 ORA-31642 ORA-06512 ORA-06550 PLS-00201 ORA-06550 Errors (Doc ID 1564521.1)
  • NO DMSYS Schema In 11G And Above And How To Remove IT (Doc ID 1497250.1)
  • Upgrading Oracle Data Mining