Skip to main content

How to upgrade Oracle 9.2.0.1 to 9.2.0.5 or 9.2.0.6 (9i versions)

Applying Oracle upgrade to version 9.2.0.5 (patchset 3501955) or 9.2.0.6 (patchset 3948480)

Applying Oracle 9i Patch (This can be used for the Oracle client and server both)

1. Log in to the database server machine as the oracle user (administrator role in case of windows) on Unix.
2. Make sure that your environment contains the correct values for the ORACLE_HOME and ORACLE_SID variables.
3. Stop the listener process (in case of Oracle server upgrade). As the oracle user:

lsnrctl stop

4. Download the patchset Extract the 9.2.0.5 patchset (3501955) or 9.2.0.6 patch set (3948480) archive into a temporary directory.
5. Change to the directory that contains the 9.2.0.5 or 9.2.0.6 patch archive and execute the following:

Unzip the patch archive, viz. p3948480_9206_***.zip (this example is for Linux. Windows & other Unix patch names would be different.)


6. Install the Oracle Universal Installer (setup.exe in case of Windows patch) :

cd /usr/local/tmp/oracle/patches/3948480
Disk1/runInstaller &

Welcome
Next ->
Specify File Locations
Source Path: choose default
Destination Name: ORACLE_HOME
Destination Path: /usr/local/oracle/products/9.2
Next ->
Select a Product to Install
Select: Oracle Universal Installer 10.1.0.3.0
Next ->
Summary
Install ->
End of Installation
Exit


7. Install the Oracle 9.2.0.6 patch set:

Disk1/runInstaller &

Welcome
Next ->
Specify File Locations
Source Path: choose default
Destination Name: ORACLE_HOME
Destination Path:
Next ->
Select a Product to Install
Select: Oracle 9iR2 Patch Set 9.2.0.6.0
Next ->
Summary
Install ->

A window will open and you will be prompted to execute (This will not happen in Windows)
root.sh. Open a console window and run $ORACLE_HOME/root.sh as
"root" user of Unix.
OK ->
End of Installation
Exit


Database Upgrade Presteps

Before upgrading the database, do the following:

*

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

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

Set the value of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters as follows:
1. Start the database:

SQL> startup


2. Enter the following command to determine whether the system uses an initialization parameter file (initsid.ora) or a server parameter file (spfiledbname.ora):

SQL> show parameter pfile;


This displays the name and location of the server parameter file or the initialization parameter file.

If the system is using a server parameter file, do the following:
1. Execute the following command to display the current value of the SHARED_POOL_SIZE initialization parameter:

SQL> show parameter shared_pool_size;


2. If SHARED_POOL_SIZE initialization parameter is less than 150 MB, execute the following command:

SQL> alter system set shared_pool_size='150M' scope=spfile;


3. Execute the following command to display the current value of the JAVA_POOL_SIZE initialization parameter:

SQL> show parameter java_pool_size;


4. If the JAVA_POOL_SIZE initialization parameter is less than 150 MB, execute the following command:


SQL> alter system set java_pool_size='150M' scope=spfile;


3. Shut down the database:

SQL> shutdown immediate;


[Note] Note

The UF installation uses an spfile (Oracle9i Database will automatically use the $ORACLE_HOME/dbs/spfilewebct.ora file if it exists), but we want to make sure that the pfile and the spfile match up to reduce confusion.

If you adjust the above parameters, run the following:

SQL> create pfile from spfile;


Upgrading the Database

Perform the following steps on every database associated with the upgraded Oracle home:

1. Log in as the oracle user:

sqlplus /nolog
SQL> connect / as sysdba


2. Enter the following SQL*Plus commands:

SQL> startup migrate
SQL> spool patch.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> spool off


3. 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 immediate
SQL> startup


5. Execute the following script to recompile all invalid PL/SQL packages now. (This step is optional but recommended.)

SQL> @?/rdbms/admin/utlrp.sql


6. Restart the listener process (in case of Oracle server). As the oracle user:

lsnrctl start

Popular posts from this blog

Permitting a user on UNIX / Linux to use sqlplus

We encountered an interesting scenario worth mentioning.

Scenario:
Oracle is installed on a Linux server newly & another ftp user wants to just use sqlplus also.Just setting the ORACLE_HOME, LD_LIBRARY_PATH, ORACLE_SID, PATH in the user's profile did not let him run the "sqlplus" command at all. The error shown was :
$ sqlplus
ld.so.1: sqlplus: fatal: /app/oracle/product/10.2/lib/libsqlplus.so: Permission
denied
Killed
The files under "lib" directory under home didn't have permission for anybody other than "oracle" user. -rwxr-x--- 1 oracle oinstall 1320016 Jun 28 2005 /app/oracle/product/10.2/lib/libsqlplus.so
When we permit the other users (other than oracle) to read/execute…

HWM (high watermark) of a tablespace ?

How many times have you thought of calculating HWM (high watermark) of a tablespace as a DBA ? Have you ever found out that a tablespace is overallocated in a database, as compared to the data being used in it ? In such a case, you'd want to reduce the wasted space & resize the tablespace to a smaller size, although greater than the current usage.
HWM is at the maximum blocks ever occupied by a SEGMENT (unless we perform a truncate on it). Segments span over datafiles however remain within a tablespace. All segments are physically allocated by way of extents, which are contiguous blocks of data. Now, when we talk about contiguous blocks, 2 datafiles (even from same tablespace) are highly unlikely to have contiguguos data blocks. Hence, theoretically, it's possible to calculate high watermark within a datafile, not a tablespace directly, as the latter is only a logical unit. Although, we can resize a tablespace, however the resize value needs to be based on exact calculati…

Intereseting --- 249 days bug for Oracle on Linux servers

--contributed by Manjunath Ulthi, Senior DBA at Infosys Technologies limited

1 Introduction

Problem: OCI client spins when machine uptime >= 249 days in Linux machines. Once the uptime crosses 249 days the database client hangs and leads to high CPU utilization and server hung state. The user will not be able to connect to the databases This is oracle known bug 4612267.


Solution: This can be handled by applying the patch 4612267.


The patch has been applied in the servers server126, server041 & server120 and tested. After the patch has been applied the client connections were normal after 249 days and all the operations of databases/applications were normal and didn’t encounter any problems.

For further information refer the Metalink Notes

Note: 338461.1: SQL*Plus With Instant Client 10.2.0.1 Hangs, When System Uptime Is More Than 248 Days

Note:4612267.8 : Bug 4612267 - OCI client spins when machine uptime >= 249 days

2 General Instructions

Procedure to apply the patch

1. Download the …