Patch Installation

Upgrade Oracle 9.2.0.2 to 9.2.0.8

1. Patch Set Overview

Patch set release 9.2.0.8 includes all fixes in patch sets 9.2.0.8 and earlier as well as new fixes for patch set 9.2.0.8. This means that unless the patch set documentation indicates otherwise, you can apply this patch set to any earlier release 9.2 installation. You do not have to install intermediate patch sets.

2. Requirements

Oracle DB : Oracle 9.2.0.1 (later)

Operating System: Windows 2000 (Service Pack 2 or higher)

3. Pre – Installation Tasks

1. Identify prior installation

Before installing this patch you must install oracle 9.2.0.1 (or later version)

2. Download Patch set

Download and extract the p4547809_9208_WINNT.zip patch set installation archive to a directory that is not the Oracle home directory or under the Oracle home directory

3. Shutdown oracle database.

C:\>set oracle_sid=SNGDB

C:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.2.0 - Production on Tue Nov 13 10:49:26 2007

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

SQL> connect sys@sngdb as sysdba

Enter password:

Connected.

SQL> SHUTDOWN

Database closed.

Database dismounted.

ORACLE instance shut down.

4. Stop all services

C:\>net start OracleServiceSNGDB

The OracleServicesngdb service is starting.....................

The OracleServicesngdb service was started successfully.

C:\>net stop OracleServiceSNGDB

The OracleServicesngdb service is stopping...

The OracleServicesngdb service was stopped successfully.

C:\>net stop OracleOraHome92Agent

The OracleOraHome92Agent service is stopping..

The OracleOraHome92Agent service was stopped successfully.

C:\>net stop OracleOraHome92TNSListener

The OracleOraHome92TNSListener service is stopping.

The OracleOraHome92TNSListener service was stopped successfully.

C:\>net stop OracleOraHome92HTTPServer

The OracleOraHome92HTTPServer service is stopping....

The OracleOraHome92HTTPServer service was stopped successfully.

C:\>net stop OracleMTSRecoveryService

The OracleMTSRecoveryService service is stopping.

The OracleMTSRecoveryService service was stopped successfully.

Note: while patch installation time If you face Distributed Transaction Coordinator still running. You should be stop this service

C:\>net stop msdtc

The Distributed Transaction Coordinator service is stopping.

The Distributed Transaction Coordinator service was stopped successfully.

4. Backup your database.

Oracle recommends that you create a backup of the Oracle9i installation before you install the patch set.

5. Check Tablespace Sizes and Set Parameter Values

Review the following sections before upgrading a database.

6. Check SYSTEM Tablespace Size

If JServer is part of the installation ensure that there is at least 10 MB of free space allocated to the SYSTEM tablespace.

7. Check XDB Tablespace Size

For Oracle RAC installations, ensure that there is at least 50 MB of free space allocated to the XDB tablespace.

8. Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters

Users who have JVM (Java enabled) or JVM and XDB installed on their 9.2.0.1 databases should make sure that the init.ora parameters SHARED_POOL_SIZE and JAVA_POOL_SIZE are each 150 MB or more before running the catpatch.sql upgrade script. Failure to do so could result in an unrecoverable memory failure while running of the script. Please note that JVM and XML DB was shipped as part of the default 9.2.0.1 seed database, and will be present unless the user explicitly installed a 9.2.0.1 instance without them.

Set the value of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters as follows:

C:\>set oracle_sid=SNGDB

C:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.2.0 - Production on Tue Nov 13 10:49:26 2007

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

SQL> connect sys@sngdb as sysdba

Enter password:

Connected.

Note:

If the system uses an initialization parameter file, if necessary change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 150 MB in the initialization parameter file (initsid.ora).

SQL> ALTER SYSTEM SET JAVA_POOL_SIZE=150M SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=150M SCOPE=SPFILE;

System altered.

SQL> STARTUP FORCE

ORACLE instance started.

Total System Global Area 437329148 bytes

Fixed Size 453884 bytes

Variable Size 369098752 bytes

Database Buffers 67108864 bytes

Redo Buffers 667648 bytes

Database mounted.

Database opened.

SQL> SHUTDOWN

Database closed.

Database dismounted.

ORACLE instance shut down.

9. 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 MIGRATE

          SQL> SPOOL patch.log

         SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\catpatch.sql

         SQL> SPOOL OFF

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

This list provides the version and status of each SERVER component in the database.

If necessary, rerun the catpatch.sql script after correcting any problems.

4. Restart the database:

SQL> SHUTDOWN

SQL> STARTUP

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

11. If you are using the Oracle Recovery Manager catalog, enter the following command:

C:\> rman catalog username/password@orcl

RMAN> UPGRADE CATALOG;