Complete Upgrade Steps on 11.0.3 to 11i Upgrade

bash-2.05b$ echo $ORACLE_HOME
/u01/app/oracle/product/806

bash-2.05b$ echo $ORACLE_SID
dev

bash-2.05b$ echo $PATH
/usr/bin:/etc:/usr/sbin:/usr/ucb:/home/oracle/bin:/usr/bin/X11:/sbin:/usr/bin:/etc:/usr/sbin:/usr/ucb:/usr/bin/X11:/sbin:/usr/java14/jre/bin:/usr/java14/bin:.:/u01/app/oracle/product/806/bin::/u01/app/oracle/product/806/bin:/u01/app/oracle/product/806/bin

b-2.05b$ echo $LD_LIBRARY_PATH
/u01/app/oracle/local/java/jre1.1.6/lib:/u01/app/oracle/product/806/lib:


bash-2.05b$ svrmgrl

Oracle Server Manager Release 3.0.6.0.0 - Production

(c) Copyright 1999, Oracle Corporation. All Rights Reserved.

Oracle8 Enterprise Edition Release 8.0.6.3.0 - Production
PL/SQL Release 8.0.6.3.0 - Production

SVRMGR> connect internal
Connected.
SVRMGR> startup nomount pfile=/u01/app/oracle/product/806/dbs/initdev.ora
ORACLE instance started.
Total System Global Area 1919251520 bytes
Fixed Size 50240 bytes
Variable Size 1069846528 bytes
Database Buffers 838860800 bytes
Redo Buffers 10493952 bytes
SVRMGR> @/u01/app/oracle/product/bmcntl.sql
Statement processed.

SVRMGR> ALTER DATABASE OPEN RESETLOGS;
Statement processed.


SVRMGR> SELECT STATUS,LOGINS,INSTANCE_NAME FROM V$INSTANCE;
STATUS LOGINS INSTANCE_NAME
------- ---------- ----------------
OPEN ALLOWED dev
1 row selected.



Sys Passwd Change
orapwd file=$ORACLE_HOME/dbs/orapwdev password=oracle entries=5


Configure Tnsnames and Listeners


Subject: Migrating Apps Release 11.0 from UNIX Host To A Second UNIX Host
Doc ID: Note:74838.1

select distinct LOGFILE_NODE_NAME from fnd_concurrent_requests;
select distinct OUTFILE_NODE_NAME from fnd_concurrent_requests;



SVRMGR> select distinct LOGFILE_NODE_NAME from fnd_concurrent_requests;
LOGFILE_NODE_NAME
------------------------------
Atlas

update fnd_concurrent_requests set LOGFILE_NODE_NAME='instancezu'
where LOGFILE_NODE_NAME='atlas'

SVRMGR> select distinct LOGFILE_NODE_NAME from fnd_concurrent_requests;
LOGFILE_NODE_NAME
------------------------------
instancezu

2 rows selected.

SVRMGR> select distinct OUTFILE_NODE_NAME from fnd_concurrent_requests;
OUTFILE_NODE_NAME
------------------------------
atlas

update fnd_concurrent_requests set OUTFILE_NODE_NAME='instancezu'
where OUTFILE_NODE_NAME='atlas'


SVRMGR> update fnd_concurrent_requests set OUTFILE_NODE_NAME='instancezu'
where OUTFILE_NODE_NAME='atlas';

18313 rows processed.

SVRMGR> select distinct OUTFILE_NODE_NAME from fnd_concurrent_requests;
OUTFILE_NODE_NAME
------------------------------
instancezu


OLD_HOST
/u01/app/applmgr/1103/prod/admin/logprod/l31706384.req
/u01/app/applmgr/1103/prod/admin/outprod/INVMANG.31706384

NEW_HOST
/u01/app/applmgr/1103/dev/admin/logdev
/u01/app/applmgr/1103/dev/admin/outdev


LOGFILE_NAME

update fnd_concurrent_requests set
LOGFILE_NAME=replace(LOGFILE_NAME,'/u01/app/applmgr/1103/prod/admin/logprod/','/u01/app/applmgr/1103/dev/admin/logdev')


OUTFILE_NAME

update fnd_concurrent_requests set
OUTFILE_NAME=replace(OUTFILE_NAME,'/u01/app/applmgr/1103/prod/admin/outprod','/u01/app/applmgr/1103/dev/admin/outdev')


FND_CONCURRENT_PROCESSES

The logfile name of the concurrent managers is stored in
fnd_concurrent_processes.LOGFILE_NAME

The nodename of the concurrent managers is stored in
FND_CONCURRENT_PROCESSES.NODE_NAME



select distinct(NODE_NAME) from fnd_concurrent_processes;

update fnd_concurrent_processes set NODE_NAME = 'instancezu';


SVRMGR> select distinct(NODE_NAME) from fnd_concurrent_processes;
NODE_NAME
------------------------------
Atlas

1 row selected.


SVRMGR> update fnd_concurrent_processes set NODE_NAME = 'instancezu';
63 rows processed.


SVRMGR> select distinct(NODE_NAME) from fnd_concurrent_processes;
NODE_NAME
------------------------------
instancezu
1 row selected.


update global_name set global_name='DEV.WORLD' where global_name='PROD.WORLD'


/u01/app/applmgr/1103/dev/html/html/US
Change PROD to DEV
ICXUDUL_DEV.htm



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

1- sqlplus applsys/xxxx@afstatdr.sql applsys xxxx
2- sqlplus applsys/xxxxx @afstatsg.sql oracle apps
3- imp parfile=afstats2.dat userid=applsys/xxxxxx
Or
3-imp userid=applsys/xxxxxx file=afstats2.dmp ignore=y grants=n full=y commit=n buffer=8000000
4- sqlplus applsys/xxxxxx @afstatgt.sql applsys xxxxxx apps xxxxxx
5- sqlplus apps/xxxxxx @AFSTATSS.pls
6- sqlplus apps/xxxxxx @fndpker.sql apps xxxxxx PACKAGE FND_STATS
7- sqlplus apps/xxxxxx @AFSTATSB.pls
8- sqlplus apps/xxxxxx @fndpker.sql apps xxxxxx PACKAGE_BODY FND_STATS


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 and Apps Tier
./rapidwiz

3.0.3 Apply the Forms Patch set 18


If Reuired will Apply
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

cd $APPL_TOP/admin/preupg
sqlplus apps/oracle11i afindxpr.sql applsys
sqlplus apps/oracle11i afpregdi.sql

Make sure orders are in a supported status

cd $ONT_TOP/patch/115/sql
sqlplus apps/oracle11i@ontexc07.sql

Review Item Validation Org settings

cd $ONT_TOP/patch/115/sql
sqlplus apps/oracle11i@ontexc05.sql

Close open pick slips/picking batches or open deliveries/departures

cd $WSH_TOP/patch/115/sql
sqlplus apps/oracle11i@wshbdord.sql

Validate inventory organization data

cd $WSH_TOP/patch/115/sql

sqlplus apps/oracle11i@wshpre00.sql

Review cycles that may not be upgraded

cd $ONT_TOP/patch/115/sql
sqlplus apps/oracle11i@ontexc08.sql

Clear open interface tables

cd $APPL_TOP/admin/preupg
sqlplus apps/oracle11i@
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

cd $APPL_TOP/admin/preupg
sqlplus apps/oracle11i@apuinimp.sql

Diagnose problems in the data

cd $AR_TOP/patch/115/sql
sqlplus apps/oracle11i@ar115chk.sql

Identify potential ORACLE schema conflicts

cd $APPL_TOP/admin/preupg
sqlplus applsys/oracle11i@adpuver.sql

3.0.10 Apply Family Consolidated Upgrade Patch for Financials - 3993353 with preinstall


Please Find the 806 - 9i Upgrade Steps

Subject: Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.0.1 to Oracle9iR2 (9.2.0) Doc ID: Note: 159657.1


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

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;

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/oracle11idev
@addb920.sql -- Sets up database SYS schema

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

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


sqlplus /nolog
connect SYSTEM/oracle11idev
@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;







Change Roll Back to UNDO Tablespace

select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SYSTEM SYSTEM
ROLL301 RBS3
ROLL302 RBS3



select file_name,tablespace_name, bytes from dba_data_files where tablespace_name = 'RBS3';


select 'alter rollback segment ' SEGMENT_NAME' offline;' from dba_rollback_segs;

SQL> select 'alter rollback segment ' SEGMENT_NAME' offline;' from dba_rollback_segs;

SEGMENT_NAME
=============

alter rollback segment ROLL301 offline;
alter rollback segment ROLL302 offline;


SQL> alter rollback segment ROLL302 offline;

alter rollback segment ROLL303 offline;

Rollback segment altered.


SQL> alter rollback segment ROLL304 offline;

alter rollback segment ROLL305 offline;
alter rollback segment ROLL306 offline;

Rollback segment altered.



SQL> alter rollback segment ROLL307 offline;

Rollback segment altered.

SQL> alter rollback segment ROLL308 offline;

Rollback segment altered.



SQL> drop rollback segment ;

select 'drop rollback segment ' SEGMENT_NAME';' FROM from dba_rollback_segs;


SQL> select 'drop rollback segment ' SEGMENT_NAME';' FROM dba_rollback_segs;

'DROPROLLBACKSEGMENT'SEGMENT_NAME';'
------------------------------------------------------

drop rollback segment ROLL301;
drop rollback segment ROLL302;

alter tablespace RBS3 offline;


SQL> alter tablespace RBS3 offline;
alter tablespace RBS2 offline;

drop tablespace RBS2;


Tablespace altered.


create undo tablespace

create undo tablespace APPS_UNDOTS1 datafile '/db2/oradata/dev/data/undodbs01.dbf' size 3000M reuse extent management local;

ALTER TABLESPACE APPS_UNDOTS1 ADD DATAFILE '/db2/oradata/dev/data/undodbs02.dbf' size 3000M;

create undo tablespace APPS_UNDOTS2 datafile '/db2/oradata/dev/data/undodbs04.dbf' size 3000M reuse extent management local;

ALTER TABLESPACE APPS_UNDOTS2 ADD DATAFILE '/db2/oradata/dev/data/undodbs05.dbf' size 2000M;



CREATE TEMPORARY TABLESPACE

alter tablespace temp offline;

SQL> alter tablespace temp offline;

SQL> drop tablespace temp;


CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/db2/oradata/dev/data/temp01.dbf' SIZE 3000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

ALTER TABLESPACE temp
ADD TEMPFILE '/db2/oradata/dev/data/temp02.dbf' SIZE 2000M REUSE;


select * from dba_temp_files;
SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

Set Default TEMP TBS

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;


Convert existing tablespaces to local extent management


We recommend using local extent management to increase performance. To
convert all non-SYSTEM tablespaces from Data Dictionary extent management to
local extent management, run the following script:

UNIX:
$ cd $APPL_TOP/admin/preupg
$ sqlplus system/finalf @adtbscnv.pls finalf


SQL> desc dba_tablespaces;
SQL> select EXTENT_MANAGEMENT, count(*) from dba_tablespaces group by EXTENT_MANAGEMENT;


bash-2.05b$ pwd
/u012/11i/ora/oracle/devsappl/admin
adsysapp2.sql

bash-2.05b$ sqlplus system/finalf@dev

SQL> @adsysapp2.sql
Enter value for 1: finalf



alter database datafile '/db2/oradata/dev/data/system01.dbf' resize 10000M;



To continue to use the existing tablespace model (OFA-compliant):

Run adgnofa.sql as follows. On the command line, indicate the as
NEW to create new product tablespaces (tablespaces for existing products are
not affected), or ALL to create tablespaces for new products and resize existing
product tablespaces.

UNIX:
$ cd $AD_TOP/patch/115/sql
$ sqlplus apps/xxxxxx @adgnofa.sql ALL

$sqlplus "/as sysdba"
@/u012/11i/ora/oracle/devsappl/ad/11.5.0/patch/115/sql/adcrtbsp.sql


alter database datafile '/db2/oradata/dev/index/jax01.dbf' resize 50M;


CREATE TABLESPACE APPLSYSD
DATAFILE '/db2/oradata/dev/data/applsysd01.dbf' size 200M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 1M;

CREATE TABLESPACE APPLSYSX
DATAFILE '/db2/oradata/dev/data/applsysx01.dbf' size 200M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 1M;


CREATE TABLESPACE CTXD DATAFILE '/db2/oradata/dev/data/ctxd01.dbf' SIZE 100M; AUTOEXTEND ON NEXT 5 MAXSIZE 200m;.








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 /R11idev/db/9.2.0/appsutil/admin/xmlparser/lib/java
loadjava -user apps/oracle11i-r -v xmlparserv2.jar
loadjava -user apps/oracle11i-r -v xmlplsql.jar
cd ../sql
cat load.sql sqlplus apps/cl_11i_gbook_sta
cd /R11idev/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 /R11idev/oradata/R11iEXP/applmgr/r11iexpcomn/java/xmlparserv2.zip .
loadjava -user apps/oracle11i-r -v xmlparserv2.zip



Gathered database information
Login to 11i Mid tier
cd $APPL_TOP/admin/preupg
sqlplus apps/oracle11i@adupinfo.sql


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’;



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

$ adaimgr consolidated_tablespace=N


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