##########################
## 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:
Nice and accurate read
Thanks!!! It helped me to overcome the issue.
Post a Comment