Reinstall XDB Component in Oracle

How to install/reinstall/deinstall XDB in oracle:

In this article I will cover one of the important issue faced after sustainment patching. It is sometime observed after patching the XDB Component gets Invalid and recompiling does not fix it.

Known Issues:

Few known errors like few XDB objects are Invalid under DBA_OBJECTS and in DBA_REGISTRY XDB is showing as Invalid. 

Query to check:

DBA Registry:

set pages 300

set lines 400

col COMP_ID for a20

col Comp_Name for a40

col Version for a10

col Version_Full for a20

col Status for a10

select COMP_ID,

  COMP_NAME,

  VERSION,

  VERSION_FULL,

  STATUS

from dba_registry;

DBA Objects:

col owner for a20

select owner,

  count(*)

from dba_objects

where status = 'INVALID'

group by owner;

As per Oracle, deinstall and reinstall it, but that will impact if there is any data that is using the XML. So, It is aways a good idea to reinstall it. 

Reinstallation:

(this is applicable for Databases version greater than 10g)

Command:

catqm.sql xdb_password xdb_ts_name temp_ts_name secure_file_for_repos

Where:

xdb_password is the password

xdb_ts_name is the tablespace to use for Oracle XML DB

temp_ts_name is the temporary tablespace

secure_file_for_repo is YES or NO (uppercase), YES meaning to use SecureFile LOB storage for Oracle XML DB Repository

For example:

catqm.sql <XDB user password> SYSAUX TEMP YES

Alternate Method:

In few cases I have seen issues with catqm.sql and not fixing few invalid objects, so if you are ok, you can always run catproc.sql. Please donot run this in prod without proper backup. 

spool reinstall_xdb.log

set echo on;

connect / as sysdba

shutdown immediate;

startup

@?/rdbms/admin/prvtnoqm.plb

drop trigger SYS.XDB_PI_TRIG;

alter profile default limit PASSWORD_VERIFY_FUNCTION null;

@?/rdbms/admin/catproc.sql

@?/rdbms/admin/utlrp.sql

spool off;

This will fix the XDB component and the status should be Valid now. 

If reinstalling doesnot fix it, you can completely deinstall and install the component but doing so might have some impact. so it is recommended to reach out to Oracle SR for confirmation. I will be outlining the steps:

Deinstall/Uninstall XDB:

--- The catnoqm.sql script deinstall/Uninstall the XDB Component

spool deinstall_xdb.log

set echo on;

connect / as sysdba

shutdown immediate;

startup

@?/rdbms/admin/catnoqm.sql

spool off;

shutdown immediate;

startup;

@?/rdbms/admin/utlrp.sql

Install XDB:

Pease follow the same procedure as earlier installation methods.

Verify Installation:

spool verify_xdb.log

set echo on;

connect / as sysdba

set pagesize 1000

col comp_name format a36

col version format a12

col status format a8

col owner format a12

col object_name format a35

col name format a25

-- Check status of XDB

select comp_name,

  version,

  Version_Full,

  status

from dba_registry

where comp_id = 'XDB';

-- Check for invalid objects in SYS and XDB schema

-- This is a common issue after installing XDB

select owner,

  object_name,

  object_type,

  status

from dba_objects

where status = 'INVALID'

  and owner in ('SYS', 'XDB');

spool off;

Hope this helps. 

Reference: 

How to Reinstall XDB on 12c and above? (Doc ID 2403285.1)

Primary Note for Oracle XML Database (XDB) Install / Deinstall (Doc ID 1292089.1)