DB UPGRADE 9206 TO 10203

System Overview
Version 11.5.10.2
Oracle Applications Vision Instance
OS: Redhat Linux 5 update 5
Type: Single Node instance
Applications OS User: appl11i
Database 9i OS User : db11i (same user for 9i and 10g)
9i Database Home = /d011/db11i/db/oaproddb/9.2.0
10g Database Home = /d011/db11i/db/oaproddb/10.2.0

Instance SID: DB11i
Host: demo
url:demo.conacent.com:8000
listener port:1521


Task – To Upgrade database from 9.2.0.6 to 10.2.0.3 for Oracle
Applications 11.5.10.2.


Pre Upgrade Steps

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


Upgrade Steps

   a> Use manual steps or dbua to upgrade the db from 9.2.0.6 to
      10.2.0.3. Manual upgrade is preferred in the industry

Post Upgrade Steps

   a> On the application tier generate appsutil.zip file
   b> Copy the appsutil.zip file generated on application tier to the         
      database tier inside 10g Oracle Home
   c> create a context file on database tier
   d> run autoconfig

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: After apply patch 5478710(before run autoconfig) change  LD_ASSUME_KERNEL TO XD_ASSUME_KERNEL BOTH db and appl
      than runs autocfg.

###########################################################################################################

1   Before  install 10g software to change
     vi /etc/redhat-release
    change:Red Hat Enterprise Linux ES release 4 (Nahant Update 2)

2.change inventory location

     #inventory_loc=/etc/oraInventory
     inventory_loc=/d011/db11i/db/oaproddb/10.2.0/inventory

3.change oratab location:
     cat oratab
    
       DB11i:/d011/db11i/db/oaproddb/10.2.0:N

4.Install 10 g software only

5. before  companios cd installion
  set variable in bash_profile


export KSH_VERSION='@(#)PD KSH v5.2.14 99/07/13.2'
#. /d011/db11i/db/oaproddb/10.2.0/DB11i_demo.env
export PATH


ORACLE_BASE=/d011/db11i/db/oaproddb
export ORACLE_BASE

ORACLE_HOME=/d011/db11i/db/oaproddb/10.2.0/
export ORACLE_HOME

6.Install Companions cd.

Note:  In a Product to install
      Oracle Database 10g Producst 10.2.0
       

7. Install 10g patchset3

   p5337014_10203_LINUX.zip

#####################################################################################################
1.Copy dbs,appsutil,admin,.env,network/admin in 9i to 10g.

2. Replace all the file with 9.2.0 to 10.2.0.

3. set .bash_profile to new oracle_home

4.  Runs create  perl cr9idata.pl

5.   Set ORA_NLS10

   vi .env($oracle_home)10g
  if test "db920" = "db101" -o "db920" != "db102"; then

6.Apply opatch in 10g (before upgrade)
   p5648872_10203_LINUX.zip

7.Make the below changes in initTEST.ora (10g)

#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.

8.shutdown 9i db and 9ilistener

9.set vi .bash_profile in new home(10g)

10.oratab set old home(9.2.0)

11. upgrade database

   a.Manual

    cd $ORACLE_HOME/bin

    sqlplus “/as sysdba”

     startup upgrade

12. Create sysaux tablespace

  CREATE TABLESPACE sysaux DATAFILE '/d01/oracle/singlenode/visdata/sysaux01.dbfSIZE 1000M REUSE
  EXTENT MANAGEMENT LOCAL
   SEGMENT SPACE MANAGEMENT AUTO
   ONLINE;

13.    Run catupgrade.sql
   cd $ORACLE_HOME/rdbms/admin
   @catupgrade.sql

  b. For dbua
     cd $ORACLE_HOME/bin
    ./dbua
############################################################################################################

Post Upgrade Steps:



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

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

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

 perl /a01/appl11i/appl/oaprodappl/ad/11.5.0/bin/admkappsutil.pl

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

$ORACLE_HOME/appsutil/bin
perl adbldxml.pl tier=db appsuser=apps appspasswd=apps

Note:copy jre folder in $ORACLE_HOME/appsutil to 10 oracle_home/appsutil/ than cretae context file

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

vi)start Application and Db

----------------------------------------------------------------------------------------------------------------
Login Page

-----------


   checking URL = http://demo.conacent.com:8000/OA_HTML/AppsLogin

   RW-50016: Error: - {0} was not created:
      File = {1}

apply opatch 5890966 for login page issue.