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-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:
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:
— Database status:
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 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:
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:
COLUMN name format a30 tru
col VALUE FOR a48 wra
SELECT name, VALUE FROM v$parameter WHERE isdefault = 'FALSE' ORDER BY 1;
— Registry status:
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:
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:
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:
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.:
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.
