Wednesday, May 20, 2015

Removing Oracle Components From Standared Edition Database

Oracle database components that are not supported on standard edition will get installed if the database was created using a template (e.g OLTP template). This can results in warnings during upgrades and at times issues with other oracle utilities such as export/import data pumps.
It may be helpful and could speed up the upgrade if these unsupported and unwanted (by the application DB is used for) components are removed before the upgrade. This post list steps for removing some of these components. It's best to take a full database backup before running these components removal scripts.
The database used is a SE database created with the OLTP template. Currently all the components have the status valid.
--> Oracle Catalog Views         [upgrade]  VALID
--> Oracle Packages and Types    [upgrade]  VALID
--> JServer JAVA Virtual Machine [upgrade]  VALID
--> Oracle XDK for Java          [upgrade]  VALID
--> Oracle Workspace Manager     [upgrade]  VALID
--> OLAP Analytic Workspace      [upgrade]  VALID
--> OLAP Catalog                 [upgrade]  VALID
--> EM Repository                [upgrade]  VALID
--> Oracle Text                  [upgrade]  VALID
--> Oracle XML Database          [upgrade]  VALID
--> Oracle Java Packages         [upgrade]  VALID
--> Oracle interMedia            [upgrade]  VALID
--> Spatial                      [upgrade]  VALID
--> Expression Filter            [upgrade]  VALID
--> Rule Manager                 [upgrade]  VALID
--> Oracle OLAP API              [upgrade]  VALID
But if ultrp was run some of the unsupported components will become invalid.
1. Removing Application Express
2. Removing Multimeida (interMedia)
3. Removing Spatial
sqlplus "/as sysdba"
set pagesize 0
set feed off
spool dropsyn.sql
select 'drop public synonym "' || synonym_name || '";' from dba_synonyms where table_owner='MDSYS';
spool off;
set feedback on

drop user mddata cascade;
drop user spatial_csw_admin_usr cascade;
drop user spatial_wfs_admin_usr cascade;
drop user MDSYS cascade;
4.Removing Expression Filter

5. Removing OLAP components (also refer 1362752.1, 565773.1)
drop user olapsys cascade;
spool olapdrop.sql
select 'drop public synonym ' || synonym_name || ';' from dba_synonyms where owner='PUBLIC' and table_owner='OLAPSYS';
spool off
select * from sys.exppkgact$ where package = 'DBMS_AW_EXP' and  schema= 'SYS';
delete from sys.exppkgact$ where package = 'DBMS_AW_EXP' and schema= 'SYS';

create table exppkgact$_bck as select * from exppkgact$;
delete from exppkgact$ where package = 'DBMS_CUBE_EXP' and schema = 'SYS';

 drop package CWM2_OLAP_INSTALLER;
At this stage Oracle database packages and types may be invalid. To fix this start the DB in restrict mode and run
6. Removing XDK for Java
 drop public synonym xslprocessor;
7. Removing Java packages
8. Removing JServer
9. Removing Oracle Warehouse Builder (OWB)
These are the only components removed as the application still uses wm_concat workspace manager could not be removed. But if required following script could be used to remove workspace manager
Removing Oracle text may have consequences for expdp/impdp (not tested) so it was left. But if required this too could be removed with
 drop procedure sys.validate_context;
At this stage the OLAP components (only two will left in the dba_registry listing) may have status invalid. Other components will have status valid or removed. This doesn't affect the upgrade from to Once upgraded the OLAP components will have the status option_off.
 COMP_NAME                                STATUS
---------------------------------------- -------------
Oracle Enterprise Manager                VALID
Oracle XML Database                      VALID
Oracle Text                              VALID
Oracle Workspace Manager                 VALID
Oracle Database Catalog Views            VALID
Oracle Database Packages and Types       VALID
JServer JAVA Virtual Machine             REMOVED
Oracle XDK                               REMOVED
Oracle Database Java Packages            REMOVED
OLAP Analytic Workspace                  OPTION OFF
Oracle OLAP API                          OPTION OFF
Useful metalink note
Information On Installed Database Components and Schemas [ID 472937.1]