Results of dba_registry and utlu102s.sql differ

After a roller coaster upgrade from 9.2.0.8 to 10.2.0.4, I finally achieved this:

select comp_name,version,status
from dba_registry

SQL> /
Oracle Database Catalog Views
10.2.0.4.0 VALID

Oracle Database Packages and Types
10.2.0.4.0 VALID

JServer JAVA Virtual Machine
10.2.0.4.0 VALID

Oracle Database Java Packages
10.2.0.4.0 VALID

Oracle XDK
10.2.0.4.0 VALID

Oracle Text
10.2.0.4.0 VALID

Oracle Real Application Clusters
10.2.0.4.0 INVALID

Spatial
10.2.0.4.0 VALID

Oracle XML Database
10.2.0.4.0 VALID

Oracle interMedia
10.2.0.4.0 VALID


10 rows selected.

However utlu102s.sql still gives Oracle Database Server status as INVALID:

SQL> @utlu102s.sql
.
Oracle Database 10.2 Upgrade Status Utility 03-12-2009 18:21:06
.
Component Status Version HH:MM:SS
Oracle Database Server INVALID 10.2.0.4.0 01:01:55
JServer JAVA Virtual Machine VALID 10.2.0.4.0 00:15:19
Oracle XDK VALID 10.2.0.4.0 00:11:22
Oracle Database Java Packages VALID 10.2.0.4.0 00:00:47
Oracle Text VALID 10.2.0.4.0 00:02:42
Oracle XML Database VALID 10.2.0.4.0 00:03:17
Oracle Real Application Clusters INVALID 10.2.0.4.0 00:00:03
Oracle interMedia VALID 10.2.0.4.0 00:06:36
Spatial VALID 10.2.0.4.0 00:06:56
.
Total Upgrade Time: 02:16:51

PL/SQL procedure successfully completed.

Metalink Note 456845.1 describes this issue. However I went a little deeper and figured out a way to correct this:

utlu102s.sql reads results from dba_registry_log. A dbms_metadata query on dba_registry_log shows this:

SQL> select dbms_metadata.get_ddl('VIEW','DBA_REGISTRY_LOG','SYS') FROM DUAL;

DBMS_METADATA.GET_DDL('VIEW','DBA_REGISTRY_LOG','SYS')
--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SYS"."DBA_REGISTRY_LOG" ("OPTIME", "NAMESPACE",


SQL> SET LONG2000
SQL> /

DBMS_METADATA.GET_DDL('VIEW','DBA_REGISTRY_LOG','SYS')
--------------------------------------------------------------------------------

CREATE OR REPLACE FORCE VIEW "SYS"."DBA_REGISTRY_LOG" ("OPTIME", "NAMESPACE",
"COMP_ID", "OPERATION", "MESSAGE") AS
SELECT optime,
namespace, cid,
DECODE(operation, 0, 'INVALID',
1, 'VALID',
2, 'LOADING',
3, 'LOADED',
4, 'UPGRADING',
5, 'UPGRADED',
6, 'DOWNGRADING',
7, 'DOWNGRADED',
8, 'REMOVING',
9, 'OPTION OFF',
10, 'NO SCRIPT',
99, 'REMOVED',
100, 'ERROR',
NULL),
errmsg
FROM registry$log

SQL>

So the view dba_registry_log is actually based on registry$log table.

SQL> desc registry$log
Name Null? Type
----------------------------------------- -------- ----------------------------
CID VARCHAR2(30)
NAMESPACE VARCHAR2(30)
OPERATION NOT NULL NUMBER
OPTIME TIMESTAMP(6)
ERRMSG VARCHAR2(1000)

SQL> select cid,operation from registry$log;
SQL> /
UPGRD_BGN -1
JAVAVM 1
CATPROC 1
RDBMS 1
XML 1
CATJAVA 1
CONTEXT 1
XDB 1
RAC 0
ORDIM 1
SDO 1
UPGRD_END -1
UTLRP_BGN -1
UTLRP_BGN -1
UTLRP_END -1
UTLRP_BGN -1
UTLRP_END -1
UTLRP_BGN -1
UTLRP_END -1
UTLRP_BGN -1
UTLRP_END -1
UTLRP_BGN -1
UTLRP_END -1

23 rows selected.

It is clear from above that 1 is the code for VALID and 0 is the code for INVALID.

So I passed this update statement.

SQL> update registry$log
2 set operation=1
3 where cid in ('CATPROC','RDBMS');

2 rows updated.

SQL> commit;

Commit complete.

SQL> @?/rdbms/admin/utlu102s.sql
.
Oracle Database 10.2 Upgrade Status Utility 03-12-2009 18:29:19
.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.4.0 01:01:55
JServer JAVA Virtual Machine VALID 10.2.0.4.0 00:15:19
Oracle XDK VALID 10.2.0.4.0 00:11:22
Oracle Database Java Packages VALID 10.2.0.4.0 00:00:47
Oracle Text VALID 10.2.0.4.0 00:02:42
Oracle XML Database VALID 10.2.0.4.0 00:03:17
Oracle Real Application Clusters INVALID 10.2.0.4.0 00:00:03
Oracle interMedia VALID 10.2.0.4.0 00:06:36
Spatial VALID 10.2.0.4.0 00:06:56
.
Total Upgrade Time: 02:16:51

PL/SQL procedure successfully completed.

SQL>