how to assert Oracle installation correctness ?

From: Giovanni Azua <bravegag_at_hotmail.com>
Date: Fri, 7 Oct 2005 11:02:23 +0200
Message-ID: <3qmrt0Ffr7l6U1@individual.net>

Hi all,

When we migrate the database instances "to catpatch it" after applying patchsets, chances are that we get a final report that looks like the following:

COMP_NAME                           STATUS      VERSION
---------------------------------------------------------
Oracle9i Catalog Views                 VALID       9.2.0.5.0
Oracle9i Packages and Types        VALID       9.2.0.5.0
Oracle Workspace Manager          VALID       9.2.0.1.0
JServer JAVA Virtual Machine      VALID       9.2.0.5.0
Oracle XDK for Java                     VALID       9.2.0.7.0
Oracle9i Java Packages                  VALID       9.2.0.5.0
Oracle interMedia                           VALID       9.2.0.5.0
Spatial                                            VALID       9.2.0.5.0
Oracle Text                                    VALID       9.2.0.5.0
Oracle XML Database                   VALID       9.2.0.5.0
Oracle Ultra Search                        VALID       9.2.0.5.0

COMP_NAME                           STATUS      VERSION
---------------------------------------------------------
Oracle Data Mining                        VALID       9.2.0.5.0
OLAP Analytic Workspace     UPGRADED    9.2.0.5.0
Oracle OLAP API                   UPGRADED    9.2.0.5.0
OLAP Catalog                               VALID       9.2.0.5.0

But in the very worse case, despite the fact that all components appear really O.K. we might still miss critical errors somewhere during execution of catpatch e.g.

(taken from one problematic installation)

begin if initjvmaux.startstep('CREATE_JAVA_SYSTEM') then *
ERROR at line 1:

ORA-22288: file or LOB operation FILEOPEN failed
ORA-06512: at "SYS.INITJVMAUX", line 15
ORA-06512: at line 5

ERROR at line 1:
ORA-20000: JServer has not been correctly loaded into the database. ORA-06512: at line 3

My question is:

How can I assert the total correctness of any Oracle installation (including patchsets and migration) without ending up in many core dumps or extremelly hard to diagnose e.g. strange behavior of the optimizer or bizzarre errors etc?

I have found lack of *correct* installation of catpatch by receiving:

Invalid behavior of the optimizer e.g. full table scans where it must use indexes (all CBO prerequisites ok e.g. statistics etc).
Components unable to be installed e.g. Oracle Workflow.
Major core dumps executing innocuous queries.

All of the above are symptoms for me to diagnose lack of correct Oracle installation.

I would really appreciate to be pointed to some diagnosing package for this purpose and I will then probably make my applications to have the precondition of "Oracle installation OK".

Many thanks in advance,
Best Regards,
Giovanni Received on Fri Oct 07 2005 - 04:02:23 CDT