Showing posts with label dbms_cube_exp. Show all posts
Showing posts with label dbms_cube_exp. Show all posts

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

Posted by at
Labels: , , , , , , ,

Wednesday, January 18, 2012

dbms_cube_exp.schema_info_imp_beg error while importing with impdp

Following error came up when doing a schema import from a 11gR1 standard edition database to another 11gR1 standard edition database.Beginning of import
ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
ORA-12663: Services required by client not available on the server
ORA-36961: Oracle OLAP is not available.
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.00.00');COMMIT; END;
and at the end 
ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
ORA-12663: Services required by client not available on the server
ORA-36961: Oracle OLAP is not available.
Failing sql is:
BEGIN 
dbms_cube_exp.schema_info_imp_beg(1, '11.01.00.00.00');
dbms_cube_exp.schema_info_imp_loop(1, '11.01.00.00.00', '<?xml version="1.0" encoding="UTF-16"?>
<Metadata
Version="1.1"
CreateOnly="True">
</Metadata>');
dbms_cube_exp.schema_info_imp_end(1, '11.01.00.00.00');COMMIT; END;
The import completed without any further errors and schema objects were available on the imported database. Metalink note DataPump Import (IMPDP) Receives The Errors ORA-39083 PLS-201 identifier DBMS_CUBE_EXP.SCHEMA_INFO_IMP_BEG [ID 789549.1] mentions that this is a ignorable error.

OLAP is not an option available with standard edition database but it gets added in an invalid state when a database is created using one of the provided database templates (transaction processing database) with oracle. Although option get installed it remains in a invalid state.
Metalink note "How To Find Out If OLAP Is Being Used And How To Remove OLAP [Doc ID 739032.1]" could be used to verify if olap is installed and used. (being used in a standard edition system is unlikely). The remove steps listed in this metalink note cannot be run on standard edition as those files are not installed with standard edition.

There's another metalink that list "How to Remove OLAP From the Standard Edition database. [ID 1362752.1]" which is by dropping the OLAPSYS  user and running utlrp afterwards. This will remove the OLAP Catalog option from the database but OLAP Analytic Workspace and Oracle OLAP API will remain in an invalid state.
SQL> select comp_id, comp_name, version, status
  2         from dba_registry
  3         where comp_name like '%OLAP%';

COMP_ COMP_NAME                 VERSION    STATUS
----- ------------------------- ---------- --------
APS   OLAP Analytic Workspace   11.1.0.7.0 INVALID
XOQ   Oracle OLAP API           11.1.0.7.0 INVALID
Metalink note Remove Invalid OLAP Objects From SYS And OLAPSYS Schemas [ID 565773.1] could be used to resolve any invalid objects issues after the removal of olap catalog.

SR has been raised to find out ways to remove these options from a standard edition database.

Useful metalink notes
How To Remove or De-activate OLAP After Migrating From 9i To 10g or 11g [ID 467643.1]
ORA-39127 SYS.DBMS_CUBE_EXP.INSTANCE_EXTENDED_INFO_EXP ORA-44002 On Expdp After Upgrade To 11.2.0.2 [ID 1353491.1]
How To Find Out If OLAP Is Being Used And How To Remove OLAP [Doc ID 739032.1]
How to Remove OLAP From the Standard Edition database. [ID 1362752.1]
Remove Invalid OLAP Objects From SYS And OLAPSYS Schemas [ID 565773.1]
How To Remove Or To Reinstall The OLAP Option To 10g And 11g [ID 332351.1]
DataPump Import(IMPDP) Receives The Errors ORA-39083 PLS-201 identifier DBMS_CUBE_EXP.SCHEMA_INFO_IMP_BEG [ID 789549.1]

Workaround
First of all it must be strenuously stressed that this workaround is not an official oracle supported one (yet! maybe!).

Copied the olap folder ($ORACLE_HOME/olap) from a enterprise installation into standard edition home and ran the remove scripts mentioned in Doc ID 739032.1.
@?/olap/admin/catnoamd.sql
@?/olap/admin/olapidrp.plb
@?/olap/admin/catnoaps.sql
@?/olap/admin/catnoxoq.sql
Afterwards ran @?/rdbms/admin/utlrp and execution didn't show any errors. But three sys object related OLAP came as invalid
SQL> select object_name,object_type from dba_objects where owner='SYS' and status <> 'VALID';

OBJECT_NAME              OBJECT_TYPE
------------             -------------
INTERACTIONEXECUTE        FUNCTION
XOQ_VALIDATE              PROCEDURE
CWM2_OLAP_INSTALLER       PACKAGE BODY
Dropped them and ran utlrp again still no errors. The OLAP option that remained invalid in the dba_registry is now shown as removed.
SQL> select comp_id, comp_name, version, status
  2         from dba_registry
  3         where comp_name like '%OLAP%';

COMP_ COMP_NAME                 VERSION    STATUS
----- ------------------------- ---------- --------
APS   OLAP Analytic Workspace   11.1.0.7.0 REMOVED
XOQ   Oracle OLAP API           11.1.0.7.0 REMOVED

Update 02 February 2012
Outcome from the SR was these options were never intended to be in SE (Standard Edition) but could get in through imp and using the template that has some EE (Enterprise Edition) components (was the case here). So there's no "official" way to remove these components from SE and require a custom approach since the script provided to remove these components from EE are not even installed on SE. Therefore it wouldn't be a issue if those scripts are copied from EE to SE to remove the unsupported components.

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

Posted by at
Labels: , , , , ,
Subscribe to: Posts (Atom)