XDB Clean Up in Oracle Database 11.2-12.2 will apply to Oracle Database 11.2.0.x only as XDB became a mandatory component since Oracle Database 12.1.0.1. You can’t have an Oracle 12c database without XDB. Therefore the following blog post won’t explain how to clean up XDB from an Oracle 12c database. It explains only briefly the case before Oracle Database 12c.
XDB Clean Up in Oracle Database 11.2-12.2
Oracle XML DB provides full support for all of the key XML standards, including XML, Namespaces, DOM, XQuery, SQL/XML and XSLT. By providing full support for XML standards, Oracle XML DB supports native XML application development. Application developers are able to use XML-centric techniques to store, manage, organize, and manipulate XML content stored in the database. You can find more information here: http://www.oracle.com/technetwork/database/database-technologies/xmldb/overview/index.html.
Before you start removing anything from your database please make sure you read my introductory blog post about how to Remove and Clean Up Components from Oracle 11.2 – 12.2 . You’ll find links to monitor proper component removal there as well as in the SQL Scripts Repository on this blog.
You can find some general guidance in MOS Note:1292089.1 – Master Note for Oracle XML Database (XDB) Install / Deinstall.
Oracle Database 11.2.0.4
In Oracle Database 11.2.0.4 XDB can be removed in theory. But in my experience the removal process is neither straight forward nor does it work as documented. Therefore I disrecommend removing XDB from a database. It is often easier and cleaner to recreate a database without XDB.
First of all, the removal requires the database to be in upgrade
mode. This means downtime.
$ sqlplus / as sysdba SQL> shutdown immediate SQL> startup upgrade
Then you must run the XDB removal script:
SQL> @?/rdbms/admin/catnoqm.sql
The result of this script are 35 invalid objects:
SQL> select owner, object_type, object_name, status from DBA_OBJECTS where status='INVALID' order by 1,2; OWNER OBJECT_TYPE OBJECT_NAME STATUS ------------ ------------ ------------------------------ -------- SYS PACKAGE BODY DBMS_METADATA_INT INVALID SYS PACKAGE BODY DBMS_METADATA INVALID SYS PACKAGE BODY DBMS_METADATA_UTIL INVALID SYS VIEW KU$_TABLE_XMLSCHEMA_VIEW INVALID SYS VIEW KU$_COLUMN_VIEW INVALID SYS VIEW KU$_PCOLUMN_VIEW INVALID SYS VIEW KU$_NT_PARENT_VIEW INVALID SYS VIEW KU$_FHTABLE_VIEW INVALID SYS VIEW KU$_10_2_FHTABLE_VIEW INVALID SYS VIEW KU$_10_1_FHTABLE_VIEW INVALID SYS VIEW KU$_PFHTABLE_VIEW INVALID SYS VIEW KU$_10_1_PFHTABLE_VIEW INVALID SYS VIEW KU$_ACPTABLE_VIEW INVALID SYS VIEW KU$_IOTABLE_VIEW INVALID SYS VIEW KU$_10_1_IOTABLE_VIEW INVALID SYS VIEW KU$_PIOTABLE_VIEW INVALID SYS VIEW KU$_10_1_PIOTABLE_VIEW INVALID SYS VIEW KU$_XMLSCHEMA_TYPES_VIEW INVALID SYS VIEW KU$_TTS_TYPES_VIEW INVALID SYS VIEW KU$_VIEW_VIEW INVALID SYS VIEW KU$_ZM_VIEW_FH_VIEW INVALID SYS VIEW KU$_M_VIEW_FH_VIEW INVALID SYS VIEW KU$_ZM_VIEW_PFH_VIEW INVALID SYS VIEW KU$_M_VIEW_PFH_VIEW INVALID SYS VIEW KU$_ZM_VIEW_IOT_VIEW INVALID SYS VIEW KU$_M_VIEW_IOT_VIEW INVALID SYS VIEW KU$_ZM_VIEW_PIOT_VIEW INVALID SYS VIEW KU$_M_VIEW_PIOT_VIEW INVALID SYS VIEW KU$_M_VIEW_LOG_FH_VIEW INVALID SYS VIEW KU$_M_VIEW_LOG_PFH_VIEW INVALID SYS VIEW KU$_CLUSTER_VIEW INVALID SYS VIEW KU$_OPQTYPE_VIEW INVALID SYS VIEW KU$_XMLSCHEMA_ELMT_VIEW INVALID SYS VIEW KU$_XMLSCHEMA_VIEW INVALID SYS VIEW KU$_EXP_XMLSCHEMA_VIEW INVALID 35 rows selected.
Neither a recompilation nor a restart of the database does solve this situation. But MOS Note:1269470.1 – XDB Deinstallation script catnoqm.sql leads to Invalid SYS Objects provides a solution. To implement the solution, XDB dummy views and Data Pump related objects needs to be updated manually. Open:
$ sqlplus / as sysdba
and execute the following SQL scripts:
-- Make XDB Dummy views start ?/rdbms/admin/catxdbdv.sql -- update Data Pump related objects and KU$_ views start ?/rdbms/admin/dbmsmeta.sql start ?/rdbms/admin/dbmsmeti.sql start ?/rdbms/admin/dbmsmetu.sql start ?/rdbms/admin/dbmsmetb.sql start ?/rdbms/admin/dbmsmetd.sql start ?/rdbms/admin/dbmsmet2.sql start ?/rdbms/admin/catmeta.sql start ?/rdbms/admin/prvtmeta.plb start ?/rdbms/admin/prvtmeti.plb start ?/rdbms/admin/prvtmetu.plb start ?/rdbms/admin/prvtmetb.plb start ?/rdbms/admin/prvtmetd.plb start ?/rdbms/admin/prvtmet2.plb start ?/rdbms/admin/catmet2.sql
Afterwards XDB is removed and no invalid objects exist anymore.
Oracle Database 12.1.0.2
Oracle XDB is mandatory and thus can’t be removed.
Oracle Database 12.2.0.1
Oracle XDB is mandatory and this can’t be removed.
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/
Thanks a lot for the clue, Mike. I faced the issue with a lot of invalid objects today during upgrade of 11.2.0.4 to 19.11.0 and your advice above helped me to remove XDB without getting invalid objects.
Thanks Bair!
Cheers,
Mike