HomenewsUPGRADEORACLE PACKAGES AND TYPES IN DBA_REGISTRY ORA-01775

ORACLE PACKAGES AND TYPES IN DBA_REGISTRY ORA-01775

  • December 26, 2017
  • Posted by: admin
  • Category: UPGRADE
No Comments

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


Leave a Reply