Sunday, March 17, 2013

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]

4 comments:

  1. no EXPDP, resolvi o problema colocando o parametro job_name=expdp_schemaname

    ReplyDelete
    Replies
    1. Rafael podria explicar mejor donde debo colocar job_name=expdp_schemaname

      Delete
  2. while import use the below parameter:

    exclude=PROCACT_INSTANCE

    ReplyDelete
  3. you have any solution for EXPDP instead?

    ReplyDelete