Friday, July 10, 2009

Upgrade R12 Database server

How to upgrade R12 Database server from 10.2.0 to 11.1.0 (10g to 11g) manually ?

We cannot upgrade the existing Oracle Home, since 11g is not a patchset. We have to install 11g oracle home
as a separate ORACLE_HOME in parallel to 10g Oracle Home.

We have to install the new database for upgrading the existing database.
But before installing check the software versions for.....

a)Oracle database version and check the appropriate upgrade path accordingly depending on the present version.

b)E-business suite version---minimum is 12.0.4 and apply interoperability patch for 11g on apps node using adpatch

c)Autoconfig has to be upgraded to latest version by applying 7207440.

I did not need to apply as my instance is on 12.0.6 and 6728000(12.0.6 upgrade patch) supersedes this autoconfig patch..

Now steps for database installation

1.Apply patch 6400501 to Apps Oracle-home

2. Deregister the current database if you want to change database sid,host,port.
And update autoconfig for database also before that.

$ perl $ORACLE_HOME/appsutil/bin/ appspass=apps
contextfile=$CONTEXT_FILE -removeserver

3. Update applications context file with new database parameters....

to new database values. Don't run auto config on apps tier now....
otherwise apps will not be able to connect to database at all.
Run autoconfig only after complete upgrade process is finished..

Database Installation

The 11.1.0 Oracle home must be installed on the database server node
in a different directory other than the current Oracle home

Log in to the database server node as the owner
of the Oracle RDBMS file system and database instance. Ensure
that environment settings, such as ORACLE_HOME, are set for the new
Oracle home you are about to create, and not for any existing
Oracle homes on the database server node

Choose to install software only (without any default database) in the new location.

After the installation,

Run utlu111i.sql (located in 11g_ORACLE_HOME/rdbms/admin) on source 10g database
and check the output.

It displays warnings and recommends steps to clear the issues.

Time Zone Issue

select * from v$timezone_file;

------------ ----------
timezlrg.dat 3

If time zone file version is less than 4 then apply time zone patch 5632264

This can be done using opatch.
unzip the patch and run opatch going into the unzipped patch directory.

or manually copy the .dat files under 5632264/files/oracore/zoneinfo into

Bounce the database and check the TIMEZONE version.

Do not forget to take a backup of old zoneinfo directory before this.

Re-run utlu111i.sql after patching the database to record
the new timezone file version.

This time it says something like Database contains stale optimizer statistics.

Gather statistics


And other schemas which ever it recommends for.

Run the pre-upgrade utility once again to make sure,
you don’t get any warnings.

Copy initialization file (pfile) from source (10g) to target (11g)

Modify initialization parameters
background_dump_dest replaced by diagnostic_dest
user_dump_dest replaced by diagnostic_dest
core_dump_dest replaced by diagnostic_dest

comment above three deprecated parameters

and add

Change *.compatible=’10.2.0′

to *.compatible=’11.1.0′

Set Environment to new Oracle home

export ORACLE_HOME=/oraDB/app/oracle/product/11.1.0/db_1
export TNS_ADMIN=$ORACLE_HOME/network/admin/Context_Name

sqlplus '/as sysdba'

Startup the database in upgrade mode.

SQL> startup upgrade
SQL> SPOOL upgrade.log

SQL> @catupgrd.sql

This takes considerably a long time depending on the size of database.

Once the upgrade finishes,It will shut down the database automatically.
Login again as sysdba and startup in normal mode.

You will get NLS errors as nls directory under new oracle_home does not have 9idata directory

Run the $ORACLE_HOME/nls/data/old/ script to create the $ORACLE_HOME/nls/data/9idata directory.

I , actually copied the 9idata directory from 10g oracle_home to new location.

After creating the directory,

make sure that the ORA_NLS10 environment variable is set to the full path of the 9idata directory whenever you enable the 11g Oracle home.


Check the dba_registry for the components and its status

SQL> select comp_name,version, status from dba_registry;

Run Post-Upgrade Status Tool provides a summary of the upgrade


Perform upgrade actions that do not require the database to be in UPGRADE mode


Check for invalid objects

SQL> select object_name, owner, object_type from all_objects where status= ‘INVALID’;

Compile Invalids

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

Copy tnsnames.ora, listener.ora, sqlnet.ora and include file from source oracle_home to target oracle_home

This finishes upgrade of database to in E-Business Suite R12.

Further we have to upgrade the existing to using a patchset.

which I will discuss in my next post..

After Upgrade
Start the new database listener

Set the TNS_ADMIN environment variable to the directory where you created your listener.ora and tnsnames.ora files.

Run adgrants.sql
Copy $APPL_TOP/admin/adgrants.sql (adgrants_nt.sql for Windows) from the administration server node to the database server node.

$ sqlplus '/ as sysdba'
SQL> @adgrants.sql APPLSYS

Grant create procedure privilege on CTXSYS

Copy $AD_TOP/patch/115/sql/adctxprv.sql from
the administration server node to the database server node.
$ sqlplus apps/apps
SQL>@adctxprv.sql SYSTEM_password CTXSYS

Implement and run AutoConfig in the new database Oracle Home.

How to implement autoconfig in a new oracle_home ...coming up.....

No comments:

Post a Comment