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. set lines 200; col COMP_ID for a15; col COMP_NAME for a40; col VERSION for a10; col STATUS for a15; select comp_id, comp_name, version, status from dba_registry where comp_name like '%OLAP%'; select name "FEATURE", first_usage_date "FROM", last_usage_date "TO" from DBA_FEATURE_USAGE_STATISTICS where name like '%OLAP%'; ========================================================================= Running the validate_ordim gives the following errors SQL> set serveroutput on SQL> execute validate_ordim; Locator INVALID OBJECTS: PRVT_IDX - 5 - 11 Locator INVALID OBJECTS: SDO_GEOM - 5 - 11 Locator INVALID OBJECTS: QRY2OPT - 5 - 8 Locator INVALID OBJECTS: SDO_PQRY - 5 - 8 Locator INVALID OBJECTS: SAMCLUST_IMP_T - 5 - 14 PL/SQL procedure successfully completed. ========================================================================= From the output it seems that some of the spatial component related objects are invalid (since spatial option is off after the upgrade) thus oracle media that depends on it also becomes invalid. Remove the Oracle media component which also removes spatial component which is depends on media. SQL> @?/rdbms/admin/catcmprm.sql ORDIM About to remove Oracle Multimedia. Checking to see if anyone is using Oracle Multimedia. Oracle Multimedia is not being used PL/SQL procedure successfully completed. Are you sure you want to remove Oracle Multimedia (Y/N): Y PL/SQL procedure successfully completed. Removing Oracle Multimedia PL/SQL procedure successfully completed. ========================================================================= SQL> select comp_id, comp_name, version, status from dba_registry order by 4; COMP_ID COMP_NAME VERSION STATUS --------------- ---------------------------------------- ---------- --------------- AMD OLAP Catalog 10.2.0.5.0 OPTION OFF XOQ Oracle OLAP API 10.2.0.5.0 OPTION OFF APS OLAP Analytic Workspace 10.2.0.5.0 OPTION OFF ODM Oracle Data Mining 10.2.0.5.0 OPTION OFF CATJAVA Oracle Database Java Packages 11.2.0.3.0 VALID EXF Oracle Expression Filter 11.2.0.3.0 VALID RUL Oracle Rules Manager 11.2.0.3.0 VALID OWM Oracle Workspace Manager 11.2.0.3.0 VALID CATALOG Oracle Database Catalog Views 11.2.0.3.0 VALID EM Oracle Enterprise Manager 11.2.0.3.0 VALID JAVAVM JServer JAVA Virtual Machine 11.2.0.3.0 VALID XML Oracle XDK 11.2.0.3.0 VALID CONTEXT Oracle Text 11.2.0.3.0 VALID XDB Oracle XML Database 11.2.0.3.0 VALID RAC Oracle Real Application Clusters 11.2.0.3.0 VALID CATPROC Oracle Database Packages and Types 11.2.0.3.0 VALID 16 rows selected. =========================================================================== This will increase the number of invalid objects but these will be OLAP related objects and has no impact on the rest of the database operation. SQL> select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type; OWNER OBJECT_TYPE COUNT(*) ------------------------------ ------------------- ---------- OLAPSYS VIEW 25 PUBLIC SYNONYM 92 OLAPSYS PACKAGE BODY 3 =========================================================================== It is possible to drop the olapsys user and also drop remaining public synonyms. DROP USER OLAPSYS CASCADE; select 'drop '||object_type||' '||substr(object_name,1,40) ||';' from dba_objects where status='INVALID'; =========================================================================== After this expdp/impdp could run into following error ORA-39127: unexpected error from call to "SYS"."DBMS_CUBE_EXP"."INSTANCE_EXTENDED_INFO_EXP" ORA-44002: invalid object name ORA-06512: at "SYS.DBMS_ASSERT", line 316 ORA-06512: at "SYS.DBMS_METADATA", line 9114 Metalink notes How To Remove or De-activate OLAP After Migrating From 9i To 10g or 11g [ID 467643.1] and ORA-39127 SYS.DBMS_CUBE_EXP.INSTANCE_EXTENDED_INFO_EXP ORA-44002 On Expdp After Upgrade To 11.2.0.2 [ID 1353491.1] explains the solution for this, which is If the OLAP option is not used delete DBMS_CUBE_EXP OLAP package from the export view as follows SQL> select PACKAGE,SCHEMA,class from exppkgact$ where (schema,package) not in (select owner,object_name from dba_objects where object_type='PACKAGE'); PACKAGE SCHEMA CLASS ------------------------------ ------------------------------ ---------- DBMS_CUBE_EXP SYS 4 SQL> delete from sys.exppkgact$ where package = 'DBMS_CUBE_EXP' and schema= 'SYS'; 1 row deleted. SQL> commit; Commit complete. ============================================================================ After this export will work as expected and there will no invalid objects nor any invalid database components @?/rdbms/admin/utlrp ============================================================================ select comp_name,version,status from dba_registry order by 3,1,2; COMP_NAME VERSION STATUS ---------------------------------------- ---------- --------------- OLAP Analytic Workspace 10.2.0.5.0 OPTION OFF Oracle Data Mining 10.2.0.5.0 OPTION OFF Oracle OLAP API 10.2.0.5.0 OPTION OFF JServer JAVA Virtual Machine 11.2.0.3.0 VALID Oracle Database Catalog Views 11.2.0.3.0 VALID Oracle Database Java Packages 11.2.0.3.0 VALID Oracle Database Packages and Types 11.2.0.3.0 VALID Oracle Enterprise Manager 11.2.0.3.0 VALID Oracle Expression Filter 11.2.0.3.0 VALID Oracle Real Application Clusters 11.2.0.3.0 VALID Oracle Rules Manager 11.2.0.3.0 VALID Oracle Text 11.2.0.3.0 VALID Oracle Workspace Manager 11.2.0.3.0 VALID Oracle XDK 11.2.0.3.0 VALID Oracle XML Database 11.2.0.3.0 VALID 15 rows selected.
Remove OLAP From the Standard Edition database
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment