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 INVALIDMetalink 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.sqlAfterwards 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 BODYDropped 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
