Upgrade oracle patch from 10.2.0.1 to 10.2.0.4

1. Patch Set Overview

Patch set release 10.2.0.4. Before installing this patch set you must be need 10.2.0.1 version.

2. Requirements

Oracle DB : Oracle 10.2.0.1 (later)
Operating System: Windows

3. Pre - Installation Tasks

1. Identify prior installation
Before installing this patch you must verify oracle version 10.2.0.1 (or later version)
sql> select * from v$version;
BANNER----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

2. Download Patch set
Download 6810189 patch set installation archive to a directory that is not the Oracle home directory or under the Oracle home directory.
Note: required metalink userid/pwd to download patch

3. Shutdown oracle database.
C:\>set oracle_sid= TEST

C:\>sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 13 10:49:26 2007 Copyright (c) 1982, 2002, Oracle Corporation.
All rights reserved.

SQL> connect sys@test as sysdba
Enter password: xxx
Connected.

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

4. Stop all services
C:\>set oracle_sid=TEST

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

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

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

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

4. Backup your database.
Oracle recommends that you create a backup of the Oracle 10g installation before you install the patch set.

5. Check Tablespace Sizes and Set Parameter Values

8. Upgrade the Database

After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:

1. Start all services
2. Connect sys user
C:\> sqlplus /NOLOG
SQL> CONNECT SYS/SYS_password AS SYSDBA

3. Enter the following SQL*Plus commands:

SQL> STARTUP UPGRADE
SQL> SPOOL patch.log
SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\catupgrd.sql
SQL> SPOOL OFF

Review the patch.log file for errors and inspect the list of components that is displayed at the end of catupgrd.sql script.

This list provides the version and status of each SERVER component in the database. If necessary, rerun the catupgrd.sql script after correcting any problems.

4. Shutdown and Restart the database:

5. Compile Invalid Objects
Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time.
This step is optional but recommended.
SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sql

SQL> select * from v$version;
BANNER ----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.4.0 - Production
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production