Tuesday, June 12, 2012

ORA-39127: unexpected error from call to export_string :=SYS.DBMS_CUBE_EXP.SCHEMA_INFO_EXP while Exporting

Database is a 11.2.0.3 standard edition standalone created with custom database template. There are no invalid options in the database registry and OLAP is not installed.
COMP_NAME                           STATUS
----------------------------------- ---------
Oracle XML Database                 VALID
Oracle Expression Filter            VALID
Oracle Rules Manager                VALID
Oracle Workspace Manager            VALID
Oracle Database Catalog Views       VALID
Oracle Database Packages and Types  VALID
JServer JAVA Virtual Machine        VALID
Oracle XDK                          VALID
Oracle Database Java Packages       VALID

9 rows selected.
While trying to export a user schema following error could be observed, even though export job completed with all rows being exported.
expdp asanga/asa directory=supdpdir dumpfile=asa.dmp logfile=asa.log schemas=asanga

Export: Release 11.2.0.3.0 - Production on Tue Jun 12 20:01:26 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
Starting "ASANGA"."SYS_EXPORT_SCHEMA_01":  asanga/******** directory=supdpdir dumpfile=asa.dmp logfile=asa.log schemas=asanga
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_CUBE_EXP.SCHEMA_INFO_EXP('ASANGA',0,1,'11.02.00.00.00',newblock)
ORA-04063: package body "SYS.DBMS_CUBE_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_CUBE_EXP"
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 9427
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_CUBE_EXP.SCHEMA_INFO_EXP('ASANGA',1,1,'11.02.00.00.00',newblock)
ORA-04063: package body "SYS.DBMS_CUBE_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_CUBE_EXP"
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 9427
. . exported "ASANGA"."X"                                5.679 KB     100 rows
Master table "ASANGA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ASANGA.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/dpdumps/asa.dmp
Job "ASANGA"."SYS_EXPORT_SCHEMA_01" completed with 2 error(s) at 20:02:09
Root cause of this error is missing OLAP components. But on standard editoin this isn't an option at all.
One solution is to remove the OLAP related components as mentioned in a related post.
This may not be applicable in this case as the database was created with custom template option and does not have the OLAP components at all (not even in invalid state).
Solution is to remove the DBMS_CUBE_EXP package. First create a backup of the package and then delete it. It's also advised to take a full backup of the database before running below sql statements
SQL> create table sys.exppkgact$_backup as select * from sys.exppkgact$;

Table created.

SQL> delete from sys.exppkgact$ where package = 'DBMS_CUBE_EXP' and schema= 'SYS';

1 row deleted.

SQL> commit;
This seem to resolve the issue as afterwards export doesn't show the error
 expdp asanga/asa directory=supdpdir dumpfile=asa.dmp logfile=asa.log schemas=asanga

Export: Release 11.2.0.3.0 - Production on Tue Jun 12 20:03:52 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
Starting "ASANGA"."SYS_EXPORT_SCHEMA_01":  asanga/******** directory=supdpdir dumpfile=asa.dmp logfile=asa.log schemas=asanga
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "ASANGA"."X"                                5.679 KB     100 rows
Master table "ASANGA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ASANGA.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/dpdumps/asa.dmp
Job "ASANGA"."SYS_EXPORT_SCHEMA_01" successfully completed at 20:04:01
Update 13 June 2012
It turns out with the above solution would only eliminate exports taken from the same edition database (standard edition). Even after executing the above delete statement issue was still there when the export was done on a enterprise edition and import was done in a standard edition database. In this case export was done on a 11gR1 (11.1.0.7) enterprise edition database and import was done 11gR2 (11.2.0.3) standard edition database. Import statement still had following error messages
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
ORA-06550: line 2, column 1:
PLS-00201: identifier 'DBMS_CUBE_EXP.SCHEMA_INFO_IMP_BEG' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
ORA-06550: line 3, column 1:
PLS-00201: identifier 'DBMS_CUBE_EXP.SCHEMA_INFO_IMP_END' must be declared
ORA-06550: line 3, column 1:
PL/SQL: Statement ignored
Failing sql is:
BEGIN
dbms_cube_exp.schema_info_imp_beg(0, '11.01.00.00.00');
dbms_cube_exp.schema_info_imp_end(0, '11.01.00.0
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
...
...
...
ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
ORA-06550: line 2, column 1:
PLS-00201: identifier 'DBMS_CUBE_EXP.SCHEMA_INFO_IMP_BEG' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
ORA-06550: line 3, column 1:
PLS-00201: identifier 'DBMS_CUBE_EXP.SCHEMA_INFO_IMP_LOOP' must be declared
ORA-06550: line 3, column 1:
PL/SQL: Statement ignored
ORA-06550: line 8, column 1:
PLS-00201: identifier 'DBMS_CUBE_EXP.SCHEMA_INFO_IMP_END' must be declared
ORA-06550: line 8, col
Job "ASANGA"."SYS_IMPORT_FULL_01" completed with 18 error(s) at 13:29:50
To resolve this case the export must be done with version=10.2 (as per 789549.1) or if network link is used for import then specify the version in the impdp statement.
Following is the output with and without version=10.2 and using a network link for import.
First without version=10.2
impdp asanga/asa directory=dumpdir network_link=remoteloc logfile=imp.log schemas=asanga

Import: Release 11.2.0.3.0 - Production on Wed Jun 13 13:50:52 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
Starting "ASANGA"."SYS_IMPORT_SCHEMA_01":  asanga/******** directory=dumpdir network_link=remoteloc logfile=imp.log schemas=asanga
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 13.75 MB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"ASANGA" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
ORA-06550: line 2, column 1:
PLS-00201: identifier 'DBMS_CUBE_EXP.SCHEMA_INFO_IMP_BEG' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
ORA-06550: line 3, column 1:
PLS-00201: identifier 'DBMS_CUBE_EXP.SCHEMA_INFO_IMP_END' must be declared
ORA-06550: line 3, column 1:
PL/SQL: Statement ignored
Failing sql is:
BEGIN
dbms_cube_exp.schema_info_imp_beg(0, '11.01.00.00.00');
dbms_cube_exp.schema_info_imp_end(0, '11.01.00.0
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
ORA-31684: Object type TYPE:"ASANGA"."T_READ_CS" already exists
ORA-31684: Object type TYPE:"ASANGA"."T_READ_CS_TAB" already exists
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
ORA-31684: Object type TYPE:"ASANGA"."T_READ_CS" already exists
ORA-31684: Object type TYPE:"ASANGA"."T_READ_CS_TAB" already exists
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "ASANGA"."IT_RS2"                       300000 rows
With version=10.2
impdp asanga/asa directory=dumpdir network_link=remoteloc logfile=imp.log version=10.2 schemas=asanga

Import: Release 11.2.0.3.0 - Production on Wed Jun 13 13:41:57 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
Starting "ASANGA"."SYS_IMPORT_SCHEMA_01":  asanga/******** directory=dumpdir network_link=remoteloc logfile=imp.log version=10.2 schemas=asanga
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 13.75 MB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"ASANGA" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
ORA-31684: Object type TYPE:"ASANGA"."T_READ_CS" already exists
ORA-31684: Object type TYPE:"ASANGA"."T_READ_CS_TAB" already exists
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "ASANGA"."IT_RS2"                       300000 rows
No errors encountered when version=10.2 option is used.

Related metalink notes
Ora-39127: Unexpected Error From Call To Export_string :=Sys.Dbms_cube_exp [ID 811934.1]
DataPump Schema Export (EXPDP) Fails With Errors ORA-39127 And ORA-37111 [ID 852794.1]
DataPump Import (IMPDP) Receives The Errors ORA-39083 PLS-201 identifier DBMS_CUBE_EXP.SCHEMA_INFO_IMP_BEG [ID 789549.1]
Data Import for SY910 Fails with Error "PLS-00201: identifier 'DBMS_CUBE_EXP.SCHEMA_INFO_IMP_BEG' must be declared" During Platform Pack for Install or Upgrade to EnterpriseOne 9.1 [ID 1457747.1]

Related post : dbms_cube_exp.schema_info_imp_beg error while importing with impdp