upgrade EBS 12.2 database 12c(12.1.0.2) to 19c(19.3)
Overview
EBS is already certified to be used with Oracle Database 19c which includes the version 19.3 and 19.5. Within this, EBS will be using a single tenant architecture with one CDB and one PDB. To know more about the multitenant architecture, you should visit https://docs.oracle.com/database/121/CNCPT/cdbovrvw.htm#CNCPT89234. In this post, i have tried to give the complete step by step on how to upgrade EBS 12.2 database 12c(12.1.0.2) to 19c(19.3) on 3 particular posts. The first post has the tasks mentioned which we need to perform before the database upgrade. The second part has the actual database upgrade tasks using DBUA. The third post has the tasks mentioned which we need to perform after the database upgrade.
Important Information Regarding the Upgrade to Oracle Database 19c
1. Your source 12c database which is currently a non CDB database will be migrated to the PDB as part of database upgrade process.
2. There will be two environment variables created once the whole migration process is completed one for PDB and one for CDB.
3. Make sure the NLS character set are same in source and target(after the 19c database installations)
4. The PDB violations should not have any errors listed in it.
Documents to be referenced as part of Database upgrade
1. Interoperability Notes: Oracle E-Business Suite Release 12.2 with Oracle Database 19c (MOS Note 2552181.1)
2. Database Initialization Parameter Settings for Oracle E-Business Suite Release 12 (MOS Note 396009.1)
3. Using UTL_FILE_DIR or Database Directories for PL/SQL File I/O in Oracle E-Business Suite Releases 12.1 and 12.2 (MOS Note 2525754.1)
4. Using Oracle Database 19c RAC Multitenant (Single PDB) with Oracle E-Business Suite Release 12.2 (MOS Note 2530665.1)
Database upgrade guide https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/lot.html(section B)
Upgrading an Oracle E-Business Suite Release 12 Database to Oracle Database 19c
Before the Database Installation
- Patch
26834480 - R12.AD.C.delta.11
- Patch
28840822 -
R12.TXK.C.delta.11
ABBREVIATION
------------------------------
NAME
--------------------------------------------------------------------------------
CODELEVEL
--------------------------------------------------------------------------------
ad
Applications DBA
C.10
txk
Oracle Applications Technology Stack
C.10
ABBREVIATION
------------------------------
NAME
--------------------------------------------------------------------------------
CODELEVEL
Apply the latest AD and TXC patches. As of now, the current
is level 12.
Run adop phase=prepare in the run file system
In the patch file system:-run etcc otherwise patching will fail using the note 12.2 E-Business Suite Applications DBA Steps To Run The EBS Technology Code Level Checker (ETCC) (Doc ID 2008451.1)
Unzip patches and run adgrants.
Download and unzip the following patches:
Patch 30628681 (R12.AD.C.Delta.12)
Patch 30735865 (R12.TXK.C.Delta.12)
Run adgrants by following the instructions in the readme of Patch 30628681, which includes the latest version of adgrants.
Run the Middle Tier EBS Technology Codelevel Checker (MT-ETCC) on the patch file system.
Run the MT-ETCC utility checkMTpatch.sh (checkMTpatch.cmd on Microsoft Windows) on the Oracle E-Business Suite application tier node's patch file system.
$ . <EBS_ROOT>/EBSapps.env run
$ adop phase=finalize
$ adop phase=cutover
$ . <EBS_ROOT>/EBSapps.env run
$ adop phase=cleanup
Once these patches are applied,
a. Source the run edition environment file.
UNIX:
$ . <EBS_ROOT>/EBSapps.env run
Windows:
C:\> <RUN_BASE>\EBSapps\appl\envshell<CONTEXT_NAME>.cmd
b. Execute the admkappsutil.pl utility to create the appsutil.zip file in <INST_TOP>/admin/out.
$ perl <AD_TOP>/bin/admkappsutil.pl
On the database tier (as the oracle user):
a. Source the environment for RDBMS ORACLE_HOME.
UNIX:
$ cd <RDBMS ORACLE_HOME>
$ . ./<RDBMS ORACLE_HOME>/<CONTEXT_NAME>.env
Windows:
C:\> <RDBMS ORACLE_HOME>\<CONTEXT_NAME>.cmd
b. Copy or FTP the appsutil.zip file to <RDBMS ORACLE_HOME>.
c. Uncompress appsutil.zip, under <RDBMS ORACLE_HOME>.
$ cd <ORACLE_HOME>
$ unzip -o appsutil.zip
d. Run AutoConfig on <RDBMS ORACLE_HOME>.
Note: If you are a Windows customer on 12cR1 only, and have applied the "WINDOWS DB BUNDLE PATCH 12.1.0.2.180417(64bit):27440294" patch or later, you should copy the latest afinit_db121.ora and afinit_db121RAC.ora templates into the custom directory:
C:\> copy %ORACLE_HOME%/appsutil/template\afinit_db121.ora %ORACLE_HOME%/appsutil/template/custom/afinit_db121.ora
C:\> copy %ORACLE_HOME%/appsutil/template\afinit_db121RAC.ora %ORACLE_HOME%/appsutil/template/custom/afinit_db121RAC.ora
Edit the afinit_db121.ora and afinit_db121RAC.ora templates in the custom directory, by removing optimizer_adaptive_features=false and replacing it with optimizer_adaptive_plans=false.
UNIX:
$ sh <RDBMS_ORACLE_HOME>/appsutil/scripts/<CONTEXT_NAME>/adautocfg.sh
Apply adop phase=fs_clone
created this directory for 19c OH
/u01/install/APPS/19.3.0
Oracle Database Releases That Support Direct Upgrade Review
the supported options for direct upgrades to the latest Oracle Database
release. You can perform a direct upgrade to the new release from the following
releases: • 11.2.0.4 • 12.1.0.2 • 12.2.0.1 • 18 The path that you must take to
upgrade to the latest Oracle Database release depends on the release number of
your current database. If your current Oracle Database is a release earlier
than 11.2.0.4, then you cannot directly upgrade your Oracle Database to the
latest release. In this case, you are required to upgrade to an intermediate
release before upgrading to Oracle Database 19c. If you cannot carry out a
direct upgrade, then carry out an upgrade to the most recent release where
direct upgrades are supported.
Check the compataibility parameter of the existing database.
It should be set to min 11.2.0
In our database:-
SQL> SELECT name, value FROM v$parameter
WHERE name
= 'compatible'; 2
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
compatible
12.1.0
Create a new environment file for 19c
[oracle@apps ~]$ cat cdb_19c.env
export ORACLE_HOME=/u01/install/APPS/19.3.0
export ORACLE_SID=ebscdb
export
PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/perl/bin
export ORACLE_BASE=/u01/install/APPS
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=/u02/oratest/app/oracle/19.3/nls/data/9idata
[oracle@apps ~]$
Apply additional 19c RDBMS patches
Apply the latest Release Update for your platform documented in Table 1.6.1 or Table 1.7 of Section 3.3 of My Oracle Support Knowledge Document 1594274.1, Oracle E-Business Suite Release 12.2: Consolidated List of Patches and Technology Bug Fixes.
Create the nls/data/9idata directory
In the 19c Oracle Home
[oracle@apps bin]$ cd /u01/install/APPS/19.1.3/nls/data/old
[oracle@apps old]$ $ORACLE_HOME/perl/bin/perl cr9idata.pl
Creating directory /u01/install/APPS/19.1.3/nls/data/9idata ...
Copying files to /u01/install/APPS/19.1.3/nls/data/9idata...
Copy finished.
Please reset environment variable ORA_NLS10 to /u01/install/APPS/19.1.3/nls/data/9idata!
Create appsutil.zip and copy it to the database tier for new 19c home.
[oracle@apps ~]$ cd $AD_TOP/bin
[oracle@apps bin]$ perl admkappsutil.pl
Starting the generation of appsutil.zip
Log file located at /u01/install/APPS/fs1/inst/apps/ebsdb_apps/admin/log/MakeAppsUtil_12031012.log
output located at /u01/install/APPS/fs1/inst/apps/ebsdb_apps/admin/out/appsutil.zip
MakeAppsUtil completed successfully.
[oracle@apps bin]$ cp /u01/install/APPS/fs1/inst/apps/ebsdb_apps/admin/out/appsutil.zip /u01/install/APPS/19.3.0
[oracle@apps bin]$
Install JRE 8
[oracle@apps ~]$ cd $ORACLE_HOME/appsutil
[oracle@apps appsutil]$ cp -r $ORACLE_HOME/jdk/jre .
[oracle@apps appsutil]$ cp $ORACLE_HOME/jlib/orai18n.jar $ORACLE_HOME/appsutil/jre/lib/ext
[oracle@apps appsutil]$
Create the CDB
Some considerations:-
SID for CDB will be different than the present 12c SID. The 12c SID will become the 19c PDB and hence all the data will be migrated to 19c PDB.
run DBCA from 19c Oracle HOME
Choose the NLS characterset properly and it should match the source 12c NLS characterset.
Create the CDB MGDSYS schema
[oracle@apps admin]$ sqlplus /
as sysdba
SQL*Plus: Release 19.0.0.0.0 -
Production on Fri Dec 4 09:23:35 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019,
Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise
Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> @catmgd.sql;
Session altered.
Session altered.
.. Creating MGDSYS schema
User created.
.. lock the user and expire
the password
User altered.
.. Granting permissions to
MGDSYS
Grant succeeded.
Grant succeeded.
Grant succeeded.
Call completed.
Call completed.
Call completed.
Call completed.
Call completed.
Call completed.
Session altered.
Session altered.
Call completed.
.. Load java components for
tag translation
Session altered.
Call completed.
Session altered.
.. Check whether java has been
loaded successfully
OWNER
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
STATUS
-------
PUBLIC
oracle/mgd/idcode/IDCodeTranslator
VALID
MGDSYS
oracle/mgd/idcode/IDCodeTranslator
VALID
OWNER
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
STATUS
-------
.. Creating Oracle IDCode Types
Session altered.
Type created.
No errors.
Type created.
No errors.
.. the MGD_ID type object
Type created.
No errors.
Type created.
No errors.
Type created.
No errors.
Session altered.
.. Creating Oracle IDCode
Dictionary Tables
Session altered.
.. Creating
mgd_id_xml_validator table
Table created.
Comment created.
Comment created.
.. Creating the
mgd_id_category_tab table
Table created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
.. Creating mgd_id_scheme_tab
table
Table created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
.. Creating
mgd_id_lookup_table table
Table created.
Comment created.
Comment created.
Comment created.
Comment created.
Sequence created.
Session altered.
.. Creating Oracle IDCode views
Session altered.
.. Creating the mgd_id_category
views
View created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
View created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
.. Creating the mgd_id_scheme
views
View created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
View created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Session altered.
.. Creating Oracle IDCode
Utility Package Specification in MGDSYS
Session altered.
Package created.
No errors.
Package created.
Session altered.
.. Creating Oracle IDCode
Internal Utility Package Specification in MGDSYS
Session altered.
Package created.
No errors.
Package created.
Session altered.
.. Creating Oracle IDCode Type
Body in MGDSYS
Session altered.
Type body created.
No errors.
Type body created.
Session altered.
.. Creating Oracle IDCode
Utility Package Body in MGDSYS
Session altered.
Package body created.
No errors.
Package body created.
Session altered.
.. Creating Oracle IDCode Internal
Utility Package Body in MGDSYS
Session altered.
Package body created.
No errors.
Package body created.
Session altered.
.. Creating Oracle IDCode
triggers
Session altered.
.. Creating the user_mgd_id_scheme_ins_trig
trigger for inserting into user_mgd_id_category view
Trigger created.
No errors.
.. Creating the
user_mgd_id_category_del_trig trigger for deleting from user_mgd_id_category
view
Trigger created.
No errors.
.. Creating the user_mgd_id_category_upd_trig
trigger for updating user_mgd_id_category view
Trigger created.
No errors.
.. Creating the
idcode_scheme_before_ins_trig trigger which validates each TDT before insertion
and sets the type_name and encoding fields appropriately
Trigger created.
No errors.
.. Creating the idcode_scheme_before_upd_trig
trigger which validates the new scheme after update and refreshes the category
to which it belongs
Trigger created.
No errors.
.. Creating the user_mgd_id_scheme_ins_trig
trigger for inserting into user_mgd_id_scheme view
Trigger created.
No errors.
.. Creating the
user_mgd_id_scheme_del_trig trigger for deleting from user_mgd_id_scheme view
Trigger created.
No errors.
.. Creating the user_mgd_id_scheme_upd_trig
trigger for updating user_mgd_id_scheme view
Trigger created.
No errors.
.. Creating the
mgd_id_lookup_table_ins_trig trigger for inserting into mgd_id_lookup_table
Trigger created.
No errors.
.. Creating the mgd_id_lookup_table_upd_trig
trigger for updating mgd_id_lookup_table
Trigger created.
No errors.
Session altered.
.. Creating Public Synonyms
Session altered.
.. Creating Oracle IDCode
Privileges for Types and Packages
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
.. Creating Oracle IDCode
Public Synonymns
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Synonym created.
Procedure created.
No errors.
Procedure created.
Session altered.
.. Load metadata
Session altered.
PL/SQL procedure successfully
completed.
No errors.
PL/SQL procedure successfully
completed.
No errors.
PL/SQL procedure successfully
completed.
No errors.
Make sure these values look
OK:
Call completed.
XML_VALIDATOR_CHAR_LENGTH
-------------------------
5780
URL
----------------------------------------------------------------
DBMS_LOB.GETLENGTH(CONTENT) L
--------------------------- -
http://www.onsepc.com/managertranslation.xml
9732 Y
CATEGORY_N CATEGORY_ID
---------- -----------
EPC 1
CATEGORY_ID TYPE_NAME ENCODINGS XML_TDTS_CHAR_LENGTH
----------- ----------
---------------------- --------------------
1 GIAI-64 LEGACY,BINARY,PURE_IDE 14995
NTITY,TAG_ENCODING
1 GIAI-96 LEGACY,BINARY,PURE_IDE 14486
NTITY,TAG_ENCODING
1 GID-96
LEGACY,BINARY,PURE_IDE
2686
NTITY,TAG_ENCODING
1 GRAI-64 LEGACY,BINARY,PURE_IDE 18019
NTITY,TAG_ENCODING
CATEGORY_ID TYPE_NAME ENCODINGS XML_TDTS_CHAR_LENGTH
----------- ----------
---------------------- --------------------
1 GRAI-96 LEGACY,BINARY,PURE_IDE 17590
NTITY,TAG_ENCODING
1 SGLN-64 LEGACY,BINARY,PURE_IDE 19454
NTITY,TAG_ENCODING
1 SGLN-96 LEGACY,BINARY,PURE_IDE 18995
NTITY,TAG_ENCODING
1 SGTIN-64 ONS_HOSTNAME,LEGACY,BI 22884
CATEGORY_ID TYPE_NAME ENCODINGS XML_TDTS_CHAR_LENGTH
----------- ----------
---------------------- --------------------
NARY,PURE_IDENTITY,TAG
_ENCODING
1 SGTIN-96
ONS_HOSTNAME,LEGACY,BI
22531
NARY,PURE_IDENTITY,TAG
_ENCODING
1 SSCC-64 LEGACY,BINARY,PURE_IDE 16644
NTITY,TAG_ENCODING
1 SSCC-96 LEGACY,BINARY,PURE_IDE 16450
CATEGORY_ID TYPE_NAME ENCODINGS XML_TDTS_CHAR_LENGTH
----------- ----------
---------------------- --------------------
NTITY,TAG_ENCODING
1 USDOD-64 LEGACY,BINARY,PURE_IDE 2273
NTITY,TAG_ENCODING
1 USDOD-96 LEGACY,BINARY,PURE_IDE 2293
NTITY,TAG_ENCODING
13 rows selected.
Session altered.
Session altered.
.. lock the user and expire
the password
User altered.
.. Validate MGD installation
MGD is valid
PL/SQL procedure successfully
completed.
Session altered.
SQL>
Create the CDB TNS files
[oracle@apps ~]$ cd
$ORACLE_HOME/appsutil
[oracle@apps appsutil]$ echo
$ORACLE_HOME
/u01/install/APPS/19.3.0
[oracle@apps appsutil]$ . ./txkSetCfgCDB.env
dboraclehome=/u01/install/APPS/19.3.0
Oracle Home being passed:
/u01/install/APPS/19.3.0
[oracle@apps log]$ cd
$ORACLE_HOME/appsutil/bin
[oracle@apps bin]$ sqlplus /
as sysdba
SQL*Plus: Release 19.0.0.0.0 -
Production on Fri Dec 4 09:42:10 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019,
Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise
Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select name from
v$database;
NAME
---------
EBSDBCDB
SQL> exit
Disconnected from Oracle
Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@apps bin]$ perl
txkGenCDBTnsAdmin.pl -dboraclehome=/u01/install/APPS/19.1.3 -cdbname=ebscdb
-cdbsid=ebscdb -dbport=1521 -outdir=/u01/install/APPS/19.1.3/appsutil/log
Script Name : txkGenCDBTnsAdmin.pl
Script Version :
120.0.12020000.9
Started : Fri Dec 4 09:42:45 GMT 2020
Log File : /u01/install/APPS/19.3.0/appsutil/log/TXK_CDB_TNS_ADMIN_Fri_Dec_4_09_42_45_2020/txkGenCDBTnsAdmin.log
-----------
Values used
-----------
Database Oracle Home : /u01/install/APPS/19.1.3
CDB NAME : ebscdb
CDB SID : ebscdb
Database port : 1521
OUT Directory : /u01/install/APPS/19.1.3/appsutil/log
Is RAC? : No
Virtual Hostname :
=========================
Validating oracle home...
=========================
Oracle Home: /u01/install/APPS/19.3.0
exists.
===========================
Validating out directory...
===========================
Out directory:
/u01/install/APPS/19.3.0/appsutil/log exists.
============================
Inside getDBHostDetails()...
============================
DB Hostname : apps
DB Domain : example.com
=====================
Inside getDBPort()...
=====================
DB Port passed as an argument,
using the same.
DB Port: 1521
======================================
Inside
generateCDBTNSAdminContent()...
======================================
Creating the directory:
/u01/install/APPS/19.3.0/appsutil/log/TXK_CDB_TNS_ADMIN_Fri_Dec_4_09_42_45_2020/tns_admin_cdb_bkp
Creating the directory:
/u01/install/APPS/19.3.0/appsutil/log/TXK_CDB_TNS_ADMIN_Fri_Dec_4_09_42_45_2020/tns_admin_cdb_temp
listener_template:
listener_ora_cdb_db19.tmp
sqlnet_template:
sqlnet_ora_cdb_db19.tmp
Copying the file
----------------
SOURCE : /u01/install/APPS/19.3.0/appsutil/template/listener_ora_cdb_db19.tmp
TARGET : /u01/install/APPS/19.3.0/appsutil/log/TXK_CDB_TNS_ADMIN_Fri_Dec_4_09_42_45_2020/tns_admin_cdb_temp/listener.ora
===================================
Inside
replaceContextVariables()...
===================================
File
/u01/install/APPS/19.3.0/network/admin/listener.ora does not exist.
Copying the file
----------------
SOURCE :
/u01/install/APPS/19.3.0/appsutil/log/TXK_CDB_TNS_ADMIN_Fri_Dec_4_09_42_45_2020/tns_admin_cdb_temp/listener.ora
TARGET :
/u01/install/APPS/19.3.0/network/admin/listener.ora
Copying the file
----------------
SOURCE :
/u01/install/APPS/19.3.0/appsutil/template/tnsnames_ora_cdb_db121.tmp
TARGET :
/u01/install/APPS/19.3.0/appsutil/log/TXK_CDB_TNS_ADMIN_Fri_Dec_4_09_42_45_2020/tns_admin_cdb_temp/tnsnames.ora
===================================
Inside
replaceContextVariables()...
===================================
File
/u01/install/APPS/19.3.0/network/admin/tnsnames.ora does not exist.
Copying the file
----------------
SOURCE :
/u01/install/APPS/19.3.0/appsutil/log/TXK_CDB_TNS_ADMIN_Fri_Dec_4_09_42_45_2020/tns_admin_cdb_temp/tnsnames.ora
TARGET :
/u01/install/APPS/19.3.0/network/admin/tnsnames.ora
Copying the file
----------------
SOURCE :
/u01/install/APPS/19.3.0/appsutil/template/sqlnet_ora_cdb_db19.tmp
TARGET :
/u01/install/APPS/19.3.0/appsutil/log/TXK_CDB_TNS_ADMIN_Fri_Dec_4_09_42_45_2020/tns_admin_cdb_temp/sqlnet.ora
===================================
Inside
replaceContextVariables()...
===================================
File /u01/install/APPS/19.3.0/network/admin/sqlnet.ora
does not exist.
Copying the file
----------------
SOURCE :
/u01/install/APPS/19.3.0/appsutil/log/TXK_CDB_TNS_ADMIN_Fri_Dec_4_09_42_45_2020/tns_admin_cdb_temp/sqlnet.ora
TARGET : /u01/install/APPS/19.3.0/network/admin/sqlnet.ora
==============================
Inside updateCDBSqlNetOra()...
==============================
Updating the IFILE entry...
Exiting from the script.
Ended: Fri Dec 4 09:42:45 GMT 2020
[oracle@apps bin]$
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;
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
This completes the database steps needed before you start the database upgrade process which is continued in the next post https://samappsdba.blogspot.com/2020/12/upgrade-EBS-12.2-database-12c12.1.0.2-to-19c19.3-Part-2.html
