Oracle DB Upgrade from 9.2.0.6 to 10.2.0.2

Patching before Upgrading Database from 9.2.0.6 to 10.2.0.2

Step-1

Apply Developer 6i Patchset version 19.

Step-2

Apply TXK (FND & ADX) AUTOCONFIG ROLLUP PATCH p9535311_11i_LINUX.zip

Generate appsutil.zip and ftp to RDBMS OH.

unzip and run autoconfig

Features & Enhancements

Step-3

Apply AD.I.7 Patch p7429271_11i_LINUX.zip from AD.I.2

Step-4

Apply Patch p5989593_11i_LINUX.zip

Step-5

Apply Patch p3218526_11i_GENERIC.zip

Step-6

Apply Patch p4206794_11i_GENERIC.zip

Step-7

Apply Patch p3854951_11i_LINUX.zip

Step-7a

Apply Patch p5194357_11i_GENERIC.zip

Step-8

Patch 11i.ATG_PF.H.delta.5: 11i.ATG_PF.H.RUP p6241631_11i_LINUX.zip

select count(*) from dba_objects where status like ‘INVALID’;

Will take 4-5Hrs

Meanwhile we can install Oracle 10g and patch it.

Unzip 10201_database_linux32.zip and install

unzip Companion Cd 10201_companion_linux32.zip and install

Apply patch for 10.2.0.3 unzip p5337014_10203_LINUX.zip and install

Step-9

Apply Patch 4653225 – 11.5.10 INTEROP PATCH FOR 10GR2

Step-10

Apply additional patches for Invalid Objects.

i.p4619025_11i_GENERIC.zip

ii.p5194357_11i_GENERIC.zip

Step-11

After Applying Patches run adadmin and select recreate grants and synonyms

and complie apps schema.

Suceesfully Completed Pre Upgradation Patching Process

Upgradation

————-

Step-1

Install Oracle 10g

Install Companion CD

Apply 10.2.0.3 patch Set

Step-2

Configuration

Create nls/data/9idata directory

perl $ORACLE_HOME/nls/data/old/cr9idata.pl

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

cd $ORACLE_HOME/nls/data/9idata

if not then issue below commands

$ cp $ORACLE_HOME/nls/data/*.nlb .

$ cp $ORACLE_HOME/nls/data/old/*.nlb .

export ORA_NLS10=$ORACLE_HOME/nls/data/9idata

Step-3

Install 10g Listener

i.stop 9i listener

ii.run netca from Oracle 10g home and create new listener.

iii.copy cp /d01/oracle/VIS/db/tech_st/10.2.0/rdbms/admin/utlu102i.sql /tmp/

iv. in 9i run this script to generate a report.

Note: at this time 10g listener and 9i database is up and running.

*************************************************************************************

SQL> @utlu102i.sql

Oracle Database 10.2 Upgrade Information Utility 12-13-2010 13:48:09.

**********************************************************************

Database:

**********************************************************************

–> name: VIS

–> version: 9.2.0.6.0

–> compatible: 9.2.0

–> blocksize: 8192.

**********************************************************************

Logfiles: [make adjustments in the current environment]

**********************************************************************

–> The existing log files are adequate. No changes are required.

**********************************************************************

Tablespaces: [make adjustments in the current environment]

**********************************************************************

–> SYSTEM tablespace is adequate for the upgrade.

…. minimum required size: 8056 MB

–> APPS_UNDOTS1 tablespace is adequate for the upgrade.

…. minimum required size: 469 MB

–> TEMP tablespace is adequate for the upgrade.

…. minimum required size: 58 MB

–> APPS_TS_QUEUES tablespace is adequate for the upgrade.

…. minimum required size: 567 MB

–> APPS_TS_TX_DATA tablespace is adequate for the upgrade.

…. minimum required size: 10457 MB

–> ODM tablespace is adequate for the upgrade.

…. minimum required size: 15 MB

–> OLAP tablespace is adequate for the upgrade.

…. minimum required size: 32 MB.

**********************************************************************

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.

**********************************************************************

Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]

**********************************************************************

– No renamed parameters found. No changes are required.

**********************************************************************

Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]

**********************************************************************

–> “optimizer_max_permutations”

–> “row_locking”

–> “undo_suppress_errors”

–> “max_enabled_roles”

–> “enqueue_resources”

–> “sql_trace”.

**********************************************************************

Components: [The following database components will be upgraded or installed]

**********************************************************************

–> Oracle Catalog Views [upgrade] VALID

–> Oracle Packages and Types [upgrade] VALID

–> JServer JAVA Virtual Machine [upgrade] VALID

…The ‘JServer JAVA Virtual Machine’ JAccelerator (NCOMP)

…is required to be installed from the 10g Companion CD.

–> Oracle XDK for Java [upgrade] VALID

–> Oracle Java Packages [upgrade] VALID

–> Oracle Text [upgrade] VALID

–> Oracle XML Database [install]

–> Real Application Clusters [upgrade] INVALID

–> Oracle Data Mining [upgrade] VALID

–> OLAP Analytic Workspace [upgrade] UPGRADED

–> OLAP Catalog [upgrade] VALID

–> Oracle OLAP API [upgrade] UPGRADED

–> Oracle interMedia [upgrade] VALID

…The ‘Oracle interMedia Image Accelerator’ is

…required to be installed from the 10g Companion CD.

–> Spatial [upgrade] VALID.

**********************************************************************

Miscellaneous Warnings

**********************************************************************

WARNING: –> Passwords exist in some database links.

…. Passwords will be encrypted during the upgrade.

…. Downgrade of database links with passwords is not supported.

WARNING: –> Deprecated CONNECT role granted to some user/roles.

…. CONNECT role after upgrade has only CREATE SESSION privilege.

WARNING: –> Database contains stale optimizer statistics.

…. Refer to the 10g Upgrade Guide for instructions to update

…. statistics prior to upgrading the database.

…. Component Schemas with stale statistics:

…. SYS

…. ODM

…. OLAPSYS

…. MDSYS.

**********************************************************************

SYSAUX Tablespace:

[Create tablespace in the Oracle Database 10.2 environment]

**********************************************************************

–> New “SYSAUX” tablespace

…. minimum required size for database upgrade: 500 MB****

Miscellaneous Warnings

**********************************************************************

WARNING: –> Passwords exist in some database links.

…. Passwords will be encrypted during the upgrade.

…. Downgrade of database links with passwords is not supported.

WARNING: –> Deprecated CONNECT role granted to some user/roles.

…. CONNECT role after upgrade has only CREATE SESSION privilege.

WARNING: –> Database contains stale optimizer statistics.

…. Refer to the 10g Upgrade Guide for instructions to update

…. statistics prior to upgrading the database.

…. Component Schemas with stale statistics:

…. SYS

…. ODM

…. OLAPSYS

…. MDSYS.

**********************************************************************

SYSAUX Tablespace:

[Create tablespace in the Oracle Database 10.2 environment]

**********************************************************************

–> New “SYSAUX” tablespace

…. minimum required size for database upgrade: 500 MB.

PL/SQL procedure successfully completed.

**************************************************************************************

Step-4

——–

Copy the existing initTEST.ora(9i) to $ORACLE_HOME/dbs/initTEST.ora(10g)

Modify initTEST.ora for the below parameters

user_dump_dest =

/

background_dump_dest =

core_dump_dest =

utl_file_dir =

/VIS_linux1,/usr/tmp

# 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

Step-5

Copy the 9i environment file and modify it

Step-6

Copy the 9i network folder to 10g to get tnsnames.ora and

listener.ora

Step-7

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

# Shutdown the 9i listener and 9i database.

# Please do take a backup of database before running manual update or dbua

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

SQL> startup upgrade

cd $ORACLE_HOME

. ./VIS_linux1.env

Create sysaux tablespace

CREATE TABLESPACE sysaux DATAFILE ‘/d01/oracle/devdbdata/sysaux01.dbf’

SIZE 1000M REUSE

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO

ONLINE;

cd $ORACLE_HOME/rdbms/admin

startup upgrade

SPOOL upgrade.log

@catupgrd.sql

SPOOL OFF

SHUTDOWN IMMEDIATE

STARTUP

@utlrp.sql

Oracle Database 10.2 Upgrade Status Utility 12-13-2010 15:55:38.

Component Status Version HH:MM:SS

Oracle Database Server VALID 10.2.0.3.0 00:30:11

JServer JAVA Virtual Machine VALID 10.2.0.3.0 00:03:17

Oracle XDK VALID 10.2.0.3.0 00:06:51

Oracle Database Java Packages VALID 10.2.0.3.0 00:00:17

Oracle Text VALID 10.2.0.3.0 00:00:42

Oracle XML Database VALID 10.2.0.3.0 00:00:54

Oracle Real Application Clusters INVALID 10.2.0.3.0 00:00:01

Oracle Data Mining VALID 10.2.0.3.0 00:00:14

OLAP Analytic Workspace VALID 10.2.0.3.0 00:00:24

OLAP Catalog VALID 10.2.0.3.0 00:00:49

Oracle OLAP API VALID 10.2.0.3.0 00:00:24

Oracle interMedia VALID 10.2.0.3.0 00:04:07

Spatial VALID 10.2.0.3.0 00:02:42

Step-8

——–

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

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

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

database tier inside 10g Oracle Home.

su – oracleupd

cd $ORACLE_HOME

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

unzip appsutil.zip

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

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

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

database tier inside 10g Oracle Home.

su – oracleupd

cd $ORACLE_HOME

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

unzip appsutil.zip

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

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