Wednesday, January 21, 2009

Database upgrade from 9i to 10g for Oracle Applications

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.


+ Minimum Version of the database that can be directly upgraded to Oracle 10g Release 2 -> 10.2.X.X.X or -> 10.2.X.X.X or higher -> 10.2.X.X.X 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 -> -> 10.2.X.X.X
7.3.4 -> 8.1.7 -> -> 10.2.X.X.X
8.0.n -> 8.1.7 -> -> 10.2.X.X.X
8.1.n -> 8.1.7 -> -> 10.2.X.X.X

SQL> select banner from v$version;

Oracle9i Enterprise Edition Release - Production
PL/SQL Release - Production
CORE Production
TNS for Linux: Version - Production
NLSRTL Version - Production

Since in my case its, 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;

———- ————————————————————
—————————— ——————————
1 Default product group

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';


4) Apply either

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

11.5.10 E-Business Suite Consolidated Update 2
10g Release 2 interoperability patch for 11.5.10 (4653225)
1If 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/ 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
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
[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.


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 - Production on Sun Oct 14 04:30:52 2007

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


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 - Production on Sun Oct 14 04:33:09 2007

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

Connected to an idle instance.


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 00:55:04
JServer JAVA Virtual Machine VALID 00:06:53
Oracle XDK VALID 00:08:38
Oracle Database Java Packages VALID 00:00:44
Oracle Text VALID 00:01:20
Oracle XML Database VALID 00:02:50
Oracle Real Application Clusters INVALID 00:00:01
Oracle Data Mining VALID 00:00:30
OLAP Analytic Workspace VALID 00:00:25
OLAP Catalog VALID 00:01:32
Oracle OLAP API VALID 00:01:01
Oracle interMedia INVALID 00:09:37
Spatial INVALID 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 file.

perl /bin/

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

unzip the 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 tier=db appsuser= appspasswd=

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


While running you might encounter following issue.

[oracle@ocvmrh2081 bin]$ perl 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:
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:

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[]:

Enter Port of Database server[1521]:

and it will keep on asking the same questions.

The reason for such error is becasue 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 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 This should work.


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

cd $ORACLE_HOME/appsutils/bin contextfile= appspass=

This completes the migeration to 10g database.

Sunday, January 11, 2009

Enable Jserv Logging

While troubleshooting any 11i E-Business Suite Self Service Applications (SSWA) related error messages, to ask to enable debug logging in JServ configuration files.

The procedure to enable debug logging in JServ is to update following line in file $IAS_ORACLE_HOME/Apache/Jserv/etc/ from log=false to log=true, and then bounce Apache using:

$ cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME
$ stop
$ start

Reference : 

249669.1 - How To Collect Apache and Jserv Debugging Details For Applications 11i

After the bounce, we start seeing debug messages in the log file $IAS_ORACLE_HOME/Apache/Jserv/logs/jserv.log.


Thursday, January 8, 2009

Request View Log/Output shows "Page Cannot be Displayed"

One of my friend (S) working on a Production Issue and informed me today that users are unable to view the request log/output. When he clicks on the view log/output, FNDWR.exe is launched, but ends up with "page cannot be displayed". No information in the Apache access log too
OS :
Red Hat Enterprise Linux AS release 4 (Nahant)
Fix :
1) as APPLMGR Shutdown the Concurrent Manager
Example: $COMMON_TOP/admin/scripts/ apps/ stop
2) $cd $FND_TOP/bin
3) $mv FNDWRR.exe FNDWRR.bak
4) $ force=y "fnd FNDWRR.exe"
5) Copy the New FNDWRR.exe to the $OA_HTML/bin directory
6) Start the Concurrent Manager
Example: $common_top/admin/scripts/ apps/apps start

Reference :