I will start a series of blog posts on how to remove and clean up components from Oracle Database 11.2 – 19c.
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. 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:
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.
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.
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:
“JAVAVM/XML – Oracle Java Virtual Machine and XDK Clean Up” removal will be interesting.. hope we survive this nuke.
Of course you will – trust me 😉
Do you know offhand if the procedures outlined for 12.2 non-CDB are still applicable for 18.3 non-CDB?
Yes, there are supposed to be the same.
very helpful Information!
What can be done to delete “Oracle Real Application Clusters” option in 18.6?
there’s no need to delete it as it does not add any harm or license implications. It goes automatically ON when you set cluster_database=true – and is OFF in all other cases. It is just the representation of a bunch of views.
Can we remove component like Apex, when the database is in the upgrade mode and before starting the upgrade script?
If the answer is no, then can we exclude component – like Apex – from the upgrade and then remove it or upgrade it separately later?
APEX upgrade is not part of the database upgrade anymore since 12.2.
I never tried removing it in UPGRADE mode but I see no reason why it shouldn’t work.
Thanks Mike for your update. Let me share with you some highlights from an actual migration/upgrade that I carried out last week.
It was a migration from a T5-4 Solaris Server to another T8-4 Solaris server + upgrade from 22.214.171.124 to 126.96.36.199. We have Oracle Restart also implemented on the T8-4 server to make use of the ASM.
The main constraint in this project was that I was not allowed to touch the current production. I only have access to the database backups and archive logs, no action is allowed on the source, which is a critical core banking database. Even I was not allowed to run the preupgrade_fixups.sql on the source.
So, I restored the database from the backup on the new server, synch it using the archive logs, then I started it in the upgrade mode.
Trial #1: Upgrade was smooth (Phase 0 to 103 took around 14 minutes) until phase #104 (Apex Upgrade). Upgrade stuck here for very long time without failure or error messages.
The last message in the upgrade log file: There is a prompt to enter the Apex version!:
“Enter value for version”
On the upgrade session, it is waiting at the following phase:
Serial Phase #:104 [ETHIX] Files:1″
So, I canceled the upgrade and I had a long discussion with the client, eventually, I convinced him to remove Apex from the source database (as Apex is not used at all), and I repeated the migration and upgrade again.
I didn’t test If I can remove Apex while the database is in the upgrade mode, but I tested other actions (like: removing EM repository, compiling invalid objects, refreshing materialized views, gather_dictionary_stats), all of them are failed in upgrade mode (except purging the recyclebin succeeded)
Trial #2 (Apex removed)
Upgraded proceeded well until phase #109, and then the following error occurred:
Serial Phase #:109 [ETHIX] Files:1 wait_for_completion: unexpected error in next_proc()
catconExec: unexpected error in wait_for_completions
Unexpected error encountered in catconExec; exiting
No such file or directory
Unexpected error encountered in catctlMain; Error Stack Below; exiting
Died at catctl.pl line 7870.
I see this error in one of your presentations, and you advised to apply Patch 26024601, but this didn’t fix my issue. It ended up that there is a wrong reference for the temp file location in the control file. I renamed the temp files and restarted the upgrade from phase #109 and it succeeded this time.
Trial #4: upgrade completed all phases from 0-115 successfully, but there is a warning after that:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
ORA-06512: at line 48
Oracle support, recommended ignoring this error and just recompile invalid objects.
I recompiled the invalid objects (while the database is in the upgrade mode) in 30 minutes and everything was fine, but I decided to repeat the upgrade again and ensure there is no warnings.
So, I flashed back the database to before upgrade, purged the recycle bin while the database is in the upgrade mode, rerun the upgrade and completed successfully in about 30 minutes.
The after-upgrade tasks completed successfully, except that the utlrp.sql completed in 3-hours instead of 30 minutes. I hit the same case that you mentioned in one of your posts. My system has 80 virtual CPUs, so utlrp.sql runs in parallel with 159 slaves, it runs very slow. I’m planning to run it in serial at the go-live (using utlprp.sql 1).
Sorry for the long update, hopefully, someone will take advantage of these issues and fixes. I’m planning to write a separate post for that upgrade with more details.
thanks for sharing your experience.
Let me make a few comments.
Actually we removed APEX from the upgrade as (a) it’s an apps layer and (b) takes too long. See here: https://mikedietrichde.com/2014/06/06/save-upgrade-downtime-upgrade-apex-upfront/
I wonder why you have seen this with an upgrade to 188.8.131.52. I thought the last version we upgrade APEX as part of the database upgrade was 184.108.40.206.
Purging the recycle bin is part of preupgrade_fixups.sql. We know that not purging the recycle bin can cause problems.
I haven’t seen your problem with the temp file reference in the control file. Thanks for sharing your workaround.
Again, thanks for sharing your experience!
One more question about the RAC option. I have A PDB in an SE instance. This instance shows the RAC option in dba_registry with status OPTION_OFF. I Have an EE CDB without the RAC option in dba_registry and I want to move the PDB from the SE instance to the EE instance.
When I move the PDB, run datapatch etc pdb_plug_in_violations doesn’t complain about the RAC option not availiable on CDB:
TIME NAME CAUSE ERROR_NUMBER MESSAGE STATUS ACTION
—————- —————- —————- ———— —————- ——— —————-
01-08-20 07:19:5 PDB122EE DB Release Editi 0 CDB is Enterpris RESOLVED Please plugin th
6,582679 on e Edition (8), b e PDB in a CDB w
ut PDB is not En ith compatible d
terprise Edition atabase release
01-08-20 07:19:5 PDB122EE Oracle Opatch 0 Oracle opatch mi RESOLVED Install the Orac
6,582679 smatch: opatch 2 le opatch in the
8163133 is missi CDB.
ng in the CDB.
Can I remove the RAC option? On the EE instance the RAC option is not installed because it was installed by script and the RAC option was removed from the scripts.
“RAC Option” has no deeper meaning and you can simply ignore it. It just refers to views in the database.
And as far as I know, there is no official way to remove it but also no need for it.
Great read thanks.
A question for you.
I have a customer who has mistakenly used ACO via Datapump expdp and it is being flagged as used in the following query:
select name “feature”, detected_usages “Used”, first_usage_date “FROM”, last_usage_date “TO”
The customer wants me to do a full database rebuild via expdp/impdp so that ACO does not report as being used in the above query. My understanding is that ACO cannot be disabled/removed in 12.2 and that a full expdp/impdp will not resolve the ACO usage being flagged in the above query – is this your understanding/experience ?
yes, your understanding is correct – you can’t prevent the accidental use of ACO, especially during a Data Pump action but also when somebody creates a compressed table or index.
And please don’t shoot the messenger – cheers,
I have a lot of old components in my 220.127.116.11 database like WORKFLOW, SYNDICATION, PORTAL, DISCOVERER, etc. All of them are version 10.1.4 or lower. How do I remove them from the database prior to upgrading to 19c? There does not seem to be anything in the documentation about removing these components, I guess because they are so old.
COMP_ID VERSION STATUS
—————————— —————————— ———–
SYNDICATION 10.1.2.0.2 VALID
PORTAL 10.1.2.0.2 VALID
WORKFLOW 10.1.2.0.2 INVALID
MRC 10.1.2.0.2 VALID
B2B 10.1.2.0.2 VALID
BAM 10.1.2.0.2 VALID
OCA 10.1.2.0.2 VALID
OID 10.1.4.0.1 VALID
DCM 10.1.2.0.2 VALID
DISCOVERER 10.1.2.0.2 VALID
SSO 10.1.4.0.1 VALID
UDDI 10.1.2.0.2 VALID
WCS 10.1.2.0.2 VALID
WIRELESS 10.1.2.0.2 VALID
now I’m really scared. I have never seen any of these components in DBA_REGISTRY.
Please do me a favor and open an SR. Upload “opatch lsinventory” and especially spool outputs of DBA_REGISTRY and REGISTRY$.
Then share the SR number with me via the blog please.
I created the service request, but they want me to run tfactl on my old machine. Every time I try to run it it fails and, when I tell them that, they ask me to run it again. The SR# is 3-25836937501. I ran autoupgrade against it anyway and now I have a 19c instance with a bunch of very old components in it. Any ideas?
then please insist that a TFA does not make ANY sense. If if the engineer still insists, then escalate the SR.
We have seen this for no reason with some upgrade SRs, too, where the analyze phase failed.
Please keep pushing.
Thank you very much sir. The SR# is 3-25836937501.
I have a customer who has mistakenly used ACO via Datapump expdp and it shows compression used in the features_info column of dba_feature_usage_statistics.
Say the database has application schemas A, B, C. If we export those schemas, drop the database and recreate from scratch then import those schemas back will this resolve the issue?
Would the tables in the application schemas have some sort of tracking to show they were exported using Datapump with COMPRESSION=ALL used?
the best is to get in touch with the Oracle sales rep and ask for advice.
The mechanism to track this is in the kernel, and not associated with DB.
And unfortunetely there is no way (at least not that I know) to prevent somebody from using compression.
this link no longer works, can you update the link? thanks
Component dependencies exist – see: MOS Note: 2001512.1 for an example in Oracle Database 18.104.22.168
I checked it, and it does.
What could happen sometimes, and MOS is a terrible platform in this regards: