Step by Step Guide to Upgrade EBS Database 11g and Application 12.1 to 19c DB and 12.2.13 Apps

Upgrade EBS Database and Application to 19c DB and 12.2.13 Apps

In this post I am going to share on steps for upgrading the an EBS environment. Please read respective documents again in case doing the activity as the new patches and steps would get added based on version . The details of the my environment are as below

Assumption : You know application patching and database patching.

All the GUI are executed on the VNC.

DB means Database

Note : My Application Services are taken Down for this activity.

Source EBS System

Apps : 12.1.3

DB: 11.2.0.4

Server : OEL 8.4

Oracle SID: UAT

Datafile: Normal filesystem (Non-ASM)

DB OS user: oracle

Application OS user: applmgr

Target EBS System

Apps : 12.2.13

DB: 19.23.0.0

Server : OEL 8.4

Oracle CDBSID: UATCDB

PDB Name : UAT

Datafile: Normal filesystem (Non-ASM)

DB OS user: oracle

Application OS user: applmgr

The first part of upgrade I am going to upgrade the database to 19c.

1) Check current db version

SQL> select banner from v$version;

BANNER

-----------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

2) Check apps version

SQL> select release_name from apps.fnd_product_groups;

RELEASE_NAME

--------------------------------------------------

12.1.3

3) Check Database Character Set

SQL> select * from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

PARAMETER VALUE

---------- -----------

NLS_CHARACTERSET WE8ISO8859P1

4) Run hcheck.sql

Follow My Oracle Support Knowledge Document 136697.1 hcheck.sql - Script to Check Data Dictionary for Known Problems, to run hcheck.sql, which looks for some known common Data Dictionary problems.

On DB server , source 11g environment

sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 3 22:44:43 2024

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> spool hcheck_3jun2024.out

SQL> @hcheck.sql

HCheck Version 04AUG23 on 03-JUN-2024 22:45:24

----------------------------------------------

Catalog Version 11.2.0.4.0 (1102000400)

db_name: UAT

Catalog Fixed

Procedure Name Version Vs Release Timestamp

Result

------------------------------ ... ---------- -- ---------- --------------

------

.- LobNotInObj ... 1102000400 <= *All Rel* 06/03 22:45:24 PASS

.- MissingOIDOnObjCol ... 1102000400 <= *All Rel* 06/03 22:45:24 PASS

.- SourceNotInObj ... 1102000400 <= *All Rel* 06/03 22:45:24 PASS

.- OversizedFiles ... 1102000400 <= *All Rel* 06/03 22:45:31 PASS

.- PoorDefaultStorage ... 1102000400 <= *All Rel* 06/03 22:45:31 PASS

.- PoorStorage ... 1102000400 <= *All Rel* 06/03 22:45:31 PASS

.- TabPartCountMismatch ... 1102000400 <= *All Rel* 06/03 22:45:31 PASS

.- OrphanedTabComPart ... 1102000400 <= *All Rel* 06/03 22:45:31 PASS

.- MissingSum$ ... 1102000400 <= *All Rel* 06/03 22:45:31 PASS

.- MissingDir$ ... 1102000400 <= *All Rel* 06/03 22:45:31 PASS

.- DuplicateDataobj ... 1102000400 <= *All Rel* 06/03 22:45:31 PASS

.- ObjSynMissing ... 1102000400 <= *All Rel* 06/03 22:45:31 PASS

.- ObjSeqMissing ... 1102000400 <= *All Rel* 06/03 22:45:32 PASS

.- OrphanedUndo ... 1102000400 <= *All Rel* 06/03 22:45:32 PASS

.- OrphanedIndex ... 1102000400 <= *All Rel* 06/03 22:45:32 PASS

.- OrphanedIndexPartition ... 1102000400 <= *All Rel* 06/03 22:45:32 PASS

.- OrphanedIndexSubPartition ... 1102000400 <= *All Rel* 06/03 22:45:32 PASS

.- OrphanedTable ... 1102000400 <= *All Rel* 06/03 22:45:32 PASS

.- OrphanedTablePartition ... 1102000400 <= *All Rel* 06/03 22:45:32 PASS

.- OrphanedTableSubPartition ... 1102000400 <= *All Rel* 06/03 22:45:32 PASS

.- MissingPartCol ... 1102000400 <= *All Rel* 06/03 22:45:32 PASS

.- OrphanedSeg$ ... 1102000400 <= *All Rel* 06/03 22:45:32 PASS

.- OrphanedIndPartObj# ... 1102000400 <= *All Rel* 06/03 22:45:32 PASS

.- DuplicateBlockUse ... 1102000400 <= *All Rel* 06/03 22:45:32 PASS

.- FetUet ... 1102000400 <= *All Rel* 06/03 22:45:32 PASS

.- Uet0Check ... 1102000400 <= *All Rel* 06/03 22:45:32 PASS

.- SeglessUET ... 1102000400 <= *All Rel* 06/03 22:45:32 PASS

.- BadInd$ ... 1102000400 <= *All Rel* 06/03 22:45:32 PASS

.- BadTab$ ... 1102000400 <= *All Rel* 06/03 22:45:32 PASS

.- BadIcolDepCnt ... 1102000400 <= *All Rel* 06/03 22:45:33 PASS

.- ObjIndDobj ... 1102000400 <= *All Rel* 06/03 22:45:33 PASS

.- TrgAfterUpgrade ... 1102000400 <= *All Rel* 06/03 22:45:33 PASS

.- ObjType0 ... 1102000400 <= *All Rel* 06/03 22:45:33 PASS

.- BadOwner ... 1102000400 <= *All Rel* 06/03 22:45:33 PASS

.- StmtAuditOnCommit ... 1102000400 <= *All Rel* 06/03 22:45:33 PASS

.- BadPublicObjects ... 1102000400 <= *All Rel* 06/03 22:45:33 PASS

.- BadSegFreelist ... 1102000400 <= *All Rel* 06/03 22:45:33 PASS

.- BadDepends ... 1102000400 <= *All Rel* 06/03 22:45:33 PASS

.- CheckDual ... 1102000400 <= *All Rel* 06/03 22:45:34 PASS

.- ObjectNames ... 1102000400 <= *All Rel* 06/03 22:45:34 PASS

.- BadCboHiLo ... 1102000400 <= 1202000000 06/03 22:45:34 PASS

.- ChkIotTs ... 1102000400 <= *All Rel* 06/03 22:45:34 PASS

.- NoSegmentIndex ... 1102000400 <= *All Rel* 06/03 22:45:34 PASS

.- BadNextObject ... 1102000400 <= *All Rel* 06/03 22:45:34 PASS

.- DroppedROTS ... 1102000400 <= *All Rel* 06/03 22:45:34 PASS

.- FilBlkZero ... 1102000400 <= *All Rel* 06/03 22:45:34 PASS

.- DbmsSchemaCopy ... 1102000400 <= *All Rel* 06/03 22:45:34 PASS

.- OrphanedObjError ... 1102000400 > 1102000000 06/03 22:45:34 PASS

.- ObjNotLob ... 1102000400 <= *All Rel* 06/03 22:45:34 PASS

.- MaxControlfSeq ... 1102000400 <= *All Rel* 06/03 22:45:34 PASS

.- SegNotInDeferredStg ... 1102000400 > 1102000000 06/03 22:45:34 PASS

.- SystemNotRfile1 ... 1102000400 > 902000000 06/03 22:45:35 PASS

.- DictOwnNonDefaultSYSTEM ... 1102000400 <= *All Rel* 06/03 22:45:35 PASS

.- OrphanTrigger ... 1102000400 <= *All Rel* 06/03 22:45:35 PASS

.- ObjNotTrigger ... 1102000400 <= *All Rel* 06/03 22:45:35 PASS

---------------------------------------

03-JUN-2024 22:45:35 Elapsed: 11 secs

---------------------------------------

Found 0 potential problem(s) and 0 warning(s)

PL/SQL procedure successfully completed.

Statement processed.

Complete output is in trace file:

/u01/app/oracle/adump/diag/rdbms/uat_l/UAT/trace/UAT_ora_331868_HCHECK.trc

SQL> spool off;

5) On Application Enabling Maintenance Mode:

sqlplus apps/appspassword

SQL>@$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE

use Adadmin

6) Apply the application patches using adpatch

Patches to apply. Please read the readme of all the patches.

Patch 8796558

Patch 9239090 - Release 12.1.3 - Already Applied

Patch 23569686 - R12.AD.B.delta.8 - Already Applied

Patch 27135427 - R12.TXK.B.delta.4 - Already Applied

Patch 30033914 - 19c interoperability patch for Release 12.1.3

Patch 27102203 - Prerequisite patch for 28613638 and 30824278

Patch 28613638

Patch 28685719

Patch 29178111

Patch 29414243

Patch 29583055

Patch 29905536

Patch 30370150

Patch 30601878

Patch 31209544

Patch 31406810

Patch 31470349

Patch 26663218

Once all patches all applied please validate using below query

Select bug_number,creation_date from apps.ad_bugs where bug_number in ('26663218','8796558','9239090','23569686','27135427','30033914','27102203','28613638','28685719','29178111','29414243','29583055','29905536','30370150','30601878','31209544','31406810','31470349')

Issue faced

Few loader files were having junk characters in my environment so, I needed to remove them and re-run patch to make it successful

vi /apps01/app/UAT/apps_st/appl/fnd/12.0.0/patch/115/import/afmdmsg.lct

vi /apps01/app/UAT/apps_st/appl/fnd/12.0.0/patch/115/import/aflvmlu.lct

vi /apps01/app/UAT/apps_st/appl/fnd/12.0.0/patch/115/import/afdict.lct

vi /apps01/app/UAT/apps_st/appl/fnd/12.0.0/patch/115/import/afffload.lct

vi /apps01/app/UAT/apps_st/appl/fnd/12.0.0/patch/115/import/afscprof.lct

Disable maintenance mode

sqlplus apps/appspassword

SQL>@$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE

7) Apply Patch 6400501 - Not needed in my environment as it was already applied

If you are on a UNIX/Linux platform, apply the 10.1.0.5 version of Patch 6400501 to the iAS 10.1.2 Applications tier Oracle home.

On Application Server source application environment file

cd $ORACLE_HOME/OPatch

./opatch lsinventory |grep -i 6400501

Patch 6400501 applied on Thu Mar 26 21:13:45 2009

[ Base Bug(s): 6400501 ]

8) Allow case sensitive passwords

Check SEC_CASE_SENSITIVE_LOGON parameter is database is set to false. If not set then, please set it to false.

SQL> show parameter case

NAME TYPE VALUE

------------------------------------ ----------- ----------------------sec_case_sensitive_logon boolean FALSE

9) Make appsutil directory on application and copy to DB server

On Application Server run below command

perl $AD_TOP/bin/admkappsutil.pl

Starting the generation of appsutil.zip

Log file located at /apps01/app/UAT/inst/apps/UAT_dfccofappuat1/admin/log/MakeAppsUtil_06041838.log

output located at /apps01/app/UAT/inst/apps/UAT_dfccofappuat1/admin/out/appsutil.zip

Copy to DB server oracle home and unzip.

unzip -o appsutil.zip

10)Create the initialization parameter setup files

Below 2 files will be created after successful run.

$ORACLE_HOME/dbs/<ORACLE_SID>_initparam.sql and $ORACLE_HOME/dbs/<ORACLE_SID>_datatop.txt files.

Run the following commands to create the on 11g DB Home

$ cd $ORACLE_HOME/appsutil

$ . ./txkSetCfgCDB.env dboraclehome=<full path of ORACLE_HOME>

$ export ORACLE_SID=<ORACLE_SID>

$ cd $ORACLE_HOME/appsutil/bin

$ perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=<ORACLE_HOME> \

-outdir=<ORACLE_HOME>/appsutil/log -appsuser=<apps user> \

-dbsid=<ORACLE_SID> -skipdbshutdown=yes

cd $ORACLE_HOME/appsutil

. ./txkSetCfgCDB.env dboraclehome=/u01/app/oracle/product/11.2.0.4/dbhome_1

export ORACLE_SID=UAT

cd $ORACLE_HOME/appsutil/bin

perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=/u01/app/oracle/product/11.2.0.4/dbhome_1 \

-outdir=/u01/app/oracle/product/11.2.0.4/dbhome_1/appsutil/log -appsuser=apps \

-dbsid=UAT -skipdbshutdown=yes

11) Install 19c Database Software

Refer my previous post on installing the 19c DB Home and pre-requisites.

https://www.funoracleapps.com/2021/02/how-to-install-oracle-19c-database-on.html

The 19c Oracle home must be installed on the database server node in a different directory from the current Oracle home.

Make a directory and unzip the 19c home file.

mkdir -p /u01/app/oracle/product/19.0.0

Install rpm for 19c

yum install -y oracle-database-preinstall-19c

yum install java

Start vnc server and run installer

export DISPLAY=:1

# Fake Oracle Linux 7.

export CV_ASSUME_DISTID=OEL7.6

start runinstaller.

Refer Below screenshots. Change values according to your requirements.

Run root.sh and then click ok

12) Create a temporary environment file for 19c CDB that we will create.

cat 19cdb.env

export ORACLE_HOME=/u01/app/oracle/product/19.0.0

export ORACLE_SID=UATCDB

export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/perl/bin

export ORACLE_BASE=/u01/app/oracle

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

export PERL5LIB=$ORACLE_HOME/perl/lib/5.28.1:$ORACLE_HOME/perl/lib/site_perl/5.28.1

13) Apply patches for the database home to 19.23 version.

Patches to be applied on 19C home

Platform

UNIX/Linux

Patch 36233263

Patch 36199232

Copy patch and apply in 19c home.

Download OPATCH latest and unzip in 19c Home

unzip -o p6880880_122010_LINUX.zip

[oracle@fundb OPatch]$ ./opatch version

OPatch Version: 12.2.0.1.42

OPatch succeeded.

[oracle@fundb DB_Steps]$ cd 36233263

[oracle@fundb 36233263]$ ls

custom etc files README.html README.txt

[oracle@fundb 36233263]$ echo $ORACLE_HOME

/u01/app/oracle/product/19.0.0

[oracle@fundb 36233263]$ export PATH=$PATH:/usr/ccs/bin

[oracle@fundb 36233263]$ which opatch

/u01/app/oracle/product/19.0.0/OPatch/opatch

[oracle@fundb 36233263]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Oracle Interim Patch Installer version 12.2.0.1.42

PREREQ session

Oracle Home : /u01/app/oracle/product/19.0.0

Central Inventory : /u01/oraInventory

from : /u01/app/oracle/product/19.0.0/oraInst.loc

OPatch version : 12.2.0.1.42

OUI version : 12.2.0.7.0

Log file location : /u01/app/oracle/product/19.0.0/cfgtoollogs/opatch/opatch2024-06-04_23-09-07PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

[oracle@fundb 36233263]$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.42
Copyright (c) 2024, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/oracle/product/19.0.0
Central Inventory : /u01/oraInventory
from : /u01/app/oracle/product/19.0.0/oraInst.loc
OPatch version : 12.2.0.1.42
OUI version : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/cfgtoollogs/opatch/opatch2024-06-04_23-09-47PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 36233263

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/19.0.0')

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '36233263' to OH '/u01/app/oracle/product/19.0.0'
ApplySession: Optional component(s) [ oracle.network.gsm, 19.0.0.0.0 ] , [ oracle.crypto.rsf, 19.0.0.0.0 ] , [ oracle.pg4mq, 19.0.0.0.0 ] , [ oracle.precomp.companion, 19.0.0.0.0 ] , [ oracle.rdbms.ic, 19.0.0.0.0 ] , [ oracle.rdbms.tg4db2, 19.0.0.0.0 ] , [ oracle.tfa, 19.0.0.0.0 ] , [ oracle.net.cman, 19.0.0.0.0 ] , [ oracle.oid.client, 19.0.0.0.0 ] , [ oracle.rdbms.tg4sybs, 19.0.0.0.0 ] , [ oracle.rdbms.tg4ifmx, 19.0.0.0.0 ] , [ oracle.xdk.companion, 19.0.0.0.0 ] , [ oracle.rdbms.tg4msql, 19.0.0.0.0 ] , [ oracle.rdbms.tg4tera, 19.0.0.0.0 ] , [ oracle.ons.cclient, 19.0.0.0.0 ] , [ oracle.pg4appc, 19.0.0.0.0 ] , [ oracle.options.olap, 19.0.0.0.0 ] , [ oracle.options.olap.api, 19.0.0.0.0 ] , [ oracle.ons.eons.bwcompat, 19.0.0.0.0 ] , [ oracle.network.cman, 19.0.0.0.0 ] , [ oracle.sdo.companion, 19.0.0.0.0 ] , [ oracle.jdk, 1.8.0.191.0 ] not present in the Oracle Home or a higher version is found.

Patching component oracle.rdbms.rsf, 19.0.0.0.0...

Patching component oracle.rdbms.util, 19.0.0.0.0...

Patching component oracle.rdbms, 19.0.0.0.0...

Patching component oracle.assistants.acf, 19.0.0.0.0...

Patching component oracle.assistants.deconfig, 19.0.0.0.0...

Patching component oracle.assistants.server, 19.0.0.0.0...

Patching component oracle.blaslapack, 19.0.0.0.0...

Patching component oracle.buildtools.rsf, 19.0.0.0.0...

Patching component oracle.ctx, 19.0.0.0.0...

Patching component oracle.dbdev, 19.0.0.0.0...

Patching component oracle.dbjava.ic, 19.0.0.0.0...

Patching component oracle.dbjava.jdbc, 19.0.0.0.0...

Patching component oracle.dbjava.ucp, 19.0.0.0.0...

Patching component oracle.duma, 19.0.0.0.0...

Patching component oracle.javavm.client, 19.0.0.0.0...

Patching component oracle.ldap.owm, 19.0.0.0.0...

Patching component oracle.ldap.rsf, 19.0.0.0.0...

Patching component oracle.ldap.security.osdt, 19.0.0.0.0...

Patching component oracle.marvel, 19.0.0.0.0...

Patching component oracle.network.rsf, 19.0.0.0.0...

Patching component oracle.odbc.ic, 19.0.0.0.0...

Patching component oracle.ons, 19.0.0.0.0...

Patching component oracle.ons.ic, 19.0.0.0.0...

Patching component oracle.oracore.rsf, 19.0.0.0.0...

Patching component oracle.perlint, 5.28.1.0.0...

Patching component oracle.precomp.common.core, 19.0.0.0.0...

Patching component oracle.precomp.rsf, 19.0.0.0.0...

Patching component oracle.rdbms.crs, 19.0.0.0.0...

Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...

Patching component oracle.rdbms.deconfig, 19.0.0.0.0...

Patching component oracle.rdbms.oci, 19.0.0.0.0...

Patching component oracle.rdbms.rsf.ic, 19.0.0.0.0...

Patching component oracle.rdbms.scheduler, 19.0.0.0.0...

Patching component oracle.rhp.db, 19.0.0.0.0...

Patching component oracle.rsf, 19.0.0.0.0...

Patching component oracle.sdo, 19.0.0.0.0...

Patching component oracle.sdo.locator.jrf, 19.0.0.0.0...

Patching component oracle.sqlplus, 19.0.0.0.0...

Patching component oracle.sqlplus.ic, 19.0.0.0.0...

Patching component oracle.wwg.plsql, 19.0.0.0.0...

Patching component oracle.xdk.rsf, 19.0.0.0.0...

Patching component oracle.rdbms.hsodbc, 19.0.0.0.0...

Patching component oracle.ldap.client, 19.0.0.0.0...

Patching component oracle.rdbms.rman, 19.0.0.0.0...

Patching component oracle.rdbms.dv, 19.0.0.0.0...

Patching component oracle.xdk.parser.java, 19.0.0.0.0...

Patching component oracle.ldap.rsf.ic, 19.0.0.0.0...

Patching component oracle.oraolap.api, 19.0.0.0.0...

Patching component oracle.javavm.server, 19.0.0.0.0...

Patching component oracle.mgw.common, 19.0.0.0.0...

Patching component oracle.xdk, 19.0.0.0.0...

Patching component oracle.odbc, 19.0.0.0.0...

Patching component oracle.xdk.xquery, 19.0.0.0.0...

Patching component oracle.nlsrtl.rsf, 19.0.0.0.0...

Patching component oracle.rdbms.lbac, 19.0.0.0.0...

Patching component oracle.network.client, 19.0.0.0.0...

Patching component oracle.network.listener, 19.0.0.0.0...

Patching component oracle.install.deinstalltool, 19.0.0.0.0...

Patching component oracle.ctx.atg, 19.0.0.0.0...

Patching component oracle.nlsrtl.rsf.core, 19.0.0.0.0...

Patching component oracle.nlsrtl.rsf.ic, 19.0.0.0.0...

Patching component oracle.nlsrtl.rsf, 19.0.0.0.0...

Patching component oracle.rdbms.install.common, 19.0.0.0.0...

Patching component oracle.oraolap.dbscripts, 19.0.0.0.0...

Patching component oracle.sdo.locator, 19.0.0.0.0...

Patching component oracle.rdbms.drdaas, 19.0.0.0.0...

Patching component oracle.nlsrtl.rsf.lbuilder, 19.0.0.0.0...

Patching component oracle.oraolap, 19.0.0.0.0...

Patching component oracle.ovm, 19.0.0.0.0...

Patching component oracle.rdbms.hs_common, 19.0.0.0.0...

Patching component oracle.ctx.rsf, 19.0.0.0.0...

Patching component oracle.dbtoolslistener, 19.0.0.0.0...

Patching component oracle.ldap.ssl, 19.0.0.0.0...

Patching component oracle.rdbms.install.plugins, 19.0.0.0.0...

Patching component oracle.precomp.common, 19.0.0.0.0...

Patching component oracle.precomp.lang, 19.0.0.0.0...

Patching component oracle.jdk, 1.8.0.201.0...
Patch 36233263 successfully applied.
Sub-set patch [29517242] has become inactive due to the application of a super-set patch [36233263].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /u01/app/oracle/product/19.0.0/cfgtoollogs/opatch/opatch2024-06-04_23-09-47PM_1.log

OPatch succeeded.

[oracle@fundb DB_Steps]$ cd 36199232

[oracle@fundb 36199232]$ ls

etc files README.html README.txt

[oracle@fundb 36199232]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Oracle Interim Patch Installer version 12.2.0.1.42

PREREQ session

Oracle Home : /u01/app/oracle/product/19.0.0

Central Inventory : /u01/oraInventory

from : /u01/app/oracle/product/19.0.0/oraInst.loc

OPatch version : 12.2.0.1.42

OUI version : 12.2.0.7.0

Log file location : /u01/app/oracle/product/19.0.0/cfgtoollogs/opatch/opatch2024-06-04_23-18-25PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

[oracle@fundb 36199232]$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.42
Copyright (c) 2024, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/oracle/product/19.0.0
Central Inventory : /u01/oraInventory
from : /u01/app/oracle/product/19.0.0/oraInst.loc
OPatch version : 12.2.0.1.42
OUI version : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/cfgtoollogs/opatch/opatch2024-06-04_23-18-53PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 36199232

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/19.0.0')

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '36199232' to OH '/u01/app/oracle/product/19.0.0'

Patching component oracle.javavm.server, 19.0.0.0.0...

Patching component oracle.javavm.server.core, 19.0.0.0.0...

Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...

Patching component oracle.rdbms, 19.0.0.0.0...

Patching component oracle.javavm.client, 19.0.0.0.0...
Patch 36199232 successfully applied.
Log file location: /u01/app/oracle/product/19.0.0/cfgtoollogs/opatch/opatch2024-06-04_23-18-53PM_1.log

OPatch succeeded.

Validate the patches being applied.

[oracle@fundb 36199232]$ opatch lspatches

36199232;OJVM RELEASE UPDATE: 19.23.0.0.240416 (36199232)

36233263;Database Release Update : 19.23.0.0.240416 (36233263)

29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)

OPatch succeeded.

14) Create nls/data/9idata directory on 19c Home

On the database server node, as the owner of the Oracle RDBMS file system and database instance, run the following command to create the $ORACLE_HOME/nls/data/9idata directory.

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

[oracle@fundb ~]$ perl $ORACLE_HOME/nls/data/old/cr9idata.pl

Creating directory /u01/app/oracle/product/19.0.0/nls/data/9idata ...

Copying files to /u01/app/oracle/product/19.0.0/nls/data/9idata...

Copy finished.

Please reset environment variable ORA_NLS10 to /u01/app/oracle/product/19.0.0/nls/data/9idata!

15) Copy the appsutil zip created in step number 9 in 19c Home.

cd $ORACLE_HOME

[oracle@fundb 19.0.0]$ cp /shared_moount/appsutil.zip .

uzip -o appsutil.zip

16) Copy the orai18n.jar file

Source 19c home (created in step 12)

. 19cdb.env

Copy the orai18n.jar file

Run the following command to copy the orai18n.jar file to the $ORACLE_HOME/jdk/jre/lib/ext directory.

$ cp $ORACLE_HOME/jlib/orai18n.jar $ORACLE_HOME/jdk/jre/lib/ext

17) Create Container Database on DB server.

We will create a new container database on the DB server using 19c Home.

. 19cdb.env

Run the Database Configuration Assistant (DBCA) to create the container database (CDB).
When prompted, click on the "Create Database", "Advanced Configuration", and "General Purpose or Transaction Processing" options.
In the Specify Database Identification screen, check to create an empty container database (CDB) without a PDB.
Set the Global Database Name, the SID to the new CDB SID (maximum of 8 characters), and check the "Use Local Undo tablespace for PDBs" checkbox. The CDB SID has to be different from the current ORACLE_SID, which will be the PDB SID.
In the "Network Configuration" section, do not create a listener. In the "Specify Configuration Options" section, set the SGA and PGA sizes to 2G and 1G respectively.
Important Click on the Character Sets tab and choose the Character Set and National Character Set to be the same as in the source database. If the appropriate Character Set does not show up, uncheck the "Show recommended character sets only" box. (identified in Step 3).
In the "Select Database Creation Option" section, click on the "Customize Storage Locations" button. Set the size of the redo log files to be the same as in the source database. Other options can be configured as appropriate.
During the CDB creation, ignore ORA-00313 and ORA-27037 error messages about redo logs in the alert.log file. These messages are informational.

CDB Name I have given as UATCDB. Case sensitive

Once the DB is created we can verify the CDB is up and running.

ps -ef |grep -i pmon

18) Run Datapatch on CDB

Use the following commands to load any necessary patches on the CDB.

On UNIX/Linux:

. 19cdb.env

$ export ORACLE_SID=<CDB SID>

$ $ORACLE_HOME/OPatch/datapatch

[oracle@fundb ~]$ $ORACLE_HOME/OPatch/datapatch

SQL Patching tool version 19.23.0.0.0 Production on Wed Jun 5 00:10:11 2024

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_412708_2024_06_05_00_10_11/sqlpatch_invocation.log

Connecting to database...OK

Gathering database info...done

Note: Datapatch will only apply or rollback SQL fixes for PDBs

that are in an open state, no patches will be applied to closed PDBs.

Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation

(Doc ID 1585822.1)

Bootstrapping registry and package to current versions...done

Determining current state...done

Current state of interim SQL patches:

Interim patch 36199232 (OJVM RELEASE UPDATE: 19.23.0.0.240416 (36199232)):

Binary registry: Installed

PDB CDB$ROOT: Applied successfully on 04-JUN-24 11.46.07.047138 PM

PDB PDB$SEED: Applied successfully on 04-JUN-24 11.58.56.395362 PM

Current state of release update SQL patches:

Binary registry:

19.23.0.0.0 Release_Update 240406004238: Installed

PDB CDB$ROOT:

Applied 19.23.0.0.0 Release_Update 240406004238 successfully on 04-JUN-24 11.53.00.405006 PM

PDB PDB$SEED:

Applied 19.23.0.0.0 Release_Update 240406004238 successfully on 05-JUN-24 12.03.41.226652 AM

Adding patches to installation queue and performing prereq checks...done

Installation queue:

For the following PDBs: CDB$ROOT PDB$SEED

No interim patches need to be rolled back

No release update patches need to be installed

No interim patches need to be applied

SQL Patching tool complete on Wed Jun 5 00:10:27 2024

19) Create CDB MGDSYS Schema

. 19cdb.env

Use SQL*Plus to connect to the CDB as SYSDBA and run the $ORACLE_HOME/rdbms/admin/catmgd.sql script. This creates the new MGDSYS schema on the CDB.

$ sqlplus "/ as sysdba" @?/rdbms/admin/catmgd.sql

20) Create CDB TNS Files

. 19cdb.env

On the database server node, run the following perl script to generate the required TNS files. Note that this script does not create a listener.

Script Format

cd $ORACLE_HOME/appsutil

. ./txkSetCfgCDB.env dboraclehome=<full path of ORACLE_HOME>

cd $ORACLE_HOME/appsutil/bin

perl txkGenCDBTnsAdmin.pl -dboraclehome=<ORACLE_HOME> \

-cdbname=<CDB SID> -cdbsid=<CDB SID> -dbport=<Database port> \

-outdir=<ORACLE_HOME>/appsutil/log

cd $ORACLE_HOME/appsutil

. ./txkSetCfgCDB.env dboraclehome=/u01/app/oracle/product/19.0.0

cd $ORACLE_HOME/appsutil/bin

perl txkGenCDBTnsAdmin.pl -dboraclehome=/u01/app/oracle/product/19.0.0 \

-cdbname=UATCDB -cdbsid=UATCDB -dbport=1532 \

-outdir=/u01/app/oracle/product/19.0.0/appsutil/log

21) Shut down the CDB

Use SQL*Plus to connect to the CDB as SYSDBA and use the following command to shut down the database:

$ sqlplus "/ as sysdba"

SQL> shutdown;

22) UTL file dir setup details

On 11g DB run the below commands

Run the txkCfgUtlfileDir.pl script in getUtlFileDir mode using the following command:

Source 11g home

$ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=<DB Context File> \

-oraclehome=<11g/12c ORACLE_HOME> -outdir=<Output/Log location> \

-upgradedhome=<19c ORACLE_HOME> -mode=getUtlFileDir -servicetype=onpremise|opc

[oracle@fundb ~]$ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=/u01/app/oracle/product/11.2.0.4/dbhome_1/appsutil/UAT_fundb.xml \
-oraclehome=/u01/app/oracle/product/11.2.0.4/dbhome_1 -outdir=/u01/app/oracle/product/11.2.0.4/dbhome_1/appsutil/log \
-upgradedhome=/u01/app/oracle/product/19.0.0 -mode=getUtlFileDir -servicetype=onpremise

Enter the APPS Password:

*** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS
*** Log File = /u01/app/oracle/product/11.2.0.4/dbhome_1/appsutil/log/TXK_UTIL_DIR_Wed_Jun_5_16_03_52_2024/txkCfgUtlfileDir.log
Program : started @ Wed Jun 5 16:03:54 2024

*** Log File = /u01/app/oracle/product/11.2.0.4/dbhome_1/appsutil/log/TXK_UTIL_DIR_Wed_Jun_5_16_03_52_2024/txkCfgUtlfileDir.log

Script Name : txkCfgUtlfileDir.pl
Script Version : 120.0.12010000.9
Started : Wed Jun 5 16:03:54 +0530 2024

Log File : /u01/app/oracle/product/11.2.0.4/dbhome_1/appsutil/log/TXK_UTIL_DIR_Wed_Jun_5_16_03_52_2024/txkCfgUtlfileDir.log

Context file: /u01/app/oracle/product/11.2.0.4/dbhome_1/appsutil/UAT_fundb.xml exists.

==============================================================================

Successfully generated the below file with UTL_FILE_DIR content:
/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/UAT_utlfiledir.txt

==============================================================================

Completed : Wed Jun 5 16:03:54 +0530 2024

Successfully Completed the script
ERRORCODE = 0 ERRORCODE_END

Validate the UTL path required /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/UAT_utlfiledir.txt .Make changes as required.

On 11g environment

$ . $ORACLE_HOME/<sid>_<hostname>.env

Run the txkCfgUtlfileDir.pl script in setUtlFileDir mode using the following command:

$ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=<DB Context File> \

-oraclehome=<11g/12c ORACLE_HOME> -outdir=<Output/Log location> \

-upgradedhome=<19c ORACLE_HOME> -mode=setUtlFileDir -servicetype=onpremise|opc [ -skipdirvalidation=Yes ]

[oracle@fundb dbs]$ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=/u01/app/oracle/product/11.2.0.4/dbhome_1/appsutil/UAT_fundb.xml \
-oraclehome=/u01/app/oracle/product/11.2.0.4/dbhome_1 -outdir=/u01/app/oracle/product/11.2.0.4/dbhome_1/appsutil/log \
-upgradedhome=/u01/app/oracle/product/19.0.0 -mode=setUtlFileDir -servicetype=onpremise

Enter the APPS Password:

Enter the SYSTEM Password:

*** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS
*** Log File = /u01/app/oracle/product/11.2.0.4/dbhome_1/appsutil/log/TXK_UTIL_DIR_Wed_Jun_5_16_07_58_2024/txkCfgUtlfileDir.log
Program : started @ Wed Jun 5 16:08:04 2024

*** Log File = /u01/app/oracle/product/11.2.0.4/dbhome_1/appsutil/log/TXK_UTIL_DIR_Wed_Jun_5_16_07_58_2024/txkCfgUtlfileDir.log

Script Name : txkCfgUtlfileDir.pl
Script Version : 120.0.12010000.9
Started : Wed Jun 5 16:08:04 +0530 2024

Log File : /u01/app/oracle/product/11.2.0.4/dbhome_1/appsutil/log/TXK_UTIL_DIR_Wed_Jun_5_16_07_58_2024/txkCfgUtlfileDir.log

Context file: /u01/app/oracle/product/11.2.0.4/dbhome_1/appsutil/UAT_fundb.xml exists.

** WARNING: Incorrect value s_applptmp detected on Apps Tier nodes. Please check log for details

Completed : Wed Jun 5 16:08:05 +0530 2024

Successfully Completed the script
ERRORCODE = 0 ERRORCODE_END

23) Shut Down application Tier services Now.

On each application tier server node, shut down all server processes or services. The applications will be unavailable to users until all remaining tasks in this section are complete

24) Drop SYS.ENABLED$INDEXES (Optional)

If the SYS.ENABLED$INDEXES table exists, connect to the database as SYSDBA and run the following command to drop the table:

Source 11g DB

$ sqlplus "/ as sysdba"

SQL> drop table sys.enabled$indexes;

25) Remove the MGDSYS schema (Optional)

If you are upgrading from a database version prior to Oracle 12c, log on to the old database server node, use SQL*Plus to connect to the database as SYSDBA, and run the $ORACLE_HOME/md/admin/catnomgdidcode.sql script. This drops the MGDSYS schema.

Source 11g environment

$ sqlplus "/ as sysdba" @?/md/admin/catnomgdidcode.sql

26) Prepare for Upgrade

On 11g DB

alter system set local_listener='';

System altered.

Ensure that the oratab file contains an entry for the database to be upgraded.

Modify the following initialization parameters before the upgrade:

Comment out all the deprecated initalization parameters. Any necessary parameter will be added back in after the upgrade.

Unset the olap_page_pool_size initialization parameter.

If you have an 11g source database, set the PGA_AGGREGATE_TARGET initialization parameter to at least 10G.

Set the SGA_TARGET initialization parameter to at least 2G.

Add the event EVENT='10946 trace name context forever, level 8454144'

SQL> alter system set olap_page_pool_size=0 scope=spfile;

System altered.

SQL> show parameter pga

NAME TYPE VALUE

------------------------------------ ----------- ----------------------

pga_aggregate_target big integer 12G

SQL> show parameter sga

NAME TYPE VALUE

------------------------------------ ----------- ----------------------

lock_sga boolean FALSE

pre_page_sga boolean FALSE

sga_max_size big integer 20G

sga_target big integer 20G

SQL> alter system set event='10946 trace name context forever, level 8454144' scope=spfile;

System altered.

Shut down and restart the database to enable the parameters.

27) DB Upgrade Steps

Source 11g Environment

Run the Pre-upgrade Tool:

$ORACLE_HOME/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/rdbms/admin/preupgrade.jar TERMINAL TEXT

Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 13 on 2024-06-05T16:21:48

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
Database Name: UAT
Container Name: Not Applicable in Pre-12.1 database
Container ID: Not Applicable in Pre-12.1 database
Version: 11.2.0.4.0
DB Patch Level: No Patch Bundle applied
Compatible: 11.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 14
Database log mode: ARCHIVELOG
Readonly: FALSE
Edition: EE

Oracle Component Upgrade Action Current Status
---------------- -------------- --------------
Oracle Server [to be upgraded] VALID
JServer JAVA Virtual Machine [to be upgraded] VALID
Oracle XDK for Java [to be upgraded] VALID
Real Application Clusters [to be upgraded] INVALID
OLAP Analytic Workspace [to be upgraded] VALID
Oracle Text [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
Oracle Java Packages [to be upgraded] VALID
Oracle Multimedia [to be upgraded] VALID
Oracle Spatial [to be upgraded] VALID
Expression Filter [to be upgraded] VALID
Oracle OLAP API [to be upgraded] VALID

==============
BEFORE UPGRADE
==============

REQUIRED ACTIONS
================
1. (AUTOFIXUP) Empty the RECYCLEBIN immediately before database upgrade.

The database contains 98 objects in the recycle bin.

The recycle bin must be completely empty before database upgrade.

RECOMMENDED ACTIONS
===================
2. Remove initialization parameters that Oracle has obsoleted or removed.
This action may be done now or when starting the database in upgrade mode
using the target ORACLE HOME.

Parameter
---------
O7_DICTIONARY_ACCESSIBILITY
utl_file_dir

If parameters that are obsolete or removed from the target release are
present in the pfile/spfile, the Oracle database may not start, or it may
start with an ORA- error.

3. Run 11.2.0.4.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid
objects. You can view the individual invalid objects with

SET SERVEROUTPUT ON;
EXECUTE DBMS_PREUP.INVALID_OBJECTS;

52 objects are INVALID.

There should be no INVALID objects in SYS/SYSTEM or user schemas before
database upgrade.

4. Remove OLAP Catalog by running the 11.2.0.4.0 SQL script
$ORACLE_HOME/olap/admin/catnoamd.sql script.

The OLAP Catalog component, AMD, exists in the database.

Starting with Oracle Database 12c, the OLAP Catalog (OLAP AMD) is
desupported and will be automatically marked as OPTION OFF during the
database upgrade if present. Oracle recommends removing OLAP Catalog
(OLAP AMD) before database upgrade. This step can be manually performed
before the upgrade to reduce downtime.

5. Explicitly set RESOURCE_LIMIT to FALSE in the pfile/spfile to retain the
previous behavior.

the RESOURCE_LIMIT initialization parameter is not explicitly set in the
pfile/spfile.

The RESOURCE_LIMIT initialization parameter default value changed from
FALSE to TRUE in 12.1.0.2 onwards. Without an explicit setting, the
upgrade may introduce unintented RESOURCE_LIMIT enforcement.

6. To remove duplicate objects, refer to Doc ID 1030426.6.
To query for these duplicates owned by both SYS and SYSTEM, run:
SELECT object_name, object_type
FROM dba_objects
WHERE object_name||'_'||object_type IN
(SELECT object_name||'_'||object_type
FROM dba_objects WHERE owner = 'SYS')
AND owner = 'SYSTEM' AND object_name NOT IN ('AQ$_SCHEDULES',
'AQ$_SCHEDULES_PRIMARY','DBMS_REPCAT_AUTH','DBMS_REPCAT_AUTH');

There exists in the database duplicate object(s) that are owned by both
SYS and SYSTEM.

If an install script had been run as both SYS and SYSTEM, then it is
possible to have duplicate objects (with same object names and types)
owned by both of these Oracle users.

7. Backup the existing ACLs and their assignments for reference. Use the new
DBMS_NETWORK_ACL_ADMIN interfaces and dictionary views to administer
network privileges after upgrade.

The database contains network ACLs with privileges that will be migrated
to a new format in 12c.

Network access control list (ACL) privileges in 11g will be migrated to a
new format in 12c. As part of the migration, new DBMS_NETWORK_ACL_ADMIN
interfaces and dictionary views are provided, and privileges in the
existing ACLs will be converted to the new format with new ACL names. The
old ACL names, DBMS_NETWORK_ACL_ADMIN interfaces and dictionary views may
continue to be used but are deprecated and their use is discouraged. For
further information, refer to My Oracle Support note number 2078710.1.

8. Perform one of the following:
1) Expire user accounts that use only the old 10G password version and
follow the procedure recommended in Oracle Database Upgrade Guide under
the section entitled, "Checking for Accounts Using Case-Insensitive
Password Version".
2) Explicitly set SQLNET.ALLOWED_LOGON_VERSION_SERVER in the 19
SQLNET.ORA to a non-Exclusive Mode value, such as "11". (This is a short
term approach and is not recommended because it will retain known
security risks associated with the 10G password version.)

Your database system has at least one account with only the 10G password
version (see the PASSWORD_VERSIONS column of DBA_USERS).

Starting with Oracle Database release 12.2.0.1, Exclusive Mode is the new
default password-based authentication mode. All Exclusive Mode
login/authentication attempts will fail for preexisting user accounts
which only have the 10G password version and neither the 11G or 12C
password version (see DBA_USERS.PASSWORD_VERSIONS.) For more information,
refer to "Understanding Password Case Sensitivity and Upgrades" in the
Oracle Database Upgrade Guide.

9. Remove the SEC_CASE_SENSITIVE_LOGON instance initialization parameter
setting, to configure your system to use case sensitive password
authentication by default.

Your database system is configured to enforce case insensitive password
authentication (the SEC_CASE_SENSITIVE_LOGON instance initialization
parameter setting is FALSE).

Starting with Oracle Database release 12.2, Exclusive Mode is the default
password-based authentication mode. Case insensitive password
authentication is not supported in Exclusive Mode. If your system needs
to use case insensitive password authentication, Exclusive Mode must be
switched off prior to the upgrade. See the Network Reference Manual
chapter about the SQLNET.ORA parameter
SQLNET.ALLOWED_LOGON_VERSION_SERVER for more details on Exclusive Mode.

10. Remove Streams setup. For detailed steps, refer to the section "Removing
an Oracle Streams Configuration" in the Oracle Streams Concepts and
Administration Guide specific for the Oracle release from which you are
removing. For versions pre-12.1.0.2, the procedure
dbms_streams_adm.remove_streams_configuration must not be used as may
lead to unwanted results. Instead, use the other procedures
(dbms_capture_adm.drop_capture, dbms_apply_adm.drop_apply,
dbms_streams_adm.remove_queue, etc). For 12.1.0.2 and higher, procedure
dbms_streams_adm.remove_streams_configuration can be safely used.

Oracle Streams feature is configured in the database.

Starting with Oracle Database 19, Oracle Streams is desupported. It is
strongly advised to remove any streams configuration manually.

11. Please make sure that all the MVs are refreshed and sys.sumdelta$ becomes
empty before doing upgrade, unless you have strong business reasons not
to do so. You can use dbms_mview.refresh() to refresh the MVs except
those stale ones to be kept due to business need. If there are any stale
MVs depending on changes in sys.sumdelta$, do not truncate it, because
doing so will cause wrong results after refresh. Please refer to the
Materialized View section in MOS Note 2380601.1 for more details.

There are one or more materialized views in either stale or invalid
state, or which are currently being refreshed.

Oracle recommends that all materialized views (MV's) are refreshed before
upgrading the database because this will clear the MV logs and the
sumdelta$ table and may reduce the upgrade time. If you choose to not
refresh some MVs, the change data for those MV's will be carried through
the UPGRADE process. After UPGRADE, you can refresh the MV's and MV
incremental refresh should work in normal cases.

12. Review and remove any unnecessary HIDDEN/UNDERSCORE parameters.

The database contains the following initialization parameters whose name
begins with an underscore:

_system_trig_enabled
_sort_elimination_cost_ratio
_b_tree_bitmap_plans
_fast_full_scan_enabled
_like_with_bind_as_equality
_optimizer_autostats_job
_trace_files_public

Remove hidden parameters before database upgrade unless your application
vendors and/or Oracle Support state differently. Changes will need to be
made in the pfile/spfile.

13. Review and remove any unnecessary EVENTS.

The database contains events.

There are events set that should be removed before upgrade, unless your
application vendors and/or Oracle Support state differently. Changes
will need to be made in the pfile/spfile.

14. (AUTOFIXUP) Gather stale data dictionary statistics prior to database
upgrade in off-peak time using:

EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

Dictionary statistics do not exist or are stale (not up-to-date).

Dictionary statistics help the Oracle optimizer find efficient SQL
execution plans and are essential for proper upgrade timing. Oracle
recommends gathering dictionary statistics in the last 24 hours before
database upgrade.

For information on managing optimizer statistics, refer to the 11.2.0.4
Oracle Database Performance Tuning Guide.

INFORMATION ONLY
================
15. Here are ALL the components in this database registry:

Component Current Current Original Previous Component
CID Version Status Version Version Schema
--------- ----------- ----------- ----------- ----------- -----------
AMD 11.2.0.4.0 VALID OLAPSYS
APS 11.2.0.4.0 VALID 11.2.0.4.0 11.2.0.4.0 SYS
CATALOG 11.2.0.4.0 VALID SYS
CATJAVA 11.2.0.4.0 VALID SYS
CATPROC 11.2.0.4.0 VALID SYS
CONTEXT 11.2.0.4.0 VALID 11.2.0.4.0 11.2.0.4.0 CTXSYS
EXF 11.2.0.4.0 VALID EXFSYS
JAVAVM 11.2.0.4.0 VALID SYS
ORDIM 11.2.0.4.0 VALID ORDSYS
RAC 11.2.0.4.0 INVALID SYS
SDO 11.2.0.4.0 VALID MDSYS
XDB 11.2.0.4.0 VALID XDB
XML 11.2.0.4.0 VALID SYS
XOQ 11.2.0.4.0 VALID 11.2.0.4.0 11.2.0.4.0 SYS

Review the information before upgrading.

16. Consider removing the following deprecated initialization parameters.

Parameter
---------
sec_case_sensitive_logon

These deprecated parameters probably will be obsolete in a future release.

17. Run $ORACLE_HOME/rdbms/admin/catnoexf.sql located in the new Oracle
Database Oracle home to remove both EXF and RUL.

Expression Filter (EXF) or Rules Manager (RUL) exist in the database.

Starting with Oracle Database release 12.1, the Expression Filter (EXF)
and Database Rules Manager (RUL) features are desupported, and are
removed during the upgrade process. This step can be manually performed
before the upgrade to reduce downtime.

18. Ensure there is additional disk space in LOG_ARCHIVE_DEST_1 for at least
4486 MB of archived logs. Check alert log during the upgrade that there
is no write error to the destination due to lack of disk space.

Archiving cannot proceed if the archive log destination is full during
upgrade.

Archive Log Destination:
Parameter : LOG_ARCHIVE_DEST_1
Destination : /oradata01/PROD/archive

The database has archiving enabled. The upgrade process will need free
disk space in the archive log destination(s) to generate archived logs to.

19. Check the Oracle Backup and Recovery User's Guide for information on how
to manage an RMAN recovery catalog schema.

If you are using a version of the recovery catalog schema that is older
than that required by the RMAN client version, then you must upgrade the
catalog schema.

It is good practice to have the catalog schema the same or higher version
than the RMAN client version you are using.

20. Here is a count of invalid objects by users:

User Name Number of INVALID Objects
--------------------------- -------------------------
APPS 48
DFCC 3
DFCC_GL_EXTRACT 1

Review the information before upgrading.

ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database UAT
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following

SQL>@/u01/app/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/UAT_L/preupgrade/
preupgrade_fixups.sql

=============
AFTER UPGRADE
=============

REQUIRED ACTIONS
================
None

RECOMMENDED ACTIONS
===================
21. (AUTOFIXUP) If you use the -T option for the database upgrade, then run
$ORACLE_HOME/rdbms/admin/utluptabdata.sql after the upgrade is complete,
to VALIDATE and UPGRADE any user tables affected by changes to
Oracle-Maintained types.

There are user tables dependent on Oracle-Maintained object types.

If the -T option is used to set user tablespaces to READ ONLY during the
upgrade, user tables in those tablespaces, that are dependent on
Oracle-Maintained types, will not be automatically upgraded. If a type is
evolved during the upgrade, any dependent tables need to be re-validated
and upgraded to the latest type version AFTER the database upgrade
completes.

22. Upgrade the database time zone file using the DBMS_DST package.

The database is using time zone file version 14 and the target 19 release
ships with time zone file version 32.

Oracle recommends upgrading to the desired (latest) version of the time
zone file. For more information, refer to "Upgrading the Time Zone File
and Timestamp with Time Zone Data" in the 19 Oracle Database
Globalization Support Guide.

23. Recreate directory objects to remove any symbolic links from directory
paths. To identify paths that contain symbolic links before upgrading,
use OS commands like UNIX file or WINDOWS dir. After upgrading, run
$ORACLE_HOME/rdbms/admin/utldirsymlink.sql to identify directory objects
with symbolic links in the path.

Found 12 user directory objects to be checked: AFCHRCHK_LOG_DBOBJECT,
APPS_DATA_FILE_DIR, CSR_XML_TOP, DATA_PUMP_BI, DAT_DIR, DB_MIGRATION,
DMPDIR, EBS_DB_DIR_UTIL, ECX_UTL_LOG_DIR_OBJ, ECX_UTL_XSLT_DIR_OBJ,
FND_DIAG_DIR, ODPDIR.

Starting in Release 18c, symbolic links are not allowed in directory
object paths used with BFILE data types, the UTL_FILE package, or
external tables.

24. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
command:

EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

Oracle recommends gathering dictionary statistics after upgrade.

Dictionary statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. After a database
upgrade, statistics need to be re-gathered as there can now be tables
that have significantly changed during the upgrade or new tables that do
not have statistics gathered yet.

25. Gather statistics on fixed objects after the upgrade and when there is a
representative workload on the system using the command:

EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

This recommendation is given for all preupgrade runs.

Fixed object statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. Those
statistics are specific to the Oracle Database release that generates
them, and can be stale upon database upgrade.

For information on managing optimizer statistics, refer to the 11.2.0.4
Oracle Database Performance Tuning Guide.

INFORMATION ONLY
================
26. Check the Oracle documentation for the identified components for their
specific upgrade procedure.

The database upgrade script will not upgrade the following Oracle
components: OLAP Catalog

The Oracle database upgrade script upgrades most, but not all Oracle
Database components that may be installed. Some components that are not
upgraded may have their own upgrade scripts, or they may be deprecated or
obsolete.

ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database UAT
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following

SQL>@/u01/app/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/UAT_L/preupgrade/
postupgrade_fixups.sql

==================
PREUPGRADE SUMMARY
==================
/u01/app/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/UAT_L/preupgrade/preupgrade.log
/u01/app/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/UAT_L/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/UAT_L/preupgrade/postupgrade_fixups.sql

Do the fixes as recommended.

Execute fixup scripts as indicated below:

Before upgrade

On 11g DB

Log into the database and execute the preupgrade fixups

@/u01/app/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/UAT_L/preupgrade/preupgrade_fixups.sql

There would fixups which needs to be done manually.

Drop Duplicate SYS and SYSTEM Objects

set pause off

set heading off

set pagesize 0

set feedback off

set verify off

spool dropsys.sql

select 'DROP ' || object_type || ' SYSTEM.' || object_name || ';'

from dba_objects

where object_name not in ('AQ$_SCHEDULES_PRIMARY','DBMS_REPCAT_AUTH','AQ$_SCHEDULES','PRODUCT_USER_PROFILE','SQLPLUS_PRODUCT_PROFILE','PRODUCT_PRIVS','HELP','HELP_TOPIC_SEQ') and object_name||object_type in

(select object_name||object_type

from dba_objects

where owner = 'SYS')

and owner = 'SYSTEM';

spool off

exit

SQL> DROP PACKAGE SYSTEM.HTP;

DROP PACKAGE SYSTEM.OWA;

DROP PACKAGE SYSTEM.OWA_COOKIE;

DROP PACKAGE SYSTEM.OWA_IMAGE;

DROP PACKAGE SYSTEM.OWA_PATTERN;

DROP PACKAGE SYSTEM.OWA_SEC;

DROP PACKAGE SYSTEM.OWA_TEXT;

DROP PACKAGE SYSTEM.OWA_UTIL;

Package dropped.

SQL>

Package dropped.

SQL>

Package dropped.

SQL>

Package dropped.

SQL>

Package dropped.

SQL>

Package dropped.

SQL>

Package dropped.

SQL>

Package dropped.

SQL> drop package system.HTF;

Package dropped.

Validate entry in /etc/oratab

Entry in etc/oratab file

UAT:/u01/app/oracle/product/11.2.0.4/dbhome_1:N

UATCDB:/u01/app/oracle/product/19.0.0:N

From 19c Home Launch the Database upgrade Assistant. Remember the 11g NON CDB will be upgraded to 19c NON CDB Database

$ORACLE_HOME/bin/dbua -keepEvents

Post-Upgrade Tasks

Source 19c Environment

. 19cdb.env

export ORACLE_SID=UAT

Run post upgrade fixes.

SQL> @/u01/app/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/UAT_L/preupgrade/postupgrade_fixups.sql

Session altered.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Package created.

No errors.

Package body created.

PL/SQL procedure successfully completed.

No errors.

Package created.

No errors.

Package body created.

No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 13
Generated on: 2024-06-05 16:41:54

For Source Database: UAT
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 19.0.0.0.0

Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
15. depend_usr_tables YES None.
16. old_time_zones_exist YES None.
17. dir_symlinks NO Manual fixup recommended.
18. post_dictionary YES None.
19. post_fixed_objects NO Informational only.
Further action is optional.

The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database upgrade is not
fully complete. To resolve the outstanding issues, start by reviewing
the postupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.

PL/SQL procedure successfully completed.

Session altered.

Update the parameters in 19c NON CDB database which we upgraded.

. 19cdb.env

export ORACLE_SID=UAT

[oracle@fundb ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 5 21:47:16 2024

Version 19.23.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.23.0.0.0

There are few important post-upgrade miscellaneous tasks that need to be performed.

If you previously had the SEC_CASE_SENSITIVE_LOGON initialization parameter set to FALSE, re-enable the parameter.

In case spfile is not there then create spfile and restart database.

SQL> alter system set SEC_CASE_SENSITIVE_LOGON=false;

System altered.

SQL> alter system set compatible='19.0.0' scope=spfile;

System altered.

Restart DB

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 2.1475E+10 bytes

Fixed Size 13683928 bytes

Variable Size 2952790016 bytes

Database Buffers 1.8455E+10 bytes

Redo Buffers 53424128 bytes

Database mounted.

Database opened.

SQL> show parameter compatible

NAME TYPE VALUE

------------------------------------ ----------- ----------------------

compatible string 19.0.0

noncdb_compatible boolean FALSE

Run DataPatch

Source 19c Environment

. 19cdb.env

export ORACLE_SID=UAT

[oracle@fundb ~]$ $ORACLE_HOME/OPatch/datapatch

SQL Patching tool version 19.23.0.0.0 Production on Wed Jun 5 21:58:27 2024

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_441914_2024_06_05_21_58_27/sqlpatch_invocation.log

Connecting to database...OK

Gathering database info...done

Bootstrapping registry and package to current versions...done

Determining current state...done

Current state of interim SQL patches:

Interim patch 36199232 (OJVM RELEASE UPDATE: 19.23.0.0.240416 (36199232)):

Binary registry: Installed

SQL registry: Applied successfully on 05-JUN-24 05.08.07.764336 PM

Current state of release update SQL patches:

Binary registry:

19.23.0.0.0 Release_Update 240406004238: Installed

SQL registry:

Applied 19.23.0.0.0 Release_Update 240406004238 successfully on 05-JUN-24 05.08.07.738136 PM

Adding patches to installation queue and performing prereq checks...done

Installation queue:

No interim patches need to be rolled back

No release update patches need to be installed

No interim patches need to be applied

SQL Patching tool complete on Wed Jun 5 21:58:37 2024

28) Run ADGRANTS.sql

Copy $APPL_TOP/admin/adgrants.sql from the apps server node to the database server node. Use SQL*Plus to connect to the database as SYSDBA and run the script using the following command:

. 19cdb.env

$ export ORACLE_SID=UAT

$ sqlplus "/ as sysdba" @adgrants.sql <apps user>

29) Grant create procedure privilege on CTXSYS

Copy $AD_TOP/patch/115/sql/adctxprv.sql from the apps 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:

. 19cdb.env

$ export ORACLE_SID=UAT

$ sqlplus apps/apps @adctxprv.sql sys123 CTXSYS

30) Compile invalid objects

Use SQL*Plus to connect to the database as SYSDBA and run the $ORACLE_HOME/rdbms/admin/utlrp.sql script to compile invalid objects.

. 19cdb.env

$ export ORACLE_SID=UAT

$ sqlplus "/ as sysdba" @$ORACLE_HOME/rdbms/admin/utlrp.sql

SQL> Select count(*) from dba_objects where status='INVALID';

COUNT(*)

----------

31) Grant datastore access

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

. 19cdb.env

$ export ORACLE_SID=UAT

$ sqlplus "/ as sysdba"

SQL> grant text datastore access to public;

32) Validate Workflow ruleset

Copy $FND_TOP/patch/115/sql/wfaqupfix.sql from the apps 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:

. 19cdb.env

$ export ORACLE_SID=UAT

$ sqlplus apps/apps @wfaqupfix.sql applsys apps

33) Gather statistics for SYS schema

Copy $APPL_TOP/admin/adstats.sql from the apps server node to the database server node. Note that adstats.sql has to be run in restricted mode. Use SQL*Plus to connect to the database as SYSDBA and use the following commands to run adstats.sql in restricted mode:

. 19cdb.env

$ export ORACLE_SID=UAT

$ sqlplus "/ as sysdba"

SQL> alter system enable restricted session;

SQL> @adstats.sql

$ sqlplus "/ as sysdba"

SQL> alter system disable restricted session;

SQL> exit;

34) Create the new MGDSYS schema (Optional)

If you upgraded from an RDBMS version prior to Oracle 12c, use SQL*Plus to connect to the database as SYSDBA and run the $ORACLE_HOME/rdbms/admin/catmgd.sql script. This creates the new MGDSYS schema.

. 19cdb.env

$ export ORACLE_SID=UAT

$ sqlplus "/ as sysdba" @?/rdbms/admin/catmgd.sql

35) Convert database to Multitenant

Create the PDB descriptor

Perform the following commands to create the PDB descriptor file in the $ORACLE_HOME/dbs directory.

. 19cdb.env

cd $ORACLE_HOME/appsutil

. ./txkSetCfgCDB.env dboraclehome=/u01/app/oracle/product/19.0.0

export ORACLE_SID=UAT

cd $ORACLE_HOME/appsutil/bin

perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=/u01/app/oracle/product/19.0.0 \

-outdir=/u01/app/oracle/product/19.0.0/appsutil/log -appsuser=apps -dbsid=UAT

Update the 19c CDB initialization parameters

On the database server node, copy the <source SID>_initparam.sql and <source SID>_datatop.txt files from the source $ORACLE_HOME/dbs directory to the new $ORACLE_HOME/dbs directory. Created in Step 10 earlier. Then use SQL*Plus to connect to the CDB as SYSDBA, and run the following commands to update the CDB initialization parameters:

cp UAT_initparam.sql UAT_datatop.txt UAT_utlfiledir.txt $ORACLE_HOME/dbs

. 19cdb.env

$ cd $ORACLE_HOME/appsutil

$ . ./txkSetCfgCDB.env dboraclehome=/u01/app/oracle/product/19.0.0

$ export ORACLE_SID=UATCDB

$ sqlplus "/ as sysdba"

SQL> startup nomount;

SQL> @$ORACLE_HOME/dbs/UAT_initparam.sql

SQL> alter system set LOCAL_LISTENER="fundb:1532" scope=both;

SQL> shutdown;

SQL> startup;

Check for PDB violations

Use the following commands to run the txkChkPDBCompatability.pl script. This checks the PDB for any violations.

. 19cdb.env

$ cd $ORACLE_HOME/appsutil

$ . ./txkSetCfgCDB.env dboraclehome=/u01/app/oracle/product/19.0.0

$ export ORACLE_SID=UATCDB

$ cd $ORACLE_HOME/appsutil/bin

$perl txkChkPDBCompatability.pl -dboraclehome=/u01/app/oracle/product/19.0.0 \

-outdir=/u01/app/oracle/product/19.0.0/appsutil/log -cdbsid=UATCDB \

-pdbsid=UAT -servicetype=onpremise

Fix any issues as reported.

Create the PDB

Load the environment variables by running the following commands:

. 19cdb.env

$ cd $ORACLE_HOME/appsutil

$ . ./txkSetCfgCDB.env dboraclehome=/u01/app/oracle/product/19.0.0

$ cd $ORACLE_HOME/appsutil/bin

perl txkCreatePDB.pl -dboraclehome=/u01/app/oracle/product/19.0.0 -outdir=/u01/app/oracle/product/19.0.0/appsutil/log \

-cdbsid=UATCDB -pdbsid=UAT -dbuniquename=UATCDB -servicetype=onpremise

Run the post PDB script

Use the following commands to run the txkPostPDBCreationTasks.pl script. This updates the PDB configuration.

Add UAT entry in tnsnames.ora

. 19cdb.env

export ORACLE_SID=UATCDB

$ cd $ORACLE_HOME/appsutil

SQL> alter session set container=UAT;

Session altered.

SQL> grant SELECT_CATALOG_ROLE to apps;

Grant succeeded.

export ORACLE_SID=UAT

$ . ./txkSetCfgCDB.env dboraclehome=/u01/app/oracle/product/19.0.0

$ perl $ORACLE_HOME/appsutil/bin/txkPostPDBCreationTasks.pl -dboraclehome=/u01/app/oracle/product/19.0.0 \

-outdir=/u01/app/oracle/product/19.0.0/appsutil/log -cdbsid=UATCDB -pdbsid=UAT \

-appsuser=apps -dbport=1532 -servicetype=onpremise

The port number is for CDB port.

Validate Database Version now

SQL> SELECT BANNER, BANNER_FULL FROM v$version;

BANNER

-----------------------------------------------------------------------

BANNER_FULL

-----------------------------------------------------------------------

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.23.0.0.0

36) Setup Application tier

As the user of the applications server node modify the $TNS_ADMIN/tnsnames.ora file to specify the CDB instance name. The following shows the format of the new TNS entry.

<TWO_TASK> =

(DESCRIPTION =

(ADDRESS = (PROTOCOL=tcp)(HOST=<hostname>.<domain>)(PORT=<port number>))

(CONNECT_DATA = (SERVICE_NAME=ebs_<PDB SID>)(INSTANCE_NAME=<CDB SID>))

)

uatcdb =

(DESCRIPTION =

(ADDRESS = (PROTOCOL=tcp)(HOST=fundb.lab)(PORT=1532))

(CONNECT_DATA = (SERVICE_NAME=ebs_UAT)(INSTANCE_NAME=uatcdb))

)

Update the following values in the context file of every Applications tier server node.

Variable Name Value

s_dbport New database port

s_apps_jdbc_connect_descriptor NULL (blank entry)

s_applptmp Directory (not /usr/tmp) defined in UTL_FILE_DIR

To identify the allowable directories for s_applptmp use, connect to the Oracle E-Business Suite database instance as the apps user and run the following query:

SQL> select value from v$parameter where name='utl_file_dir';

mkdir -p /u01/app/oracle/product/temp/UAT on apps tier

Run AutoConfig on both patch and run APPL_TOPs using the following command.

$INST_TOP/admin/scripts/adautocfg.sh

Now my DB is upgraded to 19c with EBS 12.1 application.

I will now upgrade my Applications.

The Second part of upgrade I am going to upgrade the Applications to 12.2.13.

1) My Linux version is 8 . I will ensure all the pre-requisites are installed in the server.

From root user

dnf install oracle-ebs-server-R12-preinstall.x86_64

2) Download the R12.2.0 software zip and keep on the server.

Refer below on how to download. the R12.2.0 software

3) Once the software is downloaded, Please build the stage area.

Click here to know the steps to create a build stage area

4) Patch the Stage Area

After creating the stage area using startCD 12.2.0.51, follow the readme of the startCD 12.2.0.51 Consolidated Patch 32947483 to patch the Rapid Install stage area:

Patch 32947483: RAPID INSTALL CONSOLIDATED BUNDLE PATCH ON TOP OF STARTCD 51 (OCTOBER 2021)

After patching the Rapid Install stage area with the startCD 12.2.0.51 Consolidated Patch 32947483, patch the Rapid Install stage area with the following patches:

Patch 33637861 - Report Domain and OHS deployment errors

Patch 33942758 - RAPID INSTALL FAILS WITH JAVA.LANG.NULLPOINTEREXCEPTION

Patch 33756058 - RAPIDWIZ FAILING WITH ORACLE.APPS.FND.TXK.CONFIG.PROCESSSTATEEXCEPTION: BSU PROC

Patch 34226191 - RAPID INSTALL IMPROVEMENT FOR CAPTURING ERRORS

Patch 34228607 - FIX FOR EBS DOMAIN DEPLOYMENT FAILURE DUE TO LOW ENTROPY

Download the patches and unzip.

Unzip patches and apply in startCD

Apply patches to stage area

cd 32947483

sh patchRIStage.sh

cd 33756058

sh patchRIStage.sh

cd 33942758

sh patchRIStage.sh

cd 34226191

sh patchRIStage.sh

cd 34228607

sh patchRIStage.sh

When asked for path, give the path where all R12.2.0 zip are placed.

After executing patchRIStage.sh/cmd to patch the existing Rapid Install stage area, you must re-run buildStage.sh/cmd with the option 'Copy patches to existing stage area'. This is to incorporate additional platform-specific Technology patches into the Rapid Install Stage area.

5) Run ETCC DB Check on Database

Download Patch 17537119 on the server and run on DB

./checkDBpatch.sh

No patch needed as our DB as it is on 19.23 latest.

If patches are recommended please apply on Database.

6) Add Space to the Database tablespaces

APPS_TS_SEED should be 5 GB FREE

System should be 25 GB FREE

SYSAUX should be 5GB FREE

I have Enable Autoextend on

select file_id,file_name,autoextensible from dba_data_Files;

select file_id,file_name,autoextensible from dba_temp_Files;

select 'alter database datafile '||file_id ||' autoextend on;' from dba_data_Files;

select 'alter database tempfile '||file_id ||' autoextend on;' from dba_temp_Files;

select file_id,file_name,autoextensible from dba_data_Files;

select file_id,file_name,autoextensible from dba_temp_Files;

7) With the Stage are run rapidwiz and create the layout for R12.2 Filesystem on application server.

[root@funapps rapidwiz]# ls
bin            etc     jlib  rapidwiz      RapidWizVersion      template
ClientWiz.cmd  File    jre   RapidWiz.cmd  RapidWizVersion.cmd  unzip
driver         images  oui   RapidWiz.ini  TechInstallers       Xpatches
[root@funapps rapidwiz]# ./rapidwiz

Ignore the Service name warning and proceed with the installation.

Based on Server resources the File layout will take time. For Me it took 2 hrs.

Verify the new EBS12.2 file system layout has been created.

8) Run ETCC on the application and apply required patches on 12.2 file system.

Apply Latest Application Tier Technology Patches to Run File System

Immediately after the successful installation of the 12.2.0 file system using the latest startCD, you must apply the latest application tier technology patches to the run file system. After the release of the startCD, new required patches may have been discovered: it is important to perform this step.

Now EBS 12.2.0 Upgrade File System is Created. We have to Apply latest patches. We do not have to apply patches manually. We will use EBS Technology Patch Automation Tool.

I will use ETPAT-AT tool which is available from My Oracle Support as Patch 32208510. For applying the patches.

Download the patches as suggested by the ETCC tool

Patch 36006512

- EBS RELEASE 12.2 CONSOLIDATED FMW FIXES FOR JAN 2024

9) Applying patches using the ETPAT-AT Tool

Details on ETPAT-AT Tool Click Here

Create or specify a patch stage area directory. This patch stage area can be in any location accessible from the application tier. For example: <NE_BASE>/EBSapps/patch/etpat-at. If you wish, you can use your regular patch download area. This patch stage area will be used to download all the required patches prompted for by ETPAT-AT when it runs.

Download the latest ETCC Patch 17537119 to the patch stage area. If you do not obtain the patch with the latest ETCC, you will be prompted to do so when ETPAT-AT runs.

Download and unzip Patch 32208510 under your patch stage area, specifying the -o unzip option to overwrite any older version that may be installed:

Source 12.2 run file system environment file

$ cd patch stage area

$ unzip -o p32208510_R12_GENERIC.zip

[applmgr@funapp ~]$ cd $RUN_BASE

[applmgr@funapp fs1]$ ls

EBSapps FMW_Home inst

[applmgr@funapp fs1]$ cd ..

[applmgr@funapp EUAT]$ ls

fs1 fs2 fs_ne

[applmgr@funapp EUAT]$ cd fs_ne

[applmgr@funapp fs_ne]$ ls

EBSapps inst

[applmgr@funapp fs_ne]$ cd EBSapps/

[applmgr@funapp EBSapps]$ ls

appl log patch

[applmgr@funapp EBSapps]$ cd patch

[applmgr@funapp patch]$ mkdir stage

Running ETPAT-AT

Run the following command to start ETPAT-AT:

$ perl etpat_at.pl

Respond to the following prompts:

$ Enter 12.2.0 Run edition file system context file:

$ Enter APPS schema name [APPS]:

$ Enter password for APPS schema:

$ Enter directory where you downloaded ETCC Patch 17537119:

ETPAT-AT identifies the installed PSU version for Oracle WebLogic Server 10.3.6.0 (WLS PSU) on the Oracle E-Business Suite Release 12.2.0 File System and prompts you to choose the required WLS PSU version for your Oracle E-Business Suite Release 12.2 upgrade.

It will recommend to download the required patches.

[OPatch patch 6880880. Ensure that p6880880_101000_LINUX.zip, p6880880_111000_Linux-x86-64.zip are downloaded under the patch stage area]

[Fusion Middleware consolidated patch 36006512. Ensure that the patch 36006512 for platform LINUX is downloaded under patch stage area]

[WLS Patch 35710802. Ensure that p35710802_R12_GENERIC.zip is downloaded under patch stage area]

[Smart update Patch 33845432. Ensure that p33845432_R12_GENERIC.zip is downloaded under patch stage area]

Download the required patch.

For p6880880_111000_Linux-x86-64.zip use below link to download.

https://updates.oracle.com/Orion/PatchDetails/handle_plat_lang_change?release=80111000&plat_lang=46P&patch_file=&file_id=&password_required=&password_required_readme=&merged_trans=&aru=19416459&patch_num=6880880&patch_num_id=944616&default_release=80111000&default_plat_lang=226P

After the patches have been downloaded to the patch stage area, continue by responding "Yes" to the following prompt:

$ Download above patches under patch stage area and Enter 'Yes' to continue (Yes/No):

Once the patches are applied below summary and files can be reviewed.

Summary report text file location: /apps01/app/EUAT/fs_ne/EBSapps/patch/stage/UAT_etpat_at/log/ETPAT-AT_Report_UAT.txt

Summary report html file location: /apps01/app/EUAT/fs_ne/EBSapps/patch/stage/UAT_etpat_at/log/ETPAT-AT_Report_UAT.html

End Time: Sat Jun 8 07:49:51 +0530 2024

Script etpat_at.pl ran successfully.

Check the log file /apps01/app/EUAT/fs_ne/EBSapps/patch/stage/UAT_etpat_at/log/etpat_at_UAT.log for more information.

10) Validate the weblogic version

From RUN base execute below command

. $FMW_HOME/wlserver_10.3/server/bin/setWLSEnv.sh

[applmgr@funapp FMW_Home]$ java weblogic.version

WebLogic Server Temporary Patch for BUG13845626 Mon Oct 16 09:22:26 UTC 2023

WebLogic Server 10.3.6.0.231017 PSU Patch for BUG35586779 Sun Oct 15 11:00:00 UTC 2023

WebLogic Server Temporary Patch for BUG20474010 Sun Mar 01 17:22:18 IST 2015

WebLogic Server Temporary Patch for ${CRS} Mon Jul 30 16:45:20 EDT 2012

WebLogic Server 10.3.6.0 Tue Nov 15 08:52:36 PST 2011 1441050

11) Fix Timestamp Mismatch Issues for Synonym, View, Package and Package Body

Download but do not apply Patch 17268684:R12.AD.C.

Run the instructions in the patch readme to fix timestamp mismatches for Synonym, View, Package and Package Body.

1. Create <ORACLE_HOME>/appsutil/admin on the Database Tier.

2. Copy the file admin/ADZDDTFIX.sql from Patch#17268684:R12.AD.C to

<ORACLE_HOME>/appsutil/admin on the Database Tier.

3. As SYSTEM user, execute <ORACLE_HOME>/appsutil/admin/ADZDDTFIX.sql

4. Review the spooled file adzddtfix.out. If spooled file - adzddtfix.out has

any data, execute it as SYSDBA.

5. Re-execute script <ORACLE_HOME>/appsutil/admin/ADZDDTFIX.sql to check for any

pending TimeStamp Mismatch issues.

[oracle@fundb admin]$ sqlplus system/sys123

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jun 8 07:46:14 2024

Version 19.23.0.0.0

Last Successful login time: Sat Jun 08 2024 07:44:23 +05:30

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.23.0.0.0

SQL> alter session set container=UAT;

Session altered.

SQL> @ADZDDTFIX.sql

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.23.0.0.0

[oracle@dfccofdbuat1 admin]$ ls -ltr

total 4

-rwxr-x---. 1 oracle oinstall 2743 Jun 8 07:45 ADZDDTFIX.sql

-rw-r-----. 1 oracle oinstall 0 Jun 8 07:46 adzddtfix.out

NO fixed needed.

12) EBS 12.2 Upgrade Readiness Checker - Database Tier (EURC-DT)

The EBS 12.2 Upgrade Readiness Checker - Database Tier (EURC-DT) performs checks based on key technical requirements as described in the EBS Release 12.2 upgrade guides.

Specifically, EURC-DT:

Can be used when upgrading to Oracle E-Business Suite Release 12.2 from any previous release.

Is available for all platforms on which Oracle E-Business Suite is supported.

Verifies that critical system administration and other pre-upgrade technical steps have been performed.

Provides both a summary report and detailed log files for all checks performed.

Can be used for on-premises EBS instances, or instances on Oracle Cloud Infrastructure (all database services).

Is compatible with Oracle Database 19c, 12cR1, and 11gR2.

You can run EURC-DT when planning upgrades, performing test upgrades, or performing production upgrades.

Using Multitenant Architecture

If you are using the multitenant architecture, source the EBS PDB environment file with the appropriate command for your platform.

UNIX:

$ . ./<RDBMS ORACLE_HOME>/<EBS PDB Name>_<NODE_NAME>.env

EURC-DT is available from My Oracle Support as Patch 32288423. Download and unzip this patch under your patching stage area, specifying -o unzip option to overwrite any older version.

$ cd <patching stage area>

$ unzip -o p32288423_R12_GENERIC.zip

Run the following command to start EURC-DT:

$ perl eurc_dt.pl

Respond to the following prompts:

$ Enter SYSTEM password:

$ Enter APPS schema password:

Check Name : Online Patching Readiness Report
Refer: Chapter 3: Planning and Performing Pre-Upgrade Tasks => Preparing Customization's => Run the Online Patching Readiness Reports of 12.0 and 12.1 to 12.2 Upgrade Guide

12.1 customers should ensure that the online patching readiness report patch 31026891:R12.AD.B is applied on the instance.

3. Check Name : Check for Invalid objects
Refer: Chapter 4: Performing the upgrade => Performing the Upgrade to Release 12.2.0

There should be no INVALID objects in APPS schema or user schema before EBS upgrades.

There are 541 APPS invalids present in the instance before the upgrade.Refer APPS_Pre_Invalids.log.

4. Check Name : Check for Unusable Indexes
Refer: Chapter 4: Performing the upgrade => Performing the Upgrade to Release 12.2.0

There should not be any UNUSABLE indexes prior to upgrade.

There are 2 UNUSABLE indexes on the instance before the upgrade.Refer UnusableIndexList_Pre_Compilation.log.

Check for Patch 13420532:R12.XLA.B application
Refer: Chapter 4: Performing the upgrade => Preparing the System for Upgrade => Subledger Accounting

Apply patch 13420532:R12.XLA.B when upgrading from Release 12.0.x to clean up temporary advance queues that were created
by prior Create Accounting program processes.

Instance doesnt have 13420532:R12.XLA.B applied, ensure its applied.

Check Name : Gather SYS schema statistics
Refer: Chapter 4: Performing the upgrade => Database and System Administration Tasks => Gather SYS schema statistics => Fixed Object and Dictionary Statistics

Execute the below command as SYSDBA user to gather the stale stats. This increases the performance of the upgrade.

dbms_stats.gather_schema_stats( 'SYS',options=>'GATHER STALE',estimate_percent =>$ DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',cascade => TRUE);
Check Name : Fixed Object and Dictionary Statistics
Refer: Chapter 4: Performing the upgrade => Database and System Administration Tasks => Gather SYS schema statistics => Fixed Object and Dictionary Statistics

These should have been previously gathered, correct and up-to-date on the pre-upgrade environment.
Execute the below as SYSDBA user again to gain the performance benifit during upgrade.

exec dbms_stats.gather_fixed_objects_stats;
exec dbms_stats.gather_dictionary_stats;

Initialization parameters required at each stage of an upgrade may vary depending on when you upgrade your database.
Set the appropriate parameters now. The important common DB parameters are:

processes
sessions
_SYSTEM_TRIG_ENABLED=TRUE

Note:If your processes and sessions values in the init.ora file is the default values provided by the E-Business installation of 300 and 600,
then you should consider doubling these during the upgrade process to avoid connection issues.

Note: The instance should have _system_trig_enabled set to TRUE. If its sets to FALSE it will prevent from system triggers from being executed.

The value of _system_trig_enabled is set to "TRUE" on the instance.No further action.

DB parameters can be further fine tuned as per the guidelines, see Oracle E-Business Suite Release 12.2:Upgrade Sizing and
Best Practices (Doc ID: 1597531.1).

Check Name : Update init.ora with upgrade parameters -> Release-Specific Database Initialization Parameters for Oracle 19c
Refer: Chapter 4: Performing the upgrade => Performing the Upgrade to Release 12.2.0 => See Database Initialization Parameters for Oracle Applications Release 12.2 (Doc ID: 396009.1)

Initialization parameters required at each stage of an upgrade may vary depending on when you upgrade your database.
Set the appropriate parameters now. The important 19c DB specific parameters are:

optimizer_adaptive_plans = TRUE
optimizer_adaptive_statistics = FALSE
_disable_actualization_for_grant = TRUE

Some of the important Initialization parameters are not set or not valid. Please set the appropriate parameters now.
Current parameters are:

optimizer_adaptive_plans = TRUE
optimizer_adaptive_statistics = FALSE
_disable_actualization_for_grant =

Follow the instructions in Database Initialization Parameters for Oracle E-Business Suite Release 12.2 (Doc ID: 396009.1)
and reset the init.ora parameters as required.

Check Name : Additional Database Initialization Parameters For Oracle E-Business Suite Release 12.2
Refer: Chapter 4: Performing the upgrade => Performing the Upgrade to Release 12.2.0 => See Database Initialization Parameters for Oracle Applications Release 12.2 (Doc ID: 396009.1)

Initialization parameters required at each stage of an upgrade may vary depending on when you upgrade your database.
Set the appropriate parameters now.

The important 12.2 specific parameters are:

recyclebin = off
service_names=%s_dbSid%,ebs_patch

Some of the Additional Database Initialization parameters are not set or not valid. Please set the appropriate parameters now.
Current set parameters are:
recyclebin = on
service_names = UATCDB
2. Check Name : Disable Database Audit Trail
Refer: Chapter 4: Performing the upgrade => Performing the Upgrade to Release 12.2.0

Before perform the upgrade, ensure to Disable database audit trail, or else it would impatch the upgrade job execution.

Conditional Details:
=====================
2. Check Name : Set the parameter sec_case_sensitive_logon
Refer: Chapter 4: Performing the upgrade => Upgrade Your Database and Apply Mandatory E-Business Suite Release 12.2 Database Patches

For Release 12.1+ based environments (both 11204,12c and 19c), customers have a choice to set it as either TRUE or FALSE.

The value of sec_case_sensitive_logon in the instance is "FALSE". No Action Needed.

For Database 19c, the value for SQLNET.ALLOWED_LOGON_VERSION_SERVER should be 10 irrespective of the value of the sec_case_sensitive_logon parameter.

The value of SQLNET.ALLOWED_LOGON_VERSION_SERVER in the instance is "10". No

Fix the issues as reported by the Report.

Action taken:

Apply patch 31026891,13420532 on application using adpatch 12.1

On database connect to CDB and execute below

SQL> alter system set sessions=1500 scope=spfile;

System altered.

SQL> alter system set processes=1000 scope=spfile;

System altered.

SQL> alter system set "_disable_actualization_for_grant"=TRUE;

System altered.

alter system set recyclebin=off scope=spfile;

System altered.

ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 30;

SQL> alter system set audit_trail=NONE scope=spfile;

System altered.

alter system set event='10946 trace name context forever, level 8454144' scope=spfile;

Alter session set container=UAT;

grant DROP ANY PROCEDURE to apps;

Restart DB

13)Apply Consolidated Upgrade Patch and Run 12.2.0 Upgrade

As a prerequisite, apply the AD Upgrade Patch for Release 12.2 (Patch 10117518) by merging it with the latest Consolidated Upgrade Patch (CUP) for AD (Patch 33526872:R12.AD.C), CUP 13.

The AD Upgrade Patch for Release 12.2 (Patch 10117518) Readme contains information about the latest CUP for the additional AD Patch 33526872:R12.AD.C patch and instructions.

Follow the Patch 10117518 Readme, and apply the patch by merging it with the latest Consolidated Upgrade Patch for AD and any other patches mentioned in the Readme, as instructed in the Readme and by referring to the Upgrade Guides when relevant.

cd /apps01/app/EUAT/fs1/EBSapps/appl

[applmgr@funapp appl]$ pwd

/apps01/app/EUAT/fs1/EBSapps/appl

[applmgr@funapp appl]$ . APPSUAT_funapp.env

cd /apps01/app/EUAT/fs_ne/EBSapps/patch

[applmgr@funapp 122-patches]$ cp p34754463_12.2.0_R12_LINUX.zip p33526872_R12.AD.C_R12_LINUX.zip p10117518_R12_LINUX.zip /apps01/app/EUAT/fs_ne/EBSapps/patch/

Unzip the patches

[applmgr@funapp patch]$ pwd

/apps01/app/EUAT/fs_ne/EBSapps/patch

[applmgr@funapp patch]$ mkdir CUP13

[applmgr@funapp patch]$ mv 33526872 10117518 CUP13/

[applmgr@funapp patch]$ ls -ltr

mkdir PATCH_CUP13

admrgpch -s CUP13 -d PATCH_CUP13 -merge_name PATCH_CUP13 -admode

[applmgr@funapp patch]$ admrgpch -s CUP13 -d PATCH_CUP13 -merge_name PATCH_CUP13 -admode

Executing the merge of the patch drivers

-- Processing patch: CUP13/33526872

-- Processing file: CUP13/33526872/u33526872.drv

-- Done processing file: CUP13/33526872/u33526872.drv

-- Done processing patch: CUP13/33526872

-- Processing patch: CUP13/10117518

-- Processing file: CUP13/10117518/u10117518.drv

-- Done processing file: CUP13/10117518/u10117518.drv

-- Done processing patch: CUP13/10117518

Copying files...

5% complete. Copied 47 files of 925...

10% complete. Copied 93 files of 925...

15% complete. Copied 139 files of 925...

20% complete. Copied 185 files of 925...

25% complete. Copied 232 files of 925...

30% complete. Copied 278 files of 925...

35% complete. Copied 324 files of 925...

40% complete. Copied 370 files of 925...

45% complete. Copied 417 files of 925...

50% complete. Copied 463 files of 925...

55% complete. Copied 509 files of 925...

60% complete. Copied 555 files of 925...

65% complete. Copied 602 files of 925...

70% complete. Copied 648 files of 925...

75% complete. Copied 694 files of 925...

80% complete. Copied 740 files of 925...

85% complete. Copied 787 files of 925...

90% complete. Copied 833 files of 925...

95% complete. Copied 879 files of 925...

100% complete. Copied 925 files of 925...

Character-set converting files...

2 unified drivers merged.

Patch merge completed successfully

Please check the log file at ./admrgpch.log.

Copy ad grants from merge patch to db home and run

[oracle@dfccofdbuat1 19.0.0]$ . UATCDB_fundb.env

[oracle@dfccofdbuat1 19.0.0]$ export ORACLE_PDB_SID=UAT

[oracle@dfccofdbuat1 19.0.0]$ cd -

/u01/app/oracle/product/19.0.0/appsutil/admin

[oracle@dfccofdbuat1 admin]$ ls -ltr

total 112

-rwxr-x---. 1 oracle oinstall 2743 Jun 8 07:45 ADZDDTFIX.sql

-rw-r-----. 1 oracle oinstall 0 Jun 8 07:46 adzddtfix.out

-rwxr-x---. 1 oracle oinstall 108545 Jun 8 11:14 adgrants.sql

[oracle@dfccofdbuat1 admin]$ sqlplus '/nolog'

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jun 8 11:15:04 2024

Version 19.23.0.0.0

SQL> @adgrants.sql apps

Enable maintenance mode from 12.2 file system

adadmin

Apply ORACLE APPLICATIONS DBA CONSOLIDATED UPGRADE PATCH 13 (AD CUP13):

AutoPatch is complete.From 12.2 filesystem.

adpatch - use this to apply patch

Driver name u_PATCH_CUP13.drv

AutoPatch may have written informational messages to the file

/apps01/app/EUAT/fs1/EBSapps/appl/admin/UAT/log/adpatch.lgi

Errors and warnings are listed in the log file

/apps01/app/EUAT/fs1/EBSapps/appl/admin/UAT/log/adpatch.log

Compile apps schema using adadmin from 12.2

Run Adadmin:

adadmin

Compile/Reload Applications Database Entities menu -> Compile APPS schema

"Type "No" when prompt for Run Invoker Rights processing in incremental mode [No] ?”No

Apply the Consolidated Upgrade Patch (CUP) for Release 12.2.0 (Patch 34754463:12.2.0) in pre-installation mode on the run edition file system

cd /apps01/app/EUAT/fs_ne/EBSapps/patch/34754463

adpatch preinstall=y

Merge the patch drivers in <APPL_TOP>/admin/<TWO_TASK>/preinstall directory with the 12.2.0 upgrade driver $AU_TOP/patch/115/driver/u10124646.drv.

Note: All the patch driver files located under <APPL_TOP>/admin/<TWO_TASK>/preinstall are merged with the 12.2.0 upgrade driver <AU_TOP>/patch/115/driver/u10124646.drv. Therefore, you should evaluate the content of the <APPL_TOP>/admin/<TWO_TASK>/preinstall directory and retain only those patch drivers (including u34754463.drv) that are to be merged with 12.2.0 upgrade driver, u10124646.drv.

Merge patch drivers in the run file system <APPL_TOP>/admin/<TWO_TASK>/preinstall directory with <AU_TOP>/patch/115/driver/u10124646.drv as follows.

Change directory to <AU_TOP>/patch/115/driver and run the following commands:

$ cd $AU_TOP/patch/115/driver

$ admrgpch -d . -preinstall -master u10124646.drv

The default merged driver by name u_merged.drv is then created in the specified destination directory.

[applmgr@funapp 34754463]$ cd $APPL_TOP/admin/$TWO_TASK/preinstall

[applmgr@funapp preinstall]$ ls

u34754463.drv

[applmgr@funapp preinstall]$ pwd

/apps01/app/EUAT/fs1/EBSapps/appl/admin/UAT/preinstall

[applmgr@funapp preinstall]$ ls -tlr

total 344

-rw-r-----. 1 applmgr oinstall 351440 Jun 8 13:41 u34754463.drv

otal 344

-rw-r-----. 1 applmgr oinstall 351440 Jun 8 13:41 u34754463.drv

[applmgr@funapp preinstall]$ cd $AU_TOP/patch/115/driver

[applmgr@funapp driver]$ ls

augenmsg.drvx ausstats.drvx u10124646.drv u10201000.drv

[applmgr@funapp driver]$ admrgpch -d . -preinstall -master u10124646.drv

Executing the merge of the patch drivers

-- Processing file: /apps01/app/EUAT/fs1/EBSapps/appl/admin/UAT/preinstall/u34754463.drv

-- Done processing file: /apps01/app/EUAT/fs1/EBSapps/appl/admin/UAT/preinstall/u34754463.drv

-- Processing file: u10124646.drv

Apply merge driver the patch

[applmgr@funapp driver]$ cd $AU_TOP/patch/115/driver

[applmgr@funapp driver]$ ls

admrgpch.log augenmsg.drvx ausstats.drvx u10124646.drv u10201000.drv u_merged.drv

[applmgr@funapp driver]$ adpatch options=nocopyportion,nogenerateportion

Runs 3 hrs

Disable Maintenance Mode:

On run file system fs1

Using adadmin

14) Make appsutil on application node

Fs1 environment file

perl $AD_TOP/bin/admkappsutil.pl

Transfer to database node oracle home and unzip

cd $ORACLE_HOME

unzip -o appsutil.zip

15) On DB node

sqlplus apps/apps@UAT

EXEC FND_CONC_CLONE.SETUP_CLEAN;

commit;

exit

Source PDB environment file

Run Autoconfig on database

16) On Application Tier:

. Source run file system

cd $AD_TOP/patch/115/sql

sqlplus apps/apps @txkDropAdxPreAutoConfig.sql

Commit complete

sqlplus apps/apps

DROP INDEX JTF.JTF_AMV_ITEMS_URL_CTX;

exit

cd $ADMIN_SCRIPTS_HOME

sh adautocfg.sh

Make sure autoconfig is completed successfully.

17) Run Rapidwiz: applmgr to upgrade the Application now.

[root@funapps rapidwiz]# ls
bin            etc     jlib  rapidwiz      RapidWizVersion      template
ClientWiz.cmd  File    jre   RapidWiz.cmd  RapidWizVersion.cmd  unzip
driver         images  oui   RapidWiz.ini  TechInstallers       Xpatches
[root@funapps rapidwiz]# ./rapidwiz

Below screen full path with fs1 context file

Tooks 1.5 hrs to complete.

18)Run Adadmin after rapidwiz completes:

Source run file system

adadmin

Regenerate jar files:

1 > 4

Do you wish to force regeneration of all jar files? [No] ? Yes

Recompile APPS

3 > 1

Type "No" when prompt for Run Invoker Rights processing in incremental mode [No] ? Press Enter

Regenerate forms and pll's

1 > 2

All Enter Enter Enter

Recompile Menus

3 > 2

Do you wish to force compilation of all menus? [No] ? Yes

Recompile APPS

3 > 1

Type "No" when prompt for Run Invoker Rights processing in incremental mode [No] ? Press Enter

Exit

19) Run ChkFormsDeployment:

. Source run file system

perl $FND_TOP/bin/txkrun.pl -script=ChkFormsDeployment

*** LaTEST frmall.jar has NOT been copied ***

Copying the laTEST JAR file...

=============================================

Copying the laTEST JAR file

Successfully Completed the script

cd $ORACLE_HOME/forms/lib

make -f ins_forms.mk sharedlib

make -f ins_forms.mk install

cd $ORACLE_HOME/reports/lib

make -f ins_reports.mk install

adrelink.sh force=y link_debug=y "fnd fndfmxit.so"

adrelink is exiting with status 0

cd $ADMIN_SCRIPTS_HOME

sh adautocfg.sh

AutoConfig completed successfully.

20) Database Node Run Autoconfig and Restart DB.

21) Gather Stats on Database

Connect as sys dba and run gather stats

Source CDB environment

export ORACLE_PDB_SID=UAT

begin

dbms_stats.gather_schema_stats(

'SYS',

options=>'GATHER STALE',

estimate_percent =>

DBMS_STATS.AUTO_SAMPLE_SIZE,

method_opt => 'FOR ALL COLUMNS SIZE AUTO',

cascade => TRUE);

end;

commit;

exit;

22) Run adgrants on database and provide few manually.

make sure we have latest adgrants file on DB server (In $ORACLE_HOME/appsutil/admin). else copy latest version from apps server.

Source CDB environment

export ORACLE_PDB_SID=UAT

cd /u01/app/oracle/product/19.0.0.0/dbhome_1/appsutil/admin

strings -a adgrants.sql | grep '$Header'

REM $Header: adgrants.sql 120.67.12020000.69 2020/03/20 00:56:38 jwsmith ship $

sqlplus /nolog

@adgrants.sql apps

ignore warnings

exit

Source CDB environment

export ORACLE_PDB_SID=UAT

sqlplus / as sysdba

CREATE OR REPLACE PUBLIC SYNONYM "V$SYSTEM_PARAMETER2" FOR "SYS"."V_$SYSTEM_PARAMETER2" ;

grant select on sys.V_$SYSTEM_PARAMETER2 to apps ;

grant select on sys.AUX_STATS$ to apps;

grant select on DBA_STAT_EXTENSIONS to apps;

grant select on DBA_TABLES to apps;

grant select on WRI$_OPTSTAT_TAB_HISTORY to apps;

grant select on WRI$_OPTSTAT_IND_HISTORY to apps;

grant select on DBA_INDEXES to apps;

grant select on DBA_OBJECTS to apps;

grant select on DBA_SCHEDULER_JOBS to apps;

23) Run Online patch readiness script and validate. If required fix the issues.

Create the Online Patching log file location and set it as the current directory and run the following Readiness reports:

. RUN file system

mkdir $LOG_HOME/appl/op

cd $LOG_HOME/appl/op

sqlplus system/sys123 @$AD_TOP/sql/ADZDPSUM.sql

mv adzdpsum.txt adzdpsum_pre_dbprep.txt

sqlplus system/sys123 @$AD_TOP/sql/ADZDPMAN.sql

mv adzdpman.txt adzdpman_pre_dbprep.txt

sqlplus system/sys123 @$AD_TOP/sql/ADZDPAUT.sql

mv adzdpaut.txt adzdpaut_pre_dbprep.txt

sqlplus apps/apps @$AD_TOP/sql/ADZDEXRPT.sql

mv adzdexrpt.txt adzdexrpt_pre_dbprep.txt

sqlplus apps/apps @$AD_TOP/patch/115/sql/ADZDREG.sql sys123 apps CUSTOM_SCHEM1

sqlplus apps/apps @$AD_TOP/patch/115/sql/ADZDREG.sql sys123 apps CUSTOM_SCHEM2

sqlplus apps/apps @$AD_TOP/patch/115/sql/ADZDREG.sql sys123 apps CUSTOM_SCHEM3

sqlplus apps/apps @$AD_TOP/patch/115/sql/ADZDREG.sql sys123 apps CUSTOM_SCHEM4

sqlplus apps/apps

exec sys.utl_recomp.recomp_parallel;

commit;

exit

24) Verify database tablespace free space

On application node and Source the application run environment file. 12.2

perl $AD_TOP/bin/adzdreport.pl apps

a) Enter apps password (apps) when prompt to enter.

b) Select option 3 - 'Other Generic Reports'

Select the next option 3 - 'Free Space in Important Tablespaces

25) Run the Online Patching Enablement - Status Report run file

This report provides an overall status of the enabling online patching process. You can run it before, during, and after the enablement patch is applied. At this stage, you will receive report results before you enable online patching. 1. Set the current directory to $LOG_HOME/appl/op: $ cd $LOG_HOME/appl/op 2. Run the report using the following command. Ensure that you verify any invalid objects at this stage. Take special note to ensure that all online patching objects (objects that match the pattern 'AD_ZD%') are valid:

sqlplus apps/apps @$AD_TOP/sql/ADZDEXRPT.sql

26) Validate the Packages

validate XDB is VALID in 19c CDB

col COMP_NAME format a40

col version format a11

set pagesize 150

select comp_name, version, status from dba_registry;

Run the following scripts from the 19c oracle_home

Run database XDB scripts in PDB as SYSDBA

source $ORACLE_HOME/<CDB SID>_<HOST>.env

export ORACLE_PDB_SID=<PDB SID>

sqlplus / as sysdba

SQL> @?/rdbms/admin/dbmsxdbschmig.sql

SQL> @?/rdbms/admin/prvtxdbschmig.plb

sqlplus / as sysdba

GRANT SELECT, UPDATE, DELETE, INSERT on sys.xdb$moveSchemaTab to apps;

compile invalids using adadmin connect to the middletier as applmgr, and run: adadmin Re-create grants and synonyms for APPS schema adadmin compile APPS schema 

cd /u01/app/oracle/product/19.0.0/dbhome_1/appsutil/admin

sqlplus /nolog

@adgrants.sql apps

sqlplus apps/apps

select owner,object_name,object_type from dba_objects where status='INVALID' and object_name like'%ADOP%';

select owner,object_name,object_type from dba_objects where status='INVALID' and object_name like'%PREP%';

ALTER PACKAGE AD_ZD_ADOP COMPILE BODY;

alter package apps.AD_ZD_PREP compile body;

select owner,object_name,object_type from dba_objects where status='INVALID' and object_name like'%PREP%';

27) Delete if any ebs_patch service exists in Database

Connect to CDB as sysdba

SQL> alter session set container=cdb$root;

SQL> exec dbms_service.delete_service('ebs_patch');

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

28) Apply the Online Patching Enablement patch

Download patch 13543062

On the application node and source the environment file.

cd /app/R12/readiness/13543062

adpatch options=hotpatch,forceapply

Monitor the Online Patching Enablement patch application. The enablement patch application may take several hours to finish. You can monitor its progress at any time by running the DDL Status Report (ADZDSHOWDDLS.sql) as follows: $ sqlplus apps/apps @$AD_TOP/sql/ADZDSHOWDDLS.sql

29) Compile Invalid Objects Connect to sqlplus as 'apps' and run the following:

exec sys.utl_recomp.recomp_parallel

30) Re-run the Online Patching Enablement Status Report after the Online Patching Enablement patch has been applied:

. Run file system

cd $LOG_HOME/appl/op

sqlplus apps/apps @$AD_TOP/sql/ADZDEXRPT.sql

mv adzdexrpt.txt adzdexrpt_post_dbprep.txt

sqlplus system/sys123 @$AD_TOP/sql/ADZDPSUM.sql

mv adzdpsum.txt adzdpsum_post_dbprep.txt

sqlplus system/sys123 @$AD_TOP/sql/ADZDPMAN.sql

mv adzdpman.txt adzdpman_post_dbprep.txt

sqlplus system/sys123 @$AD_TOP/sql/ADZDPAUT.sql

mv adzdpaut.txt adzdpaut_post_dbprep.txt

Review the report outputs

31)Run the Online Patching Database Compliance Checker report to check for coding standards violations:

. RUN file system

cd $LOG_HOME/appl/op

sqlplus apps/apps @$AD_TOP/sql/ADZDDBCC.sql

32) On Database Node:

Make sure below parameters are included in database pfile and spfile if not enter in the spfile and bounce the database by adding them.

recyclebin=off

_SYSTEM_TRIG_ENABLED=true

33) Copy ADFIXUSER.sql from Apps Node to DB node:

. RUN file system

cd $AD_TOP/patch/115/sql

Copy the ADFIXUSER.sql to DB server ORACLE_HOME/appsutil/admin

On Database Node:

. CBD_ENVIRONMNET

export ORACLE_PDB_SID=UPG

mkdir -p /u01/app/oracle/product/19.0.0.0/dbhome_1/appsutil/etcc

cd /u01/app/oracle/product/19.0.0.0/dbhome_1/appsutil/admin

sqlplus / as sysdba

@ADFIXUSER.sql

34) Apply latest AD and TXK patches

Release 12.2: Suite-Wide Release Update Pack and AD/TXK Delta Information (Doc ID: 1583092.1) to apply the latest AD/TXK patchsets.

AD and TXK 15 version

The latest RUPs are:

R12.AD.C.Delta.15 (Patch 34695811)

R12.TXK.C.Delta.15 (Patch 34785677)

Download the patches and keep on patch top

Download and keep all patches to the default patch location, $NE_BASE/EBSapps/patch, which is pointed to by the $PATCH_TOP environment variable and is also where patches should be unzipped. The location must be accessible to all application tier nodes.

As per ETCC all required DB patches applied, if not then apply.

Start up the Oracle Weblogic Admin Server on the run edition application tier file system by entering the appropriate command for your platform.

$ sh $ADMIN_SCRIPTS_HOME/adadminsrvctl.sh start

Copy the adgrants.sql script from Patch 34695811 to the database tier server,

source $ORACLE_HOME/<CDB_NAME>_<HOSTNAME>.env

$ export ORACLE_PDB_SID=UAT

Run the adgrants.sql script using the appropriate command for your platform:

$ sqlplus /nolog

SQL> @$ORACLE_HOME/appsutil/admin/adgrants.sql apps

Please note after this adgrant.sql EBS_SYSTEM schema is created in database. it creates the EBS_SYSTEM user in a locked state.

source $ORACLE_HOME/<CDB_NAME>_<HOSTNAME>.env

$ export ORACLE_PDB_SID=UAT

sqlplus '/as sysdba'

ALTER USER EBS_SYSTEM ACCOUNT UNLOCK;

ALTER USER EBS_SYSTEM IDENTIFIED BY <keep same as system password of DB>

Validate that the EBS_SYSTEM and SYSTEM passwords are the same by running the adValidateEbssystemSchema.pl script from from the run edition file system, with the appropriate command for your system:

$ perl $PATCH_TOP/34695811/ad/bin/adValidateEbssystemSchema.pl

ON DB Server.

source $ORACLE_HOME/<CDB_NAME>_<HOSTNAME>.env

$ export ORACLE_PDB_SID=UAT

SQL> grant execute on SYS.DBMS_RESULT_CACHE to APPS;

Grant succeeded.

SQL> alter package apps.AD_ZD compile body;

Package body altered.

SQL> @?/rdbms/admin/utlrp.sql

if patch issues comes then we can create below

sqlplus '/as sysdba'

create synonym apps.ADOP_VALID_NODES for applsys.ADOP_VALID_NODES;

Apply the Patch now

Source run file system

unset which (Issue in Linux 8)

adop phase=apply patches=34695811 hotpatch=yes

adop phase=apply patches=34785677 hotpatch=yes

adop phase=apply patches=36270240 hotpatch=yes

35) Make Appsutil directory

Source the run edition file system environment file.

$ . <EBS_ROOT>/EBSapps.env run

Run the Middle Tier EBS Technology Codelevel Checker (MT-ETCC).

MT-ETCC is run as checkMTpatch.sh (UNIX) (checkMTpatch.cmd (Windows) on the run edition file system.

Stop the Oracle WebLogic Server's Admin Server and Node Manager services

Stop the Oracle Weblogic Server Admin Server on the run edition file system.

sh $ADMIN_SCRIPTS_HOME/adadminsrvctl.sh stop

sh $ADMIN_SCRIPTS_HOME/adnodemgrctl.sh stop

perl $AD_TOP/bin/admkappsutil.pl

And copy to db tier and run autoconfig on DB and APPS.

36) Apply EBS 12.2.13 Release Update Patch

Apply Oracle E-Business Suite 12.2.13 Release Update Pack Patch 34776655 on the run edition application environment, using downtime mode.

Source the run edition applications environment.

$ . <INSTALL_BASE>/EBSapps.env run

Stop the Oracle WebLogic Server's Admin Server and Node Manager services

Stop the Oracle Weblogic Server Admin Server on the run edition file system.

$ sh $ADMIN_SCRIPTS_HOME/adadminsrvctl.sh stop

$ sh $ADMIN_SCRIPTS_HOME/adnodemgrctl.sh stop

adop phase=apply apply_mode=downtime patches=34776655

37) Apply Oracle E-Business Suite Release 12.2.13 Online Help Patch

Using adop hotpatch mode on the run file system, apply Oracle E-Business Suite Release 12.2.13 Online Help Patch 34776636.

Source the run edition applications environment.

$ . <INSTALL_BASE>/EBSapps.env run

Start admin server

sh $ADMIN_SCRIPTS_HOME/adadminsrvctl.sh start

$ adop phase=apply patches=34776636 hotpatch=yes

38) Apply few other patches as identified in my environment

Source the run edition applications environment.

$ . <INSTALL_BASE>/EBSapps.env run

adop phase=apply patches=36336844 hotpatch=yes

adop phase=apply patches=35415055 hotpatch=yes

39) Copy the custom forms and reports from the OLD EBS file system 12.1 to respective product top in R12.2 (under fs1 and fs2)

Also I will put the custom entries for adop sync.

update the /apps01/app/EUAT/fs_ne/EBSapps/appl/ad/custom/adop_sync.drv

edit the file and add entries as needed.

Sample below based on my environment added in adop_sync.drv

rsync -zr %s_current_base%/EBSapps/appl/gl/12.0.0/reports/US/XX* %s_other_base%/EBSapps/appl/gl/12.0.0/reports/US/

rsync -zr %s_current_base%/EBSapps/appl/ap/12.0.0/reports/US/XX* %s_other_base%/EBSapps/appl/ap/12.0.0/reports/US/

rsync -zr %s_current_base%/EBSapps/appl/fa/12.0.0/reports/US/XX* %s_other_base%/EBSapps/appl/fa/12.0.0/reports/US/

40) Final steps for Apps Upgrade

Perform the adop cleanup actions.

$ adop phase=cleanup

Synchronize file systems.

Synchronize the file systems using the command shown below. This action will copy the new run edition code and configuration to the other file system, to ensure that both file systems are in sync before applying patches using the regular adop cycle on the other file system.

$ adop phase=fs_clone

41) On DB Server as apps user recreate below DB links.

APPS_TO_APPS

EDW_APPS_TO_WH

sqlplus apps/apps@uat

CREATE DATABASE LINK APPS_TO_APPS

CONNECT TO APPS IDENTIFIED BY apps

USING 'UAT';

CREATE DATABASE LINK EDW_APPS_TO_WH

CONNECT TO APPS IDENTIFIED BY apps

USING 'UAT';

Also recreate any other db link to be used.

This Completes the Upgrade of the Application to R12.2.13

References:

Developing and Deploying Customizations in Oracle E-Business Suite Release 12.2 (Doc ID 1577661.1)
Recommended Browsers for Oracle E-Business Suite Releases 12.2 and 12.1 (Doc ID 389422.1)
Oracle E-Business Suite Release 12.2.13 Readme (Doc ID 2958077.1)
Release 12.2: Suite-Wide Release Update Pack and AD/TXK Delta Information (Doc ID: 1583092.1) to apply the latest AD/TXK patchsets.
Interoperability Notes: Oracle E-Business Suite Release 12.1 with Oracle Database 19c (Doc ID 2580629.1)
Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.2) for Linux x86-64 (Doc ID 1330701.1)
Oracle E-Business Suite Release Notes, Release 12.2 (Doc ID 1320300.1)

EBS Technology Patch Automation Tool for Application Tier (ETPAT-AT) (Doc ID 2749774.1)

EBS 12.2 Upgrade Readiness Checker - Database Tier (EURC-DT) (Doc ID 2749775.1)

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment