Hi Tom,

We are running Oracle Apps 11i.5.9 on Red Hat Linux 2.1. Database 9.2.0.6 . How would I query for the patches applied to the Database ? Appreciate your help.

Here is an example.

Note that the software was patched but the database was not. Someone forgot to run catpatch.sql (not me).

(DEV4):/home/oracle$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.5.0 - Production on Thu May 19 07:56:57 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

SYS@DEV4> desc dba_registry;
Name Null? Type
----------------------------------------- -------- ----------------------------
COMP_ID NOT NULL VARCHAR2(30)
COMP_NAME VARCHAR2(255)
VERSION VARCHAR2(30)
STATUS VARCHAR2(11)
MODIFIED VARCHAR2(20)
CONTROL NOT NULL VARCHAR2(30)
SCHEMA NOT NULL VARCHAR2(30)
PROCEDURE VARCHAR2(61)
STARTUP VARCHAR2(8)
PARENT_ID VARCHAR2(30)

SYS@DEV4> set pages 1000
SYS@DEV4> select comp_id, version from dba_registry;

COMP_ID VERSION
------------------------------ ------------------------------
CATALOG 9.2.0.1.0
CATPROC 9.2.0.1.0
OWM 9.2.0.1.0
JAVAVM 9.2.0.1.0
XML 9.2.0.2.0
CATJAVA 9.2.0.1.0
ORDIM 9.2.0.1.0
SDO 9.2.0.1.0
CONTEXT 9.2.0.1.0
XDB 9.2.0.1.0
ODM 9.2.0.1.0
APS 9.2.0.1.0
XOQ 9.2.0.1.0
AMD 9.2.0.1.0

14 rows selected.

For what it is worth, here is how I determine patchsets that have been applied:

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>set oracle_home=c:\oracle\9.2

C:\>\oracle\opatch\opatch lsinventory

Oracle Interim Patch Installer version 1.0.0.0.52
Copyright (c) 2005 Oracle Corporation. All Rights Reserved..

We recommend you refer to the OPatch documentation under
OPatch/docs for usage reference. We also recommend using
the latest OPatch version. For the latest OPatch version
and other support related issues, please refer to document
293369.1 which is viewable from metalink.oracle.com

Oracle Home = c:\oracle\9.2
Location of Oracle Universal Installer components = C:\oracle\9.2\oui
Location of OraInstaller.jar = "C:\oracle\9.2\oui\jlib"
Oracle Universal Installer shared library = C:\oracle\9.2\oui\lib\win32\oraInstaller.dll
Location of Oracle Inventory Pointer = N/A
Location of Oracle Inventory = C:\oracle\9.2\inventory
Path to Java = "C:\oracle\9.2\jre\1.4.2\bin\java.exe"
Log file = c:\oracle\9.2/.patch_storage/<patch ID>/*.log

Result:


Installed Patch List:
=====================
1) Patch 4065410 applied on Wed Feb 16 17:05:59 GMT-05:00 2005
[ Base Bug(s): 3100625 3502524 4015165 3804250 3721136 3614514 3422392 3754340 3804515 2412991
3815921 3785250 3386901 3752329 3908083 3835429 3934961 4064698 3885860 3679149 2759457 3893567 39644
87 3638698 3896974 2960012 4031093 2670601 3819011 3800642 3228560 2924135 3169422 4039237 3505528 40
33756 3285454 3691672 1985605 3963167 3744836 3921848 3592772 3258766 3864781 3738557 3476649 3858609
3796771 3949188 ]




OPatch succeeded.
OPatch returns with error code = 0

C:\>

Excellent - exactly what I was looking for! Thanks so much!

Hello Thomas,

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 very bad cases, 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.
- 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 if you pointed me 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

www.ngssoftware.com

Patch Verification of Oracle Database Servers

pub July 2005

Tom,

I understand opatch lists patches applied on the database server, is there any sql command or ... to find the same. The reason for that is i am developing an htmldb application that will display the patches applied on the database servers.

Thanks.

Tom,

I have to schedule a patch installaton on windows 2000 server for the database version 8.1.7.4.
a)For the above case is it mandatory to take oracle home backup
b)what query can i use to find the JDK directory?
c)How can find the jar files to set class path?

Hi Tom,

Scenario:
we are currently using 9.2.0.7 Database running AIX on 2 nodes. For some reason, all the components were not installed initially. But, now the database is up and running and we need to install JVM, XML, etc components.

Problem:
When I invoke the OUI and select either one of the nodes and click on the "Installed Components" I dont find anything there. Its the same for the other node too. The component page is blank.

I looked into the v$dba_registry view and found only 3 components were installed.

Tom, please guide us how to list all of the components of the OUI, so that we could custom select from it.

Thanks and regards,
David.

Hi Tom,
1) why do we need to down the databases which uses the home to apply opatch? As on unix, i think running database uses the image of binaries from ORACLE_HOME and rarely uses the actual file.
2) To minimize the downtime, Can we create one parallel ORACLE_HOME and apply opatch on that and later attach the HOME with OraInventory using runInstller.sh ?