In Place Upgrade for an EBS 12.1.3 database 12c to 19c Multitenant in DBCS(DB System) on OCI


In this article I am explained in demo how I upgraded an 12c database on OCI to 19c. This is inplace upgrade which means on same DBCS system without creating/migrating any other DBCS system. This is totally based on experience and method. Always test the method in your development environment before trying it in live production environment.




System Information:

EBS : 12.2.7
Database: 12.1.0.1
DBCS Linux version: 7.9

Target database version.19.19

Where ever # sign is before the commands , means I have run the commands from root user.
You should know some dbcli basic commands
As per your environment and setup their might be additional steps required.


1) Apply the pre-requisites patches on EBS


a) Apply code level AD/TXK patches - 33600809, 33602997


Copy adgrants.sql to Database $ORACLE_HOME/appsutil/admin
. /u01/app/oracle/product/12.1.0.2/dbhome_1/TEST19C_foaserver.env
export ORACLE_PDB_SID=TEST19C
sqlplus / as sysdba
SQL> @$ORACLE_HOME/appsutil/admin/adgrants.sql <APPS schema name>
SQL> ALTER USER EBS_SYSTEM ACCOUNT UNLOCK;
SQL> ALTER USER EBS_SYSTEM IDENTIFIED BY welcome123;
copy patches to patch top - /u01/app/fs_ne/EBSapps/patch
adop phase=prepare
adop phase=apply patches=33600809, 33602997
adop phase=finalize
adop phase=cutover
adop phase=cleanup
adop phase=fs_clone


b) apply other required application patches


copy patches to patch top - /u01/app/fs_ne/EBSapps/patch
adop phase=prepare
adop phase=apply patches=25452805,26052406,26521736,30433124,31349591,31800803,33346385,29914546,28732161,31088182
adop phase=finalize
adop phase=cutover
adop phase=cleanup
adop phase=fs_clone




2) Obtain Pre-Authenticated Request URL- raise an Oracle SR to get the 19c Oracle home on DBCS system.




3) Install Oracle Database 19c Software on DBCS system


a) Update the dcs client service on dbcs system

# cliadm update-dbcli


b) Install the Oracle Database 19c software using the dbcli tool for Oracle Base Database Service DB Systems. (Will be given by Oracle SR)
Below is just a sample URL not the original URL given by Oracle.

# dbcli create-dbhome \
--cloneVersion 19.19.0.0.0 \
--parUrl https://objectstorage.ap-hyderabad-1.oraclecloud.com/p/UULKQC47Ys-XQT9b_pvExCT9e2ldKDqGLaUJgcuNkOJhMjHeC-/n/ebsdev/b/DB-Images/o/CustomImage/db1919ebiz.230418.tar.gz \
--sha256sum 13b41b1bff0cf88f4cd179f0149

c) To confirm that the dbcli create-dbhome command run above completed successfully

# dbcli describe-job -i <job ID>
or
# dbcli describe-job -i <job ID> -l Info
or
# dbcli describe-job -i <job ID> -l Verbose



d) Obtain the Oracle home location (get the new 19c home)


# dbcli list-dbhomes

Ensure you are able to see 19c home

dbcli list-dbhomes
ID                                       Name                 DB Version                               Home Location                                 Status    
---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------
80da18bb-3e55-4c8a-a378-6fb139e03860     OraDB12102_home1     12.1.0.2.210119                          /u01/app/oracle/product/12.1.0.2/dbhome_1     Configured
f95ec35f-b41f-47a4-8c08-83129554bec0     OraDB19000_home1     19.19.0.0.0                              /u01/app/oracle/product/19.0.0.0/dbhome_1     Configured


e) Login as oracle user
# su - oracle

set the $ORACLE_HOME and $PATH environment variables

$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

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


f) Apply additional patches using the opatch utility. These should be already applied.Just Validate

opatch lsinventory | grep 35037877
opatch lsinventory | grep 35119957


g)  Revert group permissions on oracle executable

su - grid
 ps -ef | grep -i tns    --> get the grid home path --> /u01/app/19.0.0.0/grid
# vi /etc/oratab
insert -> +ASM1:/u01/app/19.0.0.0/grid:N -> :wq!
. oraenv --> +ASM1
export PATH=$GRID_HOME/bin:$PATH
setasmgidwrap -o /u01/app/oracle/product/19.0.0.0/dbhome_1/bin/oracle


h) Create an nls directory 

$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH 
$ export PATH=$ORACLE_HOME/perl/bin/:$PATH
$ export PERL5LIB=$ORACLE_HOME/perl/lib/5.36.0:$ORACLE_HOME/perl/site_perl/5.36.0
$ export ADPERLPRG=$ORACLE_HOME/perl/bin/perl
$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/nls/data/old/cr9idata.pl

4) Prepare for Database Upgrade


a) Download and run hcheck.sql source 12c env
cd /scrpipts --> hcheck.sql
sqlplus / as sysdba
SQL> spool hcheck.log
SQL> @hcheck
SQL> spool off
b) Close any open patching cycles and reduce the number of editions [On the current run file system]
adop phase=prepare,actualize_all
adop phase=finalize finalize_mode=full
adop phase=cutover mtrestart=no
[On the new run file system]
adop phase=cleanup cleanup_mode=full
adop phase=fs_clone
c) Validate the AD and TXK Code Level

col ABBREVIATION for a10
set lines 1000
col NAME for a50
col CODELEVEL for a20
SELECT ABBREVIATION,NAME,codelevel FROM apps.AD_TRACKABLE_ENTITIES WHERE abbreviation in ('txk','ad');

ABBREVIATI NAME                                               CODELEVEL
---------- -------------------------------------------------- --------------------
ad         Applications DBA                                   C.14
txk        Oracle Applications Technology Stack               C.14


d) Check temp file from the below query and if any temp file is invalid then drop it

select * from v$tempfile;


SQL> ALTER DATABASE TEMPFILE 1 drop INCLUDING DATAFILES;   

Database altered.

e) Autoextend datafiles to avoid any tablespace issues during upgrade

set lines 200 pages 200
col FILE_NAME for a50
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;


f) Make sure case sensitive parameter is false

SQL> show parameter case

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     FALSE

 

g) Copy the tnsnames.ora in $ORACLE_HOME/network/admin on 12c.


cd /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin


[oracle@foaserver admin]$ mv tnsnames.ora tnsnames.ora_old


[oracle@foaserver admin]$ cp TEST19C_foaserver/tnsnames.ora .

Also Please create appsutil from EBS admin node and copy and unzip in both 12c and 19c home.


On Application Tier source run file system environment

perl $AD_TOP/bin/admkappsutil.pl

Copy appsutil.zip file to 12c Oracle Home and unzip
cp appsutil.zip $ORACLE_HOME
unzip -o appsutil.zip

h) Guaranteed restore point will be created automatically during the upgrade; therefore, you must have a fast recovery area defined and the database must be in ARCHIVELOG mode

$ srvctl stop database -d TEST19C_1227
$ sqlplus ""/ as sysdba""
startup mount;
alter database archivelog;
alter database open;
alter system set db_recovery_file_dest_size=20G scope=both sid='*';
alter system set db_recovery_file_dest='+RECO' scope=both sid='*';
shutdown immediate;
srvctl start database -d TEST19C_1227

i) Create the initialization parameter setup files

This has to be executed from 12c environment

Context file path=/u01/app/oracle/product/12.1.0.2/dbhome_1/appsutil/TEST19C_foaserver.xml

cd $ORACLE_HOME/appsutil
. ./txkSetCfgCDB.env dboraclehome=/u01/app/oracle/product/12.1.0.2/dbhome_1
export ORACLE_SID=TEST19C
perl $ORACLE_HOME/appsutil/bin/txkPrePDBCreationTasks.pl \
-dbcontextfile=/u01/app/oracle/product/12.1.0.2/dbhome_1/appsutil/TEST19C_foaserver.xml \
-dboraclehome=$ORACLE_HOME \
-outdir=$ORACLE_HOME/appsutil/log \
-skipdbshutdown=yes

$ perl $ORACLE_HOME/appsutil/bin/txkGenDBInitParam.pl \
-dbcontextfile=$CONTEXT_FILE \
-dboraclehome=$ORACLE_HOME \
-outdir=$ORACLE_HOME/appsutil/log

j) Set the following initialization parameter requirements for the upgrade:
 
Review the directory based parameters such as user_dump_dest, diagnostic_dest and update them if necessary.
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'" alter system set event='10946 trace name context forever, level 8454144' scope=spfile;
Shut down and restart the database to enable the parameters "srvctl stop database -d $ORACLE_UNQNAME
srvctl start database -d $ORACLE_UNQNAME


k) Ensure that sqlnet.ora in <19c ORACLE_HOME>/network/admin points to source 12c database TDE WALLET_LOCATION. If required, update sqlnet.ora in <19c ORACLE_HOME>/network/admin (Not required to change in our case)

l) As the root user run the dbcli tool in --precheck mode

If dbcli in ""precheck"" mode fails, you need to check the logs and fix the errors prior to running the upgrade command in the next section. The Database ID can be found by running the dbcli list-databases command and the Oracle Home ID can be found by running the dbcli list-dbhomes command from the root prompt.

#dbcli list-databases [Note the source Oracle 12c database ID from the output]
# dbcli list-dbhomes [Note the target 19c Oracle Home ID from the output]
# dbcli upgrade-database -i <source Oracle 12c database ID> -dh <target 19C Oracle Home ID> --upgradeOptions ""-keepEvents"" --precheck

dbcli upgrade-database -i bb600add-10c1-4b8f-9507-38f343f46d52 -dh f95ec35f-b41f-47a4-8c08-83129554bec0 --upgradeOptions ""-keepEvents"" --precheck"

The dbcli upgrade-database command in precheck mode run in the previous step generates and displays a job ID, which runs in the background. Ensure the upgrade in precheck mode completed successfully

# dbcli describe-job -i <job ID>
or
# dbcli describe-job -i <job ID> -l Info
or
# dbcli describe-job -i <job ID> -l Verbose

If precheck fails, fix the errors which are listed in the following file and rerun the precheck to ensure that it completes successfully.
<ORACLE_BASE>/cfgtoollogs/dbua/upgrade<date>_<timestamp>/<db_unique_name>/upgrade.xml

m) Recompile any invalid objects before the upgrade

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



5) Upgrade 12c Database to 19c Non CDB



a) Note the source Oracle 12c database ID and target 19c Oracle Home ID from the output of the following two commands, which will be used in the database upgrade command

# dbcli list-databases [Note the source Oracle 12c database ID from the output]
# dbcli list-dbhomes [Note the target 19c oracle Home ID from the output]

Run Command to upgrade database to 19c from root user dbcli upgrade-database -i bb600add-10c1-4b8f-9507-38f343f46d52 -dh f95ec35f-b41f-47a4-8c08-83129554bec0 --upgradeOptions "-keepEvents" 
The dbcli upgrade-database command inthe previous step generates and displays a job ID, which runs in the background. Ensure the upgrade in completed successfully

# dbcli describe-job -i <job ID>
or
# dbcli describe-job -i <job ID> -l Info
or
# dbcli describe-job -i <job ID> -l Verbose

b) Verify the DB Home path in /etc/oratab

cat /etc/oratab
+ASM1:/u01/app/19.0.0.0/grid:N
TEST19C:/u01/app/oracle/product/19.0.0.0/dbhome_1:N

c) Switch to Oracle User and verify the path in .bashrc. It should point to 19c

# .bashrc

# Source global definitions
if [ -f /etc/bashrc ]; then
. /etc/bashrc
fi

# Uncomment the following line if you don't like systemctl's auto-paging feature:
# export SYSTEMD_PAGER=

# User specific aliases and functions
PATH=$PATH:/u01/app/oracle/product/19.0.0.0/dbhome_1/bin; export PATH
## WARNING!! Modifying this file can cause failures in API/CLI provided by Cloud Tooling!!
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1; export ORACLE_HOME
PATH=$PATH:/u01/app/oracle/product/19.0.0.0/dbhome_1/bin; export PATH
LD_LIBRARY_PATH=/u01/app/oracle/product/19.0.0.0/dbhome_1/lib; export LD_LIBRARY_PATH
ORACLE_UNQNAME=TEST19C_1227;export ORACLE_UNQNAME
ORACLE_SID=TEST19C; export ORACLE_SID

d) Connect as sysdba and verify the name

SQL> show con_name

CON_NAME
------------------------------
TEST19C

SQL> show pdbs. -->No PDBS

SQL> select name,open_mode,cdb from v$database;

NAME      OPEN_MODE            CDB
--------- -------------------- ---
TEST19C   READ WRITE           NO


e) Validate number of invalid objects

SQL> select count(*) from dba_objects where status <>'VALID';

  COUNT(*)
----------
        74

f) Use 19c home now

Add TEST19c entry in tnsnames.ora

cd $ORACLE_HOME/network/admin
vi tnsnams.ora
Add below entry
TEST19C=
        (DESCRIPTION= 
                (ADDRESS=(PROTOCOL=tcp)(HOST=foaserver.xxxxxxxxx.oraclevcn.com)(PORT=1521))
            (CONNECT_DATA=
                (SERVICE_NAME=TEST19C)
                (INSTANCE_NAME=TEST19C)
            )
        )


g) Set case sensitive parameter is false

SQL> show parameter case

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE

SQL> alter system set sec_case_sensitive_logon=FALSE;

System altered.

SQL> show parameter case

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     FALSE


h) Restart database

shut immediate
startup


i) Add the following entry to the Oracle Database 19c $ORACLE_HOME/network/admin/sqlnet.ora file

SQLNET.ALLOWED_LOGON_VERSION_SERVER=10

j) Patch detail from registry

SQL>  select * from sys.registry$history;
 SQL> select * from sys.dba_registry_sqlpatch;

k) Compile SYSTEM.EBS_LOGON trigger

alter trigger SYSTEM.EBS_LOGON compile;

l) Connect to the database as SYSDBA and run the following script

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

m) Grant create procedure privilege on CTXSYS
adctxprv.sql will be present on Application EBS $AD_TOP/patch/115/sql/adctxprv.sql

I have copied it to db node and executed
[oracle@foaserver scripts]$ cd /home/oracle/scripts
[oracle@foaserver scripts]$ sqlplus apps/welcomeab @adctxprv.sql welcomeab CTXSYS

n) Give database privileges

from sysdba

grant select,update,delete,insert on sys.xdb$moveSchemaTab to apps;
alter package apps.AD_ZD_PREP compile body;
sqlplus /as sysdba
SQL> grant SELECT on "SYS"."DB_NOTIFICATIONS" to "APPS" with grant option;

SQL> grant SELECT on "SYS"."DBA_EDITION_INHERITED_OBJECTS" to "APPS" with grant option;

SQL> grant SELECT on "SYS"."DBA_OBJECTS_CE" to "APPS" with grant option;

SQL> grant SELECT on "SYS"."DBA_CHECK_TIMESTAMPS" to "APPS" with grant option;

SQL> grant SELECT on "SYS"."DBA_CHECK_MISSING_PARENT" to "APPS" with grant option;

SQL> grant SELECT on "SYS"."DBA_CHECK_MISSING_DEP_STUB" to "APPS" with grant option;

Copy the $APPL_TOP/admin/adgrants.sql to db server then run from EBS_SYSTEM user
sqlplus EBS_SYSTEM/welcome123
@adgrants.sql apps
Then from Application tier
cd $APPL_TOP/admin
sqlplus APPS @adgrants.sql apps


o) Run UTLRP

@$ORACLE_HOME/rdbms/admin/utlrp.sql

p) Copy appsutil.zip from Application Tier to Database 19c home

On Application Tier source run file system environment 

perl $AD_TOP/bin/admkappsutil.pl

Copy appsutil.zip file to 19c Oracle Home and unzip
cp appsutil.zip $ORACLE_HOME
unzip -o appsutil.zip

q) To install Java Runtime Environment (JRE) 8, copy the JRE directory from 19c $ORACLE_HOME/jdk and its subdirectories to the 19c $ORACLE_HOME/appsutil directory. 

Copy orai18n.jar from $ORACLE_HOME/jlib to $ORACLE_HOME/appsutil/jre/lib/ext.

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

r) Connect to the database as SYSDBA and install the Object Label Security (OLS) component by running the catols.sql script
$ sqlplus / as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/catols.sql

s) Run the catmac.sql script to install the Database Vault (DV) component

$ sqlplus / as sysdba
@$ORACLE_HOME/rdbms/admin/catmac.sql SYSTEM TEMP

t) Grant datastore access
sqlplus / as sysdba
SQL> grant text datastore access to public;

u) Gather statistics for the SYS schema.Copy $APPL_TOP/admin/adstats.sql from the administration server node to the database server node
$ sqlplus / as sysdba
SQL> alter system enable restricted session;
SQL> @adstats.sql
$ sqlplus / as sysdba
SQL> alter system disable restricted session;
SQL> exit;

v) Set compatibility to 19c
alter system set compatible='19.0.0' scope=spfile;

6) Empty 19c CDB Container(TESTCDB)

a) Check Database in NON CDB

SQL> select name, cdb from v$database;

NAME      CDB
--------- ---
TEST19C   NO

b) Check the DBCLI information
[root@ebs19cdb log]# dbcli describe-database -i bb600add-10c1-38f343f46d52

Database details                                                  
---------------------------------------------------------------- 
                     ID: bb600add-10c1-38f343f46d52
            Description: TEST19C
                DB Name: TEST19C
             DB Version: 19.19.0.0.0
                DB Type: Si
             DB Edition: EE
                   DBID: 2517192891
 Instance Only Database: false
                    CDB: true
               PDB Name: TEST19C_pdb1
    PDB Admin User Name: pdbuser
                  Class: Oltp
                  Shape: 
                Storage: ASM
          DB Redundancy: 
           CharacterSet: AL32UTF8
  National CharacterSet: AL16UTF16
               Language: AMERICAN
              Territory: AMERICA
                Home ID: f95ec35f-b41f-47a4-8c08-83129554bec0
        Console Enabled: false
     Level 0 Backup Day: Sunday
     AutoBackup Enabled: false
                Created: July 4, 2023 10:47:26 AM UTC
         DB Domain Name: xxxxxxxxxx.oraclevcn.com


c) Create a new Empty 19c CDB

Refer Screenshots below
















Once completed successfully, please proceed further.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TESTCDB   READ WRITE


d) Create an nls directory using the following commands on all database nodes

export ORACLE_HOME=<19c Oracle Home>
$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/nls/data/old/cr9idata.pl

e) 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 in the CDB.

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

f) Check SGA and PGA on CDB

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga            boolean     FALSE
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     TRUE
sga_max_size                         big integer 6G
sga_min_size                         big integer 0
sga_target                           big integer 6G
unified_audit_sga_queue_size         integer     1048576

SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit                  big integer 6G
pga_aggregate_target                 big integer 3G

g) Enable OLAP option for single-node DB Systems

Shutdown immediate  - TESTCDB
cd $ORACLE_HOME/rdbms/lib
$ chopt enable olap

h) Container Database Start

srvctl start database -d TESTCDB

i) Install required OLAP schema objects for single-node DB Systems If you are using a single-node DB System (not Oracle RAC), and your Oracle Database software edition is Enterprise Edition, run the following commands to install OLAP:

$ sqlplus / as sysdba
SQL> @?/olap/admin/olap.sql SYSAUX TEMP;

j) Run datapatch on the CDB

$ export ORACLE_HOME=<19c Oracle Home>
$ export ORACLE_SID=<19c CDB_SID>
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export TNS_ADMIN=<19c Oracle Home>/network/admin
$ $ORACLE_HOME/OPatch/datapatch

k) Check timezone version from database

SQL> SELECT tz_version FROM registry$database;

TZ_VERSION
----------
        41

l) Shut down the CDB

srvctl stop database -d TESTCDB

7)  Plug in Oracle Database 19c Non-CDB  to the 19c Empty Container



a) Update the initialization parameters on the Oracle 19c Container Database. This file would have been created in 12c side.

$ export ORACLE_HOME=<19c Oracle Home>
$ export ORACLE_SID=<CDB_SID>
$ export ORACLE_UNQNAME=<19c $ORACLE_UNQNAME>
$ export PATH=$ORACLE_HOME/bin:$PATH
$ sqlplus / as sysdba
SQL> startup nomount;
SQL> @$ORACLE_HOME/dbs/<DB_NAME>_initparam.sql  --> This was created earlier with TEST19c_initparams.sql in 12c home.
SQL> shutdown;


b) Ensure that TEMP table space contains at least 5GB available space We have already set autoextend on
Shut down and restart the non-CDB database in READ ONLY mode

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database open read only;

c) Create a descriptor file $ORACLE_HOME/dbs/<PDB name>_PDBDesc.xml for the non-CDB that is to be plugged in

BEGIN
DBMS_PDB.DESCRIBE(pdb_descr_file => '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/TEST19C_PDBDesc.xml');
end;
/

d) Shut down the upgraded non-CDB database, Now don't start until plugged in

shutdown immediate

e) Start CDB Database TESTCDB

export ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
export ORACLE_SID=TESTCDB
export ORACLE_UNQNAME=TESTCDB
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus / as sysdba
startup nomount
ALTER SYSTEM SET "enable_pluggable_database"= TRUE SCOPE = SPFILE;

COMMIT;
shutdown immediate

srvctl start database -d TESTCDB

f) Run txkChkPDBCompatability.pl using the following commands. This will perform prerequisite checks prior to plugging in the database as a PDB, and report any error conditions you need to fix before you proceed to the plugin phase:

cd /u01/app/oracle/product/19.0.0.0/dbhome_1/appsutil
$ . ./txkSetCfgCDB.env dboraclehome=/u01/app/oracle/product/19.0.0.0/dbhome_1
$ export ORACLE_SID=TESTCDB
$ export ORACLE_UNQNAME=TEST19C_1227
$ export TNS_ADMIN=/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin
$ perl $ORACLE_HOME/appsutil/bin/txkChkPDBCompatability.pl \
-dboraclehome=/u01/app/oracle/product/19.0.0.0/dbhome_1 \
-outdir=/u01/app/oracle/product/19.0.0.0/dbhome_1/appsutil/log \
-cdbsid=TESTCDB \
-pdbsid=TEST19C

g)Ensure that the local_listener database initialization parameter is set in the CDB

alter system set local_listener ="10.28.1.176:1521" scope=both ;

h) From Grid user Attach the ASM home in inventory

1)  /u01/app/19.0.0.0/grid/oui/bin/attachHome.sh
2)  /u01/app/19.0.0.0/grid/oui/bin/runInstaller -updateNodeList ORACLE_HOME=/u01/app/19.0.0.0/grid CRS=true

i) Plug the upgraded database into the CDB
$ cd /u01/app/oracle/product/19.0.0.0/dbhome_1/appsutil
$ . ./txkSetCfgCDB.env dboraclehome=/u01/app/oracle/product/19.0.0.0/dbhome_1
$ export ORACLE_SID=TESTCDB
$ export ORACLE_UNQNAME=TEST19C_1227
$ export TNS_ADMIN=/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin
$ perl $ORACLE_HOME/appsutil/bin/txkCreatePDB.pl \
-dboraclehome=/u01/app/oracle/product/19.0.0.0/dbhome_1 \
-outdir=/u01/app/oracle/product/19.0.0.0/dbhome_1/appsutil/log \
-dbuniquename=TESTCDB \
-cdbsid=TESTCDB \
-pdbsid=TEST19C \
-noncdbdatadir=+DATA/TEST19C_1227/DATAFILE \
-pdbdatadir=+DATA/TEST19C_1227/DATAFILE \
-servicetype=dbsystem

j) Validate the Pluggable Database

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TEST19C                        READ WRITE NO




8) Post Upgrade Tasks

a) If you are using the Grid listener, set the USE_SID_AS_SERVICE_<LISTENER_NAME>=ON parameter in the $GRID_HOME/network/admin/listener.ora file and restart the listener

USE_SID_AS_SERVICE_LISTENER=ON

LISTENER is name of listener
Back up the sqlnet.ora and tnsnames.ora files

b) Set SYS and SYSTEM password complex

Alter user sys identified by ""w3lCOm#_3ab"";
Alter user system identified by ""w3lCOm#_3ab"";
Without changing the password to complex the next step will fail.

c) Run the following script to implement AutoConfig. Post PDB Steps

cd $ORACLE_HOME/appsutil
 . ./txkSetCfgCDB.env dboraclehome=/u01/app/oracle/product/19.0.0.0/dbhome_1

perl $ORACLE_HOME/appsutil/bin/txkPostPDBCreationTasks.pl \
-dboraclehome=/u01/app/oracle/product/19.0.0.0/dbhome_1 \
-outdir=/u01/app/oracle/product/19.0.0.0/dbhome_1/appsutil/log \
-cdbname=TESTCDB \
-dbuniquename=TESTCDB \
-cdbsid=TESTCDB \
-pdbsid=TEST19C \
-appsuser=apps \
-israc=no \
-dbport=1521 \
-servicetype=dbsystem
Go to 19c DB Oracle HOME/network/admin/ and copy tnsnames.ora from CONTEXT_NAME folder to $ORACLE_HOME/network/admin

d) Set and add UTL paths

Create the following directory paths:

/tmp
mkdir -p /u01/app/oracle/product/19.0.0.0/dbhome_1/temp/TEST19C
mkdir -p /u01/app/oracle/product/19.0.0.0/dbhome_1/appsutil/outbound/TEST19C_foaserver

e) Create UTL file in Oracle Home DBS

cd $ORACLE_HOME/dbs
vi TEST19C_utlfiledir.txt

Add below lines
/tmp
/u01/app/oracle/product/19.0.0.0/dbhome_1/temp/TEST19C
/u01/app/oracle/product/19.0.0.0/dbhome_1/appsutil/outbound/TEST19C_foaserver

f) Source the PDB environment file and then run the txkCfgUtlfileDir.pl script in set url paths mode

Source PDB env
. $ORACLE_HOME/TEST19C_foaserver.env

perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=$CONTEXT_FILE \
-oraclehome=$ORACLE_HOME -outdir=$ORACLE_HOME/appsutil/log -mode=setUtlFileDir -servicetype=opc


 perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=$CONTEXT_FILE \
-oraclehome=$ORACLE_HOME -outdir=$ORACLE_HOME/appsutil/log -mode=syncUtlFileDir -servicetype=opc"

g) Source CDB environment and Validate utl paths

. TESTCDB_foaserver.env
sqlplus apps/welcomeab@test19c
SQL> SELECT value FROM apps.v$parameter WHERE name='utl_file_dir';

VALUE
--------------------------------------------------------------------------------
/tmp,/u01/app/oracle/product/19.0.0.0/dbhome_1/temp/TEST19C,/u01/app/oracle/prod
uct/19.0.0.0/dbhome_1/appsutil/outbound/TEST19C_foaserver

h)From CDB sysdba set the event

alter system set events '10946 trace name context forever, level 8454144';

i) Validate dba_directories to make sure it contains correct path  

select DIRECTORY_NAME,DIRECTORY_PATH from dba_directories;


9) Setup EBS Application Nodes and Integrate with Database.

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

          Variable Name Value
s_dbport New database port <not applicable in our case>
s_apps_jdbc_connect_descriptor NULL
s_applptmp Directory (not /usr/tmp) defined in UTL_FILE_DIR -->/tmp
Remove value for s_apps_jdbc_connect_descriptor

b) Add the CDB entry in tnsnames.ora of Application

cd $TNS_ADMIN/
mv  tnsnames.ora tnsnames.ora_bkp

<TWO_TASK> =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL=tcp)(HOST=<hostname>.<domain>)(PORT=<port number>))
  (CONNECT_DATA = (SERVICE_NAME=ebs_<PDB SID>)(INSTANCE_NAME=<CDB SID>))
 )

vi tnsnames.ora
 
TEST19C =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL=tcp)(HOST=foaserver.lxxxxxxxxxx.oraclevcn.com)(PORT=1521))
  (CONNECT_DATA = (SERVICE_NAME=ebs_TEST19C)(INSTANCE_NAME=TESTCDB))
 )

c) Source Run FS and run Ad Config

$AD_TOP/bin/adconfig.sh contextfile=$INST_TOP/appl/admin/TEST19C_test1227ebs.xml
If multimode environment then do first on admin node then on other nodes.

d) From RUN FS update the Patch context file information in database (Context File path is for patch file system)

$ADJVAPRG oracle.apps.ad.autoconfig.oam.CtxSynchronizer \
action=upload \
contextfile='/u01/app/fs2/inst/apps/TEST19C_test1227ebs/appl/admin/TEST19C_test1227ebs.xml' \
logfile=/tmp/patchctxupload.log

e) Run FS_CLone

adop phase=fs_clone force=yes

f) Run the following command to verify adop, which completes validation of the environment

adop -validate

g) Start Application services and Validate




10) Cleanup Tasks


a) Remove original TEST19c from crs
From oracle user
srvctl status database -db test19c_1227
srvctl remove database -db test19c_1227

b) From grid user verify only CDB database would be there

crsctl stat res -t

c) Comment Test19c entry from /etc/oratab

#TEST19C:/u01/app/oracle/product/19.0.0.0/dbhome_1:N

d) Cleanup 12c Home on DB System

Connect as the opc user and change to the root user.
$ sudo su - root
From the output of the following command, record the <Oracle 12c db home id> to be used in the next command.
# dbcli list-dbhomes
Run the following command on the primary node, which removes the old 12c Oracle Home from all Oracle RAC nodes.
# dbcli delete-dbhome -i <Oracle 12c db home id>

e) Re-Compile Invalid Objects in 19c PDB
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

f) Test the SQL Developer Connection to Database

g) If you have Standby then, please recreate the DR.




References:
MOS Doc Id. 2552181.1 Interoperability Notes: Oracle E-Business Suite Release 12.2 with Oracle EBS Database 19c
MOS (Doc ID 396009.1) Database Initialization Parameters for Oracle E-Business Suite Release 12
Using UTL_FILE_DIR or Database Directories for PL/SQL File I/O in Oracle E-Business Suite Releases 12.1 and 12.2 (Doc ID 2525754.1)
Upgrading Oracle Database 12c to 19c for Oracle E-Business Suite Release 12.2 with Oracle Base Database Service DB Systems (Doc ID 2714918.1)




If you like please follow and comment