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

  This post is continuation of the fifth part of the post : Part V

Apply datapatch

 

[oraprod@non-prod-db ~]$ 19cdb_env [oraprod@non-prod-db 19.3.0]$ export ORACLE_SID=EBS [oraprod@non-prod-db 19.3.0]$ sqlplus / as sysdba SQL> select open_mode from v$database;   OPEN_MODE -------------------- READ WRITE   SQL> select description, action, to_char(action_time,'DD/MM/RR HH24:MI:SS') action_date, ' ' version from dba_registry_sqlpatch;   DESCRIPTION                                                                                          ACTION          ACTION_DATE       V ---------------------------------------------------------------------------------------------------- --------------- ----------------- - Database Release Update : 19.27.0.0.250415 (37642901)                                                APPLY           02/06/25 19:10:06 SQL>exit   [oraprod@non-prod-db ~]$ datapatch -verbose SQL Patching tool version 19.27.0.0.0 Production on Tue Jun  3 11:30:57 2025 Copyright (c) 2012, 2025, Oracle.  All rights reserved.   Log file for this invocation: /dbdata/erp/cfgtoollogs/sqlpatch/sqlpatch_27077_2025_06_03_11_30_57/sqlpatch_invocation.log   Connecting to database...OK Gathering database info...done 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   SQL registry:     Applied 19.27.0.0.0 Release_Update 250406131139 successfully on 02-JUN-25 07.10.06.159334 PM   Adding patches to installation queue and performing prereq checks...done Installation queue:   No interim patches need to be rolled back   No release update patches need to be installed   No interim patches need to be applied

Change compatible parameter.Before changing we need to drop the restore points.

SQL> drop restore point pre_upgrade;  Restore point dropped.  SQL> alter system set compatible='19.0.0' scope=spfile;  System altered.  SQL>create pfile from spfile; SQL> alter system set SEC_CASE_SENSITIVE_LOGON=false scope=both;  System altered.  SQL> alter trigger SYSTEM.EBS_LOGON compile;  Trigger altered.  SQL> shu immediate  SQL> startup  SQL> show parameter compatible  NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ compatible                           string      19.0.0 noncdb_compatible                    boolean     FALSE SQL>

Connect to the database as SYSDBA and run the following scripts:

 

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

 

 

SQL> @/dbdata/erp/cfgtoollogs/EBS/preupgrade/postupgrade_fixups.sql  Session altered. PL/SQL procedure successfully completed.  PL/SQL procedure successfully completed.  PL/SQL procedure successfully completed.  Package created. No errors. Package body created.  PL/SQL procedure successfully completed.  No errors. Package created. No errors. Package body created. No errors. Executing Oracle POST-Upgrade Fixup Script   Auto-Generated by:       Oracle Preupgrade Script                          Version: 19.0.0.0.0 Build: 13 Generated on:            2025-06-02 18:17:17   For Source Database:     TEST Source Database Version: 12.1.0.2.0 For Upgrade to Version:  19.0.0.0.0   Preup                             Preupgrade Action                            Issue Is Number  Preupgrade Check Name     Remedied    Further DBA Action ------  ------------------------  ----------  --------------------------------    12.  depend_usr_tables         YES         None.    13.  old_time_zones_exist      YES         None.    14.  dir_symlinks              NO          Manual fixup recommended.    15.  post_dictionary           YES         None.    16.  post_fixed_objects        NO          Informational only.                                               Further action is optional.    17.  upg_by_std_upgrd          YES         None.   The fixup scripts have been run and resolved what they can. However, there are still issues originally identified by the preupgrade that have not been remedied and are still present in the database. Depending on the severity of the specific issue, and the nature of the issue itself, that could mean that your database upgrade is not fully complete.  To resolve the outstanding issues, start by reviewing the postupgrade_fixups.sql and searching it for the name of the failed CHECK NAME or Preupgrade Action Number listed above. There you will find the original corresponding diagnostic message from the preupgrade which explains in more detail what still needs to be done. PL/SQL procedure successfully completed.  Session altered.

Solution for above findings:-

Point no 14. dir_symlinks              NO          Manual fixup recommended.   SQL> @$ORACLE_HOME/rdbms/admin/utldirsymlink.sql The following DIRECTORY OBJECTS contain symlinks: 'EBS_APPLPTMP' 'EBS_INBOUND' 'EBS_LOG' 'EBS_OUTBOUND' 'EBS_TEMP' 'ECX_UTL_LOG_DIR_OBJ' 'ECX_UTL_XSLT_DIR_OBJ' 'ODPDIR' 8 DIRECTORY OBJECTS with symlinks found.   PL/SQL procedure successfully completed.   Solution:- Drop the above mentioned directory from database as those directories are pointed to a physical location which is a symbolic link.   Create the directories first.   SQL>select directory_name, directory_path from dba_directories where directory_name in    ('EBS_APPLPTMP', 'EBS_INBOUND', 'EBS_LOG', 'EBS_OUTBOUND', 'EBS_TEMP', 'ECX_UTL_LOG_DIR_OBJ', 'ECX_UTL_XSLT_DIR_OBJ', 'ODPDIR')  ;   DIRECTORY_NAME                 DIRECTORY_PATH ------------------------------ ------------------------- EBS_APPLPTMP                   /usr/tmp EBS_OUTBOUND                   /usr/tmp EBS_INBOUND                    /usr/tmp EBS_LOG                        /usr/tmp EBS_TEMP                       /usr/tmp ODPDIR                         /usr/tmp ECX_UTL_XSLT_DIR_OBJ           /usr/tmp ECX_UTL_LOG_DIR_OBJ            /usr/tmp   8 rows selected.   We need to recreate the directory with a new physical path that should not be a symbolic link   Drop directory EBS_APPLPTMP; Drop directory EBS_INBOUND; Drop directory  EBS_LOG; Drop directory EBS_OUTBOUND; Drop directory EBS_TEMP; Drop directory ECX_UTL_LOG_DIR_OBJ; Drop directory ECX_UTL_XSLT_DIR_OBJ; Drop directory ODPDIR;   SQL> create directory EBS_APPLPTMP as '/dbdata/erp/temp/EBS/'; SQL> create directory EBS_INBOUND  as '/dbdata/erp/temp/EBS/'; SQL> create directory EBS_LOG  as '/dbdata/erp/temp/EBS/'; SQL> create directory EBS_OUTBOUND as '/dbdata/erp/temp/EBS/'; SQL> create directory EBS_TEMP as '/dbdata/erp/temp/EBS/'; SQL> create directory ECX_UTL_LOG_DIR_OBJ as '/dbdata/erp/temp/EBS/'; SQL> create directory ECX_UTL_XSLT_DIR_OBJ as '/dbdata/erp/temp/EBS/'; SQL> create directory ODPDIR as '/dbdata/erp/temp/EBS/';   For Point No.15 .  post_dictionary           YES         None.   Gather dictionary statistics after the upgrade using the   command(This can be done after entire upgrade activity) sqlplus / as sysdba SQL>EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; For Point No.16   post_fixed_objects        NO          Informational only. SQL>EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

Natively compile PL/SQL code (Optional)

 

In our case we didn’t have any package or procedure that was compiled natively. So no conversion was required. To confirm how the object was compile, run the following query.

SQL> set lines 222 pagesize 300 SQL> col PLSQL_CODE_TYPE for a25 SQL> SELECT TYPE, PLSQL_CODE_TYPE, COUNT(*) FROM DBA_PLSQL_OBJECT_SETTINGS WHERE PLSQL_CODE_TYPE IS NOT NULL GROUP BY TYPE, PLSQL_CODE_TYPE ORDER BY TYPE, PLSQL_CODE_TYPE;   TYPE         PLSQL_CODE_TYPE             COUNT(*) ------------ ------------------------- ---------- FUNCTION     INTERPRETED                     3034 LIBRARY      INTERPRETED                      249 PACKAGE      INTERPRETED                    41691 PACKAGE BODY INTERPRETED                    40585 PROCEDURE    INTERPRETED                      385 TRIGGER      INTERPRETED                     5895 TYPE         INTERPRETED                     5074 TYPE BODY    INTERPRETED                      424   8 rows selected.

Run ADgrants

 

First Check Database Privileges

Login to database server 19c home and create admin folder inside appsutil directory

Technically we should copy adgrants.sql from application node $APPL_TOP/admin. But the version of adgrants.sql in database server was same. So we will copy from db server only

[oraprod@non-prod-db appsutil]$ mkdir -p $ORACLE_HOME/appsutil/admin   cp adgrants.sql from 12c $ORACLE_HOME/appsutil/admin folder to 19c  $ORACLE_HOME/appsutil/admin   [oraprod@non-prod-db admin]$ pwd /dbdata/erp/12.1.0/appsutil/admin   [oraprod@non-prod-db admin]$ cp adgrants.sql /dbdata/erp/19.3.0/appsutil/admin [oraprod@testdb admin]$ cd /dbdata/erp/19.3.0/appsutil/admin   [oraprod@non-prod-db ~]$ 19cdb_env [oraprod@non-prod-db ~]$ export ORACLE_SID=EBS [oraprod@non-prod-db ~]$ sqlplus / as sysdba     SQL> alter system set "_disable_actualization_for_grant" = true scope=both; SQL> @/dbdata/erp/19.3.0/appsutil/admin/adgrants.sql apps   Verify if any grants are missing. The run of adgrants.sql will fail if any grants are missing and print a message to use ADZDSHOWLOG.sql to review the missing grants.  You can run $AD_TOP/sql/ADZDSHOWLOG.sql to produce a report.**

Compile invalid objects

[oraprod@non-prod-db ~]$ sqlplus / as sysdba SQL> @?/rdbms/admin/utlrp   SQL> select count(*) from dba_objects where status='INVALID';     COUNT(*) ----------         35

Grant datastore access

Use SQL*Plus to connect to the database as SYSDBA and run the following command:

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

 

 

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:

 

Login to application server [appltest@testapp ~]$ . /apdata/erp/EBSapps.env run [appltest@testapp ~]$ cd $APPL_TOP/admin [appltest@testapp ]$ scp adstats.sql oraprod@172.15.20.115:/home/oraprod Login to DB server   [oraprod@non-prod-db ~]$ 19cdb_env [oraprod@non-prod-db ~]$ export ORACLE_SID=EBS   [oraprod@non-prod-db ~]$ sqlplus / as sysdba   SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 3 17:16:12 2025 Version 19.27.0.0.0   Copyright (c) 1982, 2024, Oracle.  All rights reserved.     Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.27.0.0.0   SQL> alter system enable restricted session;   System altered.   SQL> @/home/oraprod/adstats.sql Connected.     -------------------------------------------------- --- adstats.sql started at 2025-06-03 17:16:59 ---     Checking for the DB version and collecting statistics ...     PL/SQL procedure successfully completed.       ------------------------------------------------ --- adstats.sql ended at 2025-06-03 17:19:49 ---     Commit complete.   Rember to disable the restricted session. SQL> alter system disable restricted session;   System altered.

Convert Database to Multitenant Architecture

 

Currently ,There are two databases that are associated with the 19c Oracle home, the CDB and the non-CDB database. Moreover, the non-CDB database will be migrated to the PDB database. Set the appropriate environment variables to connect to the appropriate database.

 

• Only the txkSetCfgCDB.env/cmd file in the $ORACLE_HOME/appsutil directory is necessary. It sets certain environment variables. However, it does not distinguish between the different databases in the 19c Oracle home. It is used mainly to complete the steps in this subsection. The following steps assume that a new window is brought up every time. However, there is no need to run txkSetCfgCDB.env/cmd twice in the same window. So, you may skip running the txkSetCfgCDB.env/cmd script if it has already been run on the particular terminal you are using.
• After running the txkPostPDBCreationTasks.pl, additional environment files are created so that connecting to the database is more convenient. Load the proper environment variables and connect to the database by performing the following steps:
• For the non-CDB database, source the $ORACLE_HOME/<non-CDB SID>_<HOST>.env/cmd file. Then, run sqlplus <user>/<password>@<non-CDB SID>. (The environment file was created during the earlier upgrade steps)
• For the CDB database, source the $ORACLE_HOME/<CDB SID>_<HOST>.env/cmd file. Then, run sqlplus <user>/<password> or connect as SYSDBA.
• For the PDB database on UNIX/Linux platforms, to connect as SYSDBA, source the $ORACLE_HOME/<CDB SID>_<HOST>.env file. Set the ORACLE_PDB_SID environment variable to <PDB SID>. Then, connect as SYSDBA.
• For the PDB database on Windows platforms, to connect as SYSDBA, source the $ORACLE_HOME/<PDB SID>_<HOST>.cmd file. Then, connect as SYSDBA.

• For the PDB database, to connect to other users, source the $ORACLE_HOME/<PDB SID>_<HOST>.env/cmd file. Then, run sqlplus <user>/<password>@<PDB SID>.

a)     Create the PDB descriptor

Invoke 19c env   [oraprod@non-prod-db ~]$ 19cdb_env 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 19c ORACLE_HOME> ·         $ export ORACLE_SID=<source SID> ·         $ cd $ORACLE_HOME/appsutil/bin ·         $ perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=<ORACLE_HOME> -outdir=<ORACLE_HOME>/appsutil/log -appsuser=<apps user> -dbsid=<source SID>   [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 [oraprod@non-prod-db appsutil]$ export ORACLE_SID=EBS [oraprod@non-prod-db appsutil]$ cd $ORACLE_HOME/appsutil/bin   [oraprod@non-prod-db bin]$ perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=/dbdata/erp/19.3.0 -outdir=/dbdata/erp/19.3.0/appsutil/log -appsuser=apps -dbsid=EBS Enter the APPS Password:   Script Name    : txkOnPremPrePDBCreationTasks.pl Script Version : 120.0.12020000.17 Started        : Wed Jun  4 10:00:35 IST 2025   Log File       : /dbdata/erp/19.3.0/appsutil/log/TXK_PRE_PDB_Wed_Jun_4_10_00_32_2025/txkOnPremPrePDBCreationTasks.log     ----------- Values used ----------- DB Oracle Home        : /dbdata/erp/19.3.0 OUT Directory         : /dbdata/erp/19.3.0/appsutil/log Skip DB shutdown      : No EBS SID               : TEST APPS Schema Username  : apps Is RAC?               : No Logical Hostname      :         ========================= Validating oracle home... ========================= Oracle Home: /dbdata/erp/19.3.0 exists.     =========================== Validating out directory... =========================== Out directory: /dbdata/erp/19.3.0/appsutil/log exists.     ============================ Inside getDBHostDetails()... ============================ DB Hostname : non-prod-db DB Domain   : soumya.com Logical hostname is not passed, hence using physical hostname details. Logical hostname: non-prod-db.soumya.com     ========================== Inside setContextName()... ========================== CONTEXT_NAME: TEST_non-prod-db     ============================ Inside setFileLocations()... ============================     ===================== Inside getDBName()... ===================== Generating SQL file : /dbdata/erp/19.3.0/appsutil/log/TXK_PRE_PDB_Wed_Jun_4_10_00_32_2025/get_db_name.sql SQL output file     : /dbdata/erp/19.3.0/appsutil/log/TXK_PRE_PDB_Wed_Jun_4_10_00_32_2025/get_db_name.out Spool File          : /dbdata/erp/19.3.0/appsutil/log/TXK_PRE_PDB_Wed_Jun_4_10_00_32_2025/spool_get_db_name.log ========================== Inside executeSQLFile()... ========================== Executing the SQL...   Execute SYSTEM command : sqlplus -s /nolog @/dbdata/erp/19.3.0/appsutil/log/TXK_PRE_PDB_Wed_Jun_4_10_00_32_2025/get_db_name.sql     ============================== Inside searchFileContents()... ============================== log_file: /dbdata/erp/19.3.0/appsutil/log/TXK_PRE_PDB_Wed_Jun_4_10_00_32_2025/get_db_name.out pattern: ERROR ============================= Could not find the pattern... ============================= Removing the file: /dbdata/erp/19.3.0/appsutil/log/TXK_PRE_PDB_Wed_Jun_4_10_00_32_2025/get_db_name.sql   EXIT STATUS: 0 Getting the value of DB Name... db_name: TEST   PDB_DESC_XML: /dbdata/erp/19.3.0/dbs/TEST_PDBDesc.xml INITPARAM_FILE: /dbdata/erp/19.3.0/dbs/TEST_initparam.sql DATATOP_FILE: /dbdata/erp/19.3.0/dbs/TEST_datatop.txt     ======================== Inside getDBVersion()... ======================== DB_VERSION = db190       ============================= Shutting down the database... ============================= Shutdown mode       : IMMEDIATE Generating SQL file : /dbdata/erp/19.3.0/appsutil/log/TXK_PRE_PDB_Wed_Jun_4_10_00_32_2025/shutdown_IMMEDIATE.sql SQL output file     : /dbdata/erp/19.3.0/appsutil/log/TXK_PRE_PDB_Wed_Jun_4_10_00_32_2025/shutdown_IMMEDIATE.out ========================== Inside executeSQLFile()... ========================== Executing the SQL...   Execute SYSTEM command : sqlplus -s /nolog @/dbdata/erp/19.3.0/appsutil/log/TXK_PRE_PDB_Wed_Jun_4_10_00_32_2025/shutdown_IMMEDIATE.sql     ============================== Inside searchFileContents()... ============================== log_file: /dbdata/erp/19.3.0/appsutil/log/TXK_PRE_PDB_Wed_Jun_4_10_00_32_2025/shutdown_IMMEDIATE.out pattern: ERROR ============================= Could not find the pattern... ============================= EXIT STATUS: 0 SQL execution went through successfully. LOG FILE: /dbdata/erp/19.3.0/appsutil/log/TXK_PRE_PDB_Wed_Jun_4_10_00_32_2025/shutdown_IMMEDIATE.out.     ======================== Starting the database... ======================== Startup mode        : MOUNT Generating SQL file : /dbdata/erp/19.3.0/appsutil/log/TXK_PRE_PDB_Wed_Jun_4_10_00_32_2025/startup_MOUNT.sql SQL output file     : /dbdata/erp/19.3.0/appsutil/log/TXK_PRE_PDB_Wed_Jun_4_10_00_32_2025/startup_MOUNT.out ========================== Inside executeSQLFile()... ========================== Executing the SQL...   Execute SYSTEM command : sqlplus -s /nolog @/dbdata/erp/19.3.0/appsutil/log/TXK_PRE_PDB_Wed_Jun_4_10_00_32_2025/startup_MOUNT.sql     ============================== Inside searchFileContents()... ============================== log_file: /dbdata/erp/19.3.0/appsutil/log/TXK_PRE_PDB_Wed_Jun_4_10_00_32_2025/startup_MOUNT.out pattern: ERROR ============================= Could not find the pattern... ============================= EXIT STATUS: 0 SQL execution went through successfully. LOG FILE: /dbdata/erp/19.3.0/appsutil/log/TXK_PRE_PDB_Wed_Jun_4_10_00_32_2025/startup_MOUNT.out.     ================================== Inside createPDBDescriptorXML()... ==================================     ================================== Inside backupPDBDescriptorXML()... ==================================     File /dbdata/erp/19.3.0/dbs/TEST_PDBDesc.xml does not exist.     File /dbdata/erp/19.3.0/dbs/TEST_PDBDesc.xml does not exist.       Generating SQL file : /dbdata/erp/19.3.0/appsutil/log/TXK_PRE_PDB_Wed_Jun_4_10_00_32_2025/create_pdb_desc_xml.sql SQL output file     : /dbdata/erp/19.3.0/appsutil/log/TXK_PRE_PDB_Wed_Jun_4_10_00_32_2025/create_pdb_desc_xml.out ========================== Inside executeSQLFile()... ========================== Executing the SQL...   Execute SYSTEM command : sqlplus -s /nolog @/dbdata/erp/19.3.0/appsutil/log/TXK_PRE_PDB_Wed_Jun_4_10_00_32_2025/create_pdb_desc_xml.sql     ============================== Inside searchFileContents()... ============================== log_file: /dbdata/erp/19.3.0/appsutil/log/TXK_PRE_PDB_Wed_Jun_4_10_00_32_2025/create_pdb_desc_xml.out pattern: ERROR ============================= Could not find the pattern... ============================= EXIT STATUS: 0 PDB Descriptor XML /dbdata/erp/19.3.0/dbs/TEST_PDBDesc.xml created successfully. LOG FILE: /dbdata/erp/19.3.0/appsutil/log/TXK_PRE_PDB_Wed_Jun_4_10_00_32_2025/create_pdb_desc_xml.out.     ============================= Shutting down the database... ============================= Shutdown mode       : IMMEDIATE Generating SQL file : /dbdata/erp/19.3.0/appsutil/log/TXK_PRE_PDB_Wed_Jun_4_10_00_32_2025/shutdown_IMMEDIATE.sql SQL output file     : /dbdata/erp/19.3.0/appsutil/log/TXK_PRE_PDB_Wed_Jun_4_10_00_32_2025/shutdown_IMMEDIATE.out ========================== Inside executeSQLFile()... ========================== Executing the SQL...   Execute SYSTEM command : sqlplus -s /nolog @/dbdata/erp/19.3.0/appsutil/log/TXK_PRE_PDB_Wed_Jun_4_10_00_32_2025/shutdown_IMMEDIATE.sql     ============================== Inside searchFileContents()... ============================== log_file: /dbdata/erp/19.3.0/appsutil/log/TXK_PRE_PDB_Wed_Jun_4_10_00_32_2025/shutdown_IMMEDIATE.out pattern: ERROR ============================= Could not find the pattern... ============================= EXIT STATUS: 0 SQL execution went through successfully. LOG FILE: /dbdata/erp/19.3.0/appsutil/log/TXK_PRE_PDB_Wed_Jun_4_10_00_32_2025/shutdown_IMMEDIATE.out.         **************************************************************************************** *                                                                                      * *         IMPORTANT NOTE: DO NOT START THE DB TILL THE DATA FILES ARE MIGRATED         * *                                                                                      * *         IF DB IS STARTED, THEN PDB DESCRIPTOR XML GENERATED BECOMES INVALID          * *                                                                                      * ****************************************************************************************         Exiting from the script. Ended: Wed Jun  4 10:01:23 IST 2025   Check the logfiles carefully from the above . Make sure all output are good.   Note: 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.

Please follow the next part here :