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