How to debug a Data Pump error

Recently, at a customer site, I had an Oracle Data Pump error which was not easy to localize. Here is the error I received during a DBIOWNER schema export with Data Pump:

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
 ORA-39125: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA while calling DBMS_METADATA.FETCH_XML_CLOB [TABLE_STATISTICS]
 ORA-00942: table or view does not exist

Using Data Pump TRACE command

I began to analyze the problem with an higher expdp tracing level, by adding the below parameters into the expdp.par file:

TRACE=480300
TRACE=1FF0300

Before starting the Data Pump tracing, the privilege exp_full_database must be granted to the schema owner DBIOWNER, in order to allow the tracing.

SQL>grant exp_full_database to DBIOWNER;

Below, you will find the list of trace level values for Data Pump:

Trace     DM   DW  ORA  Lines
 level    trc  trc  trc     in
 (hex)   file file file  trace   Purpose
 ------- ---- ---- ---- ------ -----------------------------------------------
 10300      x    x    x  SHDW: To trace the Shadow process (API) (expdp/impdp)
 20300      x    x    x  KUPV: To trace Fixed table
 40300      x    x    x  'div' To trace Process services
 80300      x            KUPM: To trace Master Control Process (MCP)      (DM)
 100300     x    x       KUPF: To trace File Manager
 200300     x    x    x  KUPC: To trace Queue services
 400300          x       KUPW: To trace Worker process(es)                (DW)
 800300          x       KUPD: To trace Data Package
 1000300         x       META: To trace Metadata Package
 1FF0300    x    x    x  'all' To trace all components          (full tracing)

I did not find any real interesting information in the trace files created under background_dump_dest,
apart from the identical error “ORA-00942: table or view does not exist”. However, there was no additional informations about which table is missing…

Thus, I revoked the privilege expo_full_database from DBIOWNER and the TRACE parameter from expdb.par file, and used another method for tracing:

SQL> revoke exp_full_database from DBIOWNER;

Using Oracle event 22923

For the second method, I used the event 22923, which is documented on Oracle support to help for expdb/impdp debugging:

SQL> alter system set events '22923 trace name errorstack level 3'
 System altered.

I started expdp for the schema DBIOWNER:

SQL> alter system set events '22923 trace name errorstack off'
 System altered.

I also analyzed the trace files here, but with the same results as for the above TRACE parameter setting.

I was on the brink of opening a Service Request on Oracle Support to find out if my error is a new oracle Bug or an already existing Bug. But first, I wanted to trace the SQLs in order to look if some SQL statements report an error.

Using event 10046 for SQL tracing

I started SQL tracing for the entire database:

SQL> alter system set events='10046 trace name context forever, level 12';
 System altered.

I started expdp from the scheme DBIOWNER and stopped the SQL tracing

SQL> alter system set events='10046 trace name context off';
 System altered.

Then, I started tkprof for the trace file:

Host> tkprof pro73b_dw01_5525602.trc expdb_trace.tkprof

After analyzing the created tkprof file I soon found the problem 🙂  “I like the event 10046”

I extracted the output from the created tkprof file:

****************************************************************************** SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('M_VIEW_T', '7')), KU$.MVIEW_TAB.OBJ_NUM ,KU$.VNAME ,KU$.VNAME ,'MATERIALIZED_VIEW' ,KU$.SOWNER FROM
 SYS.KU$_M_VIEW_FH_VIEW KU$ WHERE KU$.MVIEW.SOWNER=:SCHEMA1 AND  NOT EXISTS (SELECT 1 FROM  SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE='MATERIALIZED_VIEW' AND A.NAME=KU$.V
 NAME AND A.SCHEMA=KU$.SOWNER) AND NOT EXISTS (SELECT 1 FROM  SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE='SCHEMA' AND A.NAME=KU$.SOWNER)
Error encountered: ORA-00942
 *******************************************************************************

Analyzing the above found SQL statement

I began to start a select count (*) of all involved tables of the statement and again (Bingo :-), one view was readable as user SYS but not as user DBIOWNER:

As user DBIOWNER
 ----------------- 
 SQL> select count(*) from  SYS.KU$_M_VIEW_FH_VIEW;
 select count(*) from  SYS.KU$_M_VIEW_FH_VIEW
 *
 ERROR at line 1:
 ORA-00942: table or view does not exist
As user SYS
 ----------- 
 SQL> select count(*) from  SYS.KU$_M_VIEW_FH_VIEW;
COUNT(*)
 ----------
 6

I then logged in on another database and started the below command as a normal schema owner in order to verify if the same problem appears.

SQL> select count(*) from  SYS.KU$_M_VIEW_FH_VIEW;
COUNT(*)
 ----------
 13

After some checks on both databases, I found that the below public synonyms were missing on my database:

TABLE_NAME                    GRANTEE      OWNER       GRANTOR       PRIVILEGE                                 
 ---------------------------   ------------ ----------- ------------  ----------
 KU$_10_1_PFHTABLE_VIEW        PUBLIC       SYS         SYS           SELECT                               
 KU$_M_VIEW_FH_VIEW            PUBLIC       SYS         SYS           SELECT                               
 KU$_M_VIEW_LOG_PFH_VIEW       PUBLIC       SYS         SYS           SELECT                               
 KU$_M_VIEW_PFH_VIEW           PUBLIC       SYS         SYS           SELECT

Conclusion

It is unsure that you will find your Data Pump error with the Data Pump TRACE command or the corresponding Oracle event command. But the Oracle event 10046 can be a good alternative to localize missing objects or privileges.

It was impossible for me to find out why these privileges were missing or not created during database setup (perhaps also deleted later).