I will start a series of blog posts on how to remove and clean up components from Oracle Database 11.2 – 12.2.
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 126.96.36.199, Oracle Database 188.8.131.52 and Oracle Database 184.108.40.206 (non-CDB and CDB) in this blog series.
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:
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.
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 cascadestatements (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 220.127.116.11
- 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
spoolcommands 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.plmust be used to execute removal scripts in many cases to clean up especially
- 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
FALSEto 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 18.104.22.168:
Throughout the entire blog post series I use the following scripts to monitor component removal and invalid objects:
You can download these and many more script from the SQL Scripts Repository on this blog.
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: