Critical Patch Update

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




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



SYSTEM Overview

Platforms : Windows XP SP2
Database : 10.1.0.5.0


OPatch Utility Information


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



Check Oracle Inventory Setup


C:\>set ORACLE_SID=ORCL

C:\>set ORACLE_HOME=c:\oracle\product\10.1.0\db_1

C:\>cd c:\oracle\product\10.1.0\db_1\opatch

C:\Oracle\product\10.1.0\Db_1\OPatch>opatch lsinventory

Oracle Interim Patch Installer version 1.0.0.0.53

Note: if Opatch utility version is <= 1.0.0.0.53 then you have to download OPATCH utility from metalink site.
[output cut]

Oracle Home = c:\oracle\product\10.1.0\db_1
Location of Oracle Universal Installer components = C:\oracle\product\10.1.0\db_
1\oui
Location of OraInstaller.jar = "C:\oracle\product\10.1.0\db_1\oui\jlib"
Oracle Universal Installer shared library = C:\oracle\product\10.1.0\db_1\oui\li
b\win32\oraInstaller.dll
Location of Oracle Inventory Pointer = N/A
Location of Oracle Inventory = C:\oracle\product\10.1.0\db_1\inventory
Path to Java = "C:\oracle\product\10.1.0\db_1\jre\1.4.2\bin\java.exe"
Log file = c:\oracle\product\10.1.0\db_1/.patch_storage//*.log

Creating log file "C:\oracle\product\10.1.0\db_1\.patch_storage\LsInventory__06-
13-2007_20-18-49.log"

Result:


There is no Interim Patch




OPatch succeeded.
OPatch returns with error code = 0



Preinstallation Instructions


1. Ensure that your Oracle Database installation is the same release for which you are applying this patch.


2. Back up the current database that is installed
-->ORACLE_HOME or ORA INVENTORY

3. Perform a clean shutdown of all Oracle services
C:\>net stop OracleServiceORCL
The following services are dependent on the OracleServiceORCL service.
Stopping the OracleServiceORCL service will also stop these services.

OracleDBConsoleorcl

Do you want to continue this operation? (Y/N) [N]: Y
The OracleDBConsoleorcl service is stopping...............
The OracleDBConsoleorcl service was stopped successfully.

The OracleServiceORCL service is stopping...........
The OracleServiceORCL service was stopped successfully.


C:\>net stop OracleServiceCATDB
The OracleServiceCATDB service is stopping......
The OracleServiceCATDB service was stopped successfully.


C:\>LSNRCTL stop

LSNRCTL for 32-bit Windows: Version 10.1.0.5.0 - Production on 13-JUN-2007 20:30
:30

Copyright (c) 1991, 2004, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
The command completed successfully

C:\>net stop OracleCSService

The OracleCSService service was stopped successfully.


C:\>net stop OracleOraDb10g_home1iSQL*Plus
..
The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.




4. Set the PERL5LIB and PATH environment variables to point to Perl in Oracle Home


C:\>set PERL5LIB=C:\oracle\product\10.1.0\db_1\perl\5.6.1\lib;%PERL5LIB%

C:\>set PATH=C:\oracle\product\10.1.0\db_1\perl\5.6.1\bin\MSWin32-x86;%PATH%


5. set ORACLE_HOME variable


C:\>set ORACLE_HOME=c:\oracle\product\10.1.0\db_1


6. Check if the java and jar executables are present in your Oracle Home


-->JAVA = %ORACLE_HOME%\jre\\bin
-->JAR = %ORACLE_HOME%\jdk\bin


7. Go to directory where download the patch



C:\Oracle\product\10.1.0\Db_1\OPatch>opatch apply c:\5907304




Post Installation Instructions



1. Start All Oracle Services.


C:\>net start OracleServiceORCL

C:\>net start OracleServiceCATDB

C:\>set ORACLE_SID=orcl

C:\>emctl start dbconsole

C:\>LSNRCTL start

C:\>net start OracleCSService

C:\>net start OracleOraDb10g_home1iSQL*Plus


2. For each database instance running out of the ORACLE_HOME being patched, connect to the database using SQL*Plus as SYSDBA and run catcpu.sql as follows:


C:\>rem go to CPU directory

C:\>CD C:\oracle\product\10.1.0\db_1\CPU\CPUApr2007

C:\Oracle\product\10.1.0\Db_1\cpu\cpuapr2007>sqlplus /nolog

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Jun 14 08:21:34 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP MIGRATE
ORACLE instance started.

Total System Global Area 356515840 bytes
Fixed Size 789400 bytes
Variable Size 330036328 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.

SQL> @CATCPU.SQL

[OUTPUT CUT]

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit


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


C:\>cd C:\oracle\product\10.1.0\db_1\rdbms\admin

C:\Oracle\product\10.1.0\Db_1\RDBMS\ADMIN>sqlplus /nolog

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Jun 14 08:47:26 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL> STARTUP
ORACLE instance started.

Total System Global Area 356515840 bytes
Fixed Size 789400 bytes
Variable Size 330036328 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.

SQL> @utlprp.sql 0

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

no rows selected


3. If any databases have been created without using DBCA (that is, using the command line), and you need to monitor such a database by using Oracle Enterprise Manager 10g, follow these steps:


> sqlplus SYS / AS SYSDBA
SQL> ALTER USER DBSNMP ACCOUNT UNLOCK
SQL> PASSWORD DBSNMP
Changing password for DBSNMP
New password: new_password
Retype new password: new_password