Validate Oracle XDB

XDB.PATH_VIEW is INVALID

In few cases Oracle XDB becomes Invalid and if it needs to be validated again without reinstalling the below work around might work.

Error:

XDB.PATH_VIEW and XDB.XDB_PV_TRIG are in INVALID state 

Fix:

To validate XDB, upgrade table XDB.XDB$H_LINK, then recompile the invalid objects.

Action Plan:

connect / as sysdba

alter table XDB.XDB$H_LINK upgrade;

alter view XDB.PATH_VIEW compile;

alter trigger XDB.XDB_PV_TRIG compile;

Check the XDB Component:

select comp_name,version,Version_Full,status from dba_registry where comp_id = 'XDB';

The XDB should be validated now. If this does not work then you need to reinstall the XDB and befor doing that please consult Oracle, as it might corrupt some dependent XML attributes in other tables. 

You can follow XDB Reinstall to reinstall the XDB.

OR,

Alternative Method for Re Installing:

1. Export any custom tables that has XML type, using Data Pump:

2. Deinstall and reinstall XDB

spool xdb_reinstall.log

set echo on;

connect / as sysdba

shutdown immediate

startup

@?/rdbms/admin/prvtnoqm.plb

@?/rdbms/admin/catproc.sql

@?/rdbms/admin/utlrp.sql

set pagesize 1000

set linesize 150

col comp_name format a36

col version format a12

col status format a8

col owner format a12

col object_name format a35

col object_type format a20

col name format a35

select comp_name, version, status from dba_registry order by status, comp_name;

select owner, object_name, object_type, status 

from dba_objects

where status = 'INVALID'

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

order by owner, object_name;

select owner, name, type, text

from dba_errors

where owner in ('SYS', 'XDB')

order by owner, name;

spool off;

3. Drop the tables in step 1 and import them from the dump file.

Best Wishes!!

 

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)