Oracle EBS Database Upgrade from 12c to 19c Part-1
In this article, I will be discussing the process of upgrading Oracle Database 12.1.0.2 to the latest version, 19.19 (Latest PSU APR-23), while using Oracle EBS Apps R12.2.12. This post follows the previous article on upgrading Oracle EBS Apps from version 12.2.0 to 12.2.12.
If you are new here and interested in installing and upgrading Oracle E-Business Suite R12.2.x, you can refer to the following link for a step-by-step installation guide for Oracle APPS R12.2.12
Throughout this Upgrade Process, I will be following Oracle Standard DOC ID “Oracle E-Business Suite Release 12.2 with Oracle Database 19c (Doc ID 2552181.1)”
- As part of upgrading your Oracle E-Business Suite to Oracle Database 19c, your database will undergo a conversion to a multitenant architecture. This means it will consist of a Container Database (CDB) with a single Pluggable Database. Please note that only multitenant architecture databases are supported for Oracle E-Business Suite when using Oracle Database 19c.
- During the upgrade process, you will also need to migrate directories that are defined for PL/SQL File I/O to database directory objects. This requirement arises from the de support of the UTL_FILE_DIR database initialization parameter in Oracle Database 19c.
- Oracle Database Release Update 19.3, as well as Release Updates 19.5 and later, have been certified for compatibility.
- If the products you have implemented do not support 19c, it is advised not to proceed with the database upgrade.
Section 1 – Running Health Check
[oracle@test1 ~]$ cd /home/apps/stage/stage12212
[oracle@test1 stage12212]$ ls -ltrh hcheck.sql
-rw-r–r–. 1 oracle oinstall 144729 Oct 13 2022 hcheck.sql
SQL> @hcheck.sql
HCheck Version 07MAY18 on 15-JUL-2023 23:19:16
———————————————-
Catalog Version 12.1.0.2.0 (1201000200)
db_name: PROD
Is CDB?: NO
Catalog Fixed
Procedure Name Version Vs Release Timestamp
Result
—————————— … ———- — ———- ————–
——
.- LobNotInObj … 1201000200 <= *All Rel* 07/15 23:19:16 PASS
.- MissingOIDOnObjCol … 1201000200 <= *All Rel* 07/15 23:19:16 PASS
.- SourceNotInObj … 1201000200 <= *All Rel* 07/15 23:19:16 FAIL
HCKE-0003: SOURCE$ for OBJ# not in OBJ$ (Doc ID 1360233.1)
SOURCE$ has 283 rows for 22 OBJ# values not in OBJ$
.- OversizedFiles … 1201000200 <= *All Rel* 07/15 23:19:56 PASS
.- PoorDefaultStorage … 1201000200 <= *All Rel* 07/15 23:19:56 PASS
.- PoorStorage … 1201000200 <= *All Rel* 07/15 23:19:56 PASS
.- TabPartCountMismatch … 1201000200 <= *All Rel* 07/15 23:19:57 PASS
.- OrphanedTabComPart … 1201000200 <= *All Rel* 07/15 23:19:57 PASS
.- MissingSum$ … 1201000200 <= *All Rel* 07/15 23:19:57 PASS
.- MissingDir$ … 1201000200 <= *All Rel* 07/15 23:19:57 PASS
.- DuplicateDataobj … 1201000200 <= *All Rel* 07/15 23:19:57 PASS
.- ObjSynMissing … 1201000200 <= *All Rel* 07/15 23:19:58 PASS
.- ObjSeqMissing … 1201000200 <= *All Rel* 07/15 23:19:58 PASS
.- OrphanedUndo … 1201000200 <= *All Rel* 07/15 23:19:58 PASS
.- OrphanedIndex … 1201000200 <= *All Rel* 07/15 23:19:58 PASS
.- OrphanedIndexPartition … 1201000200 <= *All Rel* 07/15 23:19:58 PASS
.- OrphanedIndexSubPartition … 1201000200 <= *All Rel* 07/15 23:19:58 PASS
.- OrphanedTable … 1201000200 <= *All Rel* 07/15 23:19:58 PASS
.- OrphanedTablePartition … 1201000200 <= *All Rel* 07/15 23:19:58 PASS
.- OrphanedTableSubPartition … 1201000200 <= *All Rel* 07/15 23:19:59 PASS
.- MissingPartCol … 1201000200 <= *All Rel* 07/15 23:19:59 PASS
.- OrphanedSeg$ … 1201000200 <= *All Rel* 07/15 23:19:59 FAIL
HCKE-0023: Orphaned SEG$ Entry (Doc ID 1360934.1)
ORPHAN SEG$: SegType=INDEX TS=381 RFILE/BLOCK=401/177675
ORPHAN SEG$: SegType=INDEX TS=381 RFILE/BLOCK=401/140907
ORPHAN SEG$: SegType=LOB TS=381 RFILE/BLOCK=401/134987
ORPHAN SEG$: SegType=LOB TS=381 RFILE/BLOCK=401/177659
ORPHAN SEG$: SegType=TYPE2 UNDO TS=368 RFILE/BLOCK=379/33
.- OrphanedIndPartObj# … 1201000200 <= *All Rel* 07/15 23:19:59 PASS
.- DuplicateBlockUse … 1201000200 <= *All Rel* 07/15 23:19:59 PASS
.- FetUet … 1201000200 <= *All Rel* 07/15 23:19:59 PASS
.- Uet0Check … 1201000200 <= *All Rel* 07/15 23:19:59 PASS
.- SeglessUET … 1201000200 <= *All Rel* 07/15 23:19:59 PASS
.- BadInd$ … 1201000200 <= *All Rel* 07/15 23:20:02 PASS
.- BadTab$ … 1201000200 <= *All Rel* 07/15 23:20:02 PASS
.- BadIcolDepCnt … 1201000200 <= *All Rel* 07/15 23:20:02 PASS
.- ObjIndDobj … 1201000200 <= *All Rel* 07/15 23:20:02 PASS
.- TrgAfterUpgrade … 1201000200 <= *All Rel* 07/15 23:20:02 PASS
.- ObjType0 … 1201000200 <= *All Rel* 07/15 23:20:02 PASS
.- BadOwner … 1201000200 <= *All Rel* 07/15 23:20:02 PASS
.- StmtAuditOnCommit … 1201000200 <= *All Rel* 07/15 23:20:02 WARN
HCKW-0013: option# in STMT_AUDIT_OPTION_MAP(ON COMMIT REFRESH) (Doc ID
1361021.1)
— Please run the following:
SQL> update STMT_AUDIT_OPTION_MAP set option#=234
where name =’ON COMMIT REFRESH’;
SQL> commit;
.- BadPublicObjects … 1201000200 <= *All Rel* 07/15 23:20:02 PASS
.- BadSegFreelist … 1201000200 <= *All Rel* 07/15 23:20:02 PASS
.- BadDepends … 1201000200 <= *All Rel* 07/15 23:20:02 PASS
.- CheckDual … 1201000200 <= *All Rel* 07/15 23:20:04 PASS
.- ObjectNames … 1201000200 <= *All Rel* 07/15 23:20:04 PASS
.- BadCboHiLo … 1201000200 <= *All Rel* 07/15 23:20:04 PASS
.- ChkIotTs … 1201000200 <= *All Rel* 07/15 23:20:04 PASS
.- NoSegmentIndex … 1201000200 <= *All Rel* 07/15 23:20:04 PASS
.- BadNextObject … 1201000200 <= *All Rel* 07/15 23:20:04 PASS
.- DroppedROTS … 1201000200 <= *All Rel* 07/15 23:20:04 PASS
.- FilBlkZero … 1201000200 <= *All Rel* 07/15 23:20:04 PASS
.- DbmsSchemaCopy … 1201000200 <= *All Rel* 07/15 23:20:04 PASS
.- OrphanedIdnseqObj … 1201000200 > 1201000000 07/15 23:20:04 PASS
.- OrphanedIdnseqSeq … 1201000200 > 1201000000 07/15 23:20:04 PASS
.- OrphanedObjError … 1201000200 > 1102000000 07/15 23:20:04 PASS
.- ObjNotLob … 1201000200 <= *All Rel* 07/15 23:20:05 PASS
.- MaxControlfSeq … 1201000200 <= *All Rel* 07/15 23:20:05 PASS
.- SegNotInDeferredStg … 1201000200 > 1102000000 07/15 23:20:05 PASS
.- SystemNotRfile1 … 1201000200 > 902000000 07/15 23:20:05 PASS
.- DictOwnNonDefaultSYSTEM … 1201000200 <= *All Rel* 07/15 23:20:05 PASS
.- OrphanTrigger … 1201000200 <= *All Rel* 07/15 23:20:05 PASS
.- ObjNotTrigger … 1201000200 <= *All Rel* 07/15 23:20:05 PASS
—————————————
15-JUL-2023 23:20:05 Elapsed: 49 secs
—————————————
Found 6 potential problem(s) and 1 warning(s)
Contact Oracle Support with the output and trace file
to check if the above needs attention or not
PL/SQL procedure successfully completed.
Statement processed.
Complete output is in trace file:
/u01/oracle/PROD/12.1.0/admin/PROD_test1/diag/
rdbms/prod/PROD/trace/PROD_ora_3726_HCHECK.trc
Run the Fowllowing Statement in sqlplus prompt.
SQL> update STMT_AUDIT_OPTION_MAP set option#=234 where name =’ON COMMIT REFRESH’;
1 row updated.
SQL> commit;
Commit complete.
|
Section 2 – Applying Interoperability Patches
Step 1. Checking AD and TXK Current Versions
Apply the necessary patches to EBS as per the requirements, considering the version and the AD/TXK delta versions.
The current AD and TXK versions in my environment are already at Delta 14 as shown below
SQL> col ABBREVIATION for a10 ABBREVIATION NAME CODELEVEL |
Step 2. Checking Patches that are Already Applied
As per our Application Version, The following software component versions must exist in our environment.
Patch 25452805
- Patch 26052406
- Patch 26521736 – 19c interoperability patch for Release 12.2
- Patch 30433124
- Patch 31349591
- Patch 31800803
- Patch 33346385
- Patch 33119203 – Apply on AD/TXK Delta 13 or later versions.
- Patch 30611319 – Apply only if using Integrated SOA Gateway (ISG).
- Patch 28732161 – Apply on Release 12.2.6 or later versions.
- Patch 31088182 – Apply on Release 12.2.4 or later versions.
For my specific environment, the patches mentioned in the above list have already been applied. If these patches have not been applied in your environment, it is necessary to apply them.
sql> SELECT bug_number, creation_date BUG_NUMBER CREATION_ 9 rows selected. |
Note: Since we have applied all the patches except (26521736, 28732161) for the ones listed below, we will proceed to download these two patches from Oracle Support Metalink and transfer them to the Linux environment.
Step 3. Applying the Interoperability Patches
[oracle@test1 ~]$ cd /u01/oracle/PROD/19.0.0/media/interoperability [oracle@test1 interoperability]$ unzip p26521736_12.2.0_R12_LINUX.zip Now Applying the patches through ADOP in one command after sourcing the application run environment [oracle@test1 ~]$ . ./EBSapps.env E-Business Suite Environment Information DB Host: test1.example.com Service/SID: PROD E-Business Suite Environment Setting Please choose the environment file you wish to source [R/P]:R Sourcing the RUN File System … [oracle@test1 ~]$ cd /u01/oracle/PROD/19.0.0/media/interoperability [oracle@test1 ~]$ adop phase=prepare,apply,finalize,cutover,cleanup mtrestart=no patches= 26521736,28732161 Enter the APPS password: Validating credentials. Initializing. Validating system setup. ADOP (C.Delta.14) |
Section 3– Creating the Initialization Parameter Files
Step 1. Make sure the below parameter is false in DB.
SQL> show parameter SEC_CASE_SENSITIVE_LOGON
NAME TYPE VALUE
———————————— ———– ——————————
sec_case_sensitive_logon boolean FALSE
Step 2. Creating the initialization parameter files
Run the following commands to create the $ORACLE_HOME/dbs/<ORACLE_SID>_initparam.sql and $ORACLE_HOME/dbs/<ORACLE_SID>_datatop.txt files.
[oracle@test1 ~]$ cd $ORACLE_HOME/appsutil Oracle Home being passed: /u01/oracle/PROD/12.1.0 Once completed you can check the below highlighted are created in cd $ORACLE_HOME/dbs [oracle@test1 bin]$ cd $ORACLE_HOME/dbs |
Section 4– Install 19c Database Oracle Home
I have downloaded the Software of 19.3 From Download Link 19c and moved it to /u01/oracle/PROD/19.0.0 directory.
[oracle@test1 ~]$ cd /u01/oracle/PROD/19.0.0
[oracle@test1 19.0.0]$ unzip -oq LINUX.X64_193000_db_home.zip
Now after unzipping the 19c software files we will launch the installer to install the 19c software
[oracle@test1 19.0.0]$ ./runInstaller
|
|
|
|
|
|
Click Next |
|
|
|
|
|
|
|
Installation Process Starts |
|
Click Yes |
|
Click Close |
Section 5 – Applying Latest Database Patchset 19.19
Step 1. Creating 19c ENV File
After successfully installing the 19c Oracle Home, you can create a straightforward environment by configuring the following parameters. In this case, “prod” represents the CDB that will be created.
Ensure to export these environment variables for easy configuration and access in the environment. |
Open New Terminal and Create 19c.env file with the following parameters given below and save the file as shown in the Picture below
[oracle@test1 ~]$ vi 19c.env
export ORACLE_HOME=/u01/oracle/PROD/19.0.0
export ORACLE_SID=PROD
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/perl/bin
export ORACLE_BASE=/u01/oracle/PROD
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
Step 2. Applying the Latest DB PSU 19.19
Here we are going to apply the Latest Database 19c Patch Set (Release Update 19.19.0.0.230418) Certified with Oracle E-Business Suite As per Oracle (Doc ID 1594274.1) (Oracle E-Business Suite Release 12.2: Consolidated List of Oracle Database Patches and Bug Fixes)
I have downloaded the patches mentioned in Pictures below and moved them to the Linux Machine on /u01/oracle/PROD/19.0.0/media directory
[oracle@test1 ~]$ cd /u01/oracle/PROD/19.0.0/media
[oracle@test1 media]$ ls -ltrh p3504*
-rwxrwxr-x. 1 oracle oinstall 4.7M Jul 16 15:40 p35047599_1919000DBRU_Linux-x86-64.zip
-rwxrwxr-x. 1 oracle oinstall 1.7G Jul 16 15:41 p35042068_190000_Linux-x86-64.zip
Unzip the Patches
[oracle@test1 media]$ unzip p35047599_1919000DBRU_Linux-x86-64.zip
[oracle@test1 media]$ unzip p35042068_190000_Linux-x86-64.zip
Also, download the latest Opatch from the link given in README.html of p35042068_190000_Linux-x86-64.zip patch files when unzipped
[oracle@test1 media]$ ls -ltrh p6880880_122010_Linux-x86-64.zip
-rwxrwxr-x. 1 oracle oinstall 120M Jul 16 15:53 p6880880_122010_Linux-x86-64.zip
[oracle@test1 media]$ unzip p6880880_122010_Linux-x86-64.zip
Create a Backup of the OLD Opatch Directory
[oracle@test1 ~]$ cd /u01/oracle/PROD/19.0.0
[oracle@test1 19.0.0]$ mv OPatch/ OPatch_BKP/
Now copy the new Opatch Directory
[oracle@test1 19.0.0]$ mv /u01/oracle/PROD/19.0.0/media/OPatch .
Now Start Applying the Database Patches Download and Unzipped
Patch 1: Patch 35042068: Database Release Update Patch 19.19.0.0.230418 (Apr 2023).
[oracle@test1 ~]$ . ./19c.env [oracle@test1 ~]$ cd /u01/oracle/PROD/19.0.0/media/35042068 [oracle@test1 35042068]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./ Oracle Interim Patch Installer version 12.2.0.1.37 Copyright (c) 2023, Oracle Corporation. All rights reserved. PREREQ session Oracle Home : /u01/oracle/PROD/19.0.0 Central Inventory : /u01/oracle/oraInventory from : /u01/oracle/PROD/19.0.0/oraInst.loc OPatch version : 12.2.0.1.37 OUI version : 12.2.0.7.0 Log file location : /u01/oracle/PROD/19.0.0/cfgtoollogs/opatch/opatch2023-07-16_16-20-19PM_1.log Invoking prereq “checkconflictagainstohwithdetail” Prereq “checkConflictAgainstOHWithDetail” passed. OPatch succeeded.
[oracle@test1 35042068]$ opatch apply Oracle Interim Patch Installer version 12.2.0.1.37 Copyright (c) 2023, Oracle Corporation. All rights reserved. Oracle Home : /u01/oracle/PROD/19.0.0 Central Inventory : /u01/oracle/oraInventory from : /u01/oracle/PROD/19.0.0/oraInst.loc OPatch version : 12.2.0.1.37 OUI version : 12.2.0.7.0 Log file location : /u01/oracle/PROD/19.0.0/cfgtoollogs/opatch/opatch2023-07-16_16-21-39PM_1.log Verifying environment and performing prerequisite checks… |
Patch 2: Patch 35050341: OJVM RELEASE UPDATE 19.19.0.0.0
[oracle@test1 media]$ cd 35050341 [oracle@test1 35050341]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./ Oracle Interim Patch Installer version 12.2.0.1.37 Copyright (c) 2023, Oracle Corporation. All rights reserved. PREREQ session Oracle Home : /u01/oracle/PROD/19.0.0 Central Inventory : /u01/oracle/oraInventory from : /u01/oracle/PROD/19.0.0/oraInst.loc OPatch version : 12.2.0.1.37 OUI version : 12.2.0.7.0 Log file location : /u01/oracle/PROD/19.0.0/cfgtoollogs/opatch/opatch2023-07-16_17-09-58PM_1.log Invoking prereq “checkconflictagainstohwithdetail” Prereq “checkConflictAgainstOHWithDetail” passed. OPatch succeeded.
[oracle@test1 35050341]$ opatch apply Oracle Interim Patch Installer version 12.2.0.1.37 Copyright (c) 2023, Oracle Corporation. All rights reserved. Oracle Home : /u01/oracle/PROD/19.0.0 Central Inventory : /u01/oracle/oraInventory from : /u01/oracle/PROD/19.0.0/oraInst.loc OPatch version : 12.2.0.1.37 OUI version : 12.2.0.7.0 Log file location : /u01/oracle/PROD/19.0.0/cfgtoollogs/opatch/opatch2023-07-16_17-10-45PM_1.log Verifying environment and performing prerequisite checks…
|
Patch 1: Patch 35406065: RESTORE DATABASE PREVIEW FAILING WITH RMAN-00600
[oracle@test1 ~]$ cd /u01/oracle/PROD/19.0.0/media/ [oracle@test1 media]$ cd 35406065 [oracle@test1 35406065]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./ Oracle Interim Patch Installer version 12.2.0.1.37 Copyright (c) 2023, Oracle Corporation. All rights reserved. PREREQ session Oracle Home : /u01/oracle/PROD/19.0.0 Central Inventory : /u01/oracle/oraInventory from : /u01/oracle/PROD/19.0.0/oraInst.loc OPatch version : 12.2.0.1.37 OUI version : 12.2.0.7.0 Log file location : /u01/oracle/PROD/19.0.0/cfgtoollogs/opatch/opatch2023-07-16_18-44-53PM_1.log Invoking prereq “checkconflictagainstohwithdetail” Prereq “checkConflictAgainstOHWithDetail” passed. OPatch succeeded.
[oracle@test1 35406065]$ opatch apply Oracle Interim Patch Installer version 12.2.0.1.37 Copyright (c) 2023, Oracle Corporation. All rights reserved. Oracle Home : /u01/oracle/PROD/19.0.0 Central Inventory : /u01/oracle/oraInventory from : /u01/oracle/PROD/19.0.0/oraInst.loc OPatch version : 12.2.0.1.37 OUI version : 12.2.0.7.0 Log file location : /u01/oracle/PROD/19.0.0/cfgtoollogs/opatch/opatch2023-07-16_18-45-17PM_1.log Verifying environment and performing prerequisite checks… OPatch continues with these patches: 35406065 |
Step 3. Create the nls/data/9idata directory
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.
[oracle@test1 ~]$ . ./19c.env
[oracle@test1 ~]$ unset PERL5LIB
[oracle@test1 ~]$ perl $ORACLE_HOME/nls/data/old/cr9idata.pl
Creating directory /u01/oracle/PROD/19.0.0/nls/data/9idata …
Copying files to /u01/oracle/PROD/19.0.0/nls/data/9idata…
Copy finished.
Please reset environment variable ORA_NLS10 to /u01/oracle/PROD/19.0.0/nls/data/9idata!
After creating the directory, make sure that the ORA_NLS10 environment variable is set to the full path of the 9idata directory whenever you enable the 19c Oracle home. Add export ORA_NLS10=/u01/oracle/PROD/19.0.0/nls/data/9idata in 19c environment file
[oracle@test1 ~]$ vi 19c.env
[oracle@test1 ~]$ cat 19c.env
export ORACLE_HOME=/u01/oracle/PROD/19.0.0export ORACLE_SID=PROD
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/perl/bin
export ORACLE_BASE=/u01/oracle/PROD
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
export ORA_NLS10=/u01/oracle/PROD/19.0.0/nls/data/9idata
Step 4. Create appsutil.zip and copy it to the database tier
On Application Tier source run file system environment
[oracle@test1 ~]$ . ./EBSapps.env run
E-Business Suite Environment Information
—————————————-
RUN File System : /u01/oracle/PROD/fs2/EBSapps/appl
PATCH File System : /u01/oracle/PROD/fs1/EBSapps/appl
Non-Editioned File System : /u01/oracle/PROD/fs_ne
DB Host: test1.example.com Service/SID: PROD
Sourcing the RUN File System …
Run the below-given command to create appsutil.zip
[oracle@test1 ~]$ perl $AD_TOP/bin/admkappsutil.pl
Starting the generation of appsutil.zip
Log file located at /u01/oracle/PROD/fs2/inst/apps/PROD_test1/admin/log/MakeAppsUtil_07161853.log
output located at /u01/oracle/PROD/fs2/inst/apps/PROD_test1/admin/out/appsutil.zip
MakeAppsUtil completed successfully.
Copy appsutil.zip file to 19c Oracle Home and unzip
[oracle@test1 ~]$ cp /u01/oracle/PROD/fs2/inst/apps/PROD_test1/admin/out/appsutil.zip /u01/oracle/PROD/19.0.0/
[oracle@test1 ~]$ cd $ORACLE_HOME
[oracle@test1 19.0.0]$ unzip -o appsutil.zip
Step 5. Install JRE 8
To install JRE 8 on the appsutil directory, copy the $ORACLE_HOME/jdk/jre directory to $ORACLE_HOME/appsutil/jre. Run the following commands:
[oracle@test1 19.0.0]$ cd $ORACLE_HOME/appsutil
[oracle@test1 appsutil]$ cp -r $ORACLE_HOME/jdk/jre .
[oracle@test1 appsutil]$ cp $ORACLE_HOME/jlib/orai18n.jar $ORACLE_HOME/appsutil/jre/lib/ext
2 comments
Pingback: Oracle EBS Database Upgrade from 12c to 19c Part-2 - Oracle Solutions
Pingback: Applying PSU Patch in Oracle 19c - Oracle Solutions