I need to remove OLAP components in a 11.2.0.3 version of the database as its not being used.
SELECT comp_id,COMP_NAME,schema,VERSION,STATUS,MODIFIED FROM DBA_REGISTRY where comp_name like '%OLAP%' order by 1;
COMP_ID COMP_NAME SCHEMA VERSION STATUS MODIFIED
--------------- ----------------------------------- --------------- --------------- --------------- -------------------------
AMD OLAP Catalog OLAPSYS 11.2.0.3.0 VALID 09-FEB-2015 10:23:04
APS OLAP Analytic Workspace SYS 11.2.0.3.0 VALID 09-FEB-2015 10:23:03
XOQ Oracle OLAP API SYS 11.2.0.3.0 VALID 09-FEB-2015 10:23:03
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ Remove OLAP @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
=====================================================================================================================
Step 1 : Remove OLAP Catalog
=====================================================================================================================
#### First lets remove OLAP Catalog
spool remove_olap.log
@/u01/app/oracle/product/11.2.0.3/olap/admin/catnoamd.sql
Once the script completes the execution, it has removed OLAP Catalog component from Database.
COMP_ID COMP_NAME SCHEMA VERSION STATUS MODIFIED
--------------- ----------------------------------- --------------- --------------- --------------- -------------------------
AMD OLAP Catalog OLAPSYS 11.2.0.3.0 VALID 09-FEB-2015 10:23:04
"catnoamd.sql" drops OLAPSYS user and its objects..
=====================================================================================================================
Step 2 : Remove OLAP API
=====================================================================================================================
@/u01/app/oracle/product/11.2.0.3/olap/admin/olapidrp.plb
@/u01/app/oracle/product/11.2.0.3/olap/admin/catnoxoq.sql
After we execute both the scripts, it drops the API component and update registry as removed like below,
COMP_ID COMP_NAME SCHEMA VERSION STATUS MODIFIED
--------------- ----------------------------------- --------------- --------------- --------------- -------------------------
XOQ Oracle OLAP API SYS 11.2.0.3.0 REMOVED 11-FEB-2015 17:34:59
=====================================================================================================================
Step 3 : Remove OLAP APS
=====================================================================================================================
@/u01/app/oracle/product/11.2.0.3/olap/admin/catnoaps.sql
COMP_ID COMP_NAME SCHEMA VERSION STATUS MODIFIED
--------------- ----------------------------------- --------------- --------------- --------------- -------------------------
XOQ Oracle OLAP API SYS 11.2.0.3.0 REMOVED 11-FEB-2015 17:34:59
"catnoaps.sql" updates registry and removes above OLAP API component and marks APS component as "REMOVED" in database registry.
COMP_ID COMP_NAME SCHEMA VERSION STATUS MODIFIED
--------------- ----------------------------------- --------------- --------------- --------------- -------------------------
APS OLAP Analytic Workspace SYS 11.2.0.3.0 REMOVED 11-FEB-2015 17:36:04
#### Drop the component
@/u01/app/oracle/product/11.2.0.3/olap/admin/cwm2drop.sql
Removed Below entry from database registry.
COMP_ID COMP_NAME SCHEMA VERSION STATUS MODIFIED
--------------- ----------------------------------- --------------- --------------- --------------- -------------------------
APS OLAP Analytic Workspace SYS 11.2.0.3.0 REMOVED 11-FEB-2015 17:36:04
=====================================================================================================================
Step 4 : Recompile INVALID Objects
=====================================================================================================================
As we removed the OLAP components some of the database objects goes to INVALID state. We have to recompile the database objects.
@/u01/app/oracle/product/11.2.0.3/rdbms/admin/utlrp.sql
spool off
select owner, object_name, object_type, status from dba_objects where status='INVALID';
=====================================================================================================================
Review the log file for detailed information.
=====================================================================================================================
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ END @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
No comments:
Post a Comment