Oracle database packages and types invalid

While upgrading Oracle database 11g to 12C via DBUA we came to know that the Packages and Types component is in invalid state in Dba_registry.

Below are the errors seen from DBUA as well as command line while upgrading.

Method to detect:

SQL> select substr(comp_name,1,30) comp_name, substr(comp_id,1,10)
comp_id,substr(version,1,12) version,status from dba_registry;

*** Installed Components ***

COMP_NAME VERSION STATUS
---------------------------------------- --------------- ----------
Oracle Database Vault 12.1.0.2.0 VALID
Oracle Application Express 4.2.5.00.08 VALID
Oracle Label Security 12.1.0.2.0 VALID
Spatial 12.1.0.2.0 VALID
Oracle Multimedia 12.1.0.2.0 VALID
Oracle Text 12.1.0.2.0 VALID
Oracle Workspace Manager 12.1.0.2.0 VALID
Oracle XML Database 12.1.0.2.0 VALID
Oracle Database Catalog Views 12.1.0.2.0 VALID
Oracle Database Packages and Types 12.1.0.2.0 INVALID
JServer JAVA Virtual Machine 12.1.0.2.0 VALID
Oracle XDK 12.1.0.2.0 VALID
Oracle Database Java Packages 12.1.0.2.0 VALID
OLAP Analytic Workspace 12.1.0.2.0 VALID
Oracle OLAP API 12.1.0.2.0 VALID
Oracle Real Application Clusters 12.1.0.2.0 OPTION OFF

16 rows selected.

Causes:

The probable causes for dba_registry components became invalid could be any of the following.

1. Applied a patch and after the patch application because of some dependent object status change registry can become invalid
2. Installed a new component and the new component installation got failed then registry components could become invalid
3. catalog.sql or catproc.sql was not successfully ran after database creation. Any of them would have failed somewhere or any of the dependent object got invalid afterward

You can try the below steps to validate if you don’t have any invalid object in the database

SQL> select count(*) from dba_objects where status=’INVALID’;
COUNT(*)
———-
0
SQL> exec DBMS_REGISTRY_SYS.VALIDATE_CATPROC;
If you have any invalid objects in the database then follow the below steps
SQL> SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)
----------
449

1 row selected.

Resolution steps:

‘Packages and Types’ can usually be validated by running catalog and catproc scripts:

$sqlplus "/as sysdba"
SQL> startup restrict
SQL > @?/rdbms/admin/catalog.sql
SQL > @?/rdbms/admin/catproc.sql
SQL > @?/rdbms/admin/utlrp.sql
SQL> select substr(comp_name,1,30) comp_name, substr(comp_id,1,10)
comp_id,substr(version,1,12) version,status from dba_registry;
2
COMP_NAME COMP_ID VERSION STATUS
---------------------------------------- ---------- ------------ -----------
Oracle XML Database XDB 12.1.0.2.0 VALID
Oracle Expression Filter EXF 12.1.0.2.0 VALID
Oracle Rule Manager RUL 12.1.0.2.0 VALID
Oracle Workspace Manager OWM 12.1.0.2.0 VALID
Oracle Database Catalog Views CATALOG 12.1.0.2.0 VALID
Oracle Database Packages and T CATPROC 12.1.0.2.0 VALID
JServer JAVA Virtual Machine JAVAVM 12.1.0.2.0 VALID
Oracle XDK XML 12.1.0.2.0 VALID
Oracle Database Java Packages CATJAVA 12.1.0.2.0 VALID

9 rows selected.

Shutdown your database in the normal mode and startup with the normal mode.

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.