XDB Clean Up in Oracle Database 11.2-12.2

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/