Invalid MDSYS Objects Types: SDO_GEO_ADDR and SDO_NETWORK_MANAGER_I after DB Upgrade [ID 1294577.1]

In this Document

Applies to:

Oracle Spatial - Version 10.2.0.5 and later
Information in this document applies to any platform.

Symptoms

After upgrading the database from 10.2.0.4.0 to 10.2.0.5.0, the STATUS of Spatial in DBA_REGISTRY is INVALID and there are 2 INVALID Types owned by MDSYS:

SQL> select owner, object_name, object_type, status
from dba_objects
where wner = 'MDSYS'
and status != 'VALID'
order by 2,3;

OWNER OBJECT_NAME OBJECT_TYPE STATUS
---------- ----------------------- ---------------- -----------
MDSYS SDO_GEO_ADDR TYPE INVALID
MDSYS SDO_NETWORK_MANAGER_I TYPE INVALID

SQL> select comp_name, version, status
from dba_registry
order by 1;

COMP_NAME VERSION STATUS
----------------------------------- --------------- ----------
JServer JAVA Virtual Machine 10.2.0.5.0 VALID
OLAP Analytic Workspace 10.2.0.5.0 VALID
OLAP Catalog 10.2.0.5.0 VALID
Oracle Data Mining 10.2.0.5.0 VALID
Oracle Database Catalog Views 10.2.0.5.0 VALID
Oracle Database Java Packages 10.2.0.5.0 VALID
Oracle Database Packages and Types 10.2.0.5.0 VALID
Oracle Expression Filter 10.2.0.5.0 VALID
Oracle OLAP API 10.2.0.5.0 VALID
Oracle Rule Manager 10.2.0.5.0 VALID
Oracle Text 10.2.0.5.0 VALID
Oracle XDK 10.2.0.5.0 VALID
Oracle XML Database 10.2.0.5.0 VALID
Oracle interMedia 10.2.0.5.0 VALID
Spatial 10.2.0.5.0 INVALID

Changes

Upgraded database from 10.2.0.4 to 10.2.0.5.

Cause

The cause of this problem has been identified and verified in an unpublished Bug 6748203. It is caused by post-upgrade script, catupgrd.sql, which creates new type objects due to their dependencies and the old versions (subname = $VSN_n) become invalid. The older versions of the types are left in an INVALID state while the newer version of the same types are VALID.

Solution

The status of old versions of types with status 6 can be changed to VALID with the following SQL statements:

SQL> connect / as sysdba

UPDATE obj$ SET status=1
WHERE type#=13 AND subname IS NOT NULL AND status=6;
COMMIT;

start ?/rdbms/admin/utlrp.sql

SQL> select owner, object_name, object_type, status
from dba_objects
where wner = 'MDSYS'
and status != 'VALID'
order by 2,3;

no rows selected

SQL> select comp_name, version, status
from dba_registry
order by 1;

COMP_NAME VERSION STATUS
----------------------------------- --------------- ----------
JServer JAVA Virtual Machine 10.2.0.5.0 VALID
OLAP Analytic Workspace 10.2.0.5.0 VALID
OLAP Catalog 10.2.0.5.0 VALID
Oracle Data Mining 10.2.0.5.0 VALID
Oracle Database Catalog Views 10.2.0.5.0 VALID
Oracle Database Java Packages 10.2.0.5.0 VALID
Oracle Database Packages and Types 10.2.0.5.0 VALID
Oracle Expression Filter 10.2.0.5.0 VALID
Oracle OLAP API 10.2.0.5.0 VALID
Oracle Rule Manager 10.2.0.5.0 VALID
Oracle Text 10.2.0.5.0 VALID
Oracle XDK 10.2.0.5.0 VALID
Oracle XML Database 10.2.0.5.0 VALID
Oracle interMedia 10.2.0.5.0 VALID
Spatial 10.2.0.5.0 VALID