Upgrading Database to 10g for Oracle Applications 11i

For Oracle Applications 11i installation, the database version that comes is 9.2.0
This post is to give a brief and clear steps for upgrading your database to 10.2.0

Instance Details

Here are the details for the Oracle Application I am trying on

Oracle Application Version: 11.5.10 CU2
Oracle Database Version: 9.2.0

Since its a 11.5.10 CU2, its autoconfig enabled. So we have to make sure that we need to make new Oracle Home also autoconfig enabled. For more information on autoconfig, please refer to metalink note ID 165195.1

The below steps are summarized from metalink note ID 423056.1.

To begin with I will briefly explain the procedure in 3 major steps.

1) Pre-Upgrade
2) Upgrade
3) Post-Upgrade

Pre-Upgrade Step:

1) Check the current database version:

For upgrading the database, you need to check the exact version of the current database and based on that check the upgrade path.

Below is the brief compatibility matrix. Reference Metalink note ID 316889.1.

COMPATIBILITY MATRIX
====================

+ Minimum Version of the database that can be directly upgraded to Oracle 10g Release 2
8.1.7.4 -> 10.2.X.X.X
9.0.1.4 or 9.0.1.5 -> 10.2.X.X.X
9.2.0.4 or higher -> 10.2.X.X.X
10.1.0.2 or higher -> 10.2.X.X.X

+ The following database version will require an indirect upgrade path.
7.3.3 (or lower) -> 7.3.4 -> 8.1.7 -> 8.1.7.4 -> 10.2.X.X.X
7.3.4 -> 8.1.7 -> 8.1.7.4 -> 10.2.X.X.X
8.0.n -> 8.1.7 -> 8.1.7.4 -> 10.2.X.X.X
8.1.n -> 8.1.7 -> 8.1.7.4 -> 10.2.X.X.X

SQL> select banner from v$version;

BANNER
—————————————————————-
Oracle9i Enterprise Edition Release 9.2.0.5.0 – Production
PL/SQL Release 9.2.0.5.0 – Production
CORE 9.2.0.6.0 Production
TNS for Linux: Version 9.2.0.5.0 – Production
NLSRTL Version 9.2.0.5.0 – Production

Since in my case its 9.2.0.5, I can directly upgrade to 10.2.0

2) Check the application version:

Oracle Application version should be above 11.5.9 (11.5.10 CU2 in my case).

SQL> SELECT product_group_id GroupID, product_group_name GroupName,
release_name Release, product_group_type GroupType, argument1
FROM fnd_product_groups;

GROUPID GROUPNAME
———- ————————————————————
RELEASE
————————————————–
GROUPTYPE ARGUMENT1
—————————— ——————————
1 Default product group
11.5.10
Standard

Check the developer 6i Patch set.

3) Check the latest autoconfig patch:

Check if the autoconfig latest patch is applied to your environment or not. Metalink note ID 165195.1
The latest patch mention in this note is 5985992. So if this patch is not applied then please apply this patch on application system.

SQL> select count(*) from ad_bugs
2 where bug_number = ‘5985992’;

COUNT(*)
———-
1

4) Apply either

• Oracle Applications Release 11.5.9 (without Oracle Demand Planning)¹
• 11.5.9 E-Business Suite Consolidated Update 2
• 10g Release 2 interoperability patch for 11.5.9 (4653217)

or

• 11.5.10 E-Business Suite Consolidated Update 2
• 10g Release 2 interoperability patch for 11.5.10 (4653225)

¹If you are using Oracle Demand Planning, your Applications version must be 11.5.10 in order to use Oracle Database 10g Release 2. The 11.5.9 version of Oracle Demand Planning is not certified against Oracle Database 10g Release 2.Upgrade Step:

1) Install Oracle Database 10g. (Software Only).

2) Since you are upgrading the 9i database to 10g database, you have to install 10g software to some different Oracle Home. So create a new Oracle Home 10.2.0 and install the software in that location.

Example if the currect 9i Oracle home location is

9i Oracle Home: /u01/app/oracle/apps11idb/9.2.0
10g Oracle Home: /u01/app/oracle/apps11idb/10.2.0

3) Create nls/data/9idata directory:

This can be done using $ORACLE_HOME/nls/data/old/cr9idata.pl script
Here ORACLE_HOME refers to 10g ORACLE_HOME.

[oracle@ocvmrh2064 old]$ export ORACLE_HOME=/u01/app/oracle/apps11idb/10.2.0
[oracle@ocvmrh2064 old]$ perl cr9idata.pl
Creating directory //u01/app/oracle/apps11idb/10.2.0/nls/data/9idata …
Copying files to //u01/app/oracle/apps11idb/10.2.0/nls/data/9idata…
Copy finished.
Please reset environment variable ORA_NLS10 to //u01/app/oracle/apps11idb/10.2.0/nls/data/9idata!
[oracle@ocvmrh2064 old]$ export ORA_NLS10=/u01/app/oracle/apps11idb/10.2.0/nls/data/9idata

4) Next Step is to perform database upgrade.

Reference used is the upgrade docs

a) copy the script utlu102i.sql provided in new Oracle Home 10g to some temporary location and run the same in your existing 9i database.

[oracle@ocvmrh2064 admin]$ pwd
/u01/app/oracle/apps11idb/10.2.0/rdbms/admin
[oracle@ocvmrh2064 admin]$ cp utlu102i.sql /tmp/

**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
WARNING: –> “streams_pool_size” is not currently defined and needs a value of
at least 50331648
WARNING: –> “large_pool_size” needs to be increased to at least 8388608
WARNING: –> “session_max_open_files” needs to be increased to at least 20

copy the init.ora parameter file from old ORACLE_HOME to new ORACLE_HOME

Set the above parameters in the new init.ora parameter file you copied to 10g ORACLE_HOME

**********************************************************************
SYSAUX Tablespace:
[Create tablespace in the Oracle Database 10.2 environment]
**********************************************************************
–> New “SYSAUX” tablespace
…. minimum required size for database upgrade: 500 MB

SQL> create tablespace sysaux datafile ‘/u01/app/oracle/apps11idata/sysaux01.dbf’ size 1000M
2 extent management local segment space management auto;

Tablespace created.

TIMESTAMP WITH TIMEZONE Datatype:

The time zone files that are supplied with Oracle Database 10g have been updated from version 1 to version 2 to reflect changes in transition rules for some time zone regions. The changes may affect existing data of TIMESTAMP WITH TIME ZONE datatype. To preserve this TIMESTAMP data for updating according to the new time zone transition rules, you must run the utltzuv2.sql script on the database before upgrading. This script is located in the new 10.2 ORACLE_HOME/rdbms/admin directory. This script analyzes your database for TIMESTAMP WITH TIME ZONE columns that are affected by the updated time zone transition rules.

If the utltzuv2.sql script identifies columns with time zone data affected by a database upgrade, then back up the data in character format before you upgrade the database. After the upgrade, you must update the tables to ensure that the data is stored based on the new rules. If you export the tables before upgrading and import them after the upgrade, the conversion will happen automatically during the import.

SQL> @?/../10.2.0/rdbms/admin/utltzuv2.sql
DROP TABLE sys.sys_tzuv2_temptab
*
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

Query sys.sys_tzuv2_temptab Table to see if any TIMEZONE data is affected by
version 2 transition rules

PL/SQL procedure successfully completed.

Commit complete.

SQL> select * from sys.sys_tzuv2_temptab;

no rows selected

b) Shutdown the database and start it again. Run the pre-upgrade utility again to check if every thing is alright before staring the upgrade.

c) Shutdown all the process on mid tier and database tier

d) Shutdown the listener process for 9i database.

e) connect to database using 10g Oracle Home.

[oracle@ocvmrh2064 9.2.0]$ export ORACLE_HOME=/u01/app/oracle/apps11idb/10.2.0
[oracle@ocvmrh2064 9.2.0]$ sqlplus “/as sysdba”

Message 133 not found; product=SQLPlus; facility=SP2
: Release 9.2.0.5.0 – Production on Sun Oct 14 04:30:52 2007

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

SQL>

You might face the issue mentioned above. This is becase the PATH variable is not having the new ORACLE_HOME/bin path.

[oracle@ocvmrh2064 9.2.0]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@ocvmrh2064 9.2.0]$ sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Sun Oct 14 04:33:09 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL>

f) Start the upgrade process

SQL> startup upgrade
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 591396864 bytes
Fixed Size 1220700 bytes
Variable Size 415240100 bytes
Database Buffers 163577856 bytes
Redo Buffers 11358208 bytes
Database mounted.
Database opened.
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql

Oracle Database 10.2 Upgrade Status Utility 10-14-2007 06:32:36
.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.1.0 00:55:04
JServer JAVA Virtual Machine VALID 10.2.0.1.0 00:06:53
Oracle XDK VALID 10.2.0.1.0 00:08:38
Oracle Database Java Packages VALID 10.2.0.1.0 00:00:44
Oracle Text VALID 10.2.0.1.0 00:01:20
Oracle XML Database VALID 10.2.0.1.0 00:02:50
Oracle Real Application Clusters INVALID 10.2.0.1.0 00:00:01
Oracle Data Mining VALID 10.2.0.1.0 00:00:30
OLAP Analytic Workspace VALID 10.2.0.1.0 00:00:25
OLAP Catalog VALID 10.2.0.1.0 00:01:32
Oracle OLAP API VALID 10.2.0.1.0 00:01:01
Oracle interMedia INVALID 10.2.0.1.0 00:09:37
Spatial INVALID 10.2.0.1.0 00:09:24
.
Total Upgrade Time: 01:52:15

g) After the upgrade is over, there will be many invalid objects, which we have to compile. Please use utlrp.sql to compile the invalids.

Once the invalids are compiled, the next step is to make the database autoconfig enabled. This are brifly explaind in post upgrade section.

Post Upgrade Steps:

1) Copy the .env file present in 9i Oracle Home to 10g oracle home.

2) Change all the 9.2.0 reference to 10.2.0 reference.
Source the .env file.

3) Restart the database again.

4) On the application tier run the below command to generate appsutil.zip file.

perl <AD_TOP>/bin/admkappsutil.pl

5) Copy the appsutil.zip file generated on application tier to the database tier inside 10g Oracle Home.

unzip the appsutil.zip file.

6) Next step is to create a context file on database tier. Run the below commands with appropriate values. This will generate context file on database tier.

cd $ORACLE_HOME/appsutil

perl adbldxml.pl tier=db appsuser=<APPSuser> appspasswd=<APPSpwd>

Please note that above oracle home we are selecting is 10g Oracle home.

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

While running adbldxml.pl you might encounter following issue.

[oracle@ocvmrh2081 bin]$ perl adbldxml.pl tier=db appsuser=apps appspasswd=apps

Starting context file generation for db tier..
Using JVM from /u01/app/oracle/proddb/10.2.0/jdk/bin/java to execute java programs..

The log file for this adbldxml session is located at:
/u01/app/oracle/proddb/10.2.0/appsutil/log/adbldxml_02172131.log
UnsatisfiedLinkError exception loading native library: njni10

Could not Connect to the Database with the above parameters, Please answer the Questions below

Enter Hostname of Database server: OCVMRH2081.us.oracle.com

Enter Port of Database server: 1521

Enter SID of Database server: PROD

Could not Connect to the Database : ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory

Could not Connect to the Database with the above parameters, Please answer the Questions below

Enter Hostname of Database server[OCVMRH2081.us.oracle.com]:

Enter Port of Database server[1521]:

and it will keep on asking the same questions.

The reason for such error is becasue adbldxml.pl is not able to make connection to your database. This is because of listener issue.
– If you do “echo $TNS_ADMIN”, it will point to your 9i ORACLE_HOME.
– Now what you have to do is to copy the <SID_hostname> directory present under 9i ORACLE_HOME/network/admin to new 10g ORACLE_HOME/netwrok/admin
– Make all the references to 9i ORACLE_HOME to 10g ORACLE_HOME in listener.ora
– make sure that listener started from 9i ORALCE_HOME is stopped from 9i ORACLE_HOME only
– Start the listner from 10g ORACLE_HOME. Then try running adbldxml.pl. This should work.

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

7) Once the context file is created on db tier, run autoconfig.

cd $ORACLE_HOME/appsutils/bin

adconfig.sh contextfile=<CONTEXT> appspass=<APPSpwd>

This completes the migeration to 10g database.