11.0.3 to 11.5.10.2 Upgrade

Production Cut-over Checklist
Date
10th Novembert, 2006

Environment Name:
PRD01

Prepared By:
Madan Mohan K



1.0 Pre-Upgrade Checks




· OS user for RDBMS
· Group for or11iuat
· OS user for APPLMGR
· Group for ap11iuat
· OS utilities to be in PATH
· Java version
· Perl version
· Zip version
· Unzip version
· Oracle Inventory file
· Oracle Inventory permissions
· Add to .profile for all Unix users
· Permission for $APPLCSF
· X-server installed
· Stage area is installed



or11i
dba
ap11i
oaa
ar cc make ld zip unzip perl java javac
1.4.2
5.8.7
2.3
5.32
/var/opt/oraInst.loc
777
ulimit -d 1048576
777
Xvfb installed
Yes
2.0 Pre Upg tasks on 11.0.3 instance

2.0.1. Apply the Patch 1268797
cd $FND_TOP/patch/110/sql
sqlplus / @afstatrn.sql True
2.0.2. Apply the TUMS patch 3422686
cd $AD_TOP/patch/110/sql
sqlplus apps/app @adtums.sql /u10/app/convr11/tmp
2.0.3 Set the File attachments

2.0.4 . Submit “Purge Concurrent
Requests” with a retention
period of 7 days

2.0.5. Check for rows in
ALR_ACTION_HISTORY Table
Select count(*) from apps. ALR_ACTION_HISTORY;
If No rows then Apply the Patch 451137
2.0.6. Disabled all the Custom Triggers and
Alerts

2.0.7 Disabled Database Audit trial in init.ora
file
Audit_trial= false or comment out the line.
2.0.8 Validate and Compile Apps Schema
Use adadmin Utility
2.0.9 Backup the INVALIDS in a Table
Create table BEFORE_PREUPG_INVALIDS as select * from dba_objects where status=’INVALID’;
2.0.10. Make a note of passwords for following
users
SYSADMIN
GUEST
Oracle user - APPS
Oracle user - SYSTEM







3.0 Pre Upgrade Tasks on 11i Instance




3.0.1. Run Rapid install on DB tier
./rapidwiz –servername
3.0.2. Run Rapid install on MT tier
./rapidwiz –servername
3.0.3 Apply the Forms Patch set 18

3.0.4 Install 9.2.0.7 software on DB tier (4163445)
./runInstaller
3.0.5 Install Latest Opatch 2617419 on DB Tier
Unzip *2617419*.zip in $OARCLE_HOME
3.0.6 Applied Database patch 4192148 for Import
Issue

3.0.7 Update the .profile in MT and DB Tiers .
On MT :
- /<>/applmgr/r11i<>appl/APPSORA.env
On DB
- $ORACLE_HOME/ R11i<> _ .env

3.0.8 InstallPrep.sh script as per Note 189256.1

3.0.9 Configured tnsnames.ora on MT to connect to 8.1.7 database of 11.0.3 instance

3.0.10. Login to MT tier and perform following pre
upgrade tasks
Verify custom index privileges - Done
cd $APPL_TOP/admin/preupg
sqlplus apps/cl_11i_gbook_sta afindxpr.sql applsys
sqlplus apps/cl_11i_gbook_sta afpregdi.sql

Make sure orders are in a supported status - Done
cd $ONT_TOP/patch/115/sql
sqlplus apps/cl_11i_gbook_sta @ontexc07.sql

Review Item Validation Org settings – Done
cd $ONT_TOP/patch/115/sql
sqlplus apps/cl_11i_gbook_sta @ontexc05.sql

Close open pick slips/picking batches or open deliveries/departures – Done
cd $WSH_TOP/patch/115/sql
sqlplus apps/cl_11i_gbook_sta @wshbdord.sql

Validate inventory organization data – Done
cd $WSH_TOP/patch/115/sql
sqlplus apps/cl_11i_gbook_sta @wshpre00.sql

Review cycles that may not be upgraded – Done
cd $ONT_TOP/patch/115/sql
sqlplus apps/cl_11i_gbook_sta @ontexc08.sql

Clear open interface tables – Done
cd $APPL_TOP/admin/preupg
sqlplus apps/cl_11i_gbook_sta @
Run above sql for each of the following scripts:
Requisitions Open Interface (pocntreq.sql)
Purchasing Documents Open Interface (pocntpoh.sql)
Receiving Open Interface (pocntrcv.sql)


Import and purge Invoice Import Interface expense reports and invoices – Done
cd $APPL_TOP/admin/preupg
sqlplus apps/cl_11i_gbook_sta @apuinimp.sql

Diagnose problems in the data – Done
cd $AR_TOP/patch/115/sql
sqlplus apps/cl_11i_gbook_sta @ar115chk.sql

Identify potential ORACLE schema conflicts - Done
cd $APPL_TOP/admin/preupg
sqlplus applsys/cl_11i_gbook_sta @adpuver.sql
3.0.10 Apply Family Consolidated Upgrade Patch for Financials - 3993353 with preinstall=y


3.0.11 Shutdown Mid Tier services
and Database for 11.0.3
Instance & Bkup the 11.0.3 DB



4.0. Database Upgrade using Export /
Import
Shutdown the 11.0.3 MID Tier Services

Login to 11.0.3 Database and Note the global_name
on Source Instance.
Select global_name from global_name;

Extracted following files which are used for export / import from patch 4872830
adclondb.sql – Ran this script to generate adcrdb.sql. adcrdb.sql contains create database and create tablespace which needs to be executed on 11i
sqlplus SYSTEM/SYSTEM_r11clone @adclondb.sql 9
Ran auque1.sql, which generates a script called auque2.sql. This is required since Advanced Queue settings are not propagated in Export / Import process. auque2.sql needs to be executed on Target Instance.
auexpimp.dat – Parameter file for Export and Import
Export 11.0.3 database using following par file
exp parfile=exp_parameter.dat




Userid=system/system_r11clone
file=exp_full_database_direct.dmp
compress=y
direct=y
full=y
statistics=none
buffer=104857600
log=exp_full_database.log


5.0. Import 11.0.3 database to 11i

Login to 11i Database Tier

Copy the initR11CLONE.ora from 11.0.3 instance to initR11i.ora on 11i and modify the directoey locations for controlfiles, all dump destinations, and also specify APPS_UNDO for undo tablespace

Updated Global_name for this Database to the value on 11.0.3
alter database rename global_name to 'R11CLONE.WORLD';

Create the apps user as

create user apps identified by cl_11i_gbook_sta default tablespace applsysd temporary tablespace
temp;

Setup the Enterprise Edition for Oracle 9i
These scripts create objects required by the RDBMS and other technology stack components on the database server
Login to 11i Database Tier
Created folder $ORACLE_HOME/appsutil/admin
Copied addb920.sql, adsy920.sql, adjv920.sql, admsc920.sql, and adgrants.sql from $APPL_TOP/admin to $ORACLE_HOME/appsutil/admin

sqlplus /nolog
connect SYSTEM/system_r11iuat
@addb920.sql -- Sets up database SYS schema

sqlplus /nolog
connect SYSTEM/system_r11iuat
@adsy920.sql -- Sets up database SYSTEM schema

sqlplus /nolog
connect SYSTEM/system_r11iuat
@adjv920.sql - Installs Java Virtual Machine



sqlplus /nolog
connect SYSTEM/system_r11iuat
@admsc920.sql FALSE CTXD TEMP $ORACLE_HOME/ctx/lib/libctxx9.so

Check for output of dba_registry is as given below

col comp_name for a40
col version for a15
select comp_name, version, status FROM dba_registry;

Imported 11.0.3 database into 11i using following parameter file


Script imp_time_taken
imp parfile=imp_parameter.dat

Userid="system/system_r11iuat"
file=exp_full_database_direct1.dmp
grants=y
buffer=104857600
indexes=y
rows=y
constraints=y
full=y
log=imp_full_R11iUAT.log
ignore=y
toid_novalidate=( SYS.AQ$_AGENT, SYS.AQ$_JMS_USERPROPERTY, SYS.AQ$_JMS_USERPROPARRAY,
SYS.AQ$_JMS_MESSAGE,
SYS.AQ$_JMS_TEXT_MESSAGE, SYS.AQ$_JMS_BYTES_MESSAGE,
SYS.AQ$_JMS_STREAM_MESSAGE, SYS.AQ$_JMS_MAP_MESSAGE, SYS.AQ$_JMS_OBJECT_MESSAGE,
SYSTEM.REPCAT$_OBJECT_NULL_VECTOR, MDSYS.SDO_GEOMETRY)

Run preparatory scripts

sqlplus /nolog
connect SYSTEM/system_r11clone
@adgrants.sql APPLSYS -- Grants necessary privileges on selected SYS objects.

Applied database patch to create / update OWS packages
Login to 11i Database Tier 3835781
mkdir $ORACLE_HOME/appsutil/admin/OWS
cd $ORACLE_HOME/appsutil/admin/OWS
cp /R11isit/staging/patches/*3835781* .
@patch.sql

Installed XML Parser for PL/SQL
Login to 11i Database Tier
mkdir $ORACLE_HOME/appsutil/admin/xmlparser
cd $ORACLE_HOME/appsutil/admin/xmlparser
cp $COMMON_TOP/util/plxmlparser_v1_0_2.zip .
unzip plxmlparser_v1_0_2.zip - This file creates several subdirectories in the current location
cp $COMMON_TOP/util/XSU12_ver1_2_1.zip .
unzip XSU12_ver1_2_1.zip - This file creates subdirectory - OracleXSU12
cd /R11iuat/db/9.2.0/appsutil/admin/xmlparser/lib/java
loadjava -user apps/cl_11i_gbook_sta -r -v xmlparserv2.jar
loadjava -user apps/cl_11i_gbook_sta -r -v xmlplsql.jar
cd ../sql
cat load.sql sqlplus apps/cl_11i_gbook_sta
cd /R11iuat/db/9.2.0/appsutil/admin/xmlparser/OracleXSU12/lib
sh oraclexmlsqlload.ksh
mkdir $ORACLE_HOME/appsutil/admin/java
cd $ORACLE_HOME/appsutil/admin/java
cp /R11iuat/oradata/R11iEXP/applmgr/r11iexpcomn/java/xmlparserv2.zip .
loadjava -user apps/cl_11i_gbook_sta -r -v xmlparserv2.zip

Re-compile Invalids
sqlplus “/ as sysdba”
@$ORACLE_HOME/rdbms/admin/utlrp.sql
create table invalids_after_db_backup as select * from dba_objects where status = ‘INVALID’;

Set up tablespaces
Increased SYSTEM tablespace size to 9 GB before you run the scripts given below
cd $AD_TOP/patch/115/sql
sqlplus APPS/cl_11i_gbook_sta @adgncons.sql apps cl_11i_gbook_sta APPLSYS
Gathered database information
Login to 11i Mid tier
cd $APPL_TOP/admin/preupg
sqlplus apps/cl_11i_gbook_sta @adupinfo.sql

Ran preparatory script on Administration node
Login to 11i Mid tier
cd $APPL_TOP/admin
sqlplus system/system_r11clone @adsysapp2.sql system_r11clone


6.0 Auto Upgrade Tasks
Login to 11i Application Tier
o Run Autoupgrade using adaimgr


6.0.1 Post-Autoupgrade tasks
Login to 11i Database Tier
Re-compile Invalids
sqlplus “/ as sysdba”
@$ORACLE_HOME/rdbms/admin/utlrp.sql
create table invalids_after_autoupgrade as select * from dba_objects where status = ‘INVALID’;

6.0.2 Performed cold Backup of
Database







7.0 Autopatch Tasks
Login to 11i Application Tier
Applied AD.I.4 (4712852)
Applied 11.5.10.2 maintenance pack
cd $AU_TOP/patch/115/driver
adpatch options=nocopyportion,nogenerateportion

Issues faced while running Autopatch are noted in Upgrade log attached to this document below
Ran OATM to move 11.0.3 data to 11.5.10.2

8.0 Post Autopatch tasks
List of Patches to be applied are included in below file.



DBA Setup activities performed after all patches are applied is listed in the following document



9.0 Clean Up Unwanted Files/Directories

10.0. Health Check & Release the Instance for
Testing