Home » Oracle » Oracle Ebuisness Suite » How to upgrade EBS database to 19c: Step by Step

How to upgrade EBS database to 19c: Step by Step

Here in this post, we will be checking out Upgrading Database to 19c with R12.1

Important points about 19c Upgrade

  1. When upgrading your Oracle E-Business Suite to Oracle Database 19c, your database will be converted to a multitenant architecture, which will consist of a Container Database (CDB) with a single Pluggable Database. Only multitenant architecture database are supported for Oracle E-Business Suite with Oracle Database 19c.
  2. During the upgrade, you will also perform steps to migrate directories defined for PL/SQL File I/O to database directory objects. This requirement is due to the desupport in Oracle Database 19c of the UTL_FILE_DIR database initialization parameter.
  3. Oracle Database Release Update 19.3 as well as Release Update 19.5 and later are certified.

High Level Steps

It consists of these high-level steps

  1. Prerequisite steps for 19C database Upgrade
  2. Database Software installation
  3. Creation of New CDB
  4. Preupgrade steps
  5. Database Upgrade
  6. Post Upgrade steps
  7. Convert Database to Multitenant Architecture

Prerequisite steps for 12c database Upgrade

(1) Oracle E-Business Suite minimum version required is  12.1.3

(2) Run hcheck.sql
This script looks for some known common Data Dictionary problems.
One critical check the script performs is to see if there is no SYSTEM data file that has a relative file number with the value of 1. If you encounter this error, do not proceed with any further steps in this document. You will need to perform an export/import.

(2) These are some of the prerequisites which need to be present

Apply these patches with adpatch with maintenance mode on

(3) Apply patch 6400501
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.

(4)Create the initialization parameter setup files
Run the following commands to create the $ORACLE_HOME/dbs/_initparam.sql and $ORACLE_HOME/dbs/_datatop.txt files.

On UNIX/Linux:

$ cd $ORACLE_HOME/appsutil
$ . ./txkSetCfgCDB.env dboraclehome=< Full path to Oracle Home>
$ export ORACLE_SID=<Oracle SID>
$ cd $ORACLE_HOME/appsutil/bin
$ perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=<Oracle Home>
-outdir=/appsutil/log -appsuser=<apps user>
-dbsid=<Oracle SID> -skipdbshutdown=yes

Example

$ cd $ORACLE_HOME/appsutil
$ . ./txkSetCfgCDB.env dboraclehome=/app/oracle/TEST/12.0/db
$ export ORACLE_SID=TEST
$ cd $ORACLE_HOME/appsutil/bin
$ perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=/app/oracle/TEST/12.0/db 
-outdir=/app/oracle/TEST/12.0/db/appsutil/log -appsuser=apps 
-dbsid=TEST -skipdbshutdown=yes

Database Installation:

(1) Prepare to create the 19c Oracle home

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

(2) Install the 19c software

Log in to the database server node as the owner of the Oracle RDBMS file system and database instance. Ensure that environment settings, such as ORACLE_HOME, are set for the new Oracle home you are about to create, and not for any existing Oracle homes on the database server node. Perform all the steps in the “Installing Oracle Database” chapter of the Oracle Database Installation Guide 19c for your platform.

In the install windows, select the options to install the database software only. Select any languages other than American English that are used by your Applications database instance. Choose the Enterprise Edition installation type.

You can look at the below post for a detailed screenshot

Step by Step Oracle 19c Database Installation on Linux

(3)Set the environment variables

After the installation, make sure that:
The ORACLE_BASE environment variable is set accordingly.
The ORACLE_HOME environment variable points to the new 19c Oracle home.
The PATH environment variable includes $ORACLE_HOME/bin and the directory where the new perl executable is located (usually $ORACLE_HOME/perl/bin).
The LD_LIBRARY_PATH environment variable includes $ORACLE_HOME/lib.
The PERL5LIB environment variable points to the directories where the new perl libraries are located: $ORACLE_HOME/perl/lib/ and $ORACLE_HOME/perl/lib/site_perl/ for UNIX/Linux, or $ORACLE_HOME/perl/lib and $ORACLE_HOME/perl/site/lib for Windows.

ORACLE_BASE=/app/oracle/TEST/DIAG
export ORACLE_BASE
ORACLE_HOME=/app/oracle/TEST/19.0/db
export ORACLE_HOME
PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$ORACLE_HOME/OPatch
LD_LIBRARY_PATH=$ORACLE_HOME/lib
PERL5LIB=$ORACLE_HOME/perl/lib/5.xx.x:$ORACLE_HOME/perl/lib/site_perl/5.xx.x
export PATH LD_LIBRARY_PATH PERL5LIB

(4) Create 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.

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

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.

(5) Apply additional 19c RDBMS patches

Patches required for 19.8.0.0.0 (Linux /Unix)

Patch 31281355 DATABASE RELEASE UPDATE 19.8.0.0.0 ((July2020)19.0.0.0.0
Patch 31219897 OJVM RELEASE UPDATE 19.8.0.0.019.0.0.0.0
Patch 2980238219.8.0.0.0
Patch 29867728  19.8.0.0.0
Patch 3062125519.8.0.0.0
Patch 3111324919.8.0.0.0
Patch 3117810319.8.0.0.0
Patch 3159695619.8.0.0.0

(6) Create appsutil.zip and copy it to the database tier

On the application tier (as the APPLMGR user):
Log in to the APPL_TOP environment (source the environment file)
Create appsutil.zip file

perl $AD_TOP/bin/admkappsutil.pl

This will create appsutil.zip in /admin/out
On the database tier (as the ORACLE user):
Copy or FTP the appsutil.zip file to the

cd <RDBMS Oracle Home>
unzip -o appsutil.zip

(7) 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

Create the CDB

(1)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.
  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.
See also  Top 30 Most Useful Concurrent Manager Queries

(2) Run datapatch on CDB

$ export ORACLE_SID=< CDM SID>
$ $ORACLE_HOME/OPatch/datapatch

Example

$ export ORACLE_SID=TESTCDB
$ $ORACLE_HOME/OPatch/datapatch

(3) 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.

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

(4) Create the initialization parameter setup 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.

$ cd $ORACLE_HOME/appsutil
$ . ./txkSetCfgCDB.env dboraclehome=<full path 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

example

$ cd $ORACLE_HOME/appsutil
$ . ./txkSetCfgCDB.env dboraclehome=/app/oracle/TEST/19.0/db
$ cd $ORACLE_HOME/appsutil/bin
$ perl txkGenCDBTnsAdmin.pl -dboraclehome=/app/oracle/TEST/19.0/db 
-cdbname=TESTCDB  -cdbsid=CDBTEST -dbport=1551 
-outdir=/app/oracle/TEST/19.0/db/appsutil/log

(5) Shutdown CDB

Database PreUpgrade

(1) Ensure that all database components/objects provided by Oracle are VALID in the source database prior to starting the upgrade.

Here is the script to check for components status

set pagesize 500
set linesize 300
col COMP_NAME format a40;
select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;

Here is the script to check for invalid object status before the upgrade

col OBJECT_NAME format a30;
col OWNER format a20
select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type
from dba_objects where status='INVALID' order by owner,object_type;

(2) Ensure that you do not have duplicate objects in the SYS and SYSTEM schema

cd <location of the script>
$ sqlplus / as sysdba
sql> alter session set nls_language='American';
sql> @dbupgdiag.sql
sql> exit

(3) Timezone should be less than or equal to the target database timezone version.

The time zone files that are supplied with Oracle Database 19c is version 32.
We can check the pre-upgraded database timezone as

SQL> select version from v$timezone_file;

if < 32, then this database upgrade will upgrade the database to Timezone version 32
if > 32, then BEFORE the upgrade you MUST patch the target 19c $ORACLE_HOME with a timezone data file of the SAME version as the one used in the source release database.

(4) Verifying Materialized View Refreshes are Complete Before Upgrade

Use this procedure to query the system to determine if there are any materialized view refreshes still in progress. Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing.

SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;

(5) Ensuring That No Files Are in Backup Mode and no files need media recovery Before Upgrading

Execute below query to check for the status of the backup:
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
Ensure that no files require media recovery:
SQL> SELECT * FROM v$recover_file;

(6) Gathering Optimizer Statistics to Decrease Oracle Database Downtime

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

(7) Purging Recycle Bin before the upgrade

SQL> PURGE DBA_RECYCLEBIN;

(8) Check the accounts use Case-Insensitive password version

col USERNAME format a40;
SELECT USERNAME,PASSWORD_VERSIONS,account_status FROM DBA_USERS;

If there are any 10g versions, suggest you to refer Oracle documentation to fix 10g versions, failing to do so, user accounts with LOCKED after the upgrade is completed.

(9) About Password Case Sensitivity

Ensure that you do not have the deprecated parameter SEC_CASE_SENSITIVE_LOGON set to FALSE.

(10) Run Pre-Upgrade information tool script

$Earlier_release_Oracle_home/jdk/bin/java -jar $New_release_Oracle_home/rdbms/admin/preupgrade.jar [FILE|TERMINAL] [TEXT|XML] [DIR output_dir]

FILE|TERMINAL – Use this option to direct script output to a file. Use TERMINAL to direct output to the terminal. If it is not specified then the default is FILE.
TEXT – Use this option to specify log should be in Text format. Use TEXT to specify text output. Use XML to specify XML output. If you do not specify an output type, then the default is text.
DIR – Logs will be created under <output_dir>. Directs the output to a specific directory. If you do not specify an output directory with the DIR option, then the output is directed to one of the default locations: If you define ORACLE_BASE environment variable then the generated scripts and log files will be created under $ORACLE_BASE/cfgtoollogs/<dbname>/preupgrade/ location else it will create under $ORACLE_HOME/cfgtoollogs/db_name/preupgrade/.

$ORACLE_HOME/jdk/bin/java -jar /app/oracle/TEST/19.0/db/rdbms/admin/preupgrade.jar FILE TEXT

We can look at the file generated

==================
PREUPGRADE SUMMARY
/uf01/app/oracle/cfgtoollogs/TEST/preupgrade/preupgrade.log
/uf01/app/oracle/cfgtoollogs/TEST/preupgrade/preupgrade_fixups.sql
/uf01/app/oracle/cfgtoollogs/TEST/preupgrade/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/uf01/app/oracle/cfgtoollogs/TEST/preupgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/uf01/app/oracle/cfgtoollogs/TEST/preupgrade/postupgrade_fixups.sql
Preupgrade complete:

We need to examine the content of preupgrade.log and take action according to it. Some of the action can be manual like increasing memory, processes, etc and other incorporated into the “preupgrade_fixups.sql”.

See also  Query to check patch applied in R12.2

We can run preupgrade fixup script as

sqlplus / as sysdba
@/u01/app/oracle/cfgtoollogs/db11g/preupgrade/preupgrade_fixups.sql
exit;

(11) Recovery options.

We need to decide about the recovery options in case of failure. We should enable the Archive log and then either take full RMAN backup or if possible create the guaranteed restore point by enabling Flashback

How to enable archive log mode in Oracle

SQL> Shutdown immediate
SQL>Startup mount
SQL>Alter database archivelog;
SQL> Alter database Open;
Verify Using
SQL> ARCHIVE LOG LIST

How to enable flashback and create a restore point

SQL> Shutdown immediate
SQL>Startup mount
SQL>Alter database flashback on;
SQL> Alter database Open;
Verify Using
SQL> select flashback_on from v$database;

SQL>create restore point BEF_UPG guarantee flashback database;

(12) Store the UTL_FILE_DIR parameter values

Retrieve the Values

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

$ . $ORACLE_HOME/<sid>_<hostname>.env
$ 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

Example
. /app/oracle/TEST/12.0/db/TEST_tnode01.env
perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=/app/oracle/TEST/12.0/db/appsutil/TEST_tnode01.xml 
-oraclehome=/app/oracle/TEST/12.0/db -outdir=/app/oracle/TEST/12.0/db/appsutil/log 
-upgradedhome=/app/oracle/TEST/19.0/db -mode=getUtlFileDir

With this command, the script retrieves the directory paths stored in the UTL_FILE_DIR database initialization parameter, modifies them to prepare them for use in Oracle Database 19c, and creates a text file named <DBNAME>_utlfiledir.txt in the $ORACLE_HOME/dbs directory with the list of modified directory paths. The script performs the following modifications in the directory paths:

Any occurrences of the path /usr/tmp are replaced with <19c ORACLE_BASE>/temp/, where is the pluggable database (PDB) name.

Any occurrences of the 11g or 12c Oracle home within a directory path are replaced with the 19c Oracle home. For example, the following directory path:

<11g/12c ORACLE_HOME>/appsutil/outbound/
is changed to the following new directory path:

<19c ORACLE_HOME/appsutil/outbound/

To store the directory path values in the database:

Create the following directory paths:

<19c Oracle Base>/temp/
<19c ORACLE_HOME>/appsutil/outbound/

Set the values

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

$ . $ORACLE_HOME/<sid>_<hostname>.env
$ 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 [ -skipdirvalidation=Yes ]

Example
. /app/oracle/TEST/12.0/db/TEST_tnode01.env
perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=/app/oracle/TEST/12.0/db/appsutil/TEST_tnode01.xml 
-oraclehome=/app/oracle/TEST/12.0/db -outdir=/app/oracle/TEST/12.0/db/appsutil/log 
-upgradedhome=/app/oracle/TEST/19.0/db -mode=setUtlFileDir


Upgrade the database instance

(1)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′

(2) If it is RAC,set cluster_database =false

(3) Start the Upgrade using dbca and select the database that needs to be upgraded

(4) There is a new parallel upgrade utility in 19c which reduces the upgrade downtime substantially. DBCA make use of it

(5) 19 c DBCA will present you with these new options
 Upgrade Parallelism: it enables the degree of parallelism for the upgrade process. By default, DBUA runs in parallel upgrade mode.
This option reduces the time needed to perform the upgrade based on the number of CPUs available to handle the running of scripts and processes. By default upgrade parallelism is set to a number of CPU’s or an alternate value can be selected from the dropdown list. By default, the upgrade can run with parallelism 4 even though the machine is a single processor.

Gather Statistics: Before Upgrade It is to reduce the overall time for the upgrade process if a decision is made to gather the statistics prior to upgrading the database.

Set User Tablespace Read-Only: During the Upgrade, it is to set user tablespaces to “read-only” during the upgrade. In case of any failure then only non-user tablespaces ( system,sysaux and undo .. ) would need to be restored.

(6) 19c DBCA also presents some RMAN options to use

Use RMAN Backup: Select either the option for DBUA to create an RMAN backup now, or to use your existing RMAN backup.

Create RMAN Backup before Upgrade: Enter the full path for a location for the backup in the Backup Location field.

Use Existing RMAN Backup: DBUA displays the timestamp for the latest RMAN backup that exists.

Use Flashback and Guaranteed Restore Point: If a flash recovery area is currently configured then the current settings are retained. DBUA also displays a screen to permit overriding these values if needed.

Create a Guaranteed Restore Point before Upgrade: DBUA automatically creates a restore point that you can revert back to in case of an upgrade failure.

Use Existing Guaranteed Restore Point: If a restore point was previously created then select the existing restore point from the dropdown list.

I have my own backup and restore strategy Use this option only if a customized backup procedure was used to back up the database. You should use customized restore procedures must be used to restore the customized backup.

(7) Once the upgrade is finished
Modify initialization parameters
Use the following sections in document 396009.1, Database Initialization Parameter Settings for Oracle Applications Release 12 on My Oracle Support as a guideline

If you previously had the SEC_CASE_SENSITIVE_LOGON initialization parameter set to FALSE, re-enable the parameter.
Set COMPATIBLE to 19.0.0.
If you modified the PGA_AGGREGATE_TARGET initialization parameter in the previous step, revert it back to the original value.

See also  What is Session Persistence,Web Entry Point and Server Load Balancer

Check the below post for a detailed screenshot of running dbca

Oracle 19c upgrade step by step


After the Database Upgrade:

(1) Run datapatch

export ORACLE_HOME=/app/oracle/TEST/19.0/db
$ export ORACLE_SID=TEST
$ $ORACLE_HOME/OPatch/datapatch

(2) Run adgrants.sql

Copy $APPL_TOP/admin/adgrants.sql from the administration 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:

sqlplus "/ as sysdba" @adgrants.sql  APPS

(3) Grant create procedure privilege on CTXSYS

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

sqlplus apps/[APPS password] @adctxprv.sql  [SYSTEM password] CTXSYS

(4) Compile invalids

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

(5) Grant datastore access

$ sqlplus "/ as sysdba"
SQL> grant text datastore access to public;

(6) Validate Workflow ruleset
On the administration server node, use SQL*Plus to connect to the database as APPS and run the $FND_TOP/patch/115/sql/wfaqupfix.sql script using the following command:

$ sqlplus [APPS user]/[APPS password] @wfaqupfix.sql
[APPLSYS user] [APPS user]

(5)Gather statistics for SYS schema

Copy $APPL_TOP/admin/adstats.sql from the administration 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:

$ sqlplus "/ as sysdba"
SQL> alter system enable restricted session;
SQL> @adstats.sql
$ sqlplus "/ as sysdba"
SQL> alter system disable restricted session;
SQL> exit;

Note: Make sure that you have at least 1.5 GB of free default temporary tablespace.

Convert Database to Multitenant Architecture

Now we have two databases (one NON-CDB and one CDB) running from 19c Oracle home. Now in this section, we will migrate the NON-CDB database to the CDB database

(1) Create the PDB descriptor

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

$ cd $ORACLE_HOME/appsutil
$ . ./txkSetCfgCDB.env dboraclehome=<full path of ORACLE_HOME>
$ export ORACLE_SID=<NON CDB SID>
$ cd $ORACLE_HOME/appsutil/bin
$ perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=<full path of ORACLE_HOME> 
-outdir=<ORACLE_HOME>/appsutil/log -appsuser=<apps user> -dbsid=<NON CDB SID>

Example
$ cd $ORACLE_HOME/appsutil
$ . ./txkSetCfgCDB.env dboraclehome=/app/oracle/TEST/19.0/db
$ export ORACLE_SID=TEST
$ cd $ORACLE_HOME/appsutil/bin
$ perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=/app/oracle/TEST/19.0/db 
-outdir=/app/oracle/TEST/19.0/db/appsutil/log -appsuser=apps -dbsid=TEST

The txkOnPremPrePDBCreationTasks.pl script shuts down the non-CDB database. Do not manually bring up the non-CDB database. There will be no access to the non-CDB database until after the migration of the non-CDB database to the PDB

(2) Update the CDB initialization parameters

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

$ cd $ORACLE_HOME/appsutil
$ . ./txkSetCfgCDB.env dboraclehome=<full path of ORACLE_HOME>
$ export ORACLE_SID=<CDB SID>
$ sqlplus "/ as sysdba"
SQL> startup nomount;
SQL> @$ORACLE_HOME/dbs/<NON CDB SID>_initparam.sql
SQL> alter system set LOCAL_LISTENER="<hostname>:<PORT>" scope=both;
SQL> shutdown;
SQL> startup;

(3) Check for PDB violations

$ cd $ORACLE_HOME/appsutil
$ . ./txkSetCfgCDB.env dboraclehome=<full path of ORACLE_HOME>
$ export ORACLE_SID=<CDB SID>
$ cd $ORACLE_HOME/appsutil/bin
$ perl txkChkPDBCompatability.pl -dboraclehome=<ORACLE HOME> 
-outdir=<ORACLE HOME>/appsutil/log -cdbsid=<CDB SID> 
-pdbsid=<NON CDB SID> -servicetype=onpremise

Example
$ cd $ORACLE_HOME/appsutil
$ . ./txkSetCfgCDB.env dboraclehome=/app/oracle/TEST/19.0/db
$ export ORACLE_SID=TESTCDB
$ cd $ORACLE_HOME/appsutil/bin
$ perl txkChkPDBCompatability.pl -dboraclehome=/app/oracle/TEST/19.0/db 
-outdir=/app/oracle/TEST/19.0/db/appsutil/log -cdbsid=TESTCDB 
-pdbsid=TEST -servicetype=onpremise

Review all warnings and resolve all errors. 

(4) Create the PDB

$ cd $ORACLE_HOME/appsutil
$ . ./txkSetCfgCDB.env dboraclehome=<full path of ORACLE_HOME>
$ cd $ORACLE_HOME/appsutil/bin
$ perl txkCreatePDB.pl -dboraclehome=<ORACLE HOME> -outdir=<ORACLE HOME>/appsutil/log 
-cdbsid=<CDB SID> -pdbsid=<NON CDB SID> -dbuniquename=<CDB SID> -servicetype=onpremise

Example
$ cd $ORACLE_HOME/appsutil
$ . ./txkSetCfgCDB.env dboraclehome=/app/oracle/TEST/19.0/db
$ cd $ORACLE_HOME/appsutil/bin
$ perl txkCreatePDB.pl -dboraclehome=/app/oracle/TEST/19.0/db -outdir=/app/oracle/TEST/19.0/db/appsutil/log 
-cdbsid=TESTCDB -pdbsid=TEST -dbuniquename=TESTCDB -servicetype=onpremise

(5) Run the post PDB script

$ cd $ORACLE_HOME/appsutil
$ . ./txkSetCfgCDB.env dboraclehome=<full path of ORACLE_HOME>
$ perl $ORACLE_HOME/appsutil/bin/txkPostPDBCreationTasks.pl -dboraclehome=<ORACLE_HOME> 
-outdir=<ORACLE_HOME>/appsutil/log -cdbsid=<CDB SID> -pdbsid=<PDB SID> 
-appsuser=apps -dbport=<TNS port number> -servicetype=onpremise

Example
$ cd $ORACLE_HOME/appsutil
$ . ./txkSetCfgCDB.env dboraclehome=/app/oracle/TEST/19.0/db
$ perl $ORACLE_HOME/appsutil/bin/txkPostPDBCreationTasks.pl -dboraclehome=/app/oracle/TEST/19.0/db 
-outdir=/app/oracle/TEST/19.0/db/appsutil/log -cdbsid=TESTCDB -pdbsid=TEST 
-appsuser=apps -dbport=1551 -servicetype=onpremise

(6) Modify initialization parameters
Use the following sections in My Oracle Support Knowledge Document 396009.1, Database Initialization Parameter Settings for Oracle E-Business Suite Release 12, as a guideline in modifying your initialization parameters.

Common Database Initialization Parameters For All Releases
Release-Specific Database Initialization Parameters For Oracle 19c
Temporary Tablespace Setup
Database Initialization Parameter Sizing

(7) Run AutoConfig on Applications 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=<host name>)(PORT=<port no>))
(CONNECT_DATA = (SERVICE_NAME=<PDB SID>)(INSTANCE_NAME=<CDB SID>))
)

Example
TEST=
(DESCRIPTION =
(ADDRESS = (PROTOCOL=tcp)(HOST=tnode01)(PORT=1551))
(CONNECT_DATA = (SERVICE_NAME=TEST)(INSTANCE_NAME=TESTCDB))
)

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
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 app’s user and run the following query:

SQL> select value from v$parameter where name=’utl_file_dir’;
Run AutoConfig on the APPL_TOP using the following command.

$ $INST_TOP/admin/scripts/adautocfg.sh


(8) Enable Database Vault (conditional)

If you need, you can re-enable the Database Vault after the upgrade is complete
Enable Oracle Database Vault as follows:
UNIX: Run the following commands. The make command enables both Oracle Database Vault (dv_on) and Oracle Label Security (lbac_on). You must enable Oracle Label Security before you can use Database Vault.
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk dv_on lbac_on ioracle

(9) Restart Applications server processes
Restart all the Application tier server processes that you shut down previously. It includes Database, database listener, Concurrent Manager, and application services.

(10) Take a fresh backup of the database

I have tried to give as much information as required for How to upgrade the R12.1 Instance database to 19c.

Related Articles
Oracle EBS with Oracle Database 19c frequently Asked Questions

4 thoughts on “How to upgrade EBS database to 19c: Step by Step”

  1. Hello,

    Thanks for providing detail information about the EBS 12.2 db upgrade from 12c to 19c.

    If we have any follow up questions, is there any email we can send the questions ?

    Thanks
    Ashish

  2. I am not able to connect to apps user after 19c upgrade, getting below error any idea???

    [oracle@ebs12219c ~]$ sqlplus apps/apps @adctxprv.sql manager CTXSYS;

    SQL*Plus: Release 19.0.0.0.0 – Production on Mon Feb 28 19:59:45 2022
    Version 19.14.0.0.0

    Copyright (c) 1982, 2021, Oracle. All rights reserved.

    ERROR:
    ORA-01017: invalid username/password; logon denied

    Enter user-name:

    1. With 19c, Multitenant comes into picture. Source the PDB env and connect like this
      sqlplus apps/apps@ @adctxprv.sql manager CTXSYS;

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top