Author – A.

Kishore
http:/www.appsdba.info

Upgrade database to 10g from 9i for Oracle Application 11.5.10.2

System Overview
Version 11.5.10.2
Oracle Applications Vision Instance
OS: Redhat Linux update 5
Type: Single Node instance
Applications OS User: applmgrupd
Installed directory: /d01/oracle/singlenode
Database 9i OS User : oracleupd
9i Database Home = /d01/oracle/singlenode/testdb/9.2.0
10g Database Home = /d01/oracle/singlenode/testdb/10.2.0

Instance SID: TEST

Host: linux1

Task – To Upgrade 9.2.0.6 to 10.2.0.3 for Oracle Applications

11.5.10.2

Let us split this activity into 3 steps

1> Pre Upgrade
2> Upgrade
3> Post Upgrade

Pre Upgrade Steps

a> Apply the patches - 5478710, 4676589, 4653225
b> Create a new home for Oracle 10g
c> Install Oracle Database 10g. (Software Only).
d> Install the companion CD.
e> Install 10.2.0.3 patch set
f> Create nls/data/9idata directory
g> Install 10g Listener

Upgrade Steps
a> Use dbua to upgrade the db from 9.2.0.6 to 10.2.0.3

Post Upgrade Steps

a> Copy the .env file present in 9i Oracle Home to 10g oracle home
b> Change all the 9.2.0 reference to 10.2.0 reference
c> Restart the database again
d> On the application tier run the below command to generate
appsutil.zip file
e> Copy the appsutil.zip file generated on application tier to the
database tier inside 10g Oracle Home
f> create a context file on database tier
g> run autoconfig
 Author – A.Kishore
http:/www.appsdba.info

Pre Upgrade Steps

a> Patches Required – 5478710, 4676589, 4653225
5478710 - TXK (FND & ADX) AUTOCONFIG ROLLUP PATCH O (December 2006)
4676589 - ATG Roll up 4
4653225 - 11.5.10 INTEROP PATCH FOR 10GR2

Note - If your Developer patch set level is earlier then patch set 17, you should apply the latest
certified patch. Check - OracleMetalink Note:125767.1

To know the version -

Navigate to help -> About Oracle Applications in any forms session window.
Look for: Oracle Forms Version : 6.0.8.XX.X

6.0.8.24.x -> patchset 15

6.0.8.25.x -> patchset 16
6.0.8.26.x -> patchset 17

1> Apply patch 5478710 - TXK (FND & ADX) AUTOCONFIG ROLLUP PATCH O
(December 2006)
su - applmgrupd
cd patch_top
unzip 5478710
cd 5478710
cd fnd/patch/115/bin
./txkprepatchcheck.pl -script=ValidateRollup -
outfile=$APPLTMP/txkValidateRollup.html -appspass=apps

adpatch
Please enter the name of your AutoPatch driver file: u5478710.drv
# Takes 20 minutes

# After applying the patch

su - applmgrupd
$ADPERLPRG $AD_TOP/bin/admkappsutil.pl

# This will create appsutil.zip in $APPL_TOP/admin/out

su - oracleupd
# Copy or FTP the appsutil.zip file to the <RDBMS ORACLE_HOME>
cd $ORACLE_HOME
cp /d01/oracle/singlenode/testappl/admin/out/appsutil.zip .
unzip -o appsutil.zip
cd $ORACLE_HOME/appsutil/scripts/TEST_linux1
sh adautocfg.sh
 Author – A.Kishore
http:/www.appsdba.info

2> Apply ATG Roll up 4 – 4676589

-- Takes around 4 hours

su - applmgrupd
cd patch_top
unzip 4676589

cd 4676589
adpatch
u4676589.drv

3> Apply 11.5.10 INTEROP PATCH FOR 10GR2 - 4653225

-- Takes longtime because compiles all the object (utlrp.sql)

10 minutes

su - applmgrupd
cd patch_top
unzip p4653225_11i_LINUX.zip
cd 4653225
-- Apply the patch in non interactive mode
adpatch workers=4
defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt
logfile=4653225.log patchtop=$PATCH_TOP/4653225 driver=u4653225.drv

b> Create a new home for Oracle 10g

su – oracleupd
mkdir –p /d01/oracle/singlenode/testdb/10.2.0

c> Install Oracle Database 10g. (Software Only).

# To enable graphics for oracleupd user

su - root
vi /etc/X11/gdm/gdm.conf
DisallowTCP=false
init 6

# connect as root
xhost localhost

# connect as oracleupd
su - oracleupd
export ORACLE_HOME=/d01/oracle/singlenode/testdb/10.2.0
export DISPLAY=localhost:0.0

Make the below changes in sysctl.conf

vi /etc/sysctl.conf
# Default setting in bytes of the socket receive buffer
net.core.rmem_default=262144
 Author – A.Kishore
http:/www.appsdba.info

# Default setting in bytes of the socket send buffer

net.core.wmem_default=262144

# Maximum socket receive buffer size which may be set by using

# the SO_RCVBUF socket option
net.core.rmem_max=262144

# Maximum socket send buffer size which may be set by using

# the SO_SNDBUF socket option
net.core.wmem_max=262144

net.ipv4.ip_local_port_range = 1024 65000

sysctl –p will make the changes effective immediately

Unzip installation file - 10201_database_linux32.zip

cd database
Execute runInstaller with oracleupd user

Home Details:
Name: OraDB10g_home
Path: /d01/oracle/singlenode/testdb/10.2.0
Author – A.Kishore
http:/www.appsdba.info
Author – A.Kishore
http:/www.appsdba.info
 Author – A.Kishore
http:/www.appsdba.info

As root user
/d01/oracle/singlenode/testdb/10.2.0/root.sh
 Author – A.Kishore
http:/www.appsdba.info

d> Install Oracle Database products from Oracle 10g Companion CD

Unzip installation file - 10201_companion_linux32.zip

Connect as oracleupd user

su – oracleupd
export ORACLE_HOME=/d01/oracle/singlenode/testdb/10.2.0
./runInstaller
Author – A.Kishore
http:/www.appsdba.info
Author – A.Kishore
http:/www.appsdba.info
Author – A.Kishore
http:/www.appsdba.info
 Author – A.Kishore
http:/www.appsdba.info

e> Install 10.2.0.3 patch set

c. unzip installation file - p5337014_10203_LINUX.zip
d. cd Disk1
e. Execute runInstaller with oracleupd user

su - oracleupd
export ORACLE_HOME=/d01/oracle/singlenode/testdb/10.2.0
export ORACLE_SID=TEST

Name and Path like in database installation

Name: OraDB10g_home
Path: /d01/oracle/singlenode/testdb/10.2.0

Click on Install
Author – A.Kishore
http:/www.appsdba.info
 Author – A.Kishore
http:/www.appsdba.info

Run root.sh
/d01/oracle/singlenode/testdb/10.2.0/root.sh
Exit Installation

f) Create nls/data/9idata directory

export ORACLE_HOME=/d01/oracle/singlenode/testdb/10.2.0
perl $ORACLE_HOME/nls/data/old/cr9idata.pl
 Author – A.Kishore
http:/www.appsdba.info

Check whether files exist in $ORACLE_HOME/nls/data/9idata

else copy the files

$ cd $ORACLE_HOME/nls/data/9idata
$ cp $ORACLE_HOME/nls/data/*.nlb .
$ cp $ORACLE_HOME/nls/data/old/*.nlb .
cd $ORACLE_HOME/nls/data/9idata
ls – check whether files exist

Note:371885.1 Ora-12705: Cannot Access Nls Data Files Or Invalid Environment

Specified Ora-127
export ORA_NLS10=$ORACLE_HOME/nls/data/9idata

g) Install 10g Listener –

a. Stop 9i listener
su - oracleupd
lsnrctl stop TEST

export ORACLE_HOME=/d01/oracle/singlenode/testdb/10.2.0
export ORA_NLS10=$ORACLE_HOME/nls/data/9idata

cd $ORACLE_HOME/bin
./netca
Author – A.Kishore
http:/www.appsdba.info
 Author – A.Kishore
http:/www.appsdba.info

Listener Name - TEST

 Author – A.Kishore
http:/www.appsdba.info

Change the port number as it was in 9i database

Author – A.Kishore
http:/www.appsdba.info
 Author – A.Kishore
http:/www.appsdba.info

2) Database Upgrade Steps

-- Takes nearly 12~20 hours for a normally vision instance

su - oracleupd
export ORACLE_HOME=/d01/oracle/singlenode/testdb/10.2.0

# connect to 9i database
cp /d01/oracle/singlenode/testdb/10.2.0/rdbms/admin/utlu102i.sql /tmp
cd /tmp
sqlplus "/as sysdba"
spool info.log
@utlu102i.sql
spool off

See Appendix A for the output at the end of the document

# Execute the Database Upgrade Assistant - $ORACLE_HOME/bin/dbua

cp the existing initTEST.ora to $ORACLE_HOME/dbs/initTEST.ora

cp /d01/oracle/singlenode/testdb/9.2.0/initTEST.ora
/d01/oracle/singlenode/testdb/10.2.0/initTEST.ora

# Shutdown all the middle tier process on application tier, if they are started

# Shutdown the 9i listener and 9i database.

# connect to database using 10g Oracle Home

# Please do take a backup of database before running dbua

# At this point in time all the services are down only 10 listener is up

# Make the below changes in initTEST.ora

#optimizer_max_permutations
#row_locking
#undo_suppress_errors
#max_enabled_roles
#enqueue_resources
#sql_trace
streams_pool_size=50331648
large_pool_size = 8388608
session_max_open_files=20
optimizer_features_enable=10.2.0.3
compatible=10.2.0
 Author – A.Kishore
http:/www.appsdba.info

Follow Metalink note 216205.1 - Database Initialization Parameters for Oracle Applications 11i.

Note: Check that 9i environment file is not running

export ORACLE_SID=TEST
PATH=$PATH:$ORACLE_HOME/bin
cd $ORACLE_HOME/bin
dbua &

Note: If database is not visible, make entries in /etc/oratab as

follows

TEST:/d01/oracle/singlenode/testdb/9.2.0:N
Author – A.Kishore
http:/www.appsdba.info
Author – A.Kishore
http:/www.appsdba.info
Author – A.Kishore
http:/www.appsdba.info
Author – A.Kishore
http:/www.appsdba.info
Author – A.Kishore
http:/www.appsdba.info
Author – A.Kishore
http:/www.appsdba.info
 Author – A.Kishore
http:/www.appsdba.info

Post Upgrade Steps:

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

cp /d01/oracle/singlenode/testdb/9.2.0/TEST_linux1.env
/d01/oracle/singlenode/testdb/10.2.0

cd /d01/oracle/singlenode/testdb/10.2.0

b> Change all the 9.2.0 reference to 10.2.0 reference

Also, Make the below change in TEST_linux1.env

export ORA_NLS10=/d01/oracle/singlenode/testdb/10.2.0/nls/data/9idata

cat TEST_linux1.env >> /home/oracle/.bash_profile

Source the .env file.

c) Restart the database again.

sqlplus “/as sysdba”

shutdown immediate
startup

@/d01/oracle/singlenode/testdb/10.2.0/rdbms/admin/utlrp.sql

Some more fixes

i) Fix Korean lexers

a. Connect to sqlplus as sysdba
b. execute $ORACLE_HOME/ctx/sample/script/drkorean.sql

ii) Run adgrants.sql

a. Connect to sqlplus as sysdba
b. Execute @$APPL_TOP/admin/adgrants.sql APPLSYS

iii) Grant create procedure privilege on CTXSYS

a. Connect to sqlplus with apps
b. Execute @$AD_TOP/patch/115/sql/adctxprv manager CTXSYS
manager is the SYSTEM password

d) On the application tier run the below command to generate

appsutil.zip file.

perl /d01/oracle/singlenode/testappl/ad/11.5.0/bin/admkappsutil.pl

e) Copy the appsutil.zip file generated on application tier to the

database tier inside 10g Oracle Home.
su - oracleupd
cd $ORACLE_HOME
 Author – A.Kishore
http:/www.appsdba.info

cp /d01/oracle/singlenode/testappl/admin/out/appsutil.zip .
unzip appsutil.zip

copy the tnsnames.ora and listener.ora

cd $ORACLE_HOME/network/admin
mkdir TEST_linux1
cp tnsnames.ora TEST_linux1
cp listener.ora TEST_linux1

f) 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/bin
perl adbldxml.pl tier=db appsuser=apps appspasswd=apps

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

cd $ORACLE_HOME/appsutil/bin
./adconfig.sh
contextfile=/d01/oracle/singlenode/testdb/10.2.0/appsutil/TEST_linux1.x
ml appspass=apps

AutoConfig completed successfully.

The log file for this session is located at:
/d01/oracle/singlenode/testdb/10.2.0/appsutil/log/TEST_linux1/12130604/
adconfig.log
 Author – A.Kishore
http:/www.appsdba.info

Added few steps

i) Gather Statistics for SYS schema

a. sqlplus “/as sysdba”
b. shutdown immediate
c. startup restrict
d. @/oracle/tst10appl/admin/adstats.sql
e. shutdown
f. startup

ii) Recreate grants and synonym for apps

a. Log in to server with applmgr user
b. Execute adadmin
c. Choose -> Maintain Applications Database Entities menu
d. Choose -> Re-create grants and synonyms for APPS schema
 Author – A.Kishore
http:/www.appsdba.info

Issues and solutions

1> while applying ATG Roll up 4 patch – 4676589

Completed: file afwf.odf on worker 3 for product fnd username

APPLSYS.

ATTENTION: All workers either have failed or are waiting:

FAILED: file afcmgr.odf on worker 2.

Solution:
adctrl
Enter 1

Control
Worker Code Context Filename Status
------ -------- ---------------------- -------------------- -------
-------
1 Run AutoPatch R115 Wait
2 Run AutoPatch R115 afcmgr.odf FAILED
3 Run AutoPatch R115 Wait
4 Run AutoPatch R115 Wait

Go the below directory

cd $APPL_TOP/admin/VIS/log
vi adwork002.log

Found the below problem.

CREATE INDEX APPLSYS.FND_CONCURRENT_REQUESTS_N10 ON

APPLSYS.FND_CONCURRENT_REQUESTS (CD_ID) LOGGING PCTFREE 10 INITRANS
11
MAXTRANS 255 COMPUTE STATISTICS TABLESPACE APPS_TS_TX_IDX

AD Worker error:
The following ORACLE error:

ORA-00054: resource busy and acquire with NOWAIT specified

Ran the below script as system user

select s.sid SID, s.serial# SRL, s.username userName,

s.osuser, ob1.name Table_name,
decode(loc.lmode,1,'NULL', 2,'RowShare', 3,'RowExcl',
4,'Share', 5,'ShareRowExcl',
6,'Exclusive','None') lockmode,
nvl(s.terminal,s.machine) terminal,
decode(s.lockwait,null,'NO','YES') Wstat
from v$session s, v$lock loc, sys.obj$ ob1
where s.sid = loc.sid and loc.id1 = ob1.obj#
and table_name ='FND_CONCURRENT_REQUESTS' and username='APPS'
 Author – A.Kishore
http:/www.appsdba.info

alter sytem kill session 'sid,serial#'

adctrl
2. Tell worker to restart a failed job

Enter your choice [1] : 2

Enter the worker number(s)/range(s) or 'all' for all workers,

or press [Return] to go back to the menu : 2

2) 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/bin
perl adbldxml.pl tier=db appsuser=apps appspasswd=apps
-----------------------------------
Error
--------------------------------------
Enter Hostname of Database server: linux1.com

Enter Port of Database server: 1521

Enter SID of Database server: VIS

Could not Connect to the Database : Io exception: The Network Adapter

could not establish the connection

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

Solution – check whether 10g listener is up or not

-- In the second attempt I got a different error

Starting context file generation for db tier..

Using JVM from /d01/oracle/visdb/10.2.0/jre/1.4.2/bin/java to execute
java programs..

The log file for this adbldxml session is located at:

/d01/oracle/visdb/10.2.0/appsutil/log/adbldxml_12130539.log
AC-20010: Error: File - listener.ora could not be found at the
location:
/d01/oracle/visdb/10.2.0/network/admin/VIS_linux1/listener.ora
indicated by TNS_ADMIN. Context file can not be generated.

Solution - created the listener

 Author – A.Kishore
http:/www.appsdba.info

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

cd $ORACLE_HOME/appsutil/bin
./adconfig.sh
contextfile=/d01/oracle/visdb/10.2.0/appsutil/VIS_linux1.xml
appspass=apps

got error -
ERROR:
ORA-12705: Cannot access NLS data files or invalid environment
specified

cd /d01/oracle/visdb/10.2.0/nls/data/9idata/
[oracle@linux1 9idata]$ ls
[oracle@linux1 9idata]$ perl $ORACLE_HOME/nls/data/old/cr9idata.pl
Directory /d01/oracle/visdb/10.2.0/nls/data/9idata already exist.
Overwriting...
Copying files to /d01/oracle/visdb/10.2.0/nls/data/9idata...
Copy finished.
Please reset environment variable ORA_NLS10 to
/d01/oracle/visdb/10.2.0/nls/data/9idata!
[oracle@linux1 9idata]$ cd /d01/oracle/visdb/10.2.0/nls/data/9idata/
[oracle@linux1 9idata]$ ls
lx00001.nlb lx1000c.nlb lx1004e6.nlb lx20052.nlb lx200e6.nlb
lx20348.nlb lx30024.nlb

export ORA_NLS10=/d01/oracle/visdb/10.2.0/nls/data/9idata

cd $ORACLE_HOME/appsutil/bin
./adconfig.sh
contextfile=/d01/oracle/visdb/10.2.0/appsutil/VIS_linux1.xml
appspass=apps

AutoConfig completed successfully.

The log file for this session is located at:
/d01/oracle/visdb/10.2.0/appsutil/log/VIS_linux1/12130604/adconfig.log

Make the below changes in the environment file

export ORA_NLS10=/d01/oracle/visdb/10.2.0/nls/data/9idata
 Author – A.Kishore
http:/www.appsdba.info

Appendix A