Pages

Wednesday, February 16, 2022

Oracle 12.2 EXPDP Fails Exporting Full PDB with ORA-39126 On KUPW$WORKER.FETCH_XML_OBJECTS

This issue happens in Oracle database 12.2 or higher version, the example in this post happened in 19.13.

When running Oracle Data Pump Export utility expdp to export PDB in Full Mode, the job fails with ORA-39126 and ORA-00942. Following is an example
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [ORA-00942: table or view does not exist

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 12630
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 9571
ORA-06512: at "SYS.DBMS_METADATA", line 2976
ORA-06512: at "SYS.DBMS_METADATA", line 3608
ORA-06512: at "SYS.DBMS_METADATA", line 5008
ORA-06512: at "SYS.DBMS_METADATA", line 5327
ORA-06512: at "SYS.DBMS_METADATA", line 9552
ORA-06512: at "SYS.KUPW$WORKER", line 15119

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
900000045fb46b00     33543  package body SYS.KUPW$WORKER.WRITE_ERROR_INFORMATION
900000045fb46b00     12651  package body SYS.KUPW$WORKER.DETERMINE_FATAL_ERROR
900000045fb46b00     15452  package body SYS.KUPW$WORKER.FETCH_XML_OBJECTS
900000045fb46b00      3917  package body SYS.KUPW$WORKER.UNLOAD_METADATA
900000045fb46b00     13746  package body SYS.KUPW$WORKER.DISPATCH_WORK_ITEMS
900000045fb46b00      2439  package body SYS.KUPW$WORKER.MAIN
b00000055ff65380         2  anonymous block

DBMS_METADATA.SET_FILTER
DBMS_METADATA.SET_FILTER
DBMS_METADATA.SET_FILTER
KUPW: In FETCH_XML_OBJECTS
KUPW: End seqno is: 24
KUPF$FILE.OPEN_CONTEXT
KUPF$FILE.OPEN_CONTEXT
DBMS_METADATA.FETCH_XML_CLOB
DBMS_METADATA.FETCH_XML_CLOB
KUPW: In procedure DETERMINE_FATAL_ERROR with ORA-00942: table or view does not exist
The issues is usually seen when components installed in pdb do not match (less than) components installed in root container. For example, in root container, components installed as following
sys@orcl> alter session set container=cdb$root;

Session altered.

sys@orcl> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 pdb01                          READ WRITE NO

sys@orcl> select comp_id,comp_name from dba_registry;

COMP_ID                        COMP_NAME
------------------------------ --------------------------------------------------
CATALOG                        Oracle Database Catalog Views
CATPROC                        Oracle Database Packages and Types
JAVAVM                         JServer JAVA Virtual Machine
XML                            Oracle XDK
CATJAVA                        Oracle Database Java Packages
APS                            OLAP Analytic Workspace
RAC                            Oracle Real Application Clusters
XDB                            Oracle XML Database
OWM                            Oracle Workspace Manager
CONTEXT                        Oracle Text
ORDIM                          Oracle Multimedia
SDO                            Spatial
XOQ                            Oracle OLAP API
OLS                            Oracle Label Security
APEX                           Oracle Application Express
DV                             Oracle Database Vault

sys@orcl> alter session set container=PDB$SEED;

Session altered.

sys@orcl> select comp_id,comp_name from dba_registry;

COMP_ID                        COMP_NAME
------------------------------ --------------------------------------------------
CATALOG                        Oracle Database Catalog Views
CATPROC                        Oracle Database Packages and Types
JAVAVM                         JServer JAVA Virtual Machine
XML                            Oracle XDK
CATJAVA                        Oracle Database Java Packages
APS                            OLAP Analytic Workspace
RAC                            Oracle Real Application Clusters
XDB                            Oracle XML Database
OWM                            Oracle Workspace Manager
CONTEXT                        Oracle Text
ORDIM                          Oracle Multimedia
SDO                            Spatial
XOQ                            Oracle OLAP API
OLS                            Oracle Label Security
APEX                           Oracle Application Express
DV                             Oracle Database Vault
Components installed in pdb
sys@orcl> alter session set container=pdb01;

Session altered.

sys@orcl> select comp_id,comp_name from dba_registry;

COMP_ID                        COMP_NAME
------------------------------ --------------------------------------------------
CATALOG                        Oracle Database Catalog Views
CATPROC                        Oracle Database Packages and Types
RAC                            Oracle Real Application Clusters
OWM                            Oracle Workspace Manager
XDB                            Oracle XML Database
Both root container and seed pdb have 16 components installed, but pdb01 only have 5 components installed. It usually happends when pdb is created from remote clone. If you got the issue in this scenario with database 12.2 or higher, it can be fixed by reloading Data Pump as following

1. Open all pluggable databases
alter pluggable database all open;
2. Run following script as SYS
@?/rdbms/admin/dpload.sql
3. Recompile invalid objects as SYS
@?/rdbms/admin/utlrp.sql
If reloading Data Pump is not applicable for you, schema mode can be used instead of full mode.

No comments: