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

  This post is continuation of the second part of the post : Part II

Applying the Latest AD and TXK Release Update Packs to Oracle E-Business Suite Release 12.2 (Doc ID 1617461.1)

Before applying the AD/TXK patches check following.

Login to DB, check for optimizer_adaptive_features

 parameter, the output should be false

SQL> show parameter optimizer_adaptive_features  NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ optimizer_adaptive_features          boolean     FALSE SQL> alter system set "_disable_actualization_for_grant" = true scope=both;

Apply AD 16 Patches(36119925 , 36989014, 36303698  ):-

Unzip  Patch 36989014 (R12.AD.C)

[oraprod@testdb 12.1.0]$ cd $ORACLE_HOME [oraprod@testdb 12.1.0]$ cp -r appsutil appsutil_bkp Create $ORACLE_HOME/appsutil/admin on database server if it doesn’t exist.   Note: we applied adgrants.sql from patch 36989014 while applied patch 36119925 because it had higher version of adgrants.sql   Copy the patches inside $PATCH_TOP [appltest@testapp scripts]$ cd /apdata/patch/ [appltest@testapp patch]$ cp -r 36989014 36119925 36303698  $PATCH_TOP    Compare version of adgrants.sql in $APPL_TOP/admin to version in patch directory. [oraprod@non-prod-db 36119925]$ cd /apdata/erp/fs_ne/EBSapps/patch/36989014/admin   [oraprod@non-prod-db admin]$ cat adgrants.sql | grep Header REM $Header: adgrants.sql 120.67.12020000.140 2024/08/29 16:00:07 rsatyava ship $  /* $Header: adgrants.sql 120.67.12020000.140 2024/08/29 16:00:07 rsatyava ship $ */  /* $Header: adgrants.sql 120.67.12020000.140 2024/08/29 16:00:07 rsatyava ship $ */  /* $Header: adgrants.sql 120.67.12020000.140 2024/08/29 16:00:07 rsatyava ship $ */  /* $Header: adgrants.sql 120.67.12020000.140 2024/08/29 16:00:07 rsatyava ship $ */    Copy whichever is the higher version of adgrants.sql to $ORACLE_HOME/appsutil/admin.   [oraprod@non-prod-db admin]$ cd $ORACLE_HOME/appsutil/admin [oraprod@non-prod-db admin]$ cat adgrants.sql | grep Header REM $Header: adgrants.sql 120.67.12020000.51 2017/02/22 21:26:05 jwsmith ship $ /* $Header: adgrants.sql 120.67.12020000.51 2017/02/22 21:26:05 jwsmith ship $ */ /* $Header: adgrants.sql 120.67.12020000.51 2017/02/22 21:26:05 jwsmith ship $ */ /* $Header: adgrants.sql 120.67.12020000.51 2017/02/22 21:26:05 jwsmith ship $ */ /* $Header: adgrants.sql 120.67.12020000.51 2017/02/22 21:26:05 jwsmith ship $ */ /* $Header: adgrants.sql 120.67.12020000.51 2017/02/22 21:26:05 jwsmith ship $ */ /* $Header: adgrants.sql 120.67.12020000.51 2017/02/22 21:26:05 jwsmith ship $ */   So copy adgrants.sql from patch top to $ORACLE_HOME/appsutil/admin [appltest@testapp admin]$ pwd /apdata/erp/fs_ne/EBSapps/patch/36989014/admin   [appltest@testapp admin]$ scp adgrants.sql oraprod@172.15.20.115:/dbdata/erp/12.1.0/appsutil/admin   Note: we applied adgrants.sql from patch 36989014 while applied patch 36119925 because it had higher version of adgrants.sql     Set environment to point to ORACLE_HOME on the database server.   Run the adgrants.sql script using SQL*Plus: [oraprod@testdb 12.1.0]$ . /dbdata/erp/12.1.0/EBS_ebsproddb.env UNIX: $ sqlplus / as sysdba   Note:-  Please disable PASSWORD_VERIFY_FUNCTION for DEFAULT profile . As adgrants.sql script creates an user EBS_SYSTEM. And during creation it throws error if the above function is not disabled.   SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION NULL;   SQL> @$ORACLE_HOME/appsutil/admin/adgrants.sql APPS     . When running the adgrants.sql script for the first time in an AD.Delta.13 or later environment, the EBS_SYSTEM user will be created and then need to be UNLOCKED using the following command      SQL> ALTER USER EBS_SYSTEM ACCOUNT UNLOCK;     The initial password for EBS_SYSTEM must the same as the SYSTEM password. To change the password, run the following command:    SQL> ALTER USER EBS_SYSTEM IDENTIFIED BY <PASSWORD>;   SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION ORA12C_STRONG_VERIFY_FUNCTION;   On application server, run following   [appltest@testapp patch]$ perl $PATCH_TOP/36119925/ad/bin/adValidateEbssystemSchema.pl   Enter the APPS password: Enter the SYSTEM password: Validating APPS credentials...validated successfully Validating SYSTEM credentials...validated successfully Validating EBS_SYSTEM user ebssys entity is not registered All required validations completed successfully

Open a new terminal and source the run edition file system environment file by running the appropriate command for your platform.

[appltest@testapp ~]$ . /apdata/erp/EBSapps.env run Start a new online patching cycle by running following command:-  [appltest@testapp ~]$ adop phase=prepare  ## Note:this phase will take less time if fs_clone was run previously.  Apply relevant AD patches as a comma-separated list of patches to this command. Add merge=yes to the end of your command, as shown here: adop phase=apply patches=36119925,<Your 1st critical AD patch>,<Your 2nd critical AD patch> merge=yes   Note: During patch apply we faced error, execute following grant as sys user to fix it SQL> grant all on SYS.DBA_OBJECTS_AE to apps with grant option; SQL> grant all on SYS.DBA_OBJECTS_AE to EBS_SYSTEM with grant option;   [appltest@testapp ~]$ adop phase=apply patches=36119925,36303698,36989014 merge=yes

Apply TXK 16 patch:-

Patch 36117775, 36641685, 37500697 Unzip above patches and copy them inside $PATCH_TOP [appltest@testapp patch]$ cd /apdata/patch/ [appltest@testapp patch]$ cp -r 36117775 36641685 37500697 $PATCH_TOP  [appltest@testapp ~]$ adop phase=apply patches=36117775,36641685,37500697 merge=yes

Execute Finalize , cutover and cleanup phase [appltest@testapp ~]$ . /apdata/erp/EBSapps.env run  [appltest@testapp ~]$ adop phase=finalize [appltest@testapp ~]$ . /apdata/erp/EBSapps.env run [appltest@testapp ~]$ adop phase=cutover [appltest@testapp ~]$ . /apdata/erp/EBSapps.env run [appltest@testapp ~]$ adop phase=cleanup cleanup_mode=full

Post patch installation steps:-

Run autoconfig on run file system in apps tier [appltest@testapp ~]$ . /apdata/erp/EBSapps.env run [appltest@testapp ~]$ cd $ADMIN_SCRIPTS_HOME  [appltest@testapp scripts]$ adstpall.sh  [appltest@testapp scripts]$ sh adautocfg.sh  [appltest@testapp ~]$ . $APPL_TOP/APPSEBS_ebsprodapp.env  [appltest@testapp ~]$ perl $AD_TOP/bin/admkappsutil.pl output located at /apdata/erp/fs1/inst/apps/EBS_ebsprodapp/admin/out/appsutil.zip  Copy appsutil.zip into $ORACLE_HOME of database server  Take backup of existing appsutil.zip folder in database server.  [oraprod@non-prod-db 12.1.0]$ cd $ORACLE_HOME [oraprod@non-prod-db 12.1.0]$ cp -r appsutil.zip appsutil_bak.zip  [appltest@testapp ~]$ scp /apdata/erp/fs1/inst/apps/EBS_ebsprodapp/admin/out/appsutil.zip oraprod@172.15.20.115:/dbdata/erp/12.1.0  Login to Database server  [oraprod@testdb 12.1.0]$ . /dbdata/erp/12.1.0/EBS_ebsproddb.env  [oraprod@non-prod-db 12.1.0]$ cd $ORACLE_HOME  [oraprod@non-prod-db 12.1.0]$ unzip -o appsutil.zip Run autoconfig on DB tier [oraprod@testdb TEST_testdb]$ cd /dbdata/erp/12.1.0/appsutil/scripts/EBS_ebsproddb [oraprod@testdb TEST_testdb]$ sh adautocfg.sh   Run adpreclone on DBtier  Login as root user and rename following file [root@testdb opc]# mv /etc/profile.d/modules.sh /etc/profile.d/modules.sh.bkup   [root@testdb opc]# su - oraprod [oraprod@non-prod-db 12.1.0]$ cd $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME  [oraprod@non-prod-db TEST_non-prod-db]$ perl adpreclone.pl dbTier   Run adpreclone on Appstier  [appltest@testapp ~]$ . /apdata/erp/EBSapps.env run  [appltest@testapp scripts]$ $ADMIN_SCRIPTS_HOME/adadminsrvctl.sh start [appltest@testapp ~]$ cd $INST_TOP/admin/scripts  [appltest@testapp scripts]$ perl adpreclone.pl appsTier

Run Autoconfig on DB & Apps tier

Login to DB server [oraprod@non-prod-db]$ cd $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME  [oraprod@non-prod-db TEST_non-prod-db]$ sh adautocfg.sh  Login to apps server [appltest@testapp scripts]$ cd $ADMIN_SCRIPTS_HOME [appltest@testapp scripts]$ sh adautocfg.sh

Run fs_clone to sync patch file system

[appltest@testapp ~]$ . /apdata/erp/EBSapps.env run  [appltest@testapp ~]$ adop phase=fs_clone  [Took around 35mins to complete]  [appltest@testapp ~]$ sqlplus apps @$AD_TOP/sql/ADZDDBCC.sql  This utility reports any violations of the Online Patching Development Standards. Run following as apps user.  SQL>exec ad_zd_table.upgrade('APPLSYS','FND_EID_ATTR_GROUPS_ZD'); SQL>exec ad_zd_table.upgrade('APPLSYS','FND_EID_DDR_MGD_ATT_VALS_ZD'); SQL>exec ad_zd_table.upgrade('APPLSYS','FND_EID_GROUPS_ZD_B'); SQL>exec ad_zd_table.upgrade('APPLSYS','FND_EID_GROUPS_ZD_TL'); SQL>exec ad_zd_table.upgrade('APPLSYS','FND_EID_INSTANCES_ZD'); SQL>exec ad_zd_table.upgrade('APPLSYS','FND_EID_MGD_AT_VAL_ADL_ZD'); SQL>exec ad_zd_table.upgrade('APPLSYS','FND_EID_PDR_ATTRS_ZD_B'); SQL>exec ad_zd_table.upgrade('APPLSYS','FND_EID_PDR_ATTRS_ZD_TL'); SQL>exec ad_zd_table.upgrade('APPLSYS','FND_EID_PDR_ATTR_PROFILES_ZD'); SQL>exec ad_zd_table.upgrade('APPLSYS','FND_EID_PRECEDENCE_RULES_ZD'); SQL>exec ad_zd_table.upgrade('APPLSYS','FND_EID_RECORD_TYPES_ZD');

Patch 23560508 , 31088182 , 26481190, 26521736

Unzip 23560508 and copy inside $PATCH_TOP [appltest@testapp scripts]$ cd /apdata/patch/ [appltest@testapp patch]$ cp -r 23560508 31088182 26481190 26521736 $PATCH_TOP  [appltest@testapp ~]$ . /apdata/erp/EBSapps.env run  [appltest@testapp ~]$ adop phase=prepare  [appltest@testapp patch]$ adop phase=apply patches=23560508,31088182,26481190,26521736  [appltest@testapp patch]$ adop phase=finalize [appltest@testapp patch]$ adop phase=cutover [appltest@testapp patch]$ adop phase=cleanup

Create the initialization parameter setup files

Login to DB server

Run the following commands to create the $ORACLE_HOME/dbs/<ORACLE_SID>_initparam.sql and $ORACLE_HOME/dbs/<ORACLE_SID>_datatop.txt files.

$ cd $ORACLE_HOME/appsutil $ . ./txkSetCfgCDB.env dboraclehome=/dbdata/erp/12.1.0 $ export ORACLE_SID=EBS $ cd $ORACLE_HOME/appsutil/bin [oraprod@non-prod-db bin]$ perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=/dbdata/erp/12.1.0 -outdir=/dbdata/erp/12.1.0/appsutil/log -appsuser=apps -dbsid=EBS -skipdbshutdown=yes

Install Oracle Database 19c

Download oracle 19c software and install binary only. This installation would be done on a new oracle home.

To get the idea of 19c installation follow this doc

https://dbasoumya.blogspot.com/2020/04/oracle-database-19c-installation-on.html

Only perform binary installation at this point.

After installation apply latest RU patch on top of 19c home

Update Opatch and apply patch

[oraprod@testdb ~]$ cd /dbdata/patch/

[oraprod@testdb patch]$ cp p6880880_190000_Linux-x86-64.zip /dbdata/erp/19.3.0

[oraprod@testdb 37642901]$ cd /dbdata/patch/37642901

[oraprod@non-prod-db 37642901]$ /dbdata/erp/19.3.0/OPatch/opatch apply

After installations, Set the environment variables accordingly which will point towards new 19c oracle home.

We created one env file 19cdb_env, with following entries  vi /home/oraprod/19cdb_env ORACLE_BASE=/dbdata/erp export ORACLE_BASE ORACLE_HOME=/dbdata/erp/19.3.0 export ORACLE_HOME PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$ORACLE_HOME/OPatch LD_LIBRARY_PATH=$ORACLE_HOME/lib;export LD_LIBRARY_PATH PERL5LIB=$ORACLE_HOME/perl/lib/5.38.2:$ORACLE_HOME/perl/lib/site_perl/5.38.2;export PERL5LIB ORA_NLS10=/dbdata/erp/19.3.0/nls/data/9idata ; export ORA_NLS10  :wq Add following line inside .bash_profile alias 19cdb_env='. /home/oraprod/19cdb_env' . .bash_profile 19cdb_env

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.

Set 19c env file. And run the following from 19c home

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

Important:-

If during 9idata directory creation , following error occurs

[ebsTEST@ccuine105 ~]$ perl $ORACLE_HOME/nls/data/old/cr9idata.pl   perl: symbol lookup error: /u01/EBSTEST/db/tech_st/19.3.0/perl/lib/5.28.1/x86_64-linux-thread-multi/auto/Tie/Hash/NamedCapture/NamedCapture.so: undefined symbol: Perl_xs_handshake

 

Solution:-

Confirm the shell environment PERL5LIB:  # env | fgrep PERL5LIB PERL5LIB=/u01/oracle/product/12.2.0/db/perl/lib/site_perl/5.22.0/x86_64-linux-thread-multi/   Remove the PERL5LIB setting: # unset PERL5LIB Verify: # env | grep PERL5LIB #  Now retry again.

 

[oraprod@non-prod-db ~]$ perl $ORACLE_HOME/nls/data/old/cr9idata.pl Creating directory /dbdata/erp/19.3.0/nls/data/9idata ... Copying files to /dbdata/erp/19.3.0/nls/data/9idata... Copy finished. Please reset environment variable ORA_NLS10 to /dbdata/erp/19.3.0/nls/data/9idata!

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.

Create appsutil.zip and copy it to the database tier

 On the application tier, as the oraprod user:

Source the run file system environment file.

[appltest@testapp ~]$ . /apdata/erp/EBSapps.env run     E-Business Suite Environment Information   ----------------------------------------   RUN File System           : /apdata/erp/fs1/EBSapps/appl   PATCH File System         : /apdata/erp/fs2/EBSapps/appl   Non-Editioned File System : /apdata/erp/fs_ne     DB Host: non-prod-db.soumya.com  Service/SID: EBS   Sourcing the RUN File System ...

Run the admkappsutil.pl utility to create the appsutil.zip file in <INST_TOP>/admin/out.

[appltest@testapp ~]$ perl $AD_TOP/bin/admkappsutil.pl Starting the generation of appsutil.zip Log file located at /apdata/erp/fs2/inst/apps/EBS_ebsprodapp/admin/log/MakeAppsUtil_06111545.log output located at /apdata/erp/fs2/inst/apps/EBS_ebsprodapp/admin/out/appsutil.zip MakeAppsUtil completed successfully.

Copy the appsutil.zip file inside the 19C ORACLE_HOME in database server

[appltest@testapp ~]$ scp /apdata/erp/fs1/inst/apps/EBS_ebsprodapp/admin/out/appsutil.zip oraprod@172.15.20.115:/dbdata/erp/19.3.0

Uncompress the appsutil.zip file under the <RDBMS_ORACLE_HOME>:

$ cd $ORACLE_HOME
$ unzip -o appsutil.zip

Make sure appsutil.zip has proper ownership 

Install JRE 8

To install JRE 8 on the appsutil directory, copy the $ORACLE_HOME/jdk/jre directory to $ORACLE_HOME/appsutil/jre. Run the following commands:

[oraprod@non-prod-db 19.3.0]$ cd $ORACLE_HOME/appsutil [oraprod@non-prod-db appsutil]$ cp -r $ORACLE_HOME/jdk/jre . [oraprod@non-prod-db appsutil]$ cp $ORACLE_HOME/jlib/orai18n.jar $ORACLE_HOME/appsutil/jre/lib/ext

Please follow the next part here :