Critical Patch Update JAN 2008 on Windows

Oracle Critical Patch Update Advisory - January 2008
OracleCPU Advisory

Below procedure based on my test enviourment if you follow this instruction please go through README.TXT file that comes with PATCH SET.

Patch Installation Procedures for Oracle Database Release 10.1.0.5
Patch Number : p6637274_10105_WINNT.zip

SYSTEM Overview

Platforms : Windows XP SP2
Database : 10.1.0.5.0

Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.

OPatch Utility Information

You must use the OPatch utility release 1.0.0.0.53 or later. You can download it from
OracleMetaLink with patch 2617419.

Check Oracle Inventory Setup

Oracle 10G onwards, the RDBMS software ships with OPatch and it can be loacated under $ORACLE_HOME/OPatch. To download, opatch you need to have an metalink.oracle.com account. On OracleMetaLink search for Patch 4898608 , select the Oracle RDBMS version and download the patch.

set ORACLE_SID=ARIES
set ORACLE_HOME=D:\oracle\product\10.1.0\Db_1
set PERL5LIB=D:\oracle\product\10.1.0\db_1\perl\5.6.1\lib;%PERL5LIB%
set PATH=D:\oracle\product\10.1.0\db_1\perl\5.6.1\bin\MSWin32-x86;%PATH%

D:\patchset10gR1\6637274>cd D:\oracle\product\10.1.0\db_1\opatch

D:\oracle\product\10.1.0\Db_1\OPatch>opatch lsinventory
[Output Edited]
Creating log file "D:\oracle\product\10.1.0\db_1\.patch_storage\LsInventory__02-05-2008_10-57-56.log"

Result:

There is no Interim Patch

OPatch succeeded.
OPatch returns with error code = 0

D:\oracle\product\10.1.0\Db_1\OPatch>

==================
D:\oracle\product\10.1.0\Db_1\OPatch>opatch apply D:\patchset10gR1\6637274

[Output Edited]
Creating log file "D:\oracle\product\10.1.0\db_1\.patch_storage\6637274\Apply_6637274_02-05-2008_11-06-12.log"

[Output Edited]
Is this system ready for updating?
Please respond Y|N >
Y
Executing the Apply pre-patch script (D:\patchset10gR1\6637274\custom\scripts\pre.bat)...
Applying patch 6637274...

[Output Edited]
Updating inventory...
Backing up comps.xml ...

********************************************************************************
** ATTENTION **
** **
** Please note that the Security Patch Installation (Patch Deinstallation) is **
** not complete until all the Post Installation (Post Deinstallation) **
** instructions noted in the Readme accompanying this patch, have been **
** successfully completed. **
** **
********************************************************************************
********************************************************************************
Executing the Apply post-patch script (D:\patchset10gR1\6637274\custom\scripts\post.bat)...

OPatch succeeded.
OPatch returns with error code = 0

D:\oracle\product\10.1.0\Db_1\OPatch>

====================================
CD D:\oracle\product\10.1.0\db_1\CPU\cpujan2008

D:\oracle\product\10.1.0\Db_1\cpu\cpujan2008>sqlplus /nolog
SQL> conn sys/sys as sysdba
Connected.
SQL> select name from v$instance;

INSTANCE_NAME
---------------
aries
SQL> shutdown immediate;

SQL> STARTUP MIGRATE
ORACLE instance started.
Database opened.
SQL>@CATCPU.SQL

[OUTPUT EDITED]
Package body created.
Package body created.
Package body created.
Session altered.

1 row selected.

Session altered.

OWA_MESSAGE
--------------------------------------------------------------------------------
Installed OWA version is: 9.0.4.0.2

Backing up previous OWA packages to: MODPLSQL\owa_restore_aries.sql

OWA_MESSAGE
-------------------

OWALOAD File: MODPLSQL\owa_all\90\owaload_patch.sql

Logfile: MODPLSQL\owa_patch_aries.log

OWA_DBG_MSG

-------------------
Installed OWA version is: 9.0.4.0.2;
Shipped OWA version is : 9.0.4.0.6;
OWA packages v9.0.4.0.6 will be installed into your database v101050
Will install owadummy.sql and owacomm.sql
[Output Edited]
No errors.
No. of Invalid Objects is :115
Please refer to README.html to for instructions on validating these objects
Logfile for the current catcpu.sql session is : APPLY_ARIES_05Feb2008_11_53_51.log
not spooling currently
SQL> SHUTDOWN IMMEDIATE
SQL> exit
D:\oracle\product\10.1.0\Db_1\cpu\cpujan2008>

If catcpu.sql reports any Invalid Objects, compile the invalid objects as follows

cd D:\oracle\product\10.1.0\db_1\rdbms\admin

D:\oracle\product\10.1.0\Db_1\RDBMS\ADMIN>sqlplus /nolog

SQL> conn sys/sys as sysdba
Connected to an idle instance.
SQL> startup
Database opened.

SQL> @utlprp.sql 0

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2008-02-05 12:08:43

PL/SQL procedure successfully completed.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2008-02-05 12:08:43

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL> select OBJECT_NAME from dba_objects where status = 'INVALID';

no rows selected

SQL> exit

Run the CATCPU.sql and UTLRP.sql on all the instances in the ORACLE_HOME individually.

To view the details about the patch applied :

select * from dba_registry_history;

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

ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS
---------------------------- -------- ---------- -------- ---------- ----------
05-FEB-08 12.30.36.406000 PM CPU 6637274 CPUJan2008

Still if you find any invalid objects then, manually recompile them. For example:

SQL> alter package schemaname.packagename compile;