ok so I am late, but grrr

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Tue, 13 Jan 2009 17:07:56 +0000
Message-ID: <7765c8970901130907v5753200bg20d31906023f35e2_at_mail.gmail.com>

I have a query that I routinely run to check versions and status of components in 9i and higher databases, especially those that have been upgraded. namely

 col comp_name format a70
col version format a10
col status format a10
select comp_name,status,version
from dba_registry
/
 where anything but 'VALID' raises a flag for me. It turns out that of the possible statuses
 INVALID
VALID
LOADING
LOADED
UPGRADING
UPGRADED
DOWNGRADING
DOWNGRADED
REMOVING
REMOVED
 at the very least LOADED and UPGRADED may also equate to valid, though this may also mean things aren't good.
 Take for example Note 729494.1
 How To Change Olap Option Status from Loaded to Valid Before Upgrading Database
 which says in effect,

> check no OLAP objects are invalid and
> After validating all the OLAPSYS objects in the dba_registry, update the
> registry .
> SQL> connect / as sysdba
> SQL> execute dbms_registry.valid('AMD');
> SQL> execute dbms_registry.valid('APS');
> SQL> execute dbms_registry.valid('XOQ');
> "
>

Meanwhile Note 252090.1 says

> Oracle Spatial - Version: 9.2.0.4
> This problem can occur on any platform.
> Symptoms
> After the migration the DBA_REGISTRY table showed the status as LOADED not
> VALID.
> COMP NAME VERSION STATUS
> -------------------------------------------------------------
> Spatial 9.2.0.4.0 LOADED
> Changes
> Upgrading directly to 9.2.0.4 from 8.1.7 and migrating the spatial data.
> Cause
> Note 214887.1 states that when upgrading directly to a 9.2 patch set
> catpatch does not need to be run. This is not correct for Oracle Spatial as
> the CS_SRS table will not be updated with some bug fixes. This may give
> incorrect results when using some of the WKT (Well Known Text) entries.
> Fix
> Manually run the required patch script. This can either be performed by
> running catpatch or by running sdopatch as follows:
> In $ORACLE_HOME/md/admin directory log into sqlplus as the SYS user, then:
> SQL>_at_sdopatch
> SQL>select comp_name, version, status from dba_registry where comp_id =
> 'SDO';
> This should then show that Spatial is now valid:
> COMP NAME VERSION STATUS
> -------------------------------------------------------------
> Spatial 9.2.0.4.0 VALID
>
>

 so in summary, if checking DBA_REGISTRY remember that it may or may not inform you of configuration issues. utlrp of course tells you about invalid objects but not component status.

--
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 13 2009 - 11:07:56 CST