Post 10G Upgrade Steps

ORACLE – 10g Apps 11i database - Post Upgrade Steps.

This document can be used to implement the post upgrade steps on migrating a 9i APPS database to 10g.

Post 10g Upgrade steps:

1.SET PARAMETERS AS PER METALINK DOCUMENT AND START 10g LISTENER.

Edit the initfinprod.ora file in the new ORACLE_HOME- 10g.

Add the following parameters which are required as per metalink document number 216205.1 (init.ora parameters for 11i databases)

_b_tree_bitmap_plans=FALSE

optimizer_secure_view_merging=FALSE

Remove the following entries – These presently exist in FINPROD so I have removed them but check the db being upgraded and remove parameters as mentioned in note above :

optimizer_features_enable

optimizer_index_caching

optimizer_index_cost_adj

large_pool_size

undo_retention=18000

Make sure UTL_FILE_DIR is identical to the 9i database- this is because the upgrade agent does not copy the full path over.

utl_file_dir = (/tmp)

compatible='10.2.0'

Shutdown immediate;

create spfile from pfile ;

Startup;

Show parameter compatible;

SQL> Show parameter compatible;

NAME TYPE VALUE

------

compatible string 10.2.0

SQL> show parameter spfile- To make sure spfile is in use.

NAME TYPE VALUE

------

spfile string /u01/finprod/finproddb/10.2/

db_1/dbs/spfilefinprod.ora

SQL>

Start database Listener-

Lsnrctl start LISTENER ( Or whatever your listener name is )

2.FIX KOREAN LEXERS

oraprod(DATABASE)@FRMBWDS03:cd $ORACLE_HOME/ctx/sample/script

oraprod(DATABASE)@FRMBWDS03:ls

drbgdml.sql drjobdml.sql drkorean.sql

oraprod(DATABASE)@FRMBWDS03:sqlplus "sys as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Feb 25 09:58:27 2008

Enter password:

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> @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_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_INCIDENTS_ALL_TL" finished.

reindexing : "CS"."CS_KB_ELEMENTS_TL" finished.

reindexing : "CS"."CS_KB_SOLN_CATEGORIES_TL" finished.

reindexing : "CS"."CS_INCIDENTS_ALL_TL" finished.

reindexing : "ICX"."ICX_QUESTIONS_TL" finished.

SQL>

3.Run ADGRANTS.sql script

cd $ORACLE_HOME

cp adgrants.sql from APPL_TOP/admin(finprod login) to $ORACLE_HOME.

cd /u01/finprod/finproddb/10.2/db_1 ( ORACLE_HOME )

cp /u01/finprod/finprodappl/admin/adgrants.sql .

Now run the job.

oraprod(DATABASE)@FRMBWDS03:sqlplus "/ as sysdba" @adgrants.sql APPLSYS

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Feb 25 10:08:57 2008

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Connected.

------

--- adgrants.sql started at 2008-02-25 10:08:57 ---

Creating PL/SQL profiler objects.

------

--- profload.sql started at 2008-02-25 10:08:57 ---

Package created.

Grant succeeded.

Synonym created.

Library created.

Package body created.

Testing for correct installation

SYS.DBMS_PROFILER successfully loaded.

PL/SQL procedure successfully completed.

------

--- profload.sql completed at 2008-02-25 10:09:12 ---

------

--- proftab.sql started at 2008-02-25 10:09:12 ---

Table dropped.

Sequence dropped.

Table created.

Comment created.

Table created.

Comment created.

Table created.

Comment created.

Sequence created.

------

--- profltab.sql completed at 2008-02-25 10:09:14 ---

PL/SQL procedure successfully completed.

Commit complete.

4.GRANT CREATE PROCEDURE PRIVILEGE TO CTXSYS

cd $AD_TOP/patch/115/sql

Note down path.

/u01/finprod/finprodappl/ad/11.5.0/patch/115/sql

Now login as oraprod ( database owner)

cd $ORACLE_HOME

cp /u01/finprod/finprodappl/ad/11.5.0/patch/115/sql/adctxprv.sql .

Now run following script- adctcprv.sql

Usage :

sqlplus apps/pwd @ adctxprv.sql <system password> CTXSYS

sqlplus apps/xxx @adctxprv.sql (system_pwd) CTXSYS

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Feb 25 10:27:16 2008

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Connecting to SYSTEM

Connected.

PL/SQL procedure successfully completed.

Commit complete.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

oraprod(DATABASE)@FRMBWDS03:

5. Edit the .profile for your database and apps login to make sure you are logging into the new 10g environment.

export PERL5LIB=$ORACLE_HOME/Apache/perl/lib/5.00503:/u01/finprod/finproddb/10.2/db_1/Apache/perl/lib/site_perl/5.005:/u01/finprod/finproddb/10.2/db_1/appsutil/perl

In ORACLE_HOME in dbtier- mkdir Apache.

Copy from FINPROD( apps owner) $IAS_ORACLE_HOME/Apache/perl directory to ORAPROD ( database owner) $ORACLE_HOME/Apache/perl

As ORAPROD:

cd $ORACLE_HOME/Apache > cp –rp $IAS_ORACLE_HOME/Apache/perl

cd /u01/finprod/finproddb/10.2/db_1/Apache

cp –rp /u01/finprod/finprodora/iAS/Apache/perl .

6. RUN AUTOCONFIG ON DB TIER.

Implement and run AutoConfig on the <10.2.0 ORACLE_HOME> as follows:

OFFICIAL Metalink Note Note:423056.1

1) Copy AutoConfig to the <10.2.0 ORACLE_HOME>

*** On the Application Tier (as the applmgr user):

a) Log in to the APPL_TOP environment and source the APPSORA.env file

b) Create appsutil.zip file. This will create appsutil.zip in $APPL_TOP/admin/out

$ perl $AD_TOP/bin/admkappsutil.pl

finprod(DATABASE)@frmbwds03: perl $AD_TOP/bin/admkappsutil.pl

Starting the generation of appsutil.zip

Log file located at /u01/finprod/finprodappl/admin/log/MakeAppsUtil_04240913.log

output located at /u01/finprod/finprodappl/admin/out/appsutil.zip

c) Copy or FTP the appsutil.zip file to the <10.2.0 ORACLE_HOME>

*** On the Database Tier (as the oracle user): Login as ORAPROD

$ cd <10.2.0 ORACLE_HOME>

$ . <CONTEXT_NAME>.env

$ cd $ORACLE_HOME

$ cp /u01/finprod/finprodappl/admin/out/appsutil.zip .

$ unzip -o appsutil.zip

RESTART LISTENER FROM 10g HOME –

vi listener.ora

FINPROD =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = frmbwds03.domain.com)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

SID_LIST_FINPROD =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /u01/finprod/finproddb/10.2/db_1)

(PROGRAM = extproc)

)

(SID_DESC =

(ORACLE_HOME= /u01/finprod/finproddb/10.2/db_1)

(SID_NAME = finprod)

)

2) Generate your Database Context File.

Execute the following commands to create your Database Context File:

Copy 9i finprod_frmbwds03.env from 9i ORACLE_HOME to 10g ORACLE_HOME

Edit the finprod_frmbwds03.env file and change all pointers to 9i ORACLE_HOME to point to 10g ORACLE_HOME.

chmod +x finprod_frmbwds03.env

$ cd <10.2.0 ORACLE_HOME>

$ . <CONTEXT_NAME>.env

$ cd <10.2.0 ORACLE_HOME>/appsutil/bin

$ perl adbldxml.pl tier=db appsuser=<APPS User> appspasswd=<APPS pwd>

export ORACLE_SID=finprod

cd $ORACLE_HOME

./ finprod_frmbwds03.env

$ cd appsutil/bin

$ pwd

/u01/finprod/finproddb/10.2/db_1/appsutil/bin

$ perl adbldxml.pl tier=db appsuser=apps appspass=harl3y

Starting context file generation for db tier..

Using JVM from /u01/finprod/finproddb/10.2/db_1/jre/1.4.2/bin/java to execute java programs..

The log file for this adbldxml session is located at:

/u01/finprod/finproddb/10.2/db_1/appsutil/log/adbldxml_04240927.log

The context file has been created at:

/u01/finprod/finproddb/10.2/db_1/appsutil/finprod_frmbwds03.xml

If the above step fails for any reason, just copy the finprod_frmbwds03.xml file from the 9i ORACLE_HOME/appsutil to the 10g ORACLE_HOME/appsutil and edit the file and make sure all pointers are to new 10g ORACLE_HOME in the file AND then run next step ignoring step above.

3) Generate and Apply AutoConfig Configuration files

Execute the following commands:

$ cd <10.2.0 ORACLE_HOME>/appsutil/bin

oraprod(DATABASE)@Frmbwds03:./adconfig.sh contextfile=/u03/findev/findevdb/10.2/db_1/appsutil/findev_frmbwas04.xml

Enter the APPS user password:

AutoConfig is configuring the Database environment...

AutoConfig will consider the custom templates if present.

Using ORACLE_HOME location : /u01/finprod/finproddb/10.2/db_1

Classpath : :/u01/finprod/finproddb/10.2/db_1/jdk/jre/lib/charsets.jar:/u01/finprod/finproddb/10.2/db_1/jdk/jre/lib/core.jar:/u01/finprod/finproddb/10.2/db_1/jdk/jre/lib/graphics.jar:/u01/finprod/finproddb/10.2/db_1/jdk/jre/lib/ibmcertpathprovider.jar:/u01/finprod/finproddb/10.2/db_1/jdk/jre/lib/ibmjaaslm.jar:/u01/finprod/finproddb/10.2/db_1/jdk/jre/lib/ibmjcefw.jar:/u01/finprod/finproddb/10.2/db_1/jdk/jre/lib/ibmjgssprovider.jar:/u01/finprod/finproddb/10.2/db_1/jdk/jre/lib/ibmjssefips.jar:/u01/finprod/finproddb/10.2/db_1/jdk/jre/lib/ibmjsseprovider.jar:/u01/finprod/finproddb/10.2/db_1/jdk/jre/lib/ibmorb.jar:/u01/finprod/finproddb/10.2/db_1/jdk/jre/lib/ibmorbapi.jar:/u01/finprod/finproddb/10.2/db_1/jdk/jre/lib/ibmpkcs.jar:/u01/finprod/finproddb/10.2/db_1/jdk/jre/lib/javaplugin.jar:/u01/finprod/finproddb/10.2/db_1/jdk/jre/lib/security.jar:/u01/finprod/finproddb/10.2/db_1/jdk/jre/lib/server.jar:/u01/finprod/finproddb/10.2/db_1/jdk/jre/lib/xml.jar:/u01/finprod/finproddb/10.2/db_1/jdk/lib/dt.jar:/u01/finprod/finproddb/10.2/db_1/jdk/lib/tools.jar:/u01/finprod/finproddb/10.2/db_1/jdbc/lib/ojdbc14.jar:/u01/finprod/finproddb/10.2/db_1/appsutil/java/xmlparserv2.zip:/u01/finprod/finproddb/10.2/db_1/appsutil/java:/u01/finprod/finproddb/10.2/db_1/jlib/netcfg.jar:/u01/finprod/finproddb/10.2/db_1/jlib/ldapjclnt10.jar

Using Context file : /u01/finprod/finproddb/10.2/db_1/appsutil/finprod_frmbwds03.xml

Context Value Management will now update the Context file

Updating Context file...COMPLETED

Attempting upload of Context file and templates to database...COMPLETED

Updating rdbms version in Context file to db102

Updating rdbms type in Context file to 64 bits

Configuring templates from ORACLE_HOME ...

AutoConfig completed successfully.

The log file for this session is located at: /u01/finprod/finproddb/10.2/db_1/appsutil/log/finprod_frmbwds03/04260831/adconfig.log

oraprod(DATABASE)@Frmbwds03:

7. RUN ADSTATS.SQL to GATHER STATS ON SYS TABLES

Copy from the APPL_TOP/admin folder the script adstats.sql to the ORACLE_HOME in the database tier.

cp /u01/finprod/finprodappl/admin/adstats.sql .

Sqlplus sys as sysdba and run adstats.sql to gather statistics on SYS owned tables.

This has to be run with the database in restricted mode.

$ sqlplus "/ as sysdba"
SQL> shutdown normal;
SQL> startup restrict;
SQL> @adstats.sql

SQL> spool arjun_adstats.log

SQL> @adstats.sql

Connected.

------

--- adstats.sql started at 2008-04-24 09:39:55 ---

Checking for the DB version and collecting statistics ...

PL/SQL procedure successfully completed.

------

--- adstats.sql ended at 2008-04-24 09:49:32 ---

Commit complete.

SQL> shutdown normal;
SQL> startup;

SQL> exit;

8.RE-CREATE GRANTS AND SYNONYMS IN THE APPS SCHEMA

AD Administration Main Menu

------

1. Generate Applications Files menu

2. Maintain Applications Files menu

3. Compile/Reload Applications Database Entities menu

4. Maintain Applications Database Entities menu

5. Change Maintenance Mode

6. Exit AD Administration

Enter your choice [6] :

ENTER 4

Maintain Applications Database Entities

------

1. Validate APPS schema

2. Re-create grants and synonyms for APPS schema

3. Maintain multi-lingual tables

4. Check DUAL table

5. Maintain Multiple Reporting Currencies schema

6. Return to Main Menu

Enter your choice [6] :

ENTER 2

AD utilities can support a maximum of 999 workers. Your

current database configuration supports a maximum of 243 workers.

Oracle recommends that you use between 8 and 16 workers.

Enter the number of workers [8] :

Recreating grants and synonyms needed by all installed applications...

sqlplus -s APPS/***** @/u01/finprod/finprodappl/fnd/11.5.0/admin/sql/afpub.sql &un_apps &pw_apps &un_pub &pw_pub

sqlplus -s APPS/***** @/u01/finprod/finprodappl/fnd/11.5.0/admin/sql/afsys.sql SYSTEM &systempwd &un_apps &un_fnd

sqlplus -s APPS/***** @/u01/finprod/finprodappl/ad/11.5.0/admin/sql/adpcpmsc.sql &un_fnd &pw_fnd &systempwd

sqlplus -s APPS/***** @/u01/finprod/finprodappl/ad/11.5.0/admin/sql/adinvmsc.sql &un_fnd &pw_fnd &systempwd

sqlplus -s APPS/***** @/u01/finprod/finprodappl/ad/11.5.0/admin/sql/adjvaprm.sql &un_apps &systempwd

0 running, 0 ready to run and 0 waiting.

Done maintaining grants and synonyms in APPS schema(s).

Telling workers to quit...

All workers have quit.

Dropping FND_INSTALL_PROCESSES table...

FND_INSTALL_PROCESSES table dropped.

Dropping AD_DEFERRED_JOBS table...

AD_DEFERRED_JOBS table dropped.

Review the messages above, then press [Return] to continue.

Enter your choice [6] : 6

ENTER 6

AD Administration Main Menu

------

1. Generate Applications Files menu

2. Maintain Applications Files menu

3. Compile/Reload Applications Database Entities menu

4. Maintain Applications Database Entities menu

5. Change Maintenance Mode

6. Exit AD Administration

Enter your choice [6] : 6

ENTER 6

Backing up restart files, if any...... Done.

sqlplus -s APPS/***** @/u01/finprod/finprodappl/ad/11.5.0/admin/sql/adtimrpt.s

ql 4964 adt04964

A job timing report has been generated for the current session.

You should check the file /u01/finprod/finprodappl/admin/finprod/out/adt04964.lst

for details.

Purging timing information for prior sessions.

sqlplus -s APPS/***** @/u01/finprod/finprodappl/ad/11.5.0/admin/sql/adtpurge.s

ql 10 1000

Done purging timing information for prior sessions.

AD Administration is complete.

Errors and warnings are listed in the log file:

/u01/finprod/finprodappl/admin/finprod/log/10g_upg.log and in other log files in the same directory.

9. RUN AUTOCONFIG IN BOTH APPS AND WEB TIERS .

cd $SCR_TOP

finprod(DATABASE)@FRMBWDS03: ./adautocfg.sh

Enter the APPS user password:

AutoConfig is configuring the Applications environment…

AutoConfig will consider the custom templates if present.

Using APPL_TOP location : /u01/finprod/finprodappl

Classpath : :/usr/java14/jre/lib/charsets.jar:/usr/jav

a14/jre/lib/core.jar:/usr/java14/jre/lib/graphics.jar:/usr/java14/jre/lib/ibmcer

tpathprovider.jar:/usr/java14/jre/lib/ibmjaaslm.jar:/usr/java14/jre/lib/ibmjcefw

.jar:/usr/java14/jre/lib/ibmjgssprovider.jar:/usr/java14/jre/lib/ibmjssefips.jar

:/usr/java14/jre/lib/ibmjsseprovider.jar:/usr/java14/jre/lib/ibmorb.jar:/usr/jav

a14/jre/lib/ibmorbapi.jar:/usr/java14/jre/lib/ibmpkcs.jar:/usr/java14/jre/lib/ja

vaplugin.jar:/usr/java14/jre/lib/security.jar:/usr/java14/jre/lib/server.jar:/us

r/java14/jre/lib/xml.jar:/usr/java14/lib/dt.jar:/usr/java14/lib/tools.jar:/u01/f

inhbosa/finprodcomn/java/appsborg2.zip:/u01/finprod/finprodcomn/java

Using Context file : /u01/finprod/finprodappl/admin/finprod_

FRMBWDS03.xml

Context Value Management will now update the Context file

Updating Context file…COMPLETED

Attempting upload of Context file and templates to database…COMPLETED

Configuring templates from all of the product tops…

Configuring AD_TOP….....COMPLETED

Configuring FND_TOP…....COMPLETED

Configuring ICX_TOP…....COMPLETED

Configuring IEO_TOP…....COMPLETED

Configuring ABM_TOP…....COMPLETED

Configuring ECX_TOP…....COMPLETED

Configuring BIS_TOP…....COMPLETED

Configuring GL_TOP….....COMPLETED

Configuring AMS_TOP…....COMPLETED

Configuring CCT_TOP…....COMPLETED

Configuring WSH_TOP…....COMPLETED

Configuring CLN_TOP…....COMPLETED

Configuring OKE_TOP…....COMPLETED

Configuring OKL_TOP…....COMPLETED

Configuring OKS_TOP…....COMPLETED

Configuring CSF_TOP…....COMPLETED

Configuring XNC_TOP…....COMPLETED

Configuring IGS_TOP…....COMPLETED

Configuring IBY_TOP…....COMPLETED

Configuring PA_TOP….....COMPLETED

Configuring JTF_TOP…....COMPLETED

Configuring MWA_TOP…....COMPLETED

Configuring CN_TOP….....COMPLETED

Configuring CSI_TOP…....COMPLETED

Configuring WIP_TOP…....COMPLETED

Configuring CSE_TOP…....COMPLETED

Configuring EAM_TOP…....COMPLETED

Configuring IMT_TOP…....COMPLETED

Configuring FTE_TOP…....COMPLETED

Configuring ONT_TOP…....COMPLETED

Configuring AR_TOP….....COMPLETED

Configuring AHL_TOP…....COMPLETED

Configuring OZF_TOP…....COMPLETED

Configuring IES_TOP…....COMPLETED

Configuring CSD_TOP…....COMPLETED

Configuring IGC_TOP…....COMPLETED

AutoConfig completed successfully.

The log file for this session is located at:

/u01/finprod/finprodappl/admin/finprod_FRMBWDS03/log/02260816/adconfig.log

finprod(DATABASE)@FRMBWDS03:

Run Autoconfig of Web Tier- - As FINPROD

cd $SCR_TOP

finprod(APPS)@FRMBWAS03:./adautocfg.sh

Enter the APPS user password:

AutoConfig is configuring the Applications environment...