How to repair INVALID “Oracle Database Packages and Type” in dba_registry – ORA-01775 upgrade error
The problem can happen if the database has been upgraded from Oracle 11gR2 to Oracle 12c
COMP_NAME STATUS VERSION ------------------------------------- -------------------------------- ------------------------------ Oracle Database Catalog Views INVALID 12.1.0.2.0 Oracle Database Packages and Types INVALID 12.1.0.2.0
cause:
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 run after database creation. Any of them would have failed somewhere or any of the dependent objects got invalid afterward
To repair and validate the invalid ‘Packages and Types’ component we can execute the catalog and catproc.sql script
SQL> shutdown immediate; SQL> startup restrict SQL> @?/rdbms/admin/catalog.sql SQL> @?/rdbms/admin/catproc.sql SQL> @?/rdbms/admin/utlrp.sql SQL> SELECT comp_name, status, version FROM dba_registry where STATUS not like 'VALID'; COMP_NAME STATUS VERSION ------------------------------------- -------------------------------- ------------------------------ Oracle Database Catalog Views VALID 12.1.0.2.0 Oracle Database Packages and Types VALID 12.1.0.2.0 Shutdown your database in the normal mode and startup with the normal mode.
Cause
Catprog.sql failed with ORA-01775: looping chain of synonyms
Solution
Drop synonyms
drop public synonym existsnode;
drop public synonym extract;
This solution was not applicable for our case as these synonyms were not present in the database.
1. drop the synonym: SQL> drop public synonym XMLCONCAT; 2. recompile the package SQL> alter package DBMS_SQLTUNE_INTERNAL compile body; 3. if it works, run utlrp.sql to recompile the other INVALID objects @?/rdbms/admin/utlrp.sql; For us, the above solution worked and we were able to compile SYS.DBMS_SQLTUNE_INTERNAL successfully and utlrp.sql ran successfully, compiling all Invalid objects. The dependent synonym ‘XMLCONCAT’ didn’t have a base object. I used the following query to obtain the list of public synonyms conflicting with synonyms being used to compile select * from ( select * from dba_synonyms a where not exists ( select 1 from dba_objects b where a.table_owner = b.owner and a.table_name = b.object_name)) where synonym_name not like '%/%' AND DB_LINK IS NULL; Before we start the upgrade process, we need to drop all public synonyms whose base object doesn’t exist