Wednesday, February 25, 2015

ORA-39065: unexpected master process exception in DISPATCH


##########################
## Error
##########################

ORA-39065: unexpected master process exception in DISPATCH

### 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 Occurred
##########################

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

##########################
## Command Executed
##########################

expdp \'/ as sysdba\' estimate_only=y

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Reproduce the Error
#########################################

$ expdp \'/ as sysdba\' estimate_only=y
Export: Release 11.2.0.4.0 - Production on Wed Feb 25 16:45:05 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
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

$

From the error it looks like there is some problem with the library.

=====================================================================================================================

#########################################
# 2) Check the INVALID Objects
#########################################

#### 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;

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.

=====================================================================================================================

#########################################
# 3) Reason for Failure
#########################################

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

And one of the oracle notes (453796.1) says that there is a possibility that the last CPU patch applied might had a problem which left some of the Datapump Metadata missing.

=====================================================================================================================

#########################################
# 4) Check CPU Patch
#########################################

#### Check the latest patch applied to the database

set lines 200
col action_time for a30
col action for a25
col namespace for a9
col version for a10
col id for 99999999
col comments for a25
col bundle_series for a25
select * from registry$history;

ACTION_TIME                    ACTION                    NAMESPACE VERSION           ID COMMENTS                  BUNDLE_SERIES
------------------------------ ------------------------- --------- ---------- --------- ------------------------- -------------------------
13-DEC-12 11.48.14.623579 AM   APPLY                     SERVER    11.2.0.2           7 CPUOct2012                CPU
28-JAN-13 04.14.40.507736 PM   APPLY                     SERVER    11.2.0.2           8 CPUJan2013                CPU
01-DEC-13 02.49.06.524419 PM   APPLY                     SERVER    11.2.0.2          11 CPUOct2013                CPU
23-FEB-14 08.34.03.016928 AM   VIEW INVALIDATE                                  8289601 view invalidation
23-FEB-14 08.34.03.159818 AM   UPGRADE                   SERVER    11.2.0.4.0           Upgraded from 11.2.0.2.0
23-FEB-14 09.09.53.453945 AM   APPLY                     SERVER    11.2.0.4           1 CPUJan2014                CPU
23-FEB-14 09.11.10.377869 AM   APPLY                     SERVER    11.2.0.4           1 CPUJan2014                CPU
04-MAY-14 11.27.08.483044 AM   APPLY                     SERVER    11.2.0.4           2 CPUApr2014                CPU
02-NOV-14 11.03.14.032696 AM   APPLY                     SERVER    11.2.0.4           4 CPUOct2014                CPU
25-JAN-15 11.02.36.070561 PM   APPLY                     SERVER    11.2.0.4           5 CPUJan2015                CPU
10 rows selected.

We have applied the last CPU patch on 25-Jan-2015 which is coinciding with what the document says.

#### 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.
=====================================================================================================================

SQL> Select owner,object_name,object_type,status,created,LAST_DDL_TIME from dba_objects where status='INVALID' and owner='SYS' order by object_name;

no rows selected

$ 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

=====================================================================================================================



2 comments:

Unknown said...

Nice and accurate read

Anonymous said...

Thanks!!! It helped me to overcome the issue.