Upgrading Oracle E-Business Suite R12.2 Database from 12.1.0.2 to 19c - Part IV

This post is continuation of the third part of the post : Part III

Create the CDB:

On the database server node:

1. Run the Database Configuration Assistant (DBCA) to create the container database (CDB).
2. When prompted, click on the "Create Database", "Advanced Configuration", and "General Purpose or Transaction Processing" options.
3. In the Specify Database Identification screen, check to create an empty container database (CDB) without a PDB.
4. 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.
5. 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.
6. 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.
7. 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.
8. During the CDB creation, ignore ORA-00313 and ORA-27037 error messages about redo logs in the alert.log file. These messages are informational.

[oraprod@non-prod-db ~]$ 19cdb_env

[oraprod@non-prod-db ~]$ dbca

Note:- Select Database character set and national character set same as source database.

NLS_CHARACTERSET  : UTF8

NLS_NCHAR_CHARACTERSET : AL16UTF16

Here we added 1 more redolog group and 1 member for each group and redolog size should be same source database.

Run datapatch on CDB

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

On UNIX/Linux(19c Home)

$ [oraprod@non-prod-db ~]$ 19cdb_env 

[oraprod@non-prod-db ~]$ export ORACLE_SID=prodcdb

$ $ORACLE_HOME/OPatch/datapatch

[oraprod@non-prod-db ~]$ export ORACLE_SID=prodcdb [oraprod@non-prod-db ~]$ $ORACLE_HOME/OPatch/datapatch SQL Patching tool version 19.27.0.0.0 Production on Wed May 28 16:19:17 2025 Copyright (c) 2012, 2025, Oracle.  All rights reserved.   Log file for this invocation: /dbtest/dbdata/cfgtoollogs/sqlpatch/sqlpatch_29396_2025_05_28_16_19_17/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:   No interim patches found   Current state of release update SQL patches:   Binary registry:     19.27.0.0.0 Release_Update 250406131139: Installed   PDB CDB$ROOT:     Applied 19.27.0.0.0 Release_Update 250406131139 successfully on 28-MAY-25 04.05.34.680815 PM   PDB PDB$SEED:     Applied 19.27.0.0.0 Release_Update 250406131139 successfully on 28-MAY-25 04.15.21.379618 PM   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 May 28 16:19:33 2025

Create the CDB MGDSYS schema

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.

[oraprod@non-prod-db ~]$ . 19cdb_env [oraprod@non-prod-db ~]$ echo $ORACLE_SID prodcdb [oraprod@non-prod-db ~]$ sqlplus "/ as sysdba" @?/rdbms/admin/catmgd.sql

Create the CDB TNS files

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.

On UNIX/Linux:

[oraprod@non-prod-db ~]$ . 19cdb_env [oraprod@non-prod-db ~]$ cd $ORACLE_HOME/appsutil  [oraprod@non-prod-db appsutil]$ . ./txkSetCfgCDB.env dboraclehome=/dbdata/erp/19.3.0  Oracle Home being passed: /dbdata/erp/19.3.0  $ 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  [oraprod@non-prod-db bin]$ perl txkGenCDBTnsAdmin.pl -dboraclehome=/dbdata/erp/19.3.0 -cdbname=prodcdb -cdbsid=prodcdb -dbport=1521 -outdir=/dbdata/erp/19.3.0/appsutil/log  Note: Listener port would be same port as source 12c database

Shut down the CDB

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

[oraprod@non-prod-db ~]$ . 19cdb_env [oraprod@non-prod-db bin]$ sqlplus / as sysdba SQL> shut immediate

Complete patching cycle and remove adop created editions

On the current run file system: [appltest@testapp ~]$ . /apdata/erp/EBSapps.env run [appltest@testapp ~]$ adop phase=prepare [appltest@testapp ~]$ adop phase=actualize_all [appltest@testapp ~]$ adop phase=finalize finalize_mode=full [appltest@testapp ~]$ adop phase=cutover  [appltest@testapp ~]$ . /apdata/erp/EBSapps.env run  [appltest@testapp ~]$ adop phase=cleanup cleanup_mode=full

Database Upgrade

1. Store the UTL_FILE_DIR parameter values

To retrieve the directory path values from the source UTL_FILE_DIR database initialization parameter:

1. Source the Oracle E-Business Suite database environment of your 12c  Oracle home.

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

oraprod@testdb ~]$ . /dbdata/erp/12.1.0/EBS_ebsproddb.env

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

Before running the script, check from database end utl_file_dir parameter and make sure all the paths are present in your current 11g environment. If its not then either create them or alter the paths in utl_file_dir parameter.

$ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=<DB Context File> \
-oraclehome=<12c/11g ORACLE_HOME> -outdir=<Output/Log location> \
-upgradedhome=<19c ORACLE_HOME> -mode=getUtlFileDir -servicetype=onpremise|opc

[oraprod@testdb ~]$ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=/dbdata/erp/12.1.0/appsutil/EBS_ebsproddb.xml -oraclehome=/dbdata/erp/12.1.0 -outdir=/home/oraprod -upgradedhome=/dbdata/erp/19.3.0 -mode=getUtlFileDir -servicetype=onpremise Enter the APPS Password:  Script Name    : txkCfgUtlfileDir.pl Script Version : 120.0.12020000.31 Started        : Thu Jun 12 09:42:57 IST 2025  Log File       : /home/oraprod/TXK_UTIL_DIR_Thu_Jun_12_09_42_54_2025/txkCfgUtlfileDir.log  Context file: /dbdata/erp/12.1.0/appsutil/EBS_ebsprodapp.xml exists.  ==============================================================================  Successfully generated the below file with UTL_FILE_DIR content: /dbdata/erp/12.1.0/dbs/EBS_utlfiledir.txt  ==============================================================================   Completed        : Thu Jun 12 09:42:58 IST 2025    Successfully Completed the script ERRORCODE = 0 ERRORCODE_END

Check the above log file and make sure all the paths are present as per utlfiledir.txt /dbdata/erp/12.1.0/dbs/EBS_utlfiledir.txt

 

 

As per utlfiledir.txt file, following directory was created

 

mkdir -p /dbdata/erp/temp/EBS

mkdir -p /dbdata /erp/19.3.0/appsutil/outbound/EBS_ebsproddb

Create the following directory paths:

• <19c Oracle Base>/temp/<PDB NAME> - for on-premises instances
• <19c ORACLE_HOME>/appsutil/outbound/<context name> - for both on-premises and Oracle Cloud instances

[oraprod@non-prod-db ~]$ echo $ORACLE_BASE /dbdata/erp [oraprod@non-prod-db]$ cd $ORACLE_BASE  [oraprod@non-prod-db ~]$ mkdir -p temp/EBS [oraprod@testdb ~]$ mkdir -p /dbdata/erp/19.3.0/appsutil/outbound/EBS_ebsproddb

Source the Oracle E-Business Suite database environment of your 12c  Oracle home.

If you are creating your Oracle Database 19c instance on a different server and cannot validate the 19c Oracle home directory, then you should pass the -skipdirvalidation=Yes parameter in the txkCfgUtlfileDir.pl script command

[oraprod@testdb ~]$ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=/dbdata/erp/12.1.0/appsutil/EBS_ebsprodapp.xml -oraclehome=/dbdata/erp/12.1.0 -outdir=/home/oraprod -upgradedhome=/dbdata/erp/19.3.0 -mode=setUtlFileDir -servicetype=onpremise Enter the APPS Password:  Script Name    : txkCfgUtlfileDir.pl Script Version : 120.0.12020000.31 Started        : Thu Jun 12 09:57:06 IST 2025  Log File       : /home/oraprod/TXK_UTIL_DIR_Thu_Jun_12_09_57_03_2025/txkCfgUtlfileDir.log  Context file: /dbdata/erp/12.1.0/appsutil/EBS_ebsprodapp.xml exists.  Enter the ebs_system Password:  Completed        : Thu Jun 12 09:57:13 IST 2025  Successfully Completed the script ERRORCODE = 0 ERRORCODE_END

Shut down the application tier server processes

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

[appltest@testapp scripts]$ . /apdata/erp/EBSapps.env run [appltest@testapp scripts]$ cd $ADMIN_SCRIPTS_HOME [appltest@testapp scripts]$ sh adstpall.sh

Drop SYS.ENABLED$INDEXES (Conditional)

Source 12c Database environment file

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

[oraprod@non-prod-db ~]$ . /dbdata/erp/12.1.0/EBS_ebsproddb.env [oraprod@non-prod-db ~]$ sqlplus / as sysdba SQL> drop table sys.enabled$indexes; drop table sys.enabled$indexes                * ERROR at line 1: ORA-00942: table or view does not exist

Gather dictionary statistics before starting the upgrade process.

Gather dictionary statistics before starting the upgrade process. This is recommended to run atleast 1 day before the upgrade.Do not run this during the upgrade To check when last time the dictionary statistics have been gathered column OPERATION format a40  set linesize 200  select to_char(max(END_TIME),'DD-MON-YY hh24:mi') LATEST, OPERATION from DBA_OPTSTAT_OPERATIONS where OPERATION in ('gather_dictionary_stats', 'gather_fixed_objects_stats') group by operation;   exec dbms_stats.gather_dictionary_Stats; exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; exec dbms_stats.gather_schema_stats('SYS'); exec dbms_stats.gather_index_stats('SYS','I_OBJ#');  exec dbms_stats.gather_index_stats('SYS','I_FILE#_BLOCK#'); exec dbms_stats.gather_index_stats('SYS','I_TS#'); exec dbms_stats.gather_index_stats('SYS','I_USER#'); exec dbms_stats.gather_index_stats('SYS','I_TOID_VERSION#');  exec dbms_stats.gather_index_stats('SYS','I_MLOG#');  exec dbms_stats.gather_index_stats('SYS','I_RG#');

Shut down the database listener

On the database tier server node, shut down the Oracle Net or Net8 database listener in the old Oracle home(12c listener).

[oraprod@ebsproddb 12.1.0]$ lsnrctl status EBS [oraprod@non-prod-db ~]$ lsnrctl stop EBS

SQL> show parameter local_listener  NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ local_listener                       string      TEST_LOCAL  SQL> alter system set local_listener='' scope=both; System altered.  SQL>  show parameter local_listener  NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ local_listener                       string

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

vi /etc/oratab Add the following entry if its not present,**IMPORTANT: make sure the sid entry is in CAPTIAL EBS:/dbdata/erp/12.1.0:N

 

• Unset the olap_page_pool_size initialization parameter.

SQL> show parameter olap   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ olap_page_pool_size                  big integer 4M   SQL> alter system set olap_page_pool_size=0 scope=spfile; System altered.

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

SQL> ALTER SYSTEM SET EVENT='10946 trace name context forever, level 8454144' SCOPE=SPFILE;   SQL> create pfile from spfile; System altered.

 

Shut down and restart the database to enable the parameters.

SQL> SHU IMMEDIATE SQL> startup

 

Please follow the next part here :