How to perform out-of place manual upgrade from 11.2.0.1 to 11.2.0.2

Solution : 11.2.0.2 patch set software is a full release. 11.2.0.2 OUI does not update existing 11.2.0.1 ORACLE_HOME files and performs a new installation.

There are two method

1. Out-of-Place (Oracle's recommended approach)
2. In-Place

But here, we need to understand what exactly this method is:

In prior releases, how it worked

 DB Upgrade 8i to 10g (Out-Of-Place)

We install Oracle binaries for 10g at new location, then use DBUA or catupgrd.sql script to upgrade DB. This is Out-of-Place migration (i.e. created new oracle home, from inventory perspective).

DB Upgrade 10.2.0.1 to 10.2.0.4 (In-Place)

Here we won’t install 10.2.0.4 patchset in new home (Oracle won’t let us do that, as it is a patch), but select the existing home to upgrade. We also keep a backup of original 10.2.0.1, as we may have to fail back.

As 11.2.0.2 is not a patch and full install.  It does not allow us to install binaries in existing 11.2.0.1 home (i.e. In-Place). But as I mentioned above, we can perform both upgrades

Case of In-Place upgrade

Upgrade an existing installation of Oracle database to Oracle Database 11g Release 2 (11.2.0.2) into the same Oracle home directory by replacing the existing database files (i.e. same physical location but new binaries). Then run the upgrade script. In case of failure, we can revert back to original home (provided we have backup of old 11.2.0.1 home).

Advantage/Disadvantage: The patch set application requires more downtime and is not recommended. This upgrade type requires less disk space.

Case of Out-of-Place upgrade

We need to install 11.2.0.2 in new home and run the upgrade script.

In both cases, we have to provision space for two oracle homes. In case of Out-of-Place upgrade it is mandatory. Out-of-place upgrade process is similar to Oracle database upgrade from one version to another and is the recommended type.

Advantage/Disadvantage: The benefit of this upgrade type is that the patch set application requires much less downtime, and is safer when the patch set application must be aborted. This upgrade type requires more disk space to accommodate two Oracle home directories simultaneously.

Example of Out –of-Place upgrade method:

STEP 1

======

stop all services

STEP 2

======

Install 11.2.0.2 into a separate ORACLE_HOME

STEP  3

======

After installing the 11.2.0.2 software, start the 11.2.0.1 instance with the 11.2.0.1 ORACLE_HOME

And  spool/run 11.2.0.2 $ORACLE_HOME/rdbms/admin/utlu112i.sql script against the running 11.2.0.1 instance.

#######Note: IF you are upgrading manually THEN############

You can either upgrade time zone version after upgrading to 11.2.0.2 using DBMS_DST package

STEP 4

======

Run dbupgdiag.sql script to verify that all the components in dba_registry are valid and no invalid data dictionary objects in dba_objects.The script needs to be run in SQL*Plus both before the upgrade on the source database and after the upgrade on the upgraded database as SYS user.

Running the Script

Connect as sysdba and execute the script

cd <location of the script>

$ sqlplus / as sysdba

sql> alter session set nls_language='American';

sql> @dbupgdiag.sql

sql> exit

If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects.

$ cd $ORACLE_HOME/rdbms/admin

$ sqlplus "/ as sysdba"

SQL> @utlrp.sql

If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql

STEP 5

======

Take a full  backup of the database

STEP 6

=======

Shut down the database cleanly

STEP 7 ( Windows Platform only)

========================

1) Set Environment variable ORACLE_HOME to point to 11.2.0.1 installation

2) Stop 11.2.0.1  Oracle database service with ORACLE_HOME set to point to 11.2.0.1 installation

  C:\> NET STOP OracleServiceORCL

3)  Delete 11.2.0.1 Oracle service using %ORACLE_HOME%\bin\ ORADIM binary

C:\> ORADIM -DELETE -SID ufcwstg

4) Set Environment variable ORACLE_HOME to point to 11.2.0.2 installation

5) Create the Oracle 11.2.0.2 service at a command prompt using the 11.2.0.2 %ORACLE_HOME%\bin\ ORADIM

C:\> ORADIM -NEW -SID SID -SYSPWD PASSWORD -STARTMODE AUTO -PFILE %ORACLE_HOME%\DATABASE\INIT<SID>.ORA

For Instance,

C:\> ORADIM -NEW -SID ufcwstg ufcwdba  -STARTMODE AUTO -PFILE %ORACLE_HOME%\DATABASE\INIT<SID>.ORA

STEP 8 (Unix & Linux)

=================

Configure the target 11.2.0.2 ORACLE_HOME

1) Make sure environment variables ORACLE_BASE,ORACLE_HOME, PATH, NLS_10 and LIBRARY_PATH are set to point to 11.2.0.2 installation

set ORACLE_SID to the 11.2.0.1 DB name to upgrade

The oratab file points to your Oracle Database 11g Release 2 (11.2.0.2) Oracle home

2) Disable Database Vault

see Note 453903.1 - Enabling and Disabling Oracle Database Vault in UNIX

3) copy init.ora/spfile and password file (orapw<sid>.ora) from 11.2.0.1 $ORACLE_HOME/dbs to 11.2.0.2 $ORACLE_HOME/dbs

4) copy network configuration files (listener.ora, sqlnet.ora, tnsnames.ora ..etc) from 11.2.0.1 $ORACLE_HOME/admin/network ( or $TNS_ADMIN) location to 11.2.0.2 $ORACLE_HOME/admin/network ( or $TNS_ADMIN) location

5)Copy also the following two directories and their contents from 11.2.0.1 to 11.2.0.2

ORACLE_HOME/<hostname_dbname>

ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_<hostname_dbname>

Specify the actual name for <hostname_dbname>

6) Make sure the COMPATIBLE initialization parameter is properly set for Oracle Database 11g Release 2 (11.2). The Pre-Upgrade Information Tool displays a warning in the Database section if COMPATIBLE is not properly set.

7) Adjust the values of the initialization parameters to at least the minimum values indicated by the Pre-Upgrade Information Tool.

STEP 9

======

Upgrade the database manually

1) start sqlplus and run catupgrd.sql script

sqlplus " / as sysdba "

SQL> spool /tmp/upgrade.log

SQL> startup upgrade

SQL> set echo on

SQL> @catupgrd.sql;

SQL> spool off

SQL> Shutdown immediate

2) Check catupgrd.sql spool file for errors

3) restart the database in normal mode

4) SQL> @$ORACLE_HOME/rdbms/admin/catuppst.sql;

5)SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql;

6) Run dbupgdiag.sql script ( se note:556610.1) and verify that all the components in dba_registry are valid and there are no invalid objects in dba_objects