Wednesday, September 19, 2007

Upgrade Oracle Applications 11i Database to 10g R2 (10.2.0.3)

In this post I’m going to describe step-by-step how to upgrade an Oracle Apps 11i Database 9i (9.2.0.6) to 10g R2 (10.2.0.3).
The upgrade was done on a single node environment installed on Red Hat Linux 4.

Technical information:
OS: RHL4
Apps version: 11.5.10.2
DB version: 9.2.0.6
ATG Rup 5 + AD.I.5

*** The following upgrade process suitable to the above starting point only!
*** For another OS or Apps version you can be helped by this post, but it's highly

*** recommended to read the related Notes mentions at the end of this post.

1) Apply patch 4653225 - 10g release 2 ineroperability patch for 11.5.10
a. Unzip patch file
b. Cd 4653225
c. Run adpatch.sh

2) Upgrade Developer 6i to the latest patch set
If your patch set level is earlier then patch set 17, you should apply the latest certified patch set (nowadays – 18).
You can use my previous post (step-by-step style) – “Upgrading Developer 6i with Oracle Apps 11i”.
And Metalink Note 125767.1 – “Upgrading Developer 6i with Oracle Applications 11i”.

3) Install 10g software
a. mkdir [rdbms_oracle_home] for the new 10g software (/oracle/tst10db/10.2)
b. Export ORACLE_HOME=[rdbms_oracle_home]
c. Unzip installation file - 10201_database_linux32.zip
d. cd database
e. Execute runInstaller with oracle user
f. Select Advanced Installation


g. Inventory directory location: /oracle/tst10db/10.2/oraInventory


h. Select Installation Type: “Enterprise Edition”


i. Home Details:
Name: OraDB10g_home
Path: /oracle/tst10db/10.2


j. Fix all errors if exists


k. Choose to Install Database Software only
l. Click on install


m. Run the following scripts with root


n. Exit

4) Install Oracle Database products from Oracle 10g Companion CD
a. Export ORACLE_HOME=[RDBMS_ORACLE_HOME]
b. Unzip installation file - 10201_companion_linux32.zip
c. cd companion
d. Execute runInstaller with oracle user


e. Select Oracle Database Products 10.2.0.1.0


f. Name and Path like database software installation.
Name: OraDB10g_home
Path: /oracle/tst10db/10.2


g. Fix errors if exists


h. Click on Install

i. Exit


5) Install 10.2.0.3 patch set
a. Export ORACLE_HOME=/oracle/tst10db/10.2
b. Export ORACLE_SID=tst10
c. unzip installation file - p5337014_10203_LINUX.zip
d. cd Disk1
e. Execute runInstaller with oracle user


f. Name and Path like in database installation
Name: OraDB10g_home
Path: /oracle/tst10db/10.2


g. Click on Install

h. Run root.sh

i. Exit installation


6) Create nls/data/9idata directory
a. Execute: perl $ORACLE_HOME/nls/data/old/cr9idata.pl
b. export ORACLE_HOME=/oracle/tst10db/10.2
c. export ORA_NLS10=$ORACLE_HOME/nls/data/9idata

7) Install 10g Listener
a. Stop 9i listener
b. Export ORACLE_HOME=/oracle/tst10db/10.2
c. cd $ORACLE_HOME/bin
d. execute ./netca (Network Configuration Assitant)
e. Name the listener TST10

8) Database Upgrade
a. Export ORACLE_HOME=/oracle/tst10/10.2
b. Export ORACLE_SID=tst10
c. cp /oracle/tst10/10.2/rdbms/admin/utlu102i.sql /tmp
d. cd /tmp
e. sqlplus “/as sysdba”
i. startup db
ii. spool info.log
iii. run $utlu102i.sql
iv. spool off
f. Check info.log for any errors
g. Add to initTST10.ora -> local_listener =’(ADDRESS= (PROTOCOL= TCP)(Host= servername.domain)(Port= 1521))’
h. Execute the Database Upgrade Assistant - $ORACLE_HOME/bin/dbua


i. Select the db to upgrade


j. SYSAUX tablespace should be on: /oracle/tst10data/sysaux01.dbf
Choose Automatically extend datafile when full (AUTOEXTEND).



k. It is strongly recommended to have a full cold backup of the database before upgrade.
You can choose to make the cold backup at this stage.



l. Enter passwords for system users.


m. Click on Finish


n. Check status of all upgraded components.


o. Close

9) Modify initialization parameters
Follow Metalink note 216205.1 - Database Initialization Parameters for Oracle Applications 11i.
Section 2.4 - Release-specific database initialization parameters for 10gR2 (10.2.X) + Removal list 10gR2 (10.2.X).

10) Fix Korean lexers
a. Connect to sqlplus as sysdba
b. execute $ORACLE_HOME/ctx/sample/script/drkorean.sql

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

12) Grant create procedure privilege on CTXSYS
a. Connect to sqlplus with apps
b. Execute @$AD_TOP/patch/115/sql/adctxprv [SYSTEM_PASSWORD] CTXSYS

13) Run Autoconfig on DB Tier and Application Tier
a. Log in to server with applmgr user
b. source /oracle/tst10appl/[context_name].env
c. perl $AD_TOP/bin/admkappsutil.pl
d. cp $APPL_TOP/admin/out/appsutil.zip /oracle/tst10db/10.2
e. Login to server with oracle user
f. cd /oracle/tst10db/10.2
g. unzip –o appsutil.zip
h. Copy the old database context file from 9.2.0 to 10.2
i. cd /oracle/livedb/10.2/appsutil/bin
j. Execute ./adconfig.sh
k. Login to server with applmgr user
l. Run autoconfig on application Tier

14) 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

15) 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

16) Apply Oracle receivables patch (5753621)
a. unzip patch file
b. cd 5753621
c. run adpatch


Related Documents:
1) Metalink Note 362203.1 - Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0).
2) Installation Guide for Linux x86
3) Oracle Database Upgrade Guide 10g Release 2 (10.2)


In the next post I will write about "Troubleshooting Oracle Apps Database upgrade to 10g".

You are welcome to leave a comment for questions, comments or just to say if it was helpul...

Aviad

26 comments:

Anonymous said...

Nice work. Wud perform the upgrade and comment again..

-Arun

Aviad said...

Thanks Arun.
Good Luck with the upgrade!

Aviad

Anonymous said...

Nice doc Aviad. I have a doubt. If my new oracle home lies in the same host as that of the old, do i need to give a new Inventory Location OR can i retail the old 9i inventory location ?

Thx
Sanjay

Aviad said...

Hi Sunjay,

You don't have to give a new Inventory location, you can use the same Inventory like the old 9i Inventory location.
When you will install the Oracle software it will check the location of the Global Inventory specified at oraInst.loc located in /etc or /var/opt/oracle (it depends on your platform).
If you won't change this file, it will automatically use the current Inventory for the new installation.

Regards,
Aviad

Anonymous said...

Hi,
My AD patcset level is 11i.AD.I.2

Can i follow the above steps ??

thx
VK

Aviad said...

Hi,

You can follow this post with your current AD.I level 2.
Even though, I recommend you to go over note 362203.1 - Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0).

Aviad

joe said...

Hi,

I wasn't sure about this step:
13) h. Copy the old database context file from 9.2.0 to 10.2

Why would you copy the old context file? It contains the old oracle home path.

Thanks.

Anonymous said...

Hi Aviad,

In step 13.h if we you will copy old context file to new OH 10g then context file will be pointing to 9.2.0 home. Re-creating context file using adbldxml will be a better idea.. What do you say?

Thanks

Aviad said...

Hi,

I'm totally agree with you both.
The better way is to use adbldxml.pl to create the new context file for 10g DB Tier
Anyway I checked my original notes and I saw that I actually used the old context file but before adconfig I changed all [OLD_ORACLE_HOME] to [NEW_ORALCLE_HOME].
In these days I'm upgrading an EBS environment to 10g with RAC (soon posting on this too) and I used adbldxml to create the new context file to the DB Tier.

Thanks for pointing this out.

Regards,
Aviad

appsdbanovice said...

hello aviad,
Firstly I would like to congratulate for explaining the database upgradation in this manner with screenshots.
And my doubt in the upgradation process comes here,i read that for database upgradation the portal(portal 3.0.9 is with apps11i) version should be upgraded to 10g application server. I am a bit confused over here whether to upgrade portal or not.

Thanks

Aviad said...

Hi,

As mentioned in Note: 423056.1 - "Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0)", Oracle Portal 3.0.9 is not certified to run on Oracle Database 10g.
In order to continue using Oracle Portal with Oracle EBS 11i, you must migrate Portal 3.0.9 to 10g.

Follow the following notes:
312349.1 - "Remove Oracle Portal 3.0.9 from E-Business Suite Release 11i"
233436.1 - "Installing Oracle Applications Server 10g with Oracle E-Business Suite Release 11i"

Good Luck!

Aviad

Anonymous said...

Dude,

very nice document.

Oracle said...

For a DB upgrade from 9.2 to 10.2, are there any ATG pre-reqs (none are showing on Metalink Note 3622203.1) – we are on RUP3 (I think) – and I am reading stuff that says that CPU2008 expects everyone to be on either RUP4, RUP5or RUP6?

Tks & Regards

kalyan said...

Hi Aviad

Thank you so so much for a document like this .My question is i have a multi node
db/conc mnr - db node and
Forms/Apache/Discoverer10g - Apps node

Now do i have to do this setups in application node as well or will that hold good if i do the above steps only in DB tier in mutli node setup.

Please advice me , as i have to start my upgrade at the earliest

Aviad said...

As far as I know there is no ATG pre requisite for 10g upgrade.
Anyway it is a good practice to apply the latest ATG and AD patchsets.

Aviad

Aviad said...

Hi Kalyan,

In a multi node setup, there are some steps should be done on the DB Tier only and some steps on Apps Tier (for example – the 10gR2 interoperabilty patch).
I strongly recommend you to read the relevant documents for each installation, since my post refers to single node setup.

Aviad

Anonymous said...

Great Job.
Its very rare to see post as good as this one.

Keep on going !

A DBA

Anonymous said...

Hi,
First of all, let me congratulate you for this very good documentation.

I have one question.

In Metalink Doc ID 362203.1 step 19, it said to Install Oracle data Mining and OLAP without the optional note which seems to mean that part was necessary.

I did not see it in your doc, is it actually optional or you have to do it whether or not you are using these features?

Thanks a lot for your help.

Aviad said...

This is an optional step.
If you are sure you are not using these products, you can skip this step.

You can check the existence and status of those products via select from dba_registry view.

Anonymous said...

Hi,

Great work.

In step 8) you are telling
i. startup db

I am not clear about this ?

Is this the source db you are talking about ?

Aviad said...

Yes, this is the source database you should startup in this step in order to check the database before upgrade.
The utlu102i.sql script will show you the steps you should do before upgrade.

Anonymous said...

hi Aviad,
a tiny thing,for step 12,it should be:@$AD_TOP/patch/115/sql/adctxprv manager CTXSYS

Anonymous said...

Hi Aviad,
Its a nice work creating such a document and sharing it with others.

I want to request Kalyan,

I'm in the process of upgrading my Oracle Apps.
Its a 2 node installation as you had quoted.

db/conc mnr - db node and
Forms/Apache/Discoverer10g - Apps node

Did U Upgrade ur setup.
How did u go about it.

Appreciate if you could share.


Regards,

Aviad said...

Thanks for paying attention.
I fixed it.

Anonymous said...

Hi,
when drkorean.sql is executed, I'm getting the following error.
@$ORACLE_HOME/ctx/sample/script/drkorean.sql
List of indexes that use KOREAN_LEXER as top level lexer:
List of indexes that use KOREAN_LEXER as a sub
lexer:
"CS"."CS_FORUM_MESSAGES_TL_N3"
"CS"."CS_FORUM_MESSAGES_TL_N4"
"CS"."CS_INCIDENTS_ALL_TL_N1"
"CS"."CS_KB_ELEMENTS_TL_N2"
"CS"."CS_KB_SETS_TL_N3"
"CS"."CS_KB_SOLN_CAT_TL_N1"
"CS"."SUMMARY_CTX_INDEX"
"ICX"."ICX_QUES_CTX"
Migrate KOREAN_LEXER to KOREAN_MORPH_LEXER
Rebuild all indexes that use korean lexer as top level lexer:
Reindex all documents that use KOREAN_LEXER as sub lexer
reindexing : "CS"."CS_FORUM_MESSAGES_TL" finished.
reindexing : "CS"."CS_FORUM_MESSAGES_TL" finished.
declare
*
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-50857: oracle error in dreii0fsh
ORA-01631: max # extents (505) reached in table CS.DR$CS_INCIDENTS_ALL_TL_N1$I
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_DDL", line 539
ORA-06512: at line 338


Again I submitted the sql.

SQL> @ORACLE_HOME/ctx/sample/script/drkorean.sql
List of indexes that use KOREAN_LEXER as top level lexer:
List of indexes that use KOREAN_LEXER as a sub lexer:
Migrate KOREAN_LEXER to KOREAN_MORPH_LEXER
Rebuild all indexes that use korean lexer as top level lexer:
Reindex all documents that use KOREAN_LEXER as sub lexer

How to fix it.

Aviad said...

Hi,

1. See "case 1" at Troubleshooting after Upgrade Oracle Applications 11i to 10g.

2. I'm not sure why it finishes successfully on 2nd run, but check max_extents for this table:

select max_extents from dba_tables where table_name='DR$CS_INCIDENTS_ALL_TL_N1$I';

and if necessary, increase it by:

alter table cs.DR$CS_INCIDENTS_ALL_TL_N1$I storage (maxextents NNNN);

HTH,
Aviad