Remove and Clean Up Components from Oracle Database 11.2 – 19c

I will start a series of blog posts on how to remove and clean up components from Oracle Database 11.2 – 19c.

Precaution

This series of blog posts is not meant to recommend the removal of any options from an Oracle Database. It is not meant to create any sort of negativeness on any of the components or options. It’s is only meant to give you some advice and guideline in the case you’ll need to remove something.

The OUI (Oracle Universal Installer) installed everything on disk – and there’s a very good reason to do this. The purpose of this blog series is only to avoid frustration in the case you will have to remove something. I learned over time that several cases are not handled very well via the official documentation or via MOS notes. But in my daily work with customers there’s sometimes need to clean up – or to reinstall. (Re-)installation should be done with the DBCA.

In addition, this blog series is not an official Oracle documentation. It is just based on my own testing and experience. I cover only Oracle Database 11.2.0.4, Oracle Database 12.1.0.2 and Oracle Database 12.2.0.1 (non-CDB and CDB) in this blog series. And I added Oracle Database 19c later on in some of the posts.

Reasons to Clean Up

There may be many reasons to clean up the option set in an Oracle database. A precreated seed databases from DBCA has all options configured by default. Therefore I did recommend to use your own templates instead and create always a custom database tailored for your environment and needs:

• Always create databases as CUSTOM databases
• Use your own templates in DBCA to create databases

Amongst the most common reasons to remove an option or a component from an Oracle Database are license topics, faster upgrades and less potential for issues. I don’t justify any of these. But it’s a fact that an Oracle upgrade will run faster in dependency of the number of installed components.

Database upgrade duration depends mainly on the number of installed components

In Oracle Database 12c the parallel upgrade has been introduced but just a few of the components can be upgraded in parallel. You can easily recognize this when looking at the parallel upgrade’s log as you will spot mostly serial phases for most components.

And of course with Oracle Multitenant the impact is even higher.

Very important – please read!

Before you approach now a major surgery on your database please read the following topics carefully.

• If you have no true reason to remove anything then ignore the entire series of blog posts
• Remove the Oracle demo users (SCOTT, SH, OE etc). Do this with drop user cascade statements (see MOS Note:457800.1)
• Take a valid backup before – and ensure that you can restore and recover it
• Test on a copy of your database first
• Component dependencies exist – see: MOS Note: 2001512.1 for an example in Oracle Database 12.1.0.2
• Workarounds are just recommendations – better workarounds may be available
• Save your data – component removal may lead to loss of information if the component is in use
• XDB is a mandatory component since Oracle Database 12c
• Always spool into a file – even though I won’t explicitly add spool commands in my examples
• Procedures work on Linux – on Windows some calls may vary
• Finally check with Oracle Support if you have doubts and questions

Special requirements for CDBs

If you plan to remove components from a Multitenant container database (CDB) then please consider the following recommendations:

• All pluggable database must be opened:

  SQL> alter pluggable database all open;

• catcon.pl must be used to execute removal scripts in many cases to clean up especially PDB$SEED.
• You will find not much documented on component removal yet – therefore my blog posts are proposals only. Please double-check with Oracle Support.
• Recompile in a Multitenant environment:

  $ cd $ORACLE_HOME/rdbms/admin
  $ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

• You must set _exclude_seed_cdb_view to FALSE to see also objects belonging to the PDB$SEED (read more here):

  SQL> alter system set "_exclude_seed_cdb_view"=false scope=both;

What is a component?

A use the terms “component” and “option“. Components are visible in DBA_REGISTRY. Database options are listed here.

This for instance is a list of standard installed components in Oracle Database 12.2.0.1:

List of components in DBA_REGISTRY in Oracle Database 12.2.0.1

Monitoring Scripts

Throughout the entire blog post series I use the following scripts to monitor component removal and invalid objects:

• check_components.sql
• check_inv_objs.sql

You can download these and many more script from the SQL Scripts Repository on this blog.

Dependencies

At least for Oracle Database 19c, this picture below shows the dependencies of components. For instance, if you are going to remove ORDIM you will need to remove SDO as well.

Removal Order

If you are going to remove components, you may follow this order. Of course, the graph does not mean that you must start with OWM. You can enter at any stage but then don’t remove JAVAVM before removing its dependent components.

Component Clean Up Series

• Remove and Clean Up Components from Oracle 11.2 – 12.2 – General Guidelines and Precautions
• APEX – Oracle Application Express Clean Up
• OWM – Oracle Workspace Manager Clean Up
• DV – Oracle Database Vault Clean Up
• OLS – Oracle Label Security Clean Up
• SDO – Oracle Spatial Data Option Clean Up
• CONTEXT – Oracle Text Clean Up
• ORDIM – Oracle Multi Media Clean Up
• XOQ – Oracle OLAP API Clean Up
• APS – Oracle OLAP Analytical Workspace Clean Up
• AMD – Oracle OLAP Catalog Clean Up
• OWB – Oracle Warehouse Builder Clean Up
• EXF/RUL – Oracle Expression Filters and Rules Manager Clean Up
• EM – Enterprise Manager Database Control Clean Up
• JAVAVM/XML – Oracle Java Virtual Machine and XDK Clean Up
• XDB – Oracle XML Database Clean Up

Finally download a slide deck about the Component Clean Up on this blog:
https://mikedietrichde.com/slides/

–Mike