Upgrade OA 11.5.8 to 11.5.10.2

Posted in Upgrade on September 18, 2008 by sunil

Apps Upgrade from 11.5.8 to 11.5.10.2,

Shared Application Tier file system conversion,

Database upgrade from

9.2.0.7 – 64 Bit to 10.2.03 – 64 bit

ATG_PF

Version 1.2

1 General

1.1 Problem Statement

There is no certified document which outlines the procedure for upgrading Foxtrot instance from 11.5.8 to 11.5.10.2, database from 9.2.0.7 – 64 bit to 10.2.0.3 – 64 bit and apply security fixes from April 2005 till October 2007.

1.2 Objective

Design and develop the procedure, which could assist the Project Team in

· Upgrading the Oracle Application from 11.5.8 to 11.5.10.2

· Upgrade Database from 9.2.0.7 – 64 bit to 10.2.0.3 – 64 bit

· Apply ATG_PF RUP5

· Apply security fixes from April 2005 till October 2007.

1.3 Project Team

Foxtrot – Evolution project team

1.4 Key Milestones

2 Upgrade Apps from 11.5.8 to 11.5.10.2

2.1 Apply TUMS Patch

· Apply patch 4238286 using adpatch on the admin node.

2.2 Run TUMS Script

Reference:

· Ensure that the database parameter UTL_FILE_DIR is set to a writable directory <DIRECTORY> where the TUMS for 11.5.10 Maintenance Pack report will be created.

· Apply the following driver file using adpatch: u4238286.drv (This is the unified driver)

· Generate the TUMS report by executing the following command:

· $ cd $AD_TOP/patch/115/sql

· $ sqlplus <APPS username>/<APPS password> @adtums.sql <DIRECTORY>

· A report file called tumsmp.html will be created in the directory. This directory path must have been listed in the UTL_FILE_DIR parameter of your database.

2.3 Apply AD.I Minipack

· Apply patch 5161676.

2.3.1 Post AD.I Tasks

· Grant privileges and create PL/SQL profiler objects

Note:

Run the adgrants.sql script as a user that can connect as SYSDBA to grant privileges to selected SYS objects and create PL/SQL profiler objects.

Reference:

· Create $ORACLE_HOME/appsutil/admin on the database server.

· Copy adgrants.sql from $APPL_TOP/admin to $ORACLE_HOME/appsutil/admin.

· Set the environment to point to ORACLE_HOME on the database server.

· Run following commands

$ sqlplus /nolog

SQL> connect / as sysdba

SQL> @$ORACLE_HOME/appsutil/admin/adgrants.sql <APPLSYS schema name>

· Run adconfig to instantiate the templates used by AD Utilities

· Perform following adadmin Tasks

· Regenerate JAR files.

· Update current view snapshot.

2.4 Update Technology Stack Templates

2.4.1 Run the Validation Script

Reference:

· From the location where this patch (5478710) unzipped, change directory to fnd/patch/115/bin .

· Run the Validation script as follows:

./txkprepatchcheck.pl -script=ValidateRollup

-outfile=$APPLTMP/txkValidateRollup.html

-appspass=<apps database password>

Review the report generated by the validation script. Perform the required actions described in the report

2.4.2 Apply patch 5478710

Apply patch 5478710 using adpatch

2.4.3 Perform post patch steps

Reference:

Update the RDBMS ORACLE_HOME file system with the AutoConfig files by performing the following steps:

· On the Application Tier (as the APPLMGR user):

Source the environment file.

Create the appsutil.zip file by executing:

$ADPERLPRG $AD_TOP/bin/admkappsutil.pl

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

· On the Database Tier (as the ORACLE user):

Copy or FTP the appsutil.zip file to the <RDBMS ORACLE_HOME>

cd <RDBMS ORACLE_HOME>

unzip -o appsutil.zip

Run AutoConfig by executing:

<RDBMS_ORACLE_HOME>/appsutil/scripts/<CONTEXT_NAME>/adautocfg.sh

2.5 Apply patch 3835781

Reference:

Unzip the patch into iAS Oracle Home/patch directory.

· Ensure you can connect as sysdba

(set following parameters to allow remote connections

REMOTE_LOGIN_PASSWORDFILE = exclusive

O7_DICTIONARY_ACCESSIBILITY = TRUE)

· Change directory to iAS Oracle Home/patch

· Run following command:

./patch.csh -m sysobjects -o <ORACLE_HOME> -c

<conn_str> -p <sys_password>

· Stop Oracle HTTP Server

· Run following command:

./patch.csh -m midtier -o <ORACLE_HOME>

2.6 Install and run Technology Stack Validation Utility

· Apply patch 3996414

· Perform following post patch steps

Reference:

· Check that the ADPERLPRG variable is set

· Run the technology validation utility on application tiers

$ADPERLPRG $FND_TOP/patch/115/bin/TXKScript.pl

-script=$FND_TOP/patch/115/bin/txkVal11510MP.pl

-txktop=$APPLTMP

-appspass=<apps_password>

-outfile=$APPLTMP/txkVal11510MP.html

· Run the technology validation utility on database tier

$ADPERLPRG $ORACLE_HOME/appsutil/bin/TXKScript.pl

-script=$ORACLE_HOME/appsutil/bin/txkVal11510MP.pl

-txktop=$ORACLE_HOME/appsutil

-appspass=<apps_password>

-outfile=$ORACLE_HOME/appsutil/temp/txkVal11510MP_DB.html

· Ensure that the report has ALLPASS for both the tiers

2.7 Configure database for new products and tablespaces

2.7.1 Perform pre patch steps

Reference:

· Change directories to the ad/patch/115/sql subdirectory of this patch(3180164).

· Run adgnofa.sql in the following manner:

$ sqlplus <APPS schema name>/<APPS password> @adgnofa.sql ALL

· The adruncts.sql and adcrtbsp.sql scripts will be created in the current directory. Open adcrtbsp.sql in a text editor and look for any “create tablespace” commands. If there are any, examine the locations of the new data files, and make changes to them if necessary. Remember that the data files will be created on the database server node, which may be a different machine than the administration server node.

· Run adruncts.sql in the following manner:

$ sqlplus system/<SYSTEM password> @adruncts.sql

· adruncts.sql will display the disk space needed for adcrtbsp.sql to complete successfully. Verify that you have sufficient available disk space on the database server node, and answer the prompt with “yes”.

· adruncts.sql will run adcrtbsp.sql, and the results will be spooled to adcrtbsp.log in the current directory. Review the log file to make sure no errors were reported.

2.7.2 Apply patch 3180164

· Apply patch 3180164 using adpatch. Ensure that this patch is applied on admin node first.

2.7.3 Perform post patch tasks

· Recreate application environment file using adadmin.

· Log off from Unix and login again before proceeding further.

2.8 Install Planner Libraries (4297568)

Perform following tasks only on concurrent processing tier

Reference:

· Log in to each concurrent processing server node as the owner of the

Applications file system.

· If the $SHT_TOP/lib/ilog/5.1 directory already exists, back up any files or

directories that it contains.

· Unzip the ilog51lib.zip file, located in the sht/lib directory of this

patch, into the $SHT_TOP/lib directory. This will create the ilog/5.1

subdirectory if it doesn’t already exist.

· Change directories to $SHT_TOP/lib/ilog/5.1/<your platform>.

· Copy the library files from the current directory to the

$SHT_TOP/lib/ilog/5.1 directory.

· Use AD Administration to relink the following specific executables:

- MSO: MSONEW

- WIP: WICDOL WICMEX WICMLX

- WPS: WPCWFS

- MSR: MSRNEW (if using Release 11.5.4 or later)

- MST: MSTNEW (if using Release 11.5.10 or later)

- ENG: ENCACN (if using Release 11.5.6 or later

2.9 Prepare Depot Repair For Upgrade

· Apply patch 3695149

Note:

The patch generates an output file csdpremr.lst that indicates problematic data, and a description of the resolution. After completing necessary corrective action you should rerun the sqlplus script csdpremr.sql under the $CSD_TOP/patch/115/sql directory and continue to correct data until no further errors are reported. The report will indicate when all errors are resolved.

2.10 Upgrade Oracle ADI

These are client side instructions.

Note:

Extract the p3731741.exe file into a temporary directory.

If the patch is NOT being applied to an installation on Citrix WinFrame or

Microsoft Windows Terminal Server DO NOT perform steps 2 and 7.

2) At a command prompt, enter the following command to place the system in install mode:

change user /install

3) Run the locally installed version of Oracle Installer from the Start

Menu.

4) From the Software Asset Manager, click the From button. Use the dialog

box to navigate to the directory where the patch files were extracted.

5) Select the product (.prd) file displayed, click Open.

6) If both the ADI Request Center and the ADI Toolbar (Excel7/97/2000/XP/2003) components are installed in your Oracle home, then select the “Applications Desktop Integrator Products 7.1.35.10.01 component in the Available Products window and click Install.

If the ADI Request Center component is installed in your Oracle home, but

the ADI Toolbar (Excel7/97/2000/XP/2003) component is not, then open the

“Applications Desktop Integrator Products 7.1.35.10.01 component in the

Available Products window, select the “ADI Request Center 7.1.35.10.01″

component, and click “Install”.

7) At a command prompt, enter the following command to place the system in execution mode: change user /execute

2.11 Change password control policy

If any password related profile options are set then you will have to set them back to null.

2.12 Add space to tablespaces

· Add space to following tablespaces:

OKSX, CSMX, IGFD, ENGX,ENGD,GMFX,GMFD,ECXD, EGOD & EGOX. The space added was 2 GB each.

2.13 Unzip the Maintenance pack files

Unzip the American English and NLS files of Maintenance Pack

2.14 Apply the Maintenance Pack patch

· Apply patch 3480000 on all application tiers.

· Apply NLS patches (Simplified & Traditional Chinese)

· These NLS patches were merged.

2.15 Perform adadmin tasks

· Recreate grants and synonyms for APPS schema(s)

· Compile APPS schema(s)

· Compile flexfield data in AOL tables

· Maintain Multiple Reporting Currencies schema(s)

· Generate message files

· Compile Java Server Pages

2.16 Perform post Upgrade tasks

· Apply latest consolidated online help patch (3275245)

· Drop MRC Schema

3 Apply ATG_PF RUP5

3.1 Apply pre-req patches

3.1.1 Apply patch 5989593

Reference:

Create $ORACLE_HOME/appsutil/admin on the database server.

Copy adgrants.sql (UNIX) from the patchtop(5989593)/admin directory to

$ORACLE_HOME/appsutil/admin.

Set the environment to point to ORACLE_HOME on the database server.

Use SQL*Plus to run the script:

$ sqlplus /nolog

SQL> connect / as sysdba

SQL> @$ORACLE_HOME/appsutil/admin/adgrants.sql <APPLSYS schema name>

3.1.2 Apply patch 4291053

· Apply patch 4291053

· Compile APPS Schema using adadmin

· Compile Flexfield data in AOL tables

3.1.3 Apply patch 3761838 – FRM.G

3.1.4 Apply patch 4206794 – FRM.H

· Apply patch 4206794 and perform following tasks

· Generate message files

· Compile APPS Schema

· Compile Flexfied data in AOL Tables

· Recreate grants and synonyms for APPS Schema

· Gather Schema Statistics

3.1.5 Apply patch 3854951

3.1.6 Apply documentation patch 5395066 using adpatch

5.1.1 – 5.1.7 patches were merged.

3.1.7 Run the technology stack validation utility

Reference:

· Source the environment logged in as applmgr

Ensure that APPLRGF environment is set, if not then set it to the value of APPLTMP

From the location where this patch(ATG_PF>RUP5) was unzipped, change directory to fnd/patch/115/bin.

Run the tech stack validation utility as follows:

./txkprepatchcheck.pl -script=ValidateRollup
-outfile=$APPLTMP/txkValidateRollup.html
-appspass=<apps database password>

Check the report file generated by this utility for any actions required to perform

3.1.8 Apply ATG_PF.RUP5 patch using adpatch.

Apply patch using adpatch

NLS Patches were merged.

3.1.9 Post ATG_PF.RUP5 steps

Apply following patches using adpatch

3865683

6145463

apply addional co-requisite patches as follows

4619025

6496781 – new patch added on feb 23

6265836 – new patch added on feb 23

6620452 – new patch added on feb 23

run adconfig

Disable default users (conditionally recommended)

Needs to confirm with Vikas / Akhilesh

Copy font files for Oracle XML Publisher

Reference:

· Copy the following font files from $FND_TOP/resource to the /lib/fonts directory (under OA_JRE_TOP and JRE_TOP) on all Web and concurrent nodes. If $FND_TOP/resource does not contain the font files, apply patch 3639533: Albany (Display) Font 4.02 Release.

· The font files are:

ALBANYWT.ttf – “single-byte” characters only

ALBANWTJ.ttf – Unicode font with Japanese Han Ideographs

ALBANWTK.ttf – Unicode font with Korean Han Ideographs

ALBANWTS.ttf – Unicode font with Simplified Chinese Han Ideographs

ALBANWTT.ttf – Unicode font with Traditional Chinese Han Ideographs

3.1.10 Apply TXK Autoconfig Rollup Q (5985992)

· Perform pre patch steps

Reference:

· Source the Applications environment file as the owner of the application tier file system. From the location where this patch was unzipped, change directory to fnd/patch/115/bin

· Run the Validation script as follows:

./txkprepatchcheck.pl -script=ValidateRollup

-outfile=$APPLTMP/txkValidateRollup.html

-appspass=<apps database password>

Review the report generated by the validation script. Perform the required actions described in the report before you apply the TXK (FND & ADX) AUTOCONFIG ROLLUP PATCH P

· Apply patch 5989992

· Perform following Post patch steps

· Update the RDBMS ORACLE_HOME file system with the AutoConfig files by performing the following steps:

On the Application Tier (as the APPLMGR user):

Source the environment file.

Create the appsutil.zip file by executing following command:

$ADPERLPRG $AD_TOP/bin/admkappsutil.pl

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

On the Database Tier (as the ORACLE user):

Copy or FTP the appsutil.zip file to the <RDBMS ORACLE_HOME>

cd <RDBMS ORACLE_HOME>

unzip -o appsutil.zip

· Run AutoConfig by executing following command:

$<RDBMS_ORACLE_HOME>/appsutil/scripts/<CONTEXT_NAME>/

adautocfg.sh

· Remove the log file sec_audit.log from the following location(s) if it exists:

* [iAS_ORACLE_HOME]/Apache/Apache/logs/ .

* [iAS_CONFIG_HOME]/Apache/Apache/logs on installations where a

Shared Oracle Home is configured.

3.1.11 Apply ATG Documentation patch 5382500 using adpatch

4 Upgrade J2SE 1.4

· Apply all the driver files of patch 3239047 using adpatch

· Obtain and apply following product related patches

§ Universal Work Queue patch 3695147

§ Collections patch 3199598

· Need to confirm with Elvis.

§ Web Applications Desktop Integrator patch 3503919

· Execute J2SE 1.4 Upgrade script

cd $FND_TOP/patch/115/bin

txkJdkM.sh jdktop=[JDK14_TOP][contextfile=<file>] [log=<file>] [appspass=<password>]

· Perform following step to upgrade the Java version used by AD Utilities

cd $AD_TOP/bin

perl <AD_TOP>/bin/adjavamig.pl java=[JDK14_TOP] [contextfile=<file>][logfile=<log_file>][-help]

run adconfig

run adadmin to generate product JAR files

5 Implement Shared Application file system

5.1 Pre-Merge Tasks

5.1.1 Maintain Snapshot Information

Reference:

· Login to each application tier server as applmgr user and run adadmin task of maintain snapshot information

5.1.2 Backup all the application tier binaries

5.1.3 Shutdown all application services

5.1.4 Prepare for Merge

Decide on the primary node and run merge scripts

Reference:

· On the primary node, login as applmgr user

$ cd <COMMON_TOP>/admin/scripts/<CONTEXT_NAME>

$ perl adpreclone.pl appsTier merge

· Once this process is complete login to remaining nodes and run

$ cd <COMMON_TOP>/admin/scripts/<CONTEXT_NAME>

$ perl adpreclone.pl appltop merge

5.1.5 Copy the files

Copy the required files for merging

Reference:

· Login to each secondary node and recursively copy

directory <COMMON_TOP>/clone/appl

- to –

directory <COMMMON_TOP>/clone/appl on Primary Node

5.1.6 Configure Tasks

Once copy is complete run the configuration tasks

Reference:

· Login to the merged APPL_TOP node(Primary Node) as the APPLMGR user and execute the following commands:

$ cd <COMMON_TOP>/clone/bin

$ perl adcfgclone.pl appsTier

5.1.7 Finishing Tasks

Perform adadmin tasks to complete the merge activity

Reference:

· Login to the merged APPL_TOP node(Primary Node) as the APPLMGR user and run following adadmin tasks

- Generate JAR Files Force = Y

- Generate message files

- relink executables

- copy files to destination

5.2 Enable shared application file system à Handover

On the primary node enable the shared file system support.

Reference:

· Stop all application services

$ cd <FND_TOP>/patch/115/bin

$ perl -I <AU_TOP>/perl txkSOHM.pl

· This script ask response for following prompts:

Absolute path of the context file

Type of Instance

Absolute path of 8.0.6 shared Oracle Home

Absolute path of iAS shared Oracle Home

Absolute path of Configuration top

Oracle Appplications APPS Schema password

5.3 Mount shared application file system on secondary node

5.4 Enable shared application file system on secondary node

Reference:

$ cd <FND_TOP>/patch/115/bin

$ perl -I <AU_TOP>/perl txkSOHM.pl

· The script will ask response for following prompts:

Absolute path of the context file

Type of Instance

Absolute path of 8.0.6 shared Oracle Home

Absolute path of iAS shared Oracle Home

Absolute path of configuration top

Oracle Applications APPS Schema password

6 Move the instance to Solaris 10 box

7 Upgrade Database to 10g Rel 2

7.1 Perform Pre Upgrade Tasks

7.1.1 Run pre-upgrade information tool

· Run the attached script on existing 9.2.0.6 / 9.2.0.7 database.

· Ensure that you connect as ‘SYS’ user to be able to run this script and spool the output to a file.

· Carry out the steps mentioned in the output file.

7.1.2 Check for Deprecated CONNECT Role

Reference:

After upgrading to 10gR2, the CONNECT role will only have the CREATE SESSION privilege; the other privileges granted to the CONNECT role in earlier releases will be revoked during the upgrade. To identify which users and roles in your database are granted the CONNECT role, use the following query:

SELECT grantee FROM dba_role_privs

WHERE granted_role = ‘CONNECT’ and

grantee NOT IN (

‘SYS’, ‘OUTLN’, ‘SYSTEM’, ‘CTXSYS’, ‘DBSNMP’,

‘LOGSTDBY_ADMINISTRATOR’, ‘ORDSYS’,

‘ORDPLUGINS’, ‘OEM_MONITOR’, ‘WKSYS’, ‘WKPROXY’,

‘WK_TEST’, ‘WKUSER’, ‘MDSYS’, ‘LBACSYS’, ‘DMSYS’,

‘WMSYS’, ‘OLAPDBA’, ‘OLAPSVR’, ‘OLAP_USER’,

‘OLAPSYS’, ‘EXFSYS’, ‘SYSMAN’, ‘MDDATA’,

‘SI_INFORMTN_SCHEMA’, ‘XDB’, ‘ODM’);

If users or roles require privileges other than CREATE SESSION, then grant

the specific required privileges prior to upgrading. The upgrade scripts

adjust the privilegesfor the Oracle-supplied users.

In Oracle 9.2.x and 10.1.x CONNECT role includes the following privileges:

SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS

WHERE GRANTEE=’CONNECT’

GRANTEE PRIVILEGE

—————————— —————————

CONNECT CREATE VIEW

CONNECT CREATE TABLE

CONNECT ALTER SESSION

CONNECT CREATE CLUSTER

CONNECT CREATE SESSION

CONNECT CREATE SYNONYM

CONNECT CREATE SEQUENCE

CONNECT CREATE DATABASE LINK

In Oracle 10.2 the CONNECT role only includes CREATE SESSION privilege.

7.1.3 Create script for dblinks in case of downgrade of database

Reference:

During the upgrade to 10gR2, any passwords in database links will be encrypted.

To downgrade back to the original release, all of the database links with encrypted passwords must be dropped prior to the downgrade. Consequently, the database links will not exist in the downgraded database. If you anticipate a requirement to be able to downgrade back to your original release, then save the information about affected database links from the SYS.LINK$ table, so that you can recreate the database links after the downgrade.

Following script can be used to construct the dblink.

SELECT

‘create ‘||DECODE(U.NAME,’PUBLIC’,'public ‘)||’database link ‘||CHR(10)

||DECODE(U.NAME,’PUBLIC’,Null, U.NAME||’.')|| L.NAME||chr(10)

||’connect to ‘ || L.USERID || ‘ identified by ”’

||L.PASSWORD||”’ using ”’ || L.host || ””

||chr(10)||’;’ TEXT

FROM sys.link$ L,

sys.user$ U

WHERE L.OWNER# = U.USER# ;;

7.1.4 List Invalid Objects

Prepare a list of invalid objects before starting the database upgrade.

7.1.5 Update the Optimizer Statistics

Update the statistics of following users.

SYS

OLAPSYS

MDSYS

7.1.6 Apply the Interoperability patch for 10g

· Apply patch 4653225

7.1.7 Change init parameters from 9i RDBMS Home

DB_CACHE_SIZE=800M

SHARED_POOL_SIZE=900M

SHARED_POOL_RESERVED_SIZE=60M

Large_pool_size=20M

PGA_AGGREGATE_TARGET=2GB

7.2 Perform database upgrade tasks

7.2.1 Shutdown the databse and application services

7.2.2 Install 10.2.0.3 – 64 bit software

7.2.3 Install database components from 10G companion CD

Install Oracle JVM and Intermedia products from companion CD

7.2.4 Apply 10.2.0.3 – 64 bit patch

7.2.5 Create 9inls directory

Run the $ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory.

à perl cr9idata.pl

After creating the directory, make sure that the ORA_NLS10 environment variable is set to the full path of the 9idata directory whenever you enable the 10g Oracle home.

7.2.6 Apply 10g Fixes

· Apply latest Opatch (patch 4898608)

(Install the Latest OPatch version (10.2.0.3.3)

Reference:

· Ensure that the directory contains the opatch script appears in your $PATH

· Backup the old Opacth directory in your Oracle Home and then unzip the above patch in 10.2.0 Oracle Home. It will create OPatch directory.

· Apply patch for file systems managed by Veritas (patch 5752399)

(Mandatory Patch 5752399 for 10.2.0.3 on Solaris 64-bit and Filesystems Managed By Veritas or Solstice Disk Suite software (lib files)

Reference:

· Set your current directory to the directory where the patch is located

· #cd 5752399

· #opatch apply

· Apply patch 4905638

(SWITCHOVER TO LOGICAL GENERATES ORA-1403 ORA-1333 ERRORS (lib files))

Reference:

· Set your current directory to the directory where the patch is located

· #cd 4905638

· #opatch apply

· Apply patch 5892355

( ASSERT IN KOPEPIC (lib & msg files))

Reference:

· Set your current directory to the directory where the patch is located

· #cd 5892355

· #opatch apply

· Apply patch 5648872

(Note:418531.1 – Dump in opidsa() after Applying the 10.2.0.3 Patch Set (lib files))

Reference:

· Set your current directory to the directory where the patch is located

· #cd 5648872

· #opatch apply

· Apply patch 5907779

(Bug 5907779 – Self deadlock hang on “cursor: pin S wait on X” (typically from DBMS_STATS) (lib files))

Reference:

· Set your current directory to the directory where the patch is located

· #cd 5907779

· #opatch apply

· Apply patch 5728380

(Bug 5728380 – DML may spin under ktspffc searching for space in ASSM segment (lib files))

Reference:

· Set your current directory to the directory where the patch is located

· #cd 5728380

· #opatch apply

· Apply patch 4430244

(GEHC: ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [KCBNEW_3], [0], [8 (lib files) Segment advisor code (eg: DBMS_SPACE.OBJECT_GROWTH_TREND) can load blocks into the cache for DROPped bjects as CURRENT leading to subsequent operations seeing an incorrect (old) version of a block)

Reference:

· Set your current directory to the directory where the patch is located

· #cd 4430244

· #opatch apply

· Apply patch 5045992

(GEHC: RDBMS Server: Patch ORA-00600 [KXSPOAC EXL 1], [23], [23] (lib files)ABSTRACT – ORA-600 [KXSPOAC : EXL 1], [23], [23]
If a SQL statement fails with Ora-600 [kxspoac : EXL 1] when executed by a parallel execution slave and that SQL statement has numeric binds then you are probably seeing this bug)

Reference:

· Set your current directory to the directory where the patch is located

· #cd 5045992

· #opatch apply

· Apply patch 6068126

(GEHC: VARIOUS ERRORS IN ALERT.LOG ORA-7445 PEVM_INSTC2 MEMCPY (lib files))

Reference:

· Set your current directory to the directory where the patch is located

· #cd 6068126

· #opatch apply

· Apply patch 5201883

( UNEXPLAINED MMAN TRACE FILE (lib files))

Reference:

· Set your current directory to the directory where the patch is located

· #cd 5201883

· #opatch apply

· Apply patch 5257698

Apply Patch 5257698 (RDBMS Server: Patch TOO MANY ‘LX40030.NLB’ FILES OPEN AFTER UPGRADE TO 10GR2 .. 10.2.0.3)

Reference:

Set your current directory to the directory where the patch is located

#cd 5257698

· #opatch apply

· Apply patch 4686909

(CONVERT FUNCTION REPORTS ORA-1482 )

Set your current directory to the directory where the patch is located

#cd 4686909

#opatch apply

· Apply patch 5587976

APPSST: ORA-07445: EXCEPTION ENCOUNTERED: CORE DUMP [DRURNEW()+129] [SIGSEGV]

Reference:

Set your current directory to the directory where the patch is located

#cd 5587896

· #opatch apply

7.2.7 Upgrade the database

· Login to the system as oracle user and ensure that following environment variables are set to point to new 10g home

§ ORACLE_HOME

§ PATH

§ ORA_NLS10

§ LD_LIBRARY_PATH

Update the utlrp script to run in parallel. Check for line

@@utlprp.sql &

And update it to

@@utlprp.sql 24

Use DBUA to upgrade the database.

From ORACLE_HOME/bin start DBUA by typing ‘dbua’ from the command prompt. Follow the instructions to complete the upgrade. While running DBUA when it asks for parallel workers give the same number of workers as above i.e. 24

7.2.8 Check if OLAP AND Data Mining is installed.

Use following SQL to confirm as to whether OLAP and Data mining is installed

SQL> select comp_id from dba_registry where comp_id=’ODM’ or comp_id=’AMD’;

If the query does not return ‘ODM’ then you need to install Oracle Data Mining.

To install Data Mining use following procedure

Connect to database as SYSDBA user and run

SQL> @$ORACLE_HOME/rdbms/admin/dminst.sql SYSAUX TEMP

If the query does not return ‘ODM’ then you need to install Oracle OLAP. To install Oracle OLAP use following procedure

Connect to database as SYSDBA user and run

SQL> @$ORACLE_HOME/olap/admin/olap.sql SYSAUX TEMP

7.2.9 Fix Korean Lexers

Use SQL*Plus to connect to the database as SYSDBA, and run drkorean.sql using the following command:

$ sqlplus “/ as sysdba” @$ORACLE_HOME/ctx/sample/script/drkorean.sql

7.3 Post Database Upgrade Tasks

7.3.1 Run adgrants.sql

Use SQL*Plus to connect to the database as SYSDBA and run the script using the following command:

$ sqlplus “/ as sysdba” @adgrants.sql <APPLSYS schema name>

7.3.2 Grant Create procedure privileve to CTXSYS

Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the database server node. Use SQL*Plus to connect to the database as APPS and run the script using the following command:

$ sqlplus apps/<APPS password> @adctxprv.sql \

<SYSTEM password> CTXSYS

7.3.3 Run adconfig

7.3.4 Gather statistics for SYS schema

Copy $APPL_TOP/admin/adstats.sql from the administration server node to the database server node Use SQL*Plus to connect to the database as SYSDBA and use the following commands to restart the database in restricted mode, run adstats.sql, and restart the database in normal mode:

$ sqlplus “/ as sysdba”

SQL> shutdown normal;

SQL> startup restrict;

SQL> @adstats.sql

SQL> shutdown normal;

SQL> startup;

SQL> exit;

7.3.5 Recreate grants and synonyms

7.4 Apply Jan 2008 CPU for Oracle database 10g

Apply patch 6646853 to new 10g Oracle Home

Reference:

· Ensure that the Opatch version installed is 10.2.0.3.4 or higher.

· Ensure that Opatch is in path and apply this patch

· Ensure that the $PATH has the following executables: make, ar, ld, and nm

· Check whether the java and jar executables are present in 10.2.0 Oracle home. By default, the OPatch utility uses the executables from these locations. However, if they are located elsewhere, you can use the opatch apply command with the -jdk flag, and then specify the full path to the JDK to be used.

· Set your current directory to the directory where the patch is located

cd 6646853

opatch napply -skip_subset -skip_duplicate

· Load modified sql files into the database.

cd $ORACLE_HOME/cpu/CPUJan2008

sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL> STARTUP

SQL> @catcpu.sql

SQL> QUIT

· Check the logfile for any errors

· If catcpu.sql reports any invalid objects run following commands

cd $ORACLE_HOME/rdbms/admin

sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL> STARTUP

SQL> @utlrp.sql

· Check whether view compilation has already been performd for the database by executing following command:

SELECT * FROM registry$history where ID = ‘6452863′;

· If the view compilation is already performed this query will return one row, else it will return no rows

· Run the view recompilation script. Note that this script is run with the database in upgrade mode, which restricts connections as SYSDBA.

cd $ORACLE_HOME/cpu/view_recompile

sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL> STARTUP UPGRADE

SQL> @view_recompile_jan2008cpu.sql

SQL> SHUTDOWN;

SQL> STARTUP;

· Check the logfile for any errors

· If any invalid objects are reported then run utlrp.sql as below:

cd $ORACLE_HOME/rdbms/admin

sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL> STARTUP

SQL> @utlrp.sql

Alert:

· On Solaris 10, OPatch may fail verification. If this happens, you must manually perform the verification. One of the reasons for failure is because Sun Microsystems changes an object file’s size when it archives the file into a library.

· In this case perform manual verification per below:

· Locate the current apply log file in $ORACLE_HOME/cfgtoollogs/opatch

· Locate the diagnostic records

· Compare the source and destination files. If all the source files are smaller by than 4 bytes compared to destination files then the patch has been applied successfully.

7.5 Gather schema statistics for sys, apps and applsys schemas

Reference:

· Execute the following statements to gather schema statistics. This is used to fasten the patching time.

· Login as 10g oracle user

SQL> connect / as sysdba

SQL>dbms_stats.gather_schema_stats(’SYS’,cascade=>TRUE,degree=>24);

SQL>dbms_stats.gather_schema_stats(’APPS’,cascade=>TRUE,degree=>24);

SQL>dbms_stats.gather_schema_stats(’APPLSYS’,cascade=>TRUE,degree=>24);

7.6 Upgrade OUI to version 2.2 (patch 5035661)

Apply patch 5035661 to both iAS and RDBMS Oracle Home

Reference:

1. Applying the patch on the iAS $ORACLE_HOME:

· Unzip the patch into the <iAS ORACLE_HOME> directory:

unzip -od <iAS ORACLE_HOME> p5035661_11i_<PLATFORM>.zip

· Source the Apps environment file

· Change directory to the <iAS ORACLE_HOME>/appsoui/setup

· Execute the perl script OUIsetup.pl:

perl OUIsetup.pl

2. Applying the patch on the RDBMS $ORACLE_HOME:

· Unzip the patch into the <RDBMS ORACLE_HOME> directory:

· unzip -od <RDBMS ORACLE_HOME> p5035661_11i_<PLATFORM>.zip

· Source the DB environment file :

· Change directory to the <RDBMS ORACLE_HOME>/appsoui/setup

· Execute the perl script OUIsetup.pl:

perl OUIsetup.pl

7.7 Update timezone files to v4

Reference:

· Apply patch 5632264 for Oracle 10.2.0.3 (Solaris SPARC-64 bit) to update the timezone files to version 4 through opatch utility.

7.8 Apply OJVM Time Zone Update February-2007

Reference:

· Backup the following files and remove them from 10.2.0 ORACLE_HOME

$ORACLE_HOME/javavm/lib/zi/Africa/Timbuktu

$ORACLE_HOME/javavm/lib/zi/America/Buenos_Aires

$ORACLE_HOME/javavm/lib/zi/America/Catamarca

$ORACLE_HOME/javavm/lib/zi/America/Coral_Harbour

$ORACLE_HOME/javavm/lib/zi/America/Cordoba

$ORACLE_HOME/javavm/lib/zi/America/Indianapolis

$ORACLE_HOME/javavm/lib/zi/America/Jujuy

$ORACLE_HOME/javavm/lib/zi/America/Louisville

$OACLE_HOME/javavm/lib/zi/America/Mendoza

$ORACLE_HOME/javavm/lib/zi/Europe/Belfast

$ORACLE_HOME/javavm/lib/zi/Pacific/Yap

· Unzip the patch 5865568 and apply with opatch

· connect as SYS and run the $ORACLE_HOME/javavm/admin/fixTZa script.

$ sqlplus “/ as sysdba”

SQL> STARTUP

SQL> @?/javavm/admin/fixTZa

· If it terminates in any way other than printing the following message,

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

Bug is in fact present, so this patch is needed

Proceed by restarting the database and running script

fixTZb

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

inspect the output to determine whether the script was either incorrectly run or the bug is in fact not present. If the latter, take no further action. If the earlier, correct the indicated condition and rerun the script.

· Shutdown and restart the database using startup migrate.

· connect as SYS and run $ORACLE_HOME/javavm/admin/fixTZb script.

SQL> SHUTDOWN

SQL> STARTUP MIGRATE

SQL> @?/javavm/admin/fixTZb

If it terminates in any way other than printing the following message,

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

# Bug is no longer present. Patch succeeded.

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

inspect the output to determine what was done incorrectly and retry as indicated.

· Shutdown the database.

SQL> SHUTDOWN

· Restart the database without startup migrate.

8 Apply patch 5700129 to Oracle HTTP Server

· Make sure that all processes running from the Oracle Application Server ORACLE_HOME being patched, are shutdown before installing this patch.

· Ensure that ORACLE_HOME is set to the Oracle Application Server ORACLE_HOME.

· Set JDK_HOME to the JDK directory (JDK 1.3.1 or later).

· Ensure $ORACLE_HOME/bin and $JDK_HOME/bin are included in your PATH environment variable, and also ensure that the following executables are present in the $PATH: make,ar,ld,nm

· Set ORAINST to the location of the oraInst.loc file only if the Oracle Universal Installer inventory has been moved from the default location.

· For example, if you have moved the inventory to /tmp directory, then set ORAINST to /tmp/oraInst.loc.

· If ORAINST is not set, then the default location for that platform is used. For example, the default location on Solaris Operating System (SPARC 32-bit) is /var/opt/oracle/oraInst.loc.

· Set your current directory to the directory where the patch is located and then run the installcpu.sh script using the following command:

· sh installcpu.sh

· Inspect the following installation log file for any errors:

· $ORACLE_HOME/cpu/CPUJan2007/install<timestamp>.log

· Run the cpu_root.sh script as user root using the following command:

sh cpu_root.sh

· Run the 5700129/remove_demo.sh script to remove vulnerable Oracle HTTP Server demos. The demos cannot be restored after you run the script. If you expect to use the demos in the future, then manually backup the files in the $ORACLE_HOME/Apache/Apache/fcgi-bin directory before running the script.

· cd 5700129

· sh remove_demo.sh

9 Apply patches for Oracle Developer

9.1 Apply patch 5713544

Unzip the patch. Change the directory to where the patch was unzipped.

cd 5713544

If you don’t have genshlib in $ORACLE_HOME/bin directory then copy this file over there.

cp genshlib $ORACLE_HOME/bin

sh patch.sh

9.2 Apply patch 5216496

Copy the zip in $ORACLE_HOME and Unzip the zip file. This will unzip into 5216496 folder.

· If you don’t have genshlib in $ORACLE_HOME/bin directory then copy this file over there.

cp genshlib $ORACLE_HOME/bin

· Copy your original libraries in case you ever need it:

cd $ORACLE_HOME/lib

cp libiff.a libiff.a.ORG

cp libiff.so.0 libiff.so.0.ORG

cp libiffw.a libiffw.a.ORG

cp libiffw.so.0 libiffw.so.0.ORG

· Archive .o into iwf and iwfw library

cd $ORACLE_HOME/5216496/opt

ar -rv $ORACLE_HOME/lib/libiff.a ifzprs.o

cd $ORACLE_HOME/5216496/optweb

ar -rv $ORACLE_HOME/lib/libiffw.a ifzprs.o

· Generate All *.so’s

cd $ORACLE_HOME/lib

$ORACLE_HOME/bin/genshlib iffw 0

$ORACLE_HOME/bin/genshlib iff 0

· Generate forms executables

cd $ORACLE_HOME/forms60/lib

make -f cus_forms60w.mk install

9.3 Apply patch 5753922

Unzip Patch file into 6i Oracle Home

cd 5753922

sh patch.sh

9.4 Apply patch 6195758

Unzip the patch into 6i Oracle Home.

cd $ORACLE_HOME

unzip p6195758_600_GENERIC.zip

Unzip the java class files and regenerate your JAR files

Backup the Forms class files,

%cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class

$ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class.PRE_BUG6195758

%cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/ComponentItem.class

$ORACLE_HOME/forms60/java/oracle/forms/handler/ComponentItem.class.PRE_BUG6195758

Inside folder 6195758 in step-2 has class files in oracle\forms\handler directory. Copy this file into ORACLE_HOME/forms60/java/oracle/forms/handler

cd $ORACLE_HOME/6195758/oracle/forms/handler

cp UICommon.class $ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class

%cp ComponentItem.class $ORACLE_HOME/forms60/java/oracle/forms/handler/ComponentItem.class

9.5 Apply Patch 5938515

Unzip Patch file in 6i Oracle Home

cd 5938515

sh patch.sh

9.6 Apply Forms Interoperability Patch

Apply patch 4888294 using adpatch

9.7 Generate JAR Files

Generate JAR files using adadmin

9.8 Apply Security Fixes to Developer 6i

· Apply patch 5687261 to forms server

Unzip patch in 6i oracle home

cd 5687261

sh patch.sh 2>&1 | tee patch.log

Please inspect patch.log for any errors

· Apply patch 5686997

Unzip the patch in 6i Oracle Home

cd 5686997

sh patch.sh 2>&1 | tee patch.log

Please inspect patch.log for any errors.

· Apply patch 5078711

· Make sure that all processes running from the ORACLE_HOME being patched are shutdown before installing this patch.

· Ensure that ORACLE_HOME is set.

· Set JDK_HOME to the JDK directory (JDK 1.3.1 or later).

· Ensure $ORACLE_HOME/bin is included in your PATH environment variable, and also ensure that the following executables must be present in the $PATH: make, ar, ld, nm

· Set your current directory to the directory where the patch is located and then run the installcpu.sh script by entering the following UNIX command:

sh installcpu.sh

Inspect the following install log file for any errors:

10 Upgrade jinitiator to 1.3.1.29

· Extrace jinit*.exe from patch 6350285 and place it in $COMMON_TOP/util/jinitiator/ directory.

· Apply interoperability patch 6169479 using adpatch

· Run the jinit.sh script from fnd/patch/115/bin directory from where patch was unzipped.

jinit.sh 13129

This script will ask for following files

Location of APPSORA.env file
Location of Context File
Password for the APPS User in the database

11 Apply security fixes

Apply following patches for fixing various security bugs

July 2005 CPU

4132004

4355663

October 2005 CPU

4522020

4613714

4583865

4566995

January 2006 CPU

4865928

4759690

April 2006 CPU

4150288

5211695

4969592

4332440

5074725

5046719

July 2006 CPU

5183582

4896755

5083302

5127737

October 2006 CPU

5447522

5486407

5335967

5483388

5521476

January 2007 CPU

5658489

3748835

5661619

April 2007 CPU

5893391

5900224

5352601

5720979

5738134

5909233

5873313

5904576

5967405

5935683

July 2007 CPU

5973659

6045931

6117954

6045983

5973898

5973651

5973629

6185758

6082483

6110945

6111066

6111008

6188705

October 2007 CPU

4897479

4514856

6181762

5889600

January 2008 CPU

You can merge and apply these patches in one go

12 Apply Application Patches

Apply following application patches using adpatch for various bugs/issues/rollups

Slno

Module

Patch ID

Patch Category

INV

4536492

One-Off

Mobile Applications

4605116

One-Off

WMS

4550026

One-Off

INV

4739140

One-Off

Assets

6001110

One-Off

Tech Stack

4608023

Pre-Req

4665900

Pre-Req

4651420

Pre-Req

Shipping

4946700

Pre-Req

Shipping

4649626

Pre-Req

BoM

4216942

Pre-Req

OPM

5527236

Pre-Req

11i.HZ.N

3618299

Pre-Req

WMS

5855276

Rollup

Projects

4626807

Pre-Req

Projects

3485155

Family Pack

Projects

3337098

Pre-Req

Projects

4252634

Pre-Req

Project Mgmt

4440890

Pre-Req

Project Mgmt

5164577

Pre-Req

Project Mgmt

4884310

Pre-Req

Project Mgmt

4463552

Pre-Req

Tech Stack

4631022

Pre-Req

Tech Stack

4609681

Pre-Req

Project Mgmt

5087961

Pre-Req

Projects

5644830

Rollup

Web ADI

4957576

Post-Req

Purchasing

4364505

Post-Req

Project Mgmt

4682341

Post-Req

Project Costing

6272075

Post-Req

BOM

4917687

Rollup

Finance

3653484

Family Pack

Payables

5974819

Pre-Req

Project Costing

5394384

Pre-Req

Payables

4551936

Family Pack

Payables

6185758

One-Off

WMS

6442635

One-Off

WIP

4635071

One-Off

WIP

6173921

One-Off

Projects

6522896

One-Off

Projects

5532386

One-Off

Finance

6329356

One-Off

5531894

One-Off

4537149

One-Off

WMS

5849272

One-Off

Shipping

6220171

One-Off

Warehouse

5948166

One-Off

Oracle iReceivables

3618333

One-Off

Oracle iReceivables

4713959

One-Off

Purchasing

6003319

One-Off

Oracle iReceivables

3618333

Rollup

Oracle iReceivables

4713959

One-Off

Projects

5507295

One-Off

Projects

5843852

One-Off

Shipping

5688014

One-Off

Purchasing

4366835

One-Off

oracle iRec.

6398572

One-Off

Projects

6509104

One-Off

4960189

One-Off

Project Billing

5532386

One-Off

Finance

4351912

One-Off

Finance

4713959

One-Off

Finance

6310878

One-Off

Finance

5404404

One-Off

Finance

6369513

One-Off

DUMMY BUG FOR FND PATCH

5733769

Pre_req for ASCP

DUMMY BUG FOR FND PATCH

5297822

Pre_req for ASCP

ASCP Planning Engine

6350793

One-Off

ATP RUPs

6374582

One-Off

Collections

6447137

One-Off

Help patch for ASCP

6080508

One-Off

Online Help

3275245

One-Off

5697729

Pre-req for 6359269

6058052

Pre-req for 6359269

4406147

Pre-req for 6359269

OM(11.5.10) Cumulative Patch

6359269

One-Off

6215459

One-Off

Mobile

6711886

One-Off

WIP

4517649

One-Off

Project

6768664

adpatch option=nocheckfile

Project

6050071

One-Off

Assests

5912795

One-Off

mobile

4941477

One-Off

We can merge and apply these patches in one go

13 Issues Faced.

13.1 Failure in patch 3180164

Solution : Backup $APPL_TOP/admin/topfile.txt.
2. Remove izu from that file.
3. Retry Patch 3180164, continue if it gives you that option.
4. After the patch is applied, run AutoConfig, and your topfile.txt should be recreated with izu
back in.

13.2 ADWorker Failed Duplication of Synonym and indexes

Solution:

14 Proposed Init.ora parameters for 10g

aq_tm_processes	1
background_dump_dest	‘/erppgfr1/oracle/10.2.0/admin/erppgfr1_tsgsd1006/bdump’
compatible	‘10.2.0′
control_files	/erppgfr1/dbdata/data1/cntrl01.dbf’, ‘/erppgfr1/dbdata/data2/cntrl02.dbf’, ‘/erppgfr1/dbdata/redo1/cntrl03.dbf’
core_dump_dest	‘/erppgfr1/oracle/10.2.0/admin/erppgfr1_tsgsd1006/cdump’
cursor_sharing	‘EXACT’
db_block_checking	‘FALSE’
db_block_checksum	‘TRUE’
db_block_size	8192
db_cache_size	750M
db_file_multiblock_read_count	8
db_files	1022
db_name	‘erppgfr1′
dml_locks	10000
java_pool_size	67108864
job_queue_processes	2
large_pool_size	33554432
log_buffer	10485760
log_checkpoint_interval	100000
log_checkpoint_timeout	1200
log_checkpoints_to_alert	TRUE
max_dump_file_size	‘20480′
nls_comp	‘binary’
nls_date_format	‘DD-MON-RR’
nls_language	‘american’
nls_length_semantics	‘BYTE’
nls_numeric_characters	‘.,’
nls_sort	‘binary’
nls_territory	‘america’
O7_DICTIONARY_ACCESSIBILITY	TRUE
olap_page_pool_size	4194304
open_cursors	600
optimizer_features_enable	‘9.2.0′ = To Be Removed
parallel_max_servers	8
parallel_min_servers	0
pga_aggregate_target	2147483648
processes	1000
query_rewrite_enabled	‘true’
session_cached_cursors	200
session_max_open_files	20
sessions	2000
shared_pool_reserved_size	100M
shared_pool_size	750M
streams_pool_size	50331648
timed_statistics	TRUE
undo_management	‘AUTO’
undo_retention	1800
undo_tablespace	‘APPS_UNDOTS1′
user_dump_dest	‘/erppgfr1/oracle/10.2.0/admin/erppgfr1_tsgsd1006/udump’
utl_file_dir	/erppgfr1/erpapp/comn/temp’, ‘/erppgfr1/oracle/10.2.0/appsutil/outbound/ erppgfr1_tsgsd1006′,’/usr/tmp’, ‘/erppgfr1/erpapp/comn/temp’, ‘/custompro
workarea_size_policy	‘AUTO’
optimizer_secure_view_merging	FALSE
plsql_code_type	‘INTERPRETED’
plsql_optimize_level	2
_b_tree_bitmap_plans	FALSE
_fast_full_scan_enabled	FALSE
_kk