Friday, September 19, 2008

Upgrading Oracle Database Server 10g Release 10.2.0.1 to Oracle Database 10g Release 10.2.0.3 on Windows 2003

Upgrading Oracle Database Server 10g Release 10.2.0.1 to Oracle Database 10g Release 10.2.0.3 on Windows 2003

We had recently upgraded one of our test databases from 10.2.0.1 to 10.2.0.3 on Windows 2003.Details:

Operating Version Details Windows 2003 Standard EditionOracle Version Details (Current) : Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionOracle
Instance Name: tom
Patchset (to be applied on) : p5337014_10203_WINNT.zip (Downloaded from http://www.metalink.oracle.com/)

Oracle Universal Installer Version RequirementsThe Oracle Universal Installer release must be 10.1.0.3 (This includes in the Patchset).

System Requirements* Oracle Database 10g (10.2.0.1) or laterPreinstallation TasksTo download and extract the patch set installation software:* Download the p5337014_10203_WINNT.zip patch set.

Set the ORACLE_HOME and ORACLE_SID Environment Variables
C:\> set ORACLE_HOME= D:\oracle\product\10.2.0\db_1
c:\> set ORACLE_SID=TOMThe oracle_home is the Oracle home directory where the

Oracle10g installation that you want to upgrade is installed, and sid is the SID of the database that you want to upgrade.


Stopping All Processes for a Single Instance Installation

**To stop Listener ServiceC:\lsnrctlLSNRCTL> stop

** To stop Enterprise Manager ServiceC:\ emctl stop dbconsole

** To stop iSQLPlus ServiceC:\ isqlplusctl stop**

To Shut Down Oracle DatabasesC:\ sqlplus /nolog
C:\ connect /as sysdbaSQL> SHUTDOWN IMMEDIATE;

Back Up the SystemOracle recommends that you create a backup of the Oracle10g installation before you install the patch set.We took the backup of Oracle home and all the database files.

Installation TasksInstalling the Oracle Database 10g Patch Set Interactively

** Log in as AdministratorGo to unzipped location of p5337014_10203_WINNT.zip file** To start Oracle Universal Installer, where patchset_directory is the directory where you unzipped the patch set softwareD:\ patchset_directory\patch\Disk1

Dubble click setup.exe

** On the Welcome screen, click Next.
** In the Specify Home Details screen, select the name of the Oracle home that you want to update, or select the Path that you want to update from the list, then click Next.** On the Summary screen, click Install.This screen lists all of the patches available for installation.** On the End of Installation screen, click Exit, then click Yes to exit from Oracle Universal Installer.Postinstallation TasksUpgrading of Oracle 10g Software from Release 10.2.0.1 to 10.2.0.3 is now successfully done. Now, earlier Oracle Database needs to be upgraded from 10.2.0.1 to 10.2.0.3. Oracle recommends two ways to upgrade the database, one is using DBUA (Database Upgrade Assistance), and another is Manual database upgrade. Here we have followed manual method for upgrade.


Upgrading a Release 10.2 Database using DBUA (Oracle Database Upgrade Assistant)
After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:

Note:
If you do not run the Oracle Database Upgrade Assistant script as described in this section, then the following errors are displayed:

ORA-01092: ORACLE instance terminated.
ORA-39700: database must be opened with UPGRADE option.


Run Oracle Database Upgrade Assistant either in the interactive or noninteractive mode:
Interactive mode:


1. Start the Oracle Database Upgrade Assistant:
From the Start menu, select Programs, then Oracle - HOME_NAME,

then Configuration and Migration Tools and then Database Upgrade Assistant to start the Oracle Database Upgrade Assistant.
Complete the following steps displayed in the Oracle Database Upgrade Assistant screen:

2. On the Welcome screen, click Next.
3. On the Databases screen, select the name of the Oracle Database that you want to update, then click Next.

4. On the Recompile invalid objects screen, select the Recompile the invalid objects at the end of upgrade option, then click Next.

5. If you have not taken the back up of the database earlier, on the Backup screen, select the I would like to take this tool to backup the database option, mention the Path, then click Next.

6. On the Summary screen, check the summary, then click Finish.

7. On the End of upgradation screen, click Exit, then click Yes to exit from Oracle Database Upgrade Assistant.

## Start All Oracle Related Services
## To Check Current Oracle Version

SQL> select * from version;BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production


Manually Upgrading a Release 10.2 Database


* Required Preupgrade Checks**

Check the SYSTEM Tablespace Size

SQL> SELECT sum(bytes/1024/1024) "Size in MB"2 FROM dba_free_space3 WHERE tablespace_name='SYSTEM';
Size in MB----------97.125

We have enough free space in SYSTEM tablespace.I

f not enough free space in SYSTEM tablespace, then add/resize the datafile as below.

## TO add a datafileALTER TABLESPACE SYSTEMADD ' D:\oracle\product\10.2.0\oradata\TOM\ SYSTEM01.DBF 'SIZE 100M;

## To resize a existing datafile.ALTER DATABASE DATAFILE ' D:\oracle\product\10.2.0\oradata\TOM\ SYSTEM01.DBF' RESIZE 500M;

* Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters

##Start the database with the NOMOUNT option:

SQL> STARTUP NOMOUNT
## To check initialization parameter file (initsid.ora or spfilesid.ora)

SQL> SHOW PARAMETER PFILE;

## To determine the current values of these parameters:
SQL> SHOW PARAMETER SHARED_POOL_SIZE

SQL> SHOW PARAMETER JAVA_POOL_SIZE

##If the system is using a server parameter file:If necessary, set the value of the SHARED_POOL_SIZE initialization parameter to at least 150 MB:

SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='150M' SCOPE=spfile;

If necessary, set the value of the JAVA_POOL_SIZE initialization parameter to at least 150 MB:

SQL> ALTER SYSTEM SET JAVA_POOL_SIZE='150M' SCOPE=spfile;

##If the system uses an initialization parameter fileIf 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).

Shut down the database:

SQL> SHUTDOWN

Upgrading a Release 10.2 Database
##Login as Windows Administrator

##Start the Listener
C:\lsnrctlLSNRCTL> START

##Connect to sqlplus as sysdba and start the database in upgrade mode
C:\sqlplus /nologSQL> connect /as sysdba
SQL> STARTUP UPGRADE;

##Run the catupgrd.sql scrips
SQL> SPOOL patch.log
SQL> @D:\oracle\product\10.2.0\db_1\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.

## Restart the database:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time.

SQL> @D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlrp.sql
Start All Oracle Related Services

To Check Current Oracle Version
SQL> select * from version;BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production


Common Errors:
** If you attempt to install this patch set in an Oracle home directory that does not contain an Oracle Database 10g release 10.2.0.1 or 10.2.0.2 installation, Oracle Universal Installer displays a warning dialog with the following error:
OUI-10091: There are no patches that need to be applied from the patch set Oracle Database 10g Release 2 Patch Set 210.2.0.3
The Oracle Universal Installer does not allow the installation to proceed. Click OK, then click Cancel to end the installation.

** If you do not run the Oracle Database Upgrade Assistant as described in this section, then the following errors are displayed:ORA-01092: ORACLE instance terminated.ORA-39700: database must be opened with UPGRADE option.
C:\sqlplus /nologSQL> connect /as sysdbaSQL> STARTUP UPGRADE;

No comments: