ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS

Error:

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS [PROCACT_SCHEMA  

ORA-06502: PL/SQL: numeric or value error

LPX-00210: expected '<' instead of '?'

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.KUPW$WORKER", line 8164

----- PL/SQL Call Stack -----

  object      line  object

  handle    number  name

700000040277c30     19028  package body SYS.KUPW$WORKER

700000040277c30      8191  package body SYS.KUPW$WORKER

700000040277c30     15631  package body SYS.KUPW$WORKER

700000040277c30      3944  package body SYS.KUPW$WORKER

700000040277c30      8874  package body SYS.KUPW$WORKER

700000040277c30      1651  package body SYS.KUPW$WORKER    

700000040217c18         2  anonymous block

When: While Using data pump to import schemas from 11.2.0.2 software version (compatible parameter set to 11.2.0.2) to 11.2.0.1 software version (compatible parameter set to 11.1.0.6) as a part of downgrade activity

Solution/Workaround:

There are multiple workarounds to this issues (as suggested by experts) but the only one which worked for me is listed at the end i.e. Workaround 3

Workaround 1:

As found on MOS, matching this error

Data Pump Import Including Statistics Terminates With Fatal Error ORA-39126 ORA-6502 LPX-225 End-Element Tag HIST_GRAM_LIST_ITEM [ID 878626.1]

Cause

The statistics included in the import may be malformed.

Solution

To work around this issue, repeat either the expdp or the impdp using the parameter EXCLUDE=STATISTICS. After import, re-gather the statistics for the imported objects in the target database using the DBMS_STATS package.

When is tried, no luck!

#impdp DIRECTORY=EXP_DIR dumpfile=expdp_tcldev_old.dmp logfile=impdp_tcldev_old.log full=y statistics=none

Import: Release 11.2.0.1.0 - Production on Sat Mar 16 05:02:10 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: system

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

Legacy Mode Active due to the following parameters:

Legacy Mode Parameter: "statistics=none" Location: Command Line, ignored.

Master table "SYSTEM"."SYS_IMPORT_FULL_04" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_04":  system/******** DIRECTORY=EXP_DIR dumpfile=expdp_tcldev_old.dmp logfile=impdp_tcldev_old.log full=y

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"FLOWS_FILES" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS [PROCACT_SCHEMA:"FLOWS_FILES"]

ORA-06502: PL/SQL: numeric or value error

LPX-00210: expected '<' instead of '?'

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.KUPW$WORKER", line 8164

----- PL/SQL Call Stack -----

  object      line  object

  handle    number  name

7000000402cd7c8     19028  package body SYS.KUPW$WORKER

7000000402cd7c8      8191  package body SYS.KUPW$WORKER

7000000402cd7c8     15631  package body SYS.KUPW$WORKER

7000000402cd7c8      3944  package body SYS.KUPW$WORKER

7000000402cd7c8      8874  package body SYS.KUPW$WORKER

7000000402cd7c8      1651  package body SYS.KUPW$WORKER

7000000402d1390         2  anonymous block

Job "SYSTEM"."SYS_IMPORT_FULL_04" stopped due to fatal error at 05:02:25

Workaround 2:

Identify the existence of PROCACT objects in source & destination.

If not found in either of source or destination then EXCLUDE it in expdp or impdp

If it still doesn't works then exclude PROCACT_SYSTEM

When I tried, no luck!

select owner,object_type,status,object_name from dba_objects where upper (object_name) like '%PROCACT%';

OWNER      OBJECT_TYPE         STATUS  OBJECT_NAME

----------- ------------------- ------- ------------------------------------

SYS              TYPE                VALID   KU$_PROCACT_T

SYS              VIEW                VALID   KU$_PROCACT_SYS_VIEW

SYS              TYPE                VALID   KU$_PROCACT_SCHEMA_T

SYS              VIEW                VALID   KU$_PROCACT_SCHEMA_VIEW

SYS              TYPE                VALID   KU$_PROCACT_INSTANCE_T

SYS              VIEW                VALID   KU$_PROCACT_INSTANCE_VIEW

6 rows selected.

impdp DIRECTORY=EXP_DIR dumpfile=expdp_tcldev _old.dmp logfile=impdp_tcldev_old.log full=y exclude=PROCACT _SYSTEM

Import: Release 11.2.0.1.0 - Production on Sat Mar 16 04:49:38 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: system

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

ORA-39002: invalid operation

ORA-39168: Object path PROCACT_SYSTEM was not found.

Workaround 3:

This is what the best solution will take you par, our old traditional export import

If you have tried all above workaround and did not worked then go back to old days & get export done using exp  

Thanks to oracle who is still keeping backward compatibilities/features

When I tried, success this time!

#imp file=exp_full.dmp fromuser=CRAMERSSO fromuser=CRAMERSSO touser=CRAMERSSO ignore=y

Import: Release 11.2.0.1.0 - Production on Sat Mar 16 05:25:37 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: system

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by SYS, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses AL32UTF8 character set (possible charset conversion)

. importing CRAMERSSO's objects into CRAMERSSO

. . importing table                    "CRAMERSSO"          0 rows imported

. . importing table           "SSO_SESSION_STATUS"          0 rows imported

. . importing table       "USERSESSIONPLANCONTEXT"          0 rows imported

Import terminated successfully without warnings.

This situation should not arise when going upwards i.e. from lower to upper version of the compatibility but it does when going backwards

So have look at the compatibility matrix of the expdb/impdp

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