We have been trying to export the database using data pump for our migration process.

Source Platform was Windows and Target platform Red Hat Linux however data pump utility (expdp) was failing with below error

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS []
ORA-31642: the following SQL statement fails:
BEGIN "SYS"."DBMS_CUBE_EXP".SCHEMA_CALLOUT(:1,0,1,'11.02.00.00.00'); END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 1749
ORA-06550: line 1, column 8:
PLS-00201: identifier 'SYS.DBMS_CUBE_EXP' must be declared
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPW$WORKER", line 8996

As per Oracle Note 1328829.1 – EXPDP: PLS-201 identifier ‘SYS.DBMS_CUBE_EXP’ must be declared (Doc ID 1328829.1) we were able to resolve the issue

Check whether OLAP is installed or NOT by running the following queries

sqlplus /nolog
connect / as sysdba
— Start spooling to file:

spool SOURCE.OUT
SET LINES 120 numwidth 12 pages 10000 long 2000000000
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
SHOW USER
SELECT sysdate FROM dual;

— Oracle version:

SELECT * FROM v$version;

— Database status:

col host_name FOR a15
col instance_name LIKE host_name
col version FOR a10
col platform_name FOR a30
SELECT dbid,name,created,supplemental_log_data_min,platform_name, open_mode,log_mode
FROM v$database;
SELECT instance_number,instance_name,host_name,version,STATUS,startup_time,archiver
FROM v$instance;

— Patch history:

col version FOR a11
col comments FOR a25
col action FOR a16
col namespace FOR a10
col action_time FOR a30
SELECT action_time, action, namespace, version, id, comments
FROM dba_registry_history ORDER BY 1;

— Feature usage:

SET line 450
col name FOR a50
col feature_info FOR a120
col description FOR a60
SELECT * FROM dba_feature_usage_statistics WHERE UPPER(name) LIKE '%DATAPUMP%';

— Non-default parameters:

SET LINES 80
COLUMN name format a30 tru
col VALUE FOR a48 wra
SELECT name, VALUE FROM v$parameter WHERE isdefault = 'FALSE' ORDER BY 1;

— Registry status:

SET LINES 90
col version FOR a12
col comp_id FOR a8
col schema LIKE version
col comp_name format a35
col STATUS FOR a12
SELECT comp_id,schema,STATUS,version,comp_name FROM dba_registry ORDER BY 1;

— invalid objects:

SET LINES 120
col STATUS FOR a9
col object_type FOR a20;
col owner.object FOR a50
SELECT STATUS, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects
WHERE STATUS != 'VALID' AND object_name NOT LIKE 'BIN$%' ORDER BY 4,2;
SELECT owner, object_type, STATUS, COUNT(*)
FROM dba_objects
WHERE STATUS != 'VALID' AND object_name NOT LIKE 'BIN$%'
GROUP BY owner, object_type, STATUS ORDER BY 1,2;

— Object specific:

SELECT owner, object_type, STATUS, COUNT(*)
FROM dba_objects
WHERE owner = 'OLAPSYS' AND object_name NOT LIKE 'BIN$%'
GROUP BY owner, object_type, STATUS ORDER BY 1,2;
SELECT username FROM dba_users WHERE username = 'OLAPSYS';

— Packages to be called by Export:

set lines 90
select * from sys.exppkgact$ where package like '%CUBE%' order by 2,1;
spool off

–If OLAP is not installed (user OLAPSYS does not exist) or if OLAP is installed incorrectly (user OLAPSYS does not have any objects), then cleanup the table SYS.EXPPKGACT$. E.g.:

CONNECT / AS sysdba
CREATE TABLE sys.exppkgact$_backup_20130916 AS SELECT * FROM sys.exppkgact$;
DELETE FROM sys.exppkgact$ WHERE package='DBMS_CUBE_EXP' AND schema='SYS';
commit;

Retry the Data Pump Job it should complete successfully now.

Share →
0 comments
Skip to toolbar