EBS Apps steps for upgrading R12.2 database to 19c

There are two parts for the db upgrade: before the upgrade and after the upgrade on EBS side.

A. Apply patches to R12.2 BEFORE database is upgraded from 12c to19c

A list of EBS patches are needed before 19c database upgrade. Doc ID 2552181.1 (Interoperability Notes: Oracle E-Business Suite Release 12.2 with Oracle  Database 19c) has details for that.

1. Apply required R12.2 patches (Section 2.1)

The following 9 patches must exist in the environment before database is upgraded to 19c.

SELECT adb.bug_number, aat.name appl_top_name, adb.language, adb.creation_date,

decode(ad_patch.is_patch_applied('R12',aat.appl_top_id,adb.bug_number,adb.language),'EXPLICIT','APPLIED','NOT_APPLIED','NOT APPLIED') status

 FROM ad_bugs adb,

(select aat.name, aat.appl_top_id

from applsys.ad_appl_tops aat,

(select distinct fat.name from applsys.fnd_appl_tops fat) fat

where aat.name=fat.name ) aat

where adb.bug_number in (

'25452805', -- Applied

'26052406', -- Applied

'26521736', -- Need (19c interoperability patch for Release 12.2)

'30433124', -- Applied

'31088182', -- Need to apply

'31349591', -- Need to apply

'31800803', -- Need to apply

'30611319', -- Applied (only if using Integrated SOA Gateway (ISG))

'28732161'  -- Need (Release 12.2.6 or later versions)

) order by adb.bug_number,aat.name,adb.language;

In my instance, 4 patches were applied. I used downtime mode to apply 5 patches:

26521736

31088182

31349591

31800803

28732161

$ sh $ADMIN_SCRIPTS_HOME/adstpall.sh apps/apps_PWD

$ ps -ef | grep $LOGNAME

$ echo $FILE_EDITION

run

$ echo $PATCH_TOP 

$NE_BASE/EBSapps/patch

$ cd $PATCH_TOP

$ unzip p26521736_12.2.0_R12_LINUX.zip

$ unzip p31088182_R12.FND.C_R12_GENERIC.zip

$ unzip p31349591_R12.OTA.C_R12_GENERIC.zip

$ unzip p31800803_R12.TXK.C_R12_GENERIC.zip

$ unzip p28732161_R12.FRM.C_R12_GENERIC.zip

$ adop phase=apply apply_mode=downtime patches=26521736,31088182,31349591,31800803,28732161

Run the SQL statement again to confirm all 9 patches were applied.  I also applied patch 31405457 (See Doc ID 2554156.1 for 19c database Export/Import):

$ unzip p31405457_R12_GENERIC.zip

$ adop phase=apply apply_mode=downtime patches=31405457

If needed, additional patches for business functions can be applied before run FS_CLONE to get it ready for database upgrade.

$ adop phase=fs_clone

2. Optional:  Instead of FS_CLONE, complete patching cycle and remove ADOP created editions (Section 2.3, Doc ID 2552181.1)

As the owner of the source administration server, run any of the following commands not previously run. This will clean up the editions created by previous adop patching cycles. These commands also complete any open patching cycle. 

I did clone the 2nd node from 1st node in one of my instances, AFTER applying above 5 patches and BEFORE running below ADOP cycle.

On the current RUN file system, I run below lines in order. The ADOP session will run on all nodes.

$ vi /etc/oraInst.loc

$ adop phase=prepare            <= It may take a couple of hours

… … 

 AutoConfig completed successfully.

 Synchronizing patch filesystem with run filesystem.
    Log: $NE_BASE/EBSapps/log/adop/5/20210X16_132846/prepare/$EBS_HOSTNAME

========================================

SUMMARY OF PATCHES BEING SYNCHRONIZED...

========================================

[1] SINGLE PATCH (DRV=u30399996.drv):  30399996
[2] SINGLE PATCH (DRV=u31948857.drv):  31948857
[3] ADADMIN action :  GEN_JARS
[4] SINGLE PATCH (DRV=u26521736.drv):  26521736
[5] SINGLE PATCH (DRV=u31088182.drv):  31088182
[6] SINGLE PATCH (DRV=u31349591.drv):  31349591
[7] SINGLE PATCH (DRV=u31800803.drv):  31800803
[8] SINGLE PATCH (DRV=u28732161.drv):  28732161
[9] SINGLE PATCH (DRV=u31405457.drv):  31405457
[10] SINGLE PATCH (DRV=u32072819.drv):  32072819
[11] SINGLE PATCH (DRV=u32157516.drv):  32157516
[12] SINGLE PATCH (DRV=u32181474.drv):  32181474
[13] SINGLE PATCH (DRV=u32266274.drv):  32266274
SYNCHRONIZATION is in progress. This may take a little longer depending on the number and size of patches that are being synchronized.
Check log file $NE_BASE/EBSapps/log/adop/....../prepare/$EBS_HOSTNAME/TXK_SYNC_update/txkADOPPreparePhaseSynchronize.log for details.

 LOGPATH is set to $NE_BASE/EBSapps/log/adop/....../prepare/$EBS_HOSTNAME/TXK_SYNC_update/log

… … 

$ adop phase=actualize_all    

It takes 4 or more hours staying below file for very long time:

$NE_BASE/EBSapps/log/adop/5/20210X16_142206/actualize_all/$EBS_HOSTNAME/log/actualize_all.log, executing ACTUALIZE_CHILD_OBJS ($AD_TOP/sql/ADZDSHOWOBJS.sql ?)

It does not change Status on any patching cycle Phase.

$ adop phase=finalize finalize_mode=full

It take a few minutes.

$ adop phase=cutover             <== it may take half hour

... ...

Creating list of nodes where cutover needs to be run.

    The cutover phase needs to be run on node: 2nd_node

    The cutover phase needs to be run on node: master_node

Running cutover phase on node(s): [2nd_node and master_node].

Broadcast message from applmgr@master_node.domain.com (Wed xxx 18:50:56 2022):

EBSDEV environment has changed.

All users must re-source the environment.

Broadcast message from applmgr@master_node.domain.com (Wed xxx 18:56:58 2022):

EBSDEV environment has changed.

All users must re-source the environment.

    Output: $NE_BASE/EBSapps/log/adop/....../cutover/remote_execution_result_level1.xml

 Statement handler reference is invalid.

 Session killed.

 Note that this is normal in the online patching cutover phase.

 DB-Handle Error Code: 28

 DB-Handle Error Message: ORA-00028: your session has been killed (DBD ERROR: OCIStmtExecute/Describe)

 Reconnecting to the database.    Log: $NE_BASE/EBSapps/log/adop/....../cutover/master_node

        txkADOPEvalSrvStatus.pl returned SUCCESS

Generating node-specific status report.

    Output: $NE_BASE/EBSapps/log/adop/....../adzdnodestat.out

Summary report for current adop session:

    Node 2nd_node: Completed successfully

       - Cutover status:    Completed successfully

    Node master_node: Completed successfully

       - Cutover status:    Completed successfully

    For more details, run the command: adop -status -detail

adop exiting with status = 0 (Success)

    

EBS services will be down for 5 to 10 minutes

It changes APPLY phase from ACTIVE to COMPLETED, and CUTOVER phase from "NOT STARTED" to COMPLETED.

After "adop phase=cutover", run the env again or login as a new session. That will change the File system from fs1 to fs2. On the new RUN file system:

$ adop phase=cleanup cleanup_mode=full                 <= It takes about 2 hours.

Enter the APPS password:

Enter the SYSTEM password:

Enter the WLSADMIN password:

Validating credentials.

Initializing.
    Run Edition context  : xxx.xml
    Patch edition context: xxx.xml
    Patch file system free space: 62.62 GB

Validating system setup.
    Node registry is valid.

Checking for existing adop sessions.

===============================================
ADOP (C.Delta.12)
Session ID: 5
Node: node_name
Phase: cleanup
Log: $NE_BASE/EBSapps/log/adop/5/20210416_185701/adop.log
===============================================

Processing cleanup actions in parallel.
    Log: $NE_BASE/EBSapps/log/adop/5/20210416_185701/cleanup/xifsapw3d/log/cleanup.log

Generating report of queued DDL actions.
    Output: $NE_BASE/EBSapps/log/adop/5/20210416_185701/cleanup/xifsapw3d/adzdallddls.out

Running cleanup in FULL mode.
    Log: @ADZDSHOWLOG.sql "2021/04/16 19:01:04"
    [ERROR]     ORA-04045: errors during recompilation/revalidation of APPS.ap_custom_PKG
ORA-04052: error occurred when looking up remote object APPLSYS.FND_PROFILE_OPTION_VALUES@a_db_link.WORLD
ORA-00604: error occurred at recursive SQL level 3
ORA-02019: connection description for remote database not found, SQL[ORA$BASE]: drop PACKAGE "APPS"."ap_custom_PKG"
    [ERROR]     Could not drop covered object [ORA$BASE] APPS.ap_custom_PKG (PACKAGE): ORA-04045: errors during recompilation/revalidation of APPS.custom_PKG
ORA-04052: error occurred when looking up remote object APPLSYS.FND_PROFILE_OPTION_VALUES@a_db_link.WORLD
ORA-00604: error occurred at recursive SQL level 3
ORA-02019: connection description for remote database not found

Generating log report.
    Output: $NE_BASE/EBSapps/log/adop/5/20210416_185701/cleanup/node_name/adzdshowlog.out

The cleanup phase completed successfully.

adop exiting with status = 0 (Success)

"adop -status -detail" to confirm all phases are COMPLETED.

Also, confirm by SQL query that 9 pre-patches still exist in all nodes (after adop CUTOVER).

3. Create a new appsutil.zip file and copy it over for database server:

$ cd $AD_TOP/bin
$ ls -al ad*mk*
$ perl admkappsutil.pl

Copy 4 files to database node for DBA steps:

$INST_TOP/admin/out/appsutil.zip

$AD_TOP/patch/115/sql/adctxprv.sql

$APPL_TOP/admin/adstats.sql

$APPL_TOP/admin/adgrants.sql        <== it is the same file as from CPU patch 30370733

4. Run hcheck as sysdba (Doc ID 136697.1)

If you get below error, you have to fix the problem before moving forward or Export/import may be the only option to upgrade the database.

Problem Summary

---------------------------------------------------

HCKW-0027: System Tablespace does not have relative file number of 1

Problem Description

---------------------------------------------------

Running hcheck.sql

Returned warning:

 - SystemNotRfile1 ... 1201000200 > 902000000 03/04 16:03:08 WARN

HCKW-0027: System Tablespace does not have relative file number of 1 (Doc ID 2364065.1)

Confirm file names caused the mess-up:

SQL> select RELATIVE_FNO fno, file_id, substr(file_name,1,50) fileName 

from dba_data_files where file_name like '%sys%';

FNO  File_ID  FILENAME

  16              1   /oradata/u10/EBSDEV/system01.dbf

    8              8   /oradata/u10/EBSDEV/system06.dbf

  87            87   /oradata/u10/EBSDEV/system07.dbf

etc … … 

SQL> select RELATIVE_FNO, file_id, substr(file_name,1,50) fileName 

from dba_data_files where file_id =16;

FNO  File_ID  FILENAME

  16           16   /oradata/u11/EBSDEV/APPS_TS_TX_IDX36.dbf

5. DBA to upgrade the database to 19c.

B. Connect to 19c database after DB upgrade completed.

In EBS R12.1, all TNS entries use SID. After database is upgraded to 19c, same TNS file in R12.1 shall continue working if the SID is kept the same.

$ tnsping ebsdev

TNS Ping Utility for Linux: Version 10.1.0.5.0 - Production on 24-MAR-2022 13:36:22

... ...

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = ebsDB1d.domain.com)(Port = 1560))) (CONNECT_DATA = (SID = EBSDEV)))

OK (20 msec)

If SID was changed during DB upgrade, SQL*Plus may give error:

$ sqlplus apps/appsPWD

ERROR:

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

IBM AIX RISC System/6000 Error: 2: No such file or directory

Additional information: 2878

Additional information: -308473993

For EBS R12.2, the TNS entries do not use SID. After the database is upgraded to 19c, tnsping still work, but Sql*Plus may not:

$ tnsping ebsdev

... ...

Used parameter files:

$INST_TOP/ora/10.1.2/network/admin/sqlnet_ifile.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=ebsDB1d.domain.com)(PORT=1560)) (CONNECT_DATA= (SERVICE_NAME=EBSDEV) (INSTANCE_NAME=EBSDEV)))

OK (0 msec)

When trying to make connection to the database, it will give error:

$ sqlplus apps/appsPWD

ERROR:

ORA-12521: TNS:listener does not currently know of instance requested in

connect descriptor

This is because 19c database has new names. DBA has to provide the new info:

CDB = CDBEDEV

PDB = EBSDEV   (Usually, the same as old DB name) 

(and new SYSTEM password)

2. Edit tnsnames.ora file

$ cd $TNS_ADMIN

$ vi tnsnames.ora

replace every INSTANCE_NAME to CDBEDEV

replace every SERVICE_NAME to ebs_EBSDEV (if it is different from 12c)

$ tnsping aebsdev

... ...

Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=ebsDB1d.domain.com)(PORT=1560)) (CONNECT_DATA= (SERVICE_NAME=ebs_EBSDEV) (INSTANCE_NAME=CDBEDEV)))

OK (0 msec)

$ sqlplus apps/appsPWD

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> 

SQL statements can be used to confirm them:

SQL> SELECT sys_context('userenv','instance_name') FROM dual;

SYS_CONTEXT('USERENV','INSTANCE_NAME')

--------------------------------------------------------------------------------

CDBEDEV

SQL> SELECT sys_context('userenv','service_name') from dual;

SYS_CONTEXT('USERENV','SERVICE_NAME')

--------------------------------------------------------------------------------

ebs_EBSDEV

SQL> select sys_context('userenv','db_name') from dual;

SYS_CONTEXT('USERENV','DB_NAME')

--------------------------------------------------------------------------------

EBSDEV

Notes: 

a). "Starting Oracle 12.2 sys_context(‘USERENV’,’DB_NAME’) will show the name of the Database in CDB$ROOT and the name of the PDB inside the PDB."

b). In 19c, db_name and global_name could be different from above.  could be confusing. 

SQL> select * from global_name;

GLOBAL_NAME

--------------------------------------------------------------------------------

EBSDEV.WORLD

SQL> select name from v$database;

NAME

---------

CDBEDEV

c). SID is the same as instance_name. Or, use query to get it:

SQL> select instance from v$thread;

INSTANCE

--------------------------------------------------------------------------------

CDBEDEV

d). "SELECT sys_context('USERENV', 'SID') FROM DUAL;" gives session_id in database.

To connect to the 19c database from EBS mid-tier nodes, DBAs have to make sure

Service "ebs_EBSDEV" and Instance "CDBEDEV" are running and have status READY.

$ ps -ef | grep _pmon_

  oracle 41419846        1   0   Apr 13      -  0:14 ora_pmon_CDBEDEV

$ export ORACLE_HOME=/path/to/

$ lsnrctl status CDBEDEV

   It should list several Services, including CDBEDEV, EBSDEV, ebs_EBSDEV, ebs_patch.

3. Edit $CONTEXT_FILE

Update the following values in the context file.

Variable Name                         Value

s_dbhost                                          New database hostname (if changed)

s_dbport                                         New database port (if changed)

s_dbGlnam                                      CDBEDEV 

s_apps_jdbc_connect_descriptor NULL (<jdbc_url oa_var="s_apps_jdbc_connect_descriptor"/>)

s_dbport                                         New database port

s_applptmp                                 Defined in UTL_FILE_DIR (shall not use /usr/tmp)

4. Run AutoConfig on all nodes

Notes: after autoconfig, s_apps_jdbc_connect_descriptor will get new value.

Check table.FND_OAM_CONTEXT_FILES, which may be cleaned during database upgrade. If CONTEXT FILE records for PATCH system are missing, run AutoConfig on PATCH file system as well. Otherwise, ADOP session will fail.

SQL> select * from FND_OAM_CONTEXT_FILES;

5. DBA runs some reports, compile invalid objects and make necessary grants.

6. Run AutoConfig on all apps nodes and start all EBS services.

All shall work by now.

SQL> @$AD_TOP/sql/ADZDSHOWLOG.sql

SQL> @$AD_TOP/sql/ADZDSHOWOBJS.sql

SQL> @ $AD_TOP/sql/ADZDSHOWED.sql

SQL> show parameter filesystemio_options

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

filesystemio_options                 string      SETALL

SQL> show parameter utl_file

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

utl_file_dir                         unknown