Components XDB,OWM,SDO,XOQ are invalid in Oracle Registry

Validate the XDB,OWM,SDO,XOQ component in Oracle Registry

Problem: The following component in Oracle is showing invalid.

select comp_name, version,status from dba_registry where STATUS <> 'VALID';

COMP_NAME                VERSION     STATUS
------------------------ ----------  -------
Oracle XML Database      12.2.0.1.0  INVALID
Oracle Workspace Manager 12.2.0.1.0  INVALID
Oracle OLAP API          12.2.0.1.0  INVALID
Spatial                  12.2.0.1.0  INVALID

Cause:
The reason may occur during patching, up-gradation, or migration of the database.

Solution:
To make them valid again. you can try following things step by step and check whether it is valid or not.

  1. Execute the UTLRP.SQL script for validating all the invalid components and objects in Oracle.
@ORACLE_HOME\rdbms\admin\utlrp.sql;

2. Check the list of objects invalid in the Oracle schemas.

select owner,object_name,object_type,status from dba_objects where status = 'INVALID' group by owner,object_name,object_type, status;

3. Compile the objects manually if utlrp.sql is not working for them.

--For package specification:
alter package owner.object_name compile;
--For package body:
alter package owner.object_name compile body;
--For Procedure:
alter procedure owner.object_name compile;
--For Function:
alter function owner.object_name compile;

4. Execute the following command to make the registry components valid.

-- For validate XDB component:
exec DBMS_REGXDB.VALIDATEXDB;
-- For validate OWM component:
exec VALIDATE_OWM;
-- For validate SDO component:
exec VALIDATE_SDO;
-- For validate XOQ component:
exec XOQ_VALIDATE;

5. Verify the registry component. If no row return means all registry components are valid.

select comp_name,version,status from dba_registry where STATUS <> 'VALID';

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.