Applying 10.2.0.4 Patch 42 (January 2011)

Release 10.2.0.4 for Microsoft Windows (32-Bit)
You should administer one Oracle product, one Oracle home at a time, for its particular Bundle Patch Update. Each Oracle home will have its own requirements. We are updating Oracle Database as below.

OPatch Utility
You must use the OPatch utility release 10.2.0.4.3 or later. If the Oracle inventory is not setup correctly, this utility will fail. To check accessibility to the inventory you can use the opatch lsinventory command. The utility must be in the PATH definition, or you must call it specifying its full path. For example:

%ORACLE_HOME%/OPatch/opatch lsinventory

D:\oracle\product\10.2.0\db_1\OPatch>set ORACLE_HOME=D:\oracle\product\10.2.0\db_1


D:\oracle\product\10.2.0\db_1\OPatch>opatch lsinventory
Invoking OPatch 10.2.0.4.2

Oracle Interim Patch Installer version 10.2.0.4.2
Copyright (c) 2007, Oracle Corporation.  All rights reserved.


Oracle Home       : D:\oracle\product\10.2.0\db_1
Central Inventory : C:\Program Files\Oracle\Inventory
   from           : n/a
OPatch version    : 10.2.0.4.2
OUI version       : 10.2.0.4.0
OUI location      : D:\oracle\product\10.2.0\db_1\oui
Log file location : D:\oracle\product\10.2.0\db_1\cfgtoollogs\opatch\opatch2011-01-26_19-35-55PM.log

Lsinventory Output file location : D:\oracle\product\10.2.0\db_1\cfgtoollogs\opatch\lsinv\lsinventory2011-01-26_19-35-55PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (2):
Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 3                            10.2.0.4.0
There are 2 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded.

Back up the current database that is installed, the %ORACLE_HOME% directory and Oracle inventory under c:\Program files\oracle directory.

Patch Installation Instructions for Single Instance

Follow these steps:
Cleanly shut down all processes running from the ORACLE_HOME. Shutdown all databases , listeners etc. Check Oracle processes using windows task manager and kill them if required like hsodbc.exe.

Set the ORACLE_HOME environment variable to the correct Oracle home that needs to be patched.

Go to the directory where you downloaded the patch:
Ensure that the directory containing the OPatch script appears in your PATH setting. Enter the following command to run OPatch
D:\PatchesTobeApplied\p10349197_10204_Win32_Jan2011PatchBundle\10349197>opatch apply

Inspect the opatch.log file generated in %ORACLE_HOME%\cfgtoollogs\opatch for any errors.

D:\PatchesTobeApplied\p10349197_10204_Win32_Jan2011PatchBundle\10349197>opatch apply
Invoking OPatch 10.2.0.4.2

Oracle Interim Patch Installer version 10.2.0.4.2
Copyright (c) 2007, Oracle Corporation.  All rights reserved.


Oracle Home       : D:\oracle\product\10.2.0\db_1
Central Inventory : C:\Program Files\Oracle\Inventory
   from           : n/a
OPatch version    : 10.2.0.4.2
OUI version       : 10.2.0.4.0
OUI location      : D:\oracle\product\10.2.0\db_1\oui
Log file location : D:\oracle\product\10.2.0\db_1\cfgtoollogs\opatch\opatch2011-01-26_19-57-46PM.log

ApplySession applying interim patch '10349197' to OH 'D:\oracle\product\10.2.0\db_1'

Running prerequisite checks...
Patch 10349197: Optional component(s) missing : [ oracle.ntoledb.odp_net_2, 10.2.0.4.0 ] , [ oracle.ntoramts, 10.2.0.4.0 ] , [ ora
cle.rdbms.ic, 10.2.0.4.0 ] , [ oracle.has.crs, 10.2.0.4.0 ] , [ oracle.rdbms.dv, 10.2.0.4.0 ] , [ oracle.precomp.lang, 10.2.0.4.0
] , [ oracle.rdbms.dv.oc4j, 10.2.0.4.0 ]

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.


Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = 'D:\oracle\product\10.2.0\db_1')


Is the local system ready for patching? [y|n]

User Responded with: Y

Verifying the update...
Inventory check OK: Patch ID 10349197 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 10349197 are present in Oracle Home.
Execution of 'cmd /C "D:\PatchesTobeApplied\p10349197_10204_Win32_Jan2011PatchBundle\10349197\custom\scripts\post.bat" -apply 1034
9197 ':

Return Code = 0

The local system has been patched and can be restarted.


OPatch succeeded.

Post Installation Instructions
Start the OracleService and Oracle Listener Services.
Start all database instances running out of the ORACLE_HOME that you just patched and run the scripts as below.

cd %ORACLE_HOME%\BUNDLE\Patch42

C:\Documents and Settings\inam>set ORACLE_SID=FRADB

C:\Documents and Settings\inam>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jan 26 20:07:22 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup pfile=D:\FRATEST\FRADB\pfile\initFRADB.ora
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  1300864 bytes
Variable Size             264242816 bytes
Database Buffers          801112064 bytes
Redo Buffers                7086080 bytes
Database mounted.
Database opened.
SQL>

SQL> spool d:\temp\fradb_catcpu_jan2011.log
SQL> @catcpu.sql

If catcpu.sql reports any Invalid Objects, compile the invalid objects as follows.
(For large numbers of objects, this compilation step can take some time. If you are applying this patch through the Oracle Enterprise Manager console, you will be prompted to run this compilation script.)

cd %ORACLE_HOME%\rdbms\admin  

SQL> @utlprp.sql 0

To check for invalid objects, execute the following statement:

SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS = 'INVALID'; 

Recompiling Views in the Database
You must recompile views for all database. 

If you want to check whether view recompilation has already been performed for the database, execute the following statement.

SELECT * FROM registry$history where ID = '6452863';

If the view recompilation has been performed, this statement returns one row. If the view recompilation has not been performed, this statement returns no rows.
To recompile the views for each database instance running out of the ORACLE_HOME being patched , follow these steps:
Run the pre-check script, which reports the maximum number of views and objects that may be recompiled:

cd %ORACLE_HOME%\BUNDLE\view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @recompile_precheck_jan2008cpu.sql

SQL> QUIT

SQL> @recompile_precheck_jan2008cpu.sql

Running precheck.sql...

Number of views to be recompiled :2216
-----------------------------------------------------------------------

Number of objects to be recompiled :51540
Please follow the README.txt instructions for running viewrecomp.sql

PL/SQL procedure successfully completed.


The purpose of this step is to help you determine whether view recompilation should be done at the same time as the Bundle install, or scheduled later.

Run the view recompilation script. Note that this script is run with the database in upgrade mode, which restricts connections as SYSDBA.

cd %ORACLE_HOME%\BUNDLE\view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> SHUTDOWN

SQL> startup upgrade
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1298168 bytes
Variable Size             180355336 bytes
Database Buffers          423624704 bytes
Redo Buffers                7090176 bytes
Database mounted.
Database opened.

SQL> @view_recompile_jan2008cpu.sql

SQL> SHUTDOWN;
SQL> STARTUP;
SQL> QUIT 

Verify patch from history
Run the query below
SQL> select substr(ACTION_TIME,1,40) action_time,substr(ACTION,1,8) action,substr(NAMESPACE,1,7) namespace,
  2  substr(VERSION,1,11) version,ID,substr(COMMENTS,1,40) comments,BUNDLE_SERIES
  3  from registry$history
  4  /

ACTION_TIME                     ACTION   NAMESPA VERSION             ID COMMENTS                         BUNDLE_SERIES
------------------------------- -------- ------- ----------- ---------- ---------------------------------------- -----------------
-------------
26-JAN-11 07.19.52.059000 PM    UPGRADE  SERVER  10.2.0.4.0             Upgraded from 10.2.0.1.0
26-JAN-11 08.44.46.915000 PM    APPLY    SERVER  10.2.0.4            42 Patch 42                         WINBUNDLE
26-JAN-11 08.58.36.649000 PM    CPU                             6452863 view recompilation

SQL>