Thursday, 24 April 2014

Making invalid Oracle XML Database component to Valid status

How to  make invalid Oracle XML Database component to Valid state/Reinstall Oracle XML Database component ?

Execute the query in sql prompt and check whether "Oracle XML Database" component is valid or not.
SQL> select substr(comp_name,1,40) comp_name, schema, status, substr(version,1,10) version from dba_registry order by comp_name;
COMP_NAME                                SCHEMA     STATUS VERSION
---------------------------------------- ---------- ------ -----------
JServer JAVA Virtual Machine             SYS        VALID  11.2.0.2.0
OLAP Analytic Workspace                  SYS        VALID  11.2.0.2.0
OLAP Catalog                                   OLAPSYS    VALID  11.2.0.2.0
Oracle Application Express               APEX_04000 VALID  4.0.2.00.0
Oracle Database Catalog Views            SYS        VALID  11.2.0.2.0
Oracle Database Java Packages            SYS        VALID  11.2.0.2.0
Oracle Database Packages and Types       SYS        VALID  11.2.0.2.0
Oracle Enterprise Manager                SYSMAN     VALID  11.2.0.2.0
Oracle Expression Filter                 EXFSYS     VALID  11.2.0.2.0
Oracle OLAP API                          SYS        VALID  11.2.0.2.0
Oracle Rules Manager                     EXFSYS     VALID  11.2.0.2.0
Oracle Text                              CTXSYS     VALID  11.2.0.2.0
Oracle Workspace Manager                 WMSYS      VALID  11.2.0.2.0
Oracle XDK                               SYS        VALID  11.2.0.2.0
Oracle XML Database                      XDB        INVALID  11.2.0.2.0

This result(Oracle XML Database --- invalid state) may be due to library path set up in Up gradation process or else it is already invalid before using this UTL_SMPT enabling.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Then deinstall XMLDB component using the following scripts and install again.
Deinstalling:
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup
SQL> @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/catnoqm.sql ---- this will deinstall XDM component
SQL> exit
Reinstalling:
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup;
SQL> @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/catqm.sql ---- this is to install XDM component
SQL> @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlrp.sql ---- this is to recompile the invalid objects
SQL> exit
  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Now check the status of  component once again as below:
SQL> select substr(comp_name,1,40) comp_name, schema, status, substr(version,1,10) version from dba_registry order by comp_name;
COMP_NAME                                SCHEMA     STATUS VERSION
---------------------------------------- ---------- ------ -----------
JServer JAVA Virtual Machine             SYS        VALID  11.2.0.2.0
OLAP Analytic Workspace                  SYS        VALID  11.2.0.2.0
OLAP Catalog                                  OLAPSYS    VALID  11.2.0.2.0
Oracle Application Express               APEX_04000 VALID  4.0.2.00.0
Oracle Database Catalog Views            SYS        VALID  11.2.0.2.0
Oracle Database Java Packages            SYS        VALID  11.2.0.2.0
Oracle Database Packages and Types       SYS        VALID  11.2.0.2.0
Oracle Enterprise Manager                SYSMAN     VALID  11.2.0.2.0
Oracle Expression Filter                 EXFSYS     VALID  11.2.0.2.0
Oracle OLAP API                          SYS        VALID  11.2.0.2.0
Oracle Rules Manager                     EXFSYS     VALID  11.2.0.2.0
Oracle Text                              CTXSYS     VALID  11.2.0.2.0
Oracle Workspace Manager                 WMSYS      VALID  11.2.0.2.0
Oracle XDK                               SYS        VALID  11.2.0.2.0
Oracle XML Database                      XDB        VALID  11.2.0.2.0

As a final check find number of invalid components of XDB user
SQL> select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type;
no rows selected

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

cheers....!!!!!

No comments:

ORA-600 [kwqitnmphe:ltbagi], [1], [0] reported in the alert log file.

ORA-00600 [kwqitnmphe:ltbagi] Cause: This issue arises in 12.1.0.2. The error occurs because there are still Historical Messages without...