ORA-39065: unexpected master process exception in DISPATCH

0
(0)

### Full Error

ORA-39006: internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-01403: no data found
ORA-39097: Data Pump job encountered unexpected error 100

Error occured while trying to run a datapump export for estimate the size of the dumpfile in 12.2.0.4.0 version

Lets check if there is any INVALID objects present in SYS schema

SET LINES 200
COL OBJECT_NAME FOR A35
COL OBJECT_TYPE FOR A25
COL Owner FOR A25
Select owner,object_name,object_type,status,created,LAST_DDL_TIME from dba_objects where status='INVALID' and owner='SYS' order by object_name;

Output like (from oracle 11g):

OWNER                     OBJECT_NAME                         OBJECT_TYPE               STATUS  CREATED   LAST_DDL_
------------------------- ----------------------------------- ------------------------- ------- --------- ---------
SYS                       DBMS_AQELM                          PACKAGE BODY              INVALID 10-DEC-12 10-DEC-12
                          DBMS_AW_EXP                         PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          DBMS_CMP_INT                        PACKAGE                   INVALID 10-DEC-12 23-FEB-14
                          DBMS_DATAPUMP_UTL                   PACKAGE BODY              INVALID 10-DEC-12 11-JUN-13
                          DBMS_FILE_GROUP                     PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          DBMS_FILE_GROUP_UTL_INVOK           PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          DBMS_LOGMNR_INTERNAL                PACKAGE BODY              INVALID 07-DEC-12 23-FEB-14
                          DBMS_PRVTAQIP                       PACKAGE                   INVALID 07-DEC-12 23-FEB-14
                          DBMS_PRVTAQIP                       PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          DBMS_REPCAT_MIGRATION               PACKAGE                   INVALID 07-DEC-12 23-FEB-14
                          DBMS_REPCAT_MIGRATION               PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          DBMS_SQLTCB_INTERNAL                PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          DBMS_STREAMS_AUTH                   PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          DBMS_STREAMS_MT                     PACKAGE                   INVALID 07-DEC-12 23-FEB-14
                          DBMS_STREAMS_MT                     PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          DBMS_STREAMS_SM                     PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          DBMS_STREAMS_SM                     PACKAGE                   INVALID 07-DEC-12 23-FEB-14
                          DBMS_SUMREF_UTIL                    PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          DBMS_WORKLOAD_REPLAY                PACKAGE BODY              INVALID 10-DEC-12 02-MAY-14
                          DBMS_WRR_INTERNAL                   PACKAGE BODY              INVALID 10-DEC-12 02-MAY-14
                          DBMS_XSTREAM_ADM_INTERNAL           PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          DBMS_XSTREAM_AUTH                   PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          HTTPURITYPE                         TYPE BODY                 INVALID 10-DEC-12 23-FEB-14
                          KUPW$WORKER                         PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          SCHEDULER$_JOB_EVENT_HANDLER        PROCEDURE                 INVALID 10-DEC-12 23-FEB-14
                          URIFACTORY                          PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          UTL_HTTP                            PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          UTL_SMTP                            PACKAGE BODY              INVALID 10-DEC-12 10-DEC-12
                          UTL_SMTP                            PACKAGE                   INVALID 07-DEC-12 25-JAN-15
                          UTL_TCP                             PACKAGE BODY              INVALID 10-DEC-12 23-FEB-14
                          UTL_URL                             PACKAGE BODY              INVALID 10-DEC-12 10-DEC-12
                          UTL_URL                             PACKAGE                   INVALID 07-DEC-12 23-FEB-14
32 rows selected.

Reason for Failure

We are receiving the error because the binary packages which belong to DATAPUMP is in INVALID state.

Query one of the Datapump Metadata.

select count(*) from metanametrans$;
SQL> select count(*) from metanametrans$;
  COUNT(*)
----------
         0

Above metadata table is empty which tells us that there is a problem. When i checked the same metadata table in another database it has 3393 rows.

Solution

Load datapump metadata using below sql and recompile all the INVALID objects in the database.

@$ORACLE_HOME/rdbms/admin/catmet2.sql
@$ORACLE_HOME/rdbms/admin/utlrp.sql

Note : You can also try once running “utlrp.sql” as its just a recompilation of INVALID objects. But in my case it was not working as the table data is missing.

Now there is no INVALID objects and export is running good now.

$ expdp \'/ as sysdba\' estimate_only=y full=y
Export: Release 11.2.0.4.0 - Production on Wed Feb 25 16:53:07 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_FULL_01":  "/******** AS SYSDBA" estimate_only=y full=y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
.  estimated "SID_OWNER"."RECOTOR":"P201404"   4.248 GB

Similar Posts:

15

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

Leave a Comment

Your email address will not be published.

Scroll to Top