EBS steps when database is upgraded to 19c using Export/Import

In some special situations, database can not be upgraded to 19c by Database Upgrade procedure. For example, run hcheck as sysdba (Doc ID 136697.1). If get below error, you have to fix the problem before moving forward with database upgrade.

Problem Summary

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

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

Problem Description

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

Running hcheck.sql

Returned warning:

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

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

Steps on EBS apps side when database export & import are used to upgrade the database:

1. Make sure all pre-requisite patches were applied to apps side 

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

2. Run through patch cycle to complete adop cleanup

On the current run file system:

Run fs_clone first (if no pending ADOP session exists). It will screen out problems and help to fix potential issues (for example, fs_clone was not executed right after ssl cert renewal):

$ adop phase=fs_clone

If troubleshooting was performed, make sure nothing is running on PATCH (say, fs2) file system and log onto EBS site to do a sanity check.

$ ps -ef | grep fs2

Starting a new OS session on primary node:

$ adop phase=prepare

$ adop phase=actualize_all

$ adop phase=finalize finalize_mode=full

$ adop -status

... ...

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

Node Name  Node Type      Phase           Status          Started              Finished             Elapsed

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

node1    master    PREPARE       COMPLETED       2022/X/14 20:32:32  2022/X/14 20:38:05  0:05:33

                            APPLY             ACTIVE

                            FINALIZE       COMPLETED       2022/X/14 21:27:31  2022/X/14 21:34:38  0:07:07

                            CUTOVER      NOT STARTED

                            CLEANUP       NOT STARTED

node2    slave      PREPARE       COMPLETED       2022/X/14 20:32:31  2022/X/14 20:38:07  0:05:36

                            APPLY            NOT STARTED

                            FINALIZE      COMPLETED       2022/X/14 21:27:31  2022/X/14 21:34:38  0:07:07

                            CUTOVER     NOT STARTED

                            CLEANUP     NOT STARTED

node3    slave      PREPARE      COMPLETED       2022/X/14 20:32:31  2022/X/14 20:39:39  0:07:08

                           APPLY             NOT STARTED

                           FINALIZE      COMPLETED       2022/X/14 21:27:31  2022/X/14 21:34:38  0:07:07

                           CUTOVER      NOT STARTED

                           CLEANUP      NOT STARTED

May see concurrent job "Online Patching In Progress"

$ adop phase=cutover

On the new run file system:

$ adop phase=cleanup cleanup_mode=full 

Also run SQL scripts by apps:

@$AD_TOP/sql/ADZDSHOWOBJS.sql - to check which objects still need to be tidied up.

@$AD_TOP/sql/ADZDSHOWLOG.sql  - to make sure that it ran through properly

@$AD_TOP/sql/ADZDSHOWED.sql     - to get the list of Editions

Shutdown apps services

3. DBA upgrades database to 19c by using Export and Import

4. Modify apps files after DBA provides new TNS entry. 

Below is an example of the new TNS entry. 

EBSDEV=

        (DESCRIPTION=

                (ADDRESS=(PROTOCOL=tcp)(HOST=ebs_db1d.domain.com)(PORT=1560))

            (CONNECT_DATA=

                (SERVICE_NAME=ebs_EBSDEV)

                (INSTANCE_NAME=CEBSDEV)

            )

        )

To modify the TNS entries, edit the $TNS_ADMIN/tnsnames.ora files to specify the CDB instance name. 

$ 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)

$ sqlplus apps/appsPWD

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> 

$ tnsping ebsdev

... ...

Used TNSNAMES adapter to resolve the alias

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)

5. Edit $CONTEXT_FILE on both RUN and PATCH file systems

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    (Seems autoconfig does not keep it)

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 (e.g. /path/to/EBSDEV/utl_dir)

6. Run AutoConfig on RUN and PATCH file systems in all nodes

SQL> select * from FND_OAM_CONTEXT_FILES;     

SQL> select * from fnd_nodes;         <== only one row for db node

$ ./adautocfg.sh

Notes: 

a). AutoConfig will fail on jtfictx.sh because jtfictx.sql requires AD_CTX_DDL, which is created in a later step. AutoConfig will be run again after AD_CTX_DDL has been created. 

b). After AutoConfig, s_apps_jdbc_connect_descriptor will get new value.

c). AutoConfig will overwrite variable s_dbGlnam ?

Table FND_OAM_CONTEXT_FILES might be cleaned during database upgrade. If CONTEXT FILE records for PATCH system are missing, run AutoConfig on PATCH file system as well to avoid ADOP session failure in the future.

To run AutoConfig on PATCH file system, disable a trigger first (and afterwards enable it).

SQL> conn system/systemPWD

Connected.

SQL> alter trigger ebs_logon disable;

Trigger altered.

SQL> select * from FND_OAM_CONTEXT_FILES;     <== entries for both RUN and PATCH

7. Create the CTX objects

Certain Oracle Text objects are not preserved by the import process. The consolidated export/import utility patch that were applied to the administration server node in Section 4 of Doc ID 2552181.1 contains a perl script, dpost_imp.pl, that will generate an AutoPatch driver file. Use this driver file to call the scripts that create these objects.

$ cd $AU_TOP/patch/115/bin

$ mkdir 6924477

$ perl dpost_imp.pl u6924477.drv 12      <== it creates file u6924477.drv

$ mv u6924477.drv 6924477                   <== repeat above steps on other node(s)

$ adop phase=apply hotpatch=yes patchtop=$AU_TOP/patch/115/bin patches=6924477 options=forceapply

8. DBAs run some reports, compile invalid objects and make necessary grants.

9. Run AutoConfig in 2nd time on all apps nodes and start all EBS services.

All shall work by now.

OPTIONAL actions: 

1. If WF agent does not start, the issue is with table AQ$_WF_CONTROL_P. Build WF queue on fly.

$ sqlplus applsys/PWD

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> set pages 10000

SQL> select instance_name from v$instance;

SQL> select count(*) from all_objects;

  COUNT(*)

----------

     62483

SQL> exec dbms_aqadm.drop_queue_table(queue_table=>'WF_CONTROL',force => TRUE);

PL/SQL procedure successfully completed.

SQL> select count(*) from all_objects;

  COUNT(*)

----------

     62446

SQL> select * from all_objects where object_name like'AQ$WF_CONTROL%';

no rows selected

SQL> select * from all_objects where object_name like'AQ$_WF_CONTROL%';

no rows selected

$ sqlplus apps/appsPWD

SQL> @/path/to/EBSPGRND/utl_dir/wfctqrec_new.sql APPLSYS appsPWD

Connected.                                               (see below Notes)

PL/SQL procedure successfully completed.

Commit complete.

$ sqlplus applsys/PWD

SQL> select count(*) from all_objects;

  COUNT(*)

----------

     62474

SQL> column object_name format a25

SQL> column object_type format a10

SQL> select object_name, object_type from all_objects 

where object_name like 'AQ$_WF_CONTROL%';

OBJECT_NAME                      OBJECT_TYP

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

AQ$_WF_CONTROL_E          QUEUE

AQ$_WF_CONTROL_F           VIEW

AQ$_WF_CONTROL_G          TABLE

AQ$_WF_CONTROL_H          TABLE

AQ$_WF_CONTROL_I           TABLE

AQ$_WF_CONTROL_L          TABLE

AQ$_WF_CONTROL_N          SEQUENCE

AQ$_WF_CONTROL_S          TABLE

AQ$_WF_CONTROL_T          TABLE

AQ$_WF_CONTROL_V          EVALUATION

                                                       CONTEXT

10 rows selected.

SQL> select object_name, object_type from all_objects 

where object_name like 'AQ$WF_CONTROL%';

OBJECT_NAME               OBJECT_TYP

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

AQ$WF_CONTROL               VIEW

AQ$WF_CONTROL_R          VIEW

AQ$WF_CONTROL_S           VIEW

Some database objects are created on "fly" after EBS services are started:

$ cd $ADMIN_SCRIPTS_HOME

$ ./adstrtal.sh apps/appsPWD

SQL> select object_name, object_type from all_objects 

where object_name like 'AQ$_WF_CONTROL%';

OBJECT_NAME               OBJECT_TYP

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

AQ$_WF_CONTROL_D          TABLE

AQ$_WF_CONTROL_E          QUEUE

AQ$_WF_CONTROL_F           VIEW

AQ$_WF_CONTROL_G          TABLE

AQ$_WF_CONTROL_H          TABLE

AQ$_WF_CONTROL_I           TABLE

AQ$_WF_CONTROL_L          TABLE

AQ$_WF_CONTROL_N          SEQUENCE

AQ$_WF_CONTROL_P          TABLE

AQ$_WF_CONTROL_S          TABLE

AQ$_WF_CONTROL_T          TABLE

AQ$_WF_CONTROL_V          EVALUATION

                                                       CONTEXT

12 rows selected.

SQL> select object_name, object_type from all_objects 

where object_name like 'AQ$WF_CONTROL%';

OBJECT_NAME                     OBJECT_TYP

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

AQ$WF_CONTROL               VIEW

AQ$WF_CONTROL_R          VIEW

AQ$WF_CONTROL_S           VIEW

SQL> select count(*) from all_objects;

  COUNT(*)

----------

     62487

Three Concurrent Managers shall be started and in running:

   . Workflow Mailer Service

   . Workflow Agent Listener Service

   . Workflow Document Web Services Service

But "Workflow Notification Mailer" may still show Down status due to a wrong SMTP server.

Notes: If running wfctqrec.sql gets error, delete some rows and save it as wfctqrec_new.sql for execution.

$ ls -altr wfctqrec*.sql

-rwxr-xr-x 1 user group 3734 Apr 12  2021 wfctqrec.sql

-rwxr-xr-x 1 user group 2865 Jul 14 17:16 wfctqrec_new.sql

$ diff wfctqrec.sql wfctqrec_new.sql

<   BEGIN

<     dbms_aqadm.stop_queue( queue_name => 'WF_CONTROL', wait => FALSE);

<   EXCEPTION

<     WHEN no_queue THEN

<       null;

<     WHEN others THEN

<       RAISE_APPLICATION_ERROR(-20000, 'Oracle Error = '

<                                     ||to_char(sqlcode)||' - '||sqlerrm);

<   END;

<

<   BEGIN

<     dbms_aqadm.drop_queue(queue_name=> 'WF_CONTROL');

<   EXCEPTION

<     WHEN no_queue THEN

<       null;

<     WHEN others THEN

<       RAISE_APPLICATION_ERROR(-20000, 'Oracle Error = '

<                                     ||to_char(sqlcode)||' - '||sqlerrm);

<   END;

<

<   BEGIN

<     dbms_aqadm.drop_queue_table( queue_table => 'WF_CONTROL', force => TRUE);

<   EXCEPTION

<     WHEN queue_table_not_exists THEN

<       null;

<     WHEN others THEN

<       RAISE_APPLICATION_ERROR(-20000, 'Oracle Error = '

<                                     ||to_char(sqlcode)||' - '||sqlerrm);

<   END;

<

2. After 19c upgrade, three AZ tables for EBS iSetup becomes missing in 19c database.  Follow 

Oracle Doc ID 832459.1 (How To Cleanup Invalid Oracle iSetup (AZ) Tables And Recreate) to re-create them

3. In R12.2, when below line was used to hold concurrent jobs for a period of time for database upgrade completion and then from GUI forms to cancel the HOLD, some concurrent jobs got "No Manager" status.

SQL> update  apps.fnd_concurrent_requests  set hold_flag = 'Y' 

where status_code in ('Q','I') and hold_flag = 'N';

The problem may be column EDITION_NAME of table fnd_concurrent_requests was populated with invalid data. The fix is to find the current edition and run an update to the column.  

SQL> select edition_name from all_editions;

EDITION_NAME

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

ORA$BASE

SQL> update fnd_concurrent_requests

set edition_name='ORA$BASE'

where request_id in (228633x, 228633x, 228632x, 228632x, 228624x, 228624x)

;

How to capture a HEAP dump for oacore process in EBS Weblogic

When users could not log onto EBS site (with error code 502 for URL /OA_JAVA/oracle/apps/fnd/jar/fndewt.jar), we saw oacore processes used very high CPU and WLS Admin site show oacore1 status is unknown (blank). We had to shut oacore1 down and then start it up in WLS Admin Console.

After checking all EBS logs, Oracle Support did not find anything that might cause the problem and so asked us to capture a HEAP dump when the issue occurs. 

Use the following steps to customize the managed server configuration (of each OACORE) via the WebLogic Server Administration Console.

1. Log on to the WebLogic Server Administration Console.

2. Click on the 'Servers' link. This link takes you to a page containing a summary of the WebLogic Administration Server and all managed servers.

3. Click on the managed server whose configuration needs to be updated. A page containing various tabs for the settings of the managed server appears (e.g. oacore_server1).

4. Navigate to Configuration Tab > Server Start Tab > You can see the Arguments Section.

5. Click on Lock and Edit button in the 'Change Center' panel.

6. Update the Heap setting to append

-XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=/u02/app/temp/HEAP_trace

(add above line to the end of the long string with a single space in between. No service bounce is needed. /u02/app/temp/HEAP_trace can be changed to a different location.)

7. Click the 'Save' button to save the configuration changes.

8. Once the customizations are complete and saved, click the 'Activate Changes' button in the 'Change Center' panel to activate the changes.

9. Next time Out of Memory error occurs (or service bounce), a *.hprof dump file will be generated in /u02/app/temp/HEAP_trace.  Compressed it by gzip and upload it to Oracle SR.

$ grep hprof $EBS_DOMAIN_HOME/servers/oacore_server1/logs/oacore_server1.out00033

Dumping heap to /u02/app/temp/HEAP_trace/java_pid21982.hprof

More details in the log file:

java.lang.OutOfMemoryError: Java heap space

Dumping heap to /ifsu02/app/temp/HEAP_trace/java_pid21982.hprof ...

Heap dump file created [5475772387 bytes in 33.025 secs]

java.lang.OutOfMemoryError: Java heap space

        at org.apache.xmlbeans.impl.store.Locale.weakCur(Locale.java:2711)

        ... ...

R12.2 Autoconfig Fails On Patch file system with error ORA-12514

When I cloned a R12.2 instance, "perl adcfgclone.pl appsTier dualfs" gave an error at the final stage:

... ...

AutoConfig has completed with errors for .patch

Do you want to startup the Application Services for EBSDEV? (y/n) [n] : n

Before fixing the error, I did not have problem in setting up the instance and starting all apps services in RUN file system. The website was available to the user while I did troubleshooting in the PATCH file system. 

The log file for cloning RUN file system does not have any error. But log file $INST_TOP/admin/log/clone/patch/RCloneApplyAppstier_10281250.log from cloning PATCH file system has a lot of errors beginning with: 

ERROR DESCRIPTION:

(*******FATAL ERROR*******

PROGRAM : ($FND_TOP/patch/115/bin/txkSetUnicodeEncodingSecFilter.pl)

TIME    : Fri Oct 28 13:01:35 2022

FUNCTION: TXK::SQLPLUS::_doExecute [ Level 3 ]

MESSAGES:

Invalid Connect string - cannot connect to database

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

When I ran AutoConfig in PATCH environment, it failed and gave similar error in the log:

*******FATAL ERROR*******

PROGRAM : ($FND_TOP/patch/115/bin/txkUpdateEBSDomain.pl)

TIME    : Wed Nov  2 11:44:02 2022

FUNCTION: TXK::SQLPLUS::_doExecute [ Level 3 ]

MESSAGES:

Invalid Connect string - cannot connect to database

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

I tried "sh adadminsrvctl.sh start forcepatchfs" in PATCH environment and it worked, which is required by ADOP later, even AutoConfig failed on PATCH.

I compared tnsnames.ora and listener.ora in both RUN and PATCH file systems and did not see any difference. After I read Doc ID 2378192.1 (12.2 Autoconfig Fails On PatchFS with error "ORA-12514: TNS:listener does not currently know of service requested"),  I made a change to database parameter SERVICE_NAMES:

SQL> show user

USER is "SYSTEM"

SQL> show parameter service_name

NAME                           TYPE        VALUE

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

service_names              string      EBSDEV

SQL> alter system set service_names='EBSDEV,EBSDEV_ebs_patch';

System altered.

SQL> show parameter service_name

NAME                          TYPE        VALUE

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

service_names              string      EBSDEV,EBSDEV_ebs_patch

After that, I ran AutoConfig in PATCH file system again, and got the same error. So, that was not the fix.

Then,  I found database parameter LOCAL_LISTENER might be incorrect. After I make below change (and maybe plus the change to service_names),  AutoConfig worked successfully in PATCH file system !

SQL> show parameter local_listener

NAME                          TYPE        VALUE

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

local_listener                string

SQL> alter system set local_listener='EBSDEV_LOCAL';

System altered.

SQL> show parameter local_listener

NAME                          TYPE        VALUE

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

local_listener                string      EBSDEV_LOCAL

After AutoConfig worked in PATCH, I ran "adop phase=fs_clone" (successfully) in RUN file system to over-write the PATCH files. Then I added 2nd node to the instance without issue.

UTL_FILE_DIR in 19c database

Starting in Oracle Database 18c, initialization parameter UTL_FILE_DIR is deprecated and no longer supported. It can not be altered at DB level "alter system set utl_file_dir='/path/to/utl_dir;". Instead, specify the name of a directory object. 

UTL_FILE_DIR is no longer listed in V$SYSTEM_PARAMETER and related views:

SQL> SELECT value FROM v$system_parameter WHERE name='utl_file_dir' ;

no rows selected

SQL> show parameter utl_file_dir

NAME                                 TYPE        VALUE

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

utl_file_dir                         unknown

But, view v$parameter has it:

SQL> column name format a15

SQL> column value format a55

SQL> SELECT value FROM v$parameter WHERE name='utl_file_dir' ;

VALUE

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

/path/to/utl_dir,/home/u01/app/oracle/product/19.0.0.0/temp/EBSDEV

SQL> SELECT value FROM apps.v$parameter WHERE name='utl_file_dir' ;

VALUE

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

/path/to/utl_dir,/home/u01/app/oracle/product/19.0.0.0/temp/EBSDEV

SQL> select name, value from apps.v$parameter2 where name = 'utl_file_dir';

NAME            VALUE

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

utl_file_dir    /path/to/utl_dir

utl_file_dir    /home/u01/app/oracle/product/19.0.0.0/temp/EBSDEV

Doc ID 2525754.1 (Using UTL_FILE_DIR or Database Directories for PL/SQL File I/O in Oracle E-Business Suite Releases 12.1 and 12.2) states:

With Oracle Database 19c, you can specify the directories that you want Oracle E-Business Suite to use for PL/SQL file I/O in the supplemental UTL_FILE_DIR parameter within the apps.v$parameter and apps.v$parameter2 views. Each directory specified in that parameter must also have a corresponding directory object defined within the database. When you upgrade your Oracle E-Business Suite database from Oracle Database 11g or Oracle Database 12c to Oracle Database 19c, you use a script called txkCfgUtlfileDir.pl to migrate the directory paths formerly specified in the UTL_FILE_DIR database initialization parameter to the new supplemental UTL_FILE_DIR parameter and to create the corresponding directory objects. After the upgrade, you also use the same script to add, modify, or delete directory paths in the supplemental UTL_FILE_DIR parameter.

SQL> show parameter event

NAME                 TYPE        VALUE

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

event                     string      10946 trace name context forever, level 8454144

xml_db_events     string      enable

SQL> show user

apps

SQL> FND_FILE.PUT_LINE(fnd_file.output, 'Hello output!');

SQL> FND_FILE.close;

This should dump a file on APPLPTMP.

FND_FILE uses the UTL_FILE package, which can only write to directories specified in init.ora (before 19c database)). APPLPTMP is a special directory used only for PLSQL temporary files. When a concurrent program uses the FND_FILE package, the concurrent manager uses APPLPTMP as the directory to which it writes temporary files. Thus, APPLPTMP must be one of the directories listed in init.ora in order for FND_FILE to work

But a concurrent program calling a PL/SQL procedure owned by a custom schema xxdoc keeps failing with errors:

ORACLE error 20100 in FDPSTP

Cause: FDPSTP failed due to ORA-20100: ORA-20100: Temporary file creation for FND_FILE failed.

Directory &FILE_DIR is invalid, contact your system administrator.

Action: Check that the directory is a valid directory with wri

The procedure in custom schema called by a concurrent program is similar to:

CREATE OR REPLACE PROCEDURE xxdoc.proc_test1(errbuf out varchar2, retcode out number) as

BEGIN

    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Test CP from custom procedure');

END;

CREATE OR REPLACE SYNONYM proc_test1 for xxdoc.proc_test1;   (by APPS)

The fix: Grant permission on directory EBS_DB_DIR_UTIL to XXDOC

Note "select * from dba_directories where directory_name = 'EBS_DB_DIR_UTIL';" returns 0 rows in R12c database of EBS.

SQL> sho pdbs

    CON_ID CON_NAME             OPEN MODE  RESTRICTED

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

         2   PDB$SEED                      READ ONLY     NO

         3   EBSDEV                          READ WRITE   NO

SQL> alter session set container=IFSDEV;

Session altered.

SQL> sho pdbs

    CON_ID CON_NAME             OPEN MODE  RESTRICTED

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

         3   EBSDEV                           READ WRITE   NO

SQL> set lines 200 pages 1234

SQL> col directory_name for a35

SQL> col directory_path for a60

SQL> col owner for a20

SQL> select * from dba_directories where directory_name like '%UTIL%';

OWNER  DIRECTORY_NAME  DIRECTORY_PATH  ORIGIN_CON_ID

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

SYS       EBS_DB_DIR_UTIL      /path/to/utl_dir             3

SQL> GRANT READ,WRITE on directory EBS_DB_DIR_UTIL to XXDOC;

Grant succeeded.

SQL> col grantee for a12

SQL> col owner for a15

SQL> col table_name for a35

SQL> col grantor for a20

SQL> col privilege for a20

SQL> select GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE 

from dba_tab_privs 

where table_name like '%UTIL%' and TYPE in ('DIRECTORY') 

order by owner,table_name,GRANTEE,privilege;

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE 

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

APPS       SYS  EBS_DB_DIR_UTIL SYSTEM  READ 

APPS       SYS  EBS_DB_DIR_UTIL SYSTEM  WRITE 

SYSTEM SYS  EBS_DB_DIR_UTIL SYS          EXECUTE 

SYSTEM SYS  EBS_DB_DIR_UTIL SYS          READ 

SYSTEM SYS  EBS_DB_DIR_UTIL SYS          WRITE 

XXDOC  SYS  EBS_DB_DIR_UTIL SYS          READ 

XXDOC  SYS  EBS_DB_DIR_UTIL SYS          WRITE 

NOTES: 19c database has UTL directories. Seems all of them point to the folder on db host.

SQL> select GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE from dba_tab_privs 

where table_name like '%UTL%' and TYPE in ('DIRECTORY') 

order by owner,table_name,GRANTEE,privilege;

REFERENCES:

- Concurrent Requests Fail With Error ORA-20100 (Doc ID 2723960.1)

- Creating a Custom Application in Oracle EBS Release 12.1.3 and above (Doc ID 1577707.1)

- Deploying Customizations in Oracle E-Business Suite Release 12.2 (Doc ID 1577661.1)

  [ Section 2.2.1: Granting Privileges to Editioned Objects

    Section 1.1.4: Tools and Scripts for Edition-Based Development ]

Re-create three AZ tables of iSetup

After database Export and Import for 19c database upgrade, three AZ tables for EBS iSetup becomes missing in 19c database:

SQL> select * from dba_tables where owner = 'AZ' and table_name in

 ('AZ_REQUESTS','AZ_REPORTER_DATA','AZ_DIFF_RESULTS'); 

no rows selected

Even iSetup is not used, Business user still wants to get them back. Oracle Doc ID 832459.1 (How To Cleanup Invalid Oracle iSetup (AZ) Tables And Recreate) provides steps on re-creating them.

$ appPWD='PWDappsXXX'

$ sysPWD='PWDsysXXX'

1.  Make sure account XDB is OPEN

SQL> SELECT status FROM dba_registry WHERE comp_id = 'XDB';

STATUS

-----------

VALID

SQL> SELECT username, account_status FROM dba_users WHERE username = 'XDB';

USERNAME  ACCOUNT_STATUS

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

XDB               LOCKED

SQL> conn system/$sysPWD

Connected.

SQL> ALTER USER xdb IDENTIFIED BY xdb ACCOUNT UNLOCK;

ALTER USER xdb IDENTIFIED BY xdb ACCOUNT UNLOCK

*

ERROR at line 1:

ORA-01031: insufficient privileges

Fix: sysdba needs to unlock XDB account on the root$ container

SQL> SELECT username, account_status FROM dba_users WHERE username = 'XDB';

USERNAME  ACCOUNT_STATUS

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

XDB               OPEN

2. Get the script ready

SQL> show user

USER is "SYSTEM"

SQL> set pagesize 200

SQL> column OWNER format a15

SQL> column TYPE_NAME format a30

SQL> SELECT owner, type_name FROM all_types WHERE type_name LIKE 'AZ%';

OWNER           TYPE_NAME 

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

APPS_NE         AZ_RPTDATA_H  

APPS_NE         AZ_RPTDATA_V_COLL   

APPS_NE         AZ_RPTDATA_V_TAGS  

APPS_NE         AZ_RPTDIFF_H     

APPS_NE         AZ_RPTDIFF_V_COLL         

APPS_NE         AZ_RPTDIFF_V_TAGS   

APPS_NE         AZ_SELSETS_EXT    

APPS_NE         AZ_SELSETS_H     

APPS_NE         AZ_SELSETS_H_COLL      

APPS_NE         AZ_SELSETS_V652_T   

APPS_NE         AZ_SELSETS_V_COLL 

11 rows selected.

3. Run "drop type" script as SYSTEM

SQL> show user

USER is "SYSTEM"

SQL> SELECT 'drop type ' || owner || '.' || type_name || ' force;' FROM all_types WHERE type_name LIKE 'AZ%';

Then run below lines to drop those types forcefully as SYSTEM:

drop type APPS_NE.AZ_RPTDATA_H force; 

drop type APPS_NE.AZ_RPTDATA_V_COLL force;

drop type APPS_NE.AZ_RPTDATA_V_TAGS force;

drop type APPS_NE.AZ_RPTDIFF_H force;      

drop type APPS_NE.AZ_RPTDIFF_V_COLL force;   

drop type APPS_NE.AZ_RPTDIFF_V_TAGS force; 

drop type APPS_NE.AZ_SELSETS_EXT force;    

drop type APPS_NE.AZ_SELSETS_H force;      

drop type APPS_NE.AZ_SELSETS_H_COLL force;  

drop type APPS_NE.AZ_SELSETS_V652_T force; 

drop type APPS_NE.AZ_SELSETS_V_COLL force;  

4. Reset AZ password if it is lost. Then, login as AZ to drop three tables if they exist.

$ FNDCPASS apps/$appPWD 0 Y system/$sysPWD ORACLE AZ azPWD

$ sqlplus az/azPWD

SQL> DROP TABLE AZ_REQUESTS;

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> DROP TABLE AZ_REPORTER_DATA;

SQL> DROP TABLE AZ_DIFF_RESULTS;

5. Run 3 XDF scripts on OS level to create 3 tables and 6 indexes:

$AZ_TOP/patch/115/xdf/az_diff_results.xdf

$AZ_TOP/patch/115/xdf/az_reporter_data.xdf

$AZ_TOP/patch/115/xdf/az_requests.xdf

Use "tnsping $TWO_TASK" to get the database connection info to replace part of below lines.

$ cd $AZ_TOP/patch/115/xdf

$ adjava "-mx128m" "-nojit" "oracle.apps.fnd.odf2.FndXdfCmp" \

"az" "azPWD" "apps" "$appPWD" \

"thin" "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<database-host>)(PORT=<database-port>))(CONNECT_DATA=(SID=<database-SID>)))" \

"table" "az_diff_results.xdf" "$FND_TOP/patch/115/xdf/xsl"

 

$ adjava "-mx128m" "-nojit" "oracle.apps.fnd.odf2.FndXdfCmp" \

"az" "azPWD" "apps" "$appPWD" \

"thin" "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<database-host>)(PORT=<database-port>))(CONNECT_DATA=(SID=<database-SID>)))" \

"table" "az_reporter_data.xdf" "$FND_TOP/patch/115/xdf/xsl"

$ adjava "-mx128m" "-nojit" "oracle.apps.fnd.odf2.FndXdfCmp" \

"az" "azPWD" "apps" "$appPWD" \

"thin" "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<database-host>)(PORT=<database-port>))(CONNECT_DATA=(SID=<database-SID>)))" \

"table" "az_requests.xdf" "$FND_TOP/patch/115/xdf/xsl"

SQL> desc AZ_DIFF_RESULTS

 Name                                      Null?    Type

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

 NAME                                              VARCHAR2(240)

 DISPLAY_NAME                            VARCHAR2(240)

 REQUEST_ID                                NOT NULL NUMBER(15)

 SOURCE                                        NOT NULL VARCHAR2(240)

 TYPE                                               NUMBER(2)

 ID                                                    NOT NULL NUMBER

 PARENT_ID                                      NUMBER

 HASHCODE_DETAILS                     VARCHAR2(2000)

 DEPTH                                              NUMBER

 IS_DIFFERENT                                 VARCHAR2(1)

 IS_TRANSFORMED                          VARCHAR2(1)

 SHOW_ONLY_DIFF                          VARCHAR2(1)

 PARAM1                                             VARCHAR2(240)

 PARAM2                                             VARCHAR2(240)

 PARAM3                                             VARCHAR2(240)

 PARAM4                                             VARCHAR2(240)

 PARAM5                                             VARCHAR2(240)

 DETAIL_LOG_STATUS                    VARCHAR2(255)

 DETAIL_LOG_MSG                         VARCHAR2(2000)

6. Run 3 scripts to restore the iSetup XMLDB Schemas

$ sqlplus apps/$appPWD @$AZ_TOP/patch/115/sql/azhDiffSchema.sql $sysPWD

... ...

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Connected.

declare

*

ERROR at line 1:

ORA-00600: internal error code, arguments: [qmtcolcb_nomatch], [ID],

[annotation], [], [], [], [], [], [], [], [], []

ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 122

ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 106

ORA-06512: at line 5

Fix:  Database has been started with the oracle user having the wrong LIBPATH set. After 19c upgrade, some of the old 12c paths mixed in with new 19c.  The fix requires a database bounce. 

See Doc ID 1292089.1 (Primary Note for Oracle XML Database (XDB) Install / Deinstall)

$ sqlplus apps/$appPWD @$AZ_TOP/patch/115/sql/azhRptSchema.sql $sysPWD

$ sqlplus apps/$appPWD @$AZ_TOP/patch/115/sql/azhSelSetSchema.sql $sysPWD

... ...

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Connected.

declare

*

ERROR at line 1:

ORA-20101: Exception occurred in Selection Set Schema Registration procedure

:ORA-64479: element 'AZ_SELSETS_V' is missing 'SQLType' annotation; schema

cannot be registered by common users

ORA-06512: at line 92

Fix:  Apply patch 31791566

adop phase=apply apply_mode=downtime patches=31791566 patchtop=/.../fs_ne/EBSapps/patch

7. Restore iSetup specific XMLTYPE columns

sqlplus apps/$appPWD @$AZ_TOP/patch/115/sql/azhAddDiffCol.sql az azPWD

sqlplus apps/$appPWD @$AZ_TOP/patch/115/sql/azhAddDriver.sql az azPWD

sqlplus apps/$appPWD @$AZ_TOP/patch/115/sql/azhAddRptCol.sql az azPWD

sqlplus apps/$appPWD @$AZ_TOP/patch/115/sql/azhAddSelSet.sql az azPWD

8. For 12.2 only. All steps have to be done outside of a patching cycle (i.e. prepare has not been run)

SQL> show user

USER is "APPS"

SQL> set serveroutput on

SQL> exec ad_zd_table.patch('AZ','AZ_REQUESTS');

PL/SQL procedure successfully completed.

SQL> exec ad_zd_table.patch('AZ','AZ_REPORTER_DATA');

PL/SQL procedure successfully completed.

SQL> exec ad_zd_table.patch('AZ','AZ_DIFF_RESULTS');

PL/SQL procedure successfully completed.

SQL> desc AZ_DIFF_RESULTS

 Name                                      Null?    Type

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

 NAME                                              VARCHAR2(240)

 DISPLAY_NAME                            VARCHAR2(240)

 REQUEST_ID                                NOT NULL NUMBER(15)

 SOURCE                                        NOT NULL VARCHAR2(240)

 TYPE                                               NUMBER(2)

 ID                                                    NOT NULL NUMBER

 PARENT_ID                                      NUMBER

 HASHCODE_DETAILS                     VARCHAR2(2000)

 DEPTH                                              NUMBER

 IS_DIFFERENT                                 VARCHAR2(1)

 IS_TRANSFORMED                          VARCHAR2(1)

 SHOW_ONLY_DIFF                          VARCHAR2(1)

 PARAM1                                             VARCHAR2(240)

 PARAM2                                             VARCHAR2(240)

 PARAM3                                             VARCHAR2(240)

 PARAM4                                             VARCHAR2(240)

 PARAM5                                             VARCHAR2(240)

 DETAIL_LOG_STATUS                    VARCHAR2(255)

 DETAIL_LOG_MSG                          VARCHAR2(2000)

 ATTR_DIFF                                        SYS.XMLTYPE(XMLSchema "http:

                                                            //isetup.oracle.com/2006/dif

                                                            fresultdata.xsd" Element "H"

                                                            )

SQL> SELECT owner, type_name FROM all_types WHERE type_name LIKE 'AZ%';

OWNER           TYPE_NAME

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

SYSTEM          AZ_RPTDATA_H

SYSTEM          AZ_RPTDATA_V_COLL

SYSTEM          AZ_RPTDATA_V_TAGS

SYSTEM          AZ_RPTDIFF_H

SYSTEM          AZ_RPTDIFF_V_COLL

SYSTEM          AZ_RPTDIFF_V_TAGS

SYSTEM          AZ_SELSETS_EXT

SYSTEM          AZ_SELSETS_H

SYSTEM          AZ_SELSETS_H_COLL

SYSTEM          AZ_SELSETS_V

SYSTEM          AZ_SELSETS_V_COLL

11 rows selected.

9. Provide appropriate grants

$ sqlplus apps/$appPWD  @$AZ_TOP/patch/115/sql/azR12grants.sql $sysPWD

... ...

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Connected.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Commit complete.

iSetup tables have column of data-type "SYS.XMLTYPE". If it becomes invalid, EBS may hit below message. That may require table re-creation also.

PLS-00905: object AZ.AZ_<xxxxx> is invalid 

Apply R12.2 April 2022 CPU patch

We decided to apply April 2022 CPU to EBS. To make patching consistent in both db side and EBS side, DBA applied 2022 April database PSU together with database 19c upgrade. The document for applying April 2022 CPU is 2856621.1. I followed below steps and order to apply it. As always, first of all identify which patches are required and download all patch files to a central location, such as /s46/path/to/CPU_April22

1. Confirm only patch 30399994 was applied:

SQL> select * from ad_bugs where bug_number in (31856779, 30399994);

30399994 Patch R12.ATG_PF.C.delta.9 (Application Technology Family)

31856779 Patch R12.ATG_PF.C.delta.10

2. Identify and decide 9 EBS patches will be applied in this time:

CPU patch:

33782739 RELEASE 12.2: CPU PATCH FOR APR 2022

Post Patching:

33908208:R12.FWK.C (12.2.10 CONSOLIDATED PATCH FOR APR CPU 2022 FIXES FOR FWK)

33207251:R12.FND.C (Application Object Library. Applied as part of 33782739)

32980025:R12.AK.C (Application Framework)

33286000:R12.OAM.C (Oracle Applications Manager)

33625264:R12.OWF.C (Workflow)

Oracle support recommended when troubleshooting oacore (optional):

34005885:R12.FWK.C ORACLE APPL FRAMEWORK (FWK) RELEASE 12.2.10 BUNDLE 9

33659063:R12.FWK.C Image Patch for 12.2.10 Post Consolidated Patch Product OAF

33973100:R12.FND.C - CONNECTION LEAK _PAGES._JSP._FND.__CLOSE._JSPSERVICE 

Patch for a bug when re-creating AZ tables (Doc ID 2816670.1):

p31791566_R12.AZ.C_R12_GENERIC.zip

$ cd $PATCH_TOP

$ cp /s46/path/to/CPU_April22/EBS/*.zip .      (9 zip files)

unzip p33782739_12.2.0_R12_LINUX.zip

unzip p33908208_R12.FWK.C_R12_GENERIC.zip

unzip p32980025_R12.AK.C_R12_GENERIC.zip

unzip p33286000_R12.OAM.C_R12_GENERIC.zip

unzip p33625264_R12.OWF.C_R12_GENERIC.zip

unzip p33659063_R12.FWK.C_R12_GENERIC.zip

unzip p33973100_R12.FND.C_R12_GENERIC.zip

unzip p34005885_R12.FWK.C_R12_GENERIC.zip

unzip p31791566_R12.AZ.C_R12_GENERIC.zip

$ echo $FILE_EDITION

run 

Note: adgrants.sql from patch 33782739 is an older version than the one in $APPL_TOP/admin

$ adop phase=apply apply_mode=downtime patches=33782739 patchtop=$NE_BASE/EBSapps/patch

                                                                                      <== it may take one hour

Post-patch steps (from README):

a) Run autoConfig in RUN file system on all Apps-tie nodes

$ ./adautocfg.sh     

b) $ perl $AD_TOP/bin/admkappsutil.pl

c) Run autoConfig on database node       (Note: I skipped this step)

$ adconfig.sh contextfile=$ORACLE_HOME/.../appsutil/$CONTEXT_NAME.xml

$ adop phase=apply apply_mode=downtime patches=33908208,34005885,33659063,33973100,32980025,33286000,33625264  patchtop=$NE_BASE/EBSapps/patch

$ adop phase=apply apply_mode=downtime patches=31791566 patchtop=$NE_BASE/EBSapps/patch

-- Verify all 10 patches were applied to all nodes

SQL> SELECT adb.bug_number, aas.name appl_top_name, adb.creation_date, adb.language,

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

FROM ad_bugs adb,

(select distinct appltop_id appl_top_id, node_name name from ad_adop_sessions 

           where node_name in (select node_name from ADOP_VALID_NODES) ) aas

where adb.bug_number in (

'33782739',

'33908208',

'34005885',

'33659063',

'33973100',

'33207251',

'32980025',

'33286000',

'33625264',

'31791566'

) order by creation_date desc, adb.bug_number,aas.name,adb.language;

3. Run latest ETCC (patch 17537119). This will help in identifying and confirming what patches will be applied.

4. Apply patches for Oracle WebLogic Server (WLS) 10.3.6.0

$ cd $FMW_HOME/utils/bsu/cache_dir                       

$ cp /s46/path/to/CPU_April22/WLS/*.zip .                   (7 .zip files)

$ mv p33800106_1036_Generic.zip ../.

$ cd ..

$ ls

$ unzip p33800106_1036_Generic.zip      (from 33845432)

$ chmod +x bsu_update.sh

$ bsu.sh -version

$ ./bsu_update.sh install           <== it runs very quick.

Installing Smart Update V5...

Updating bsu modules

Update was successful.

$ bsu.sh -version

$ cd cache_dir

$ unzip -o p33791826_1036_Generic.zip  (33946345 WLS PATCH SET UPDATE 10.3.6.0.220419)

$ unzip -o p13845626_10360220419_Generic.zip  (33812339 CONSOLIDATED FMW FIXES)

$ unzip -o p13964737_10360220419_Generic.zip  (zip file from 33812339)

$ unzip -o p33560682_1036_Generic.zip                (from 33812339)

$ unzip -o p31241365_1036_Linux-x86-64.zip      (from 33812339)

$ unzip -o p31042881_1036_Generic.zip                (from 33812339)

$ cd ..

$ ./bsu.sh -prod_dir=$FMW_HOME/wlserver_10.3 -status=applied -verbose -view | egrep -i "T64V|UCI6|N3YF|9UNH|CW7X|7BIA"

-- it detects conflicts    

$ ./bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -patchlist=T64V -prod_dir=$FMW_HOME/wlserver_10.3

Checking for conflicts..

Conflict(s) detected - resolve conflict condition and execute patch installation again

Conflict condition details follow:

Patch T64V is mutually exclusive and cannot coexist with patch(es): YVDZ,1YWL,HJT5

./bsu.sh -remove -patchlist=YVDZ -prod_dir=$FMW_HOME/wlserver_10.3

./bsu.sh -remove -patchlist=HJT5 -prod_dir=$FMW_HOME/wlserver_10.3

   

./bsu.sh -remove -patchlist=1YWL -prod_dir=$FMW_HOME/wlserver_10.3

$ ./bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -patchlist=T64V -prod_dir=$FMW_HOME/wlserver_10.3

Checking for conflicts..

No conflict(s) detected

Installing Patch ID: T64V..

Result: Success

$ ./bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -patchlist=UCI6 -prod_dir=$FMW_HOME/wlserver_10.3

Checking for conflicts..

No conflict(s) detected

Installing Patch ID: UCI6.

Result: Success

$ ./bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -patchlist=N3YF -prod_dir=$FMW_HOME/wlserver_10.3

Checking for conflicts..

No conflict(s) detected

Installing Patch ID: N3YF..

Result: Success

-- it detects a conflict

$ ./bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -patchlist=9UNH -prod_dir=$FMW_HOME/wlserver_10.3

Checking for conflicts..

Conflict(s) detected - resolve conflict condition and execute patch installation again

Conflict condition details follow:

Patch 9UNH is mutually exclusive and cannot coexist with patch(es): DN1F

$ ./bsu.sh -remove -patchlist=DN1F -prod_dir=$FMW_HOME/wlserver_10.3

Checking for conflicts..

No conflict(s) detected

Removing Patch ID: DN1F..

Result: Success

$ ./bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -patchlist=9UNH -prod_dir=$FMW_HOME/wlserver_10.3

$ ./bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -patchlist=CW7X -prod_dir=$FMW_HOME/wlserver_10.3 

$ ./bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -patchlist=7BIA -prod_dir=$FMW_HOME/wlserver_10.3 

$ ./bsu.sh -prod_dir=$FMW_HOME/wlserver_10.3 -status=applied -verbose -view | egrep -i "T64V|UCI6|N3YF|9UNH|CW7X|7BIA"

Patch ID:               T64V

PatchContainer:    T64V.jar

Patch ID:               UCI6

PatchContainer:    UCI6.jar

                   33791826 , Patch ID :T64V) in the environment

Patch ID:               N3YF

PatchContainer:    N3YF.jar

                   U (Patch Number:33791826 , Patch ID :T64V ) in the environme

Patch ID:               9UNH

PatchContainer:    9UNH.jar

Patch ID:               CW7X

PatchContainer:    CW7X.jar

Patch ID:               7BIA

PatchContainer:    7BIA.jar

5. apply 4 patches for Oracle Fusion Middleware (FMW) 11.1.1.9 OHS - Web Tier

$ echo $IAS_ORACLE_HOME  

$ export ORACLE_HOME=$IAS_ORACLE_HOME

$ echo $ORACLE_HOME

$RUN_BASE/FMW_Home/webtier

$ export PATH=$IAS_ORACLE_HOME/OPatch:$PATH

$ which opatch

$RUN_BASE/FMW_Home/webtier/OPatch/opatch

cd /s46/path/to/CPU_April22/OH          (7 zip files)

Note: when all .zip files are saved in a shared location, only unzip them once for all nodes/instances

$ unzip p33963904_111190_Linux-x86-64.zip  

$ cd 33963904

$ opatch apply -jre $ORACLE_HOME/jdk/jre

Oracle Interim Patch Installer version 11.1.0.12.9

Copyright (c) 2022, Oracle Corporation.  All rights reserved.

... ...

Patching component oracle.ohs2, 11.1.1.9.0...

RollbackSession removing interim patch '31047338' from inventory

OPatch back to application of the patch '33963904' after auto-rollback.

Patching component oracle.ohs2, 11.1.1.9.0...

Verifying the update...

Patch 33963904 successfully applied

$ cd ..

$ unzip p32287205_111190_Linux-x86-64.zip

$ cd 32287205

$ opatch apply

... ...

Patching component oracle.ldap.rsf, 11.1.1.9.0...

Patching component oracle.rdbms.rsf, 11.1.0.7.0...

RollbackSession removing interim patch '31304503' from inventory

OPatch back to application of the patch '32287205' after auto-rollback.

Patching component oracle.ldap.rsf, 11.1.1.9.0...

Patching component oracle.rdbms.rsf, 11.1.0.7.0...

Verifying the update...

Patch 32287205 successfully applied

$ cd ..

$ unzip p32928416_111190_Linux-x86-64.zip

$ cd 32928416

$ opatch apply

... ...

Patching component oracle.opmn, 11.1.1.9.0...

Verifying the update...

Patch 32928416 successfully applied

$ cd ..

$ unzip p33144848_111190_Linux-x86-64.zip

$ cd 33144848

$ opatch apply -jre $ORACLE_HOME/jdk/jre

... ...

Patching component oracle.wlsplugins, 11.1.1.9.0...

Verifying the update...

Patch 33144848 successfully applied

$ opatch lsinventory

Interim patches (17) :

6. apply 2 patches for Oracle Fusion Middleware (FMW) - Oracle Common

$ export ORACLE_HOME=$FMW_HOME/oracle_common

$ export PATH=$ORACLE_HOME/OPatch:$PATH

$ echo $ORACLE_HOME

$FMW_HOME/oracle_common

$ which opatch

$FMW_HOME/oracle_common/OPatch/opatch

$ cd ..

$ unzip p31985571_111190_Generic.zip

$ cd 31985571/oui

$ opatch apply

... ...

Patching component oracle.jrf.adfrt, 11.1.1.9.0...

RollbackSession removing interim patch '30368663' from inventory

OPatch back to application of the patch '31985571' after auto-rollback.

Patching component oracle.jrf.adfrt, 11.1.1.9.0...

Verifying the update...

Patch 31985571 successfully applied

$ cd ../..

$ unzip p26933408_111190_Generic.zip

$ cd 26933408

$ opatch apply

... ...

Patching component oracle.jrf.thirdparty.jee, 11.1.1.9.0...

Verifying the update...

Patch 26933408 successfully applied

$ opatch lsinventory

Interim patches (14) : ...

7. apply 1 patch for Oracle Fusion Middleware 10.1.2.3 - Forms and Reports

Start a new OS session

$ echo $FILE_EDITION

run

$ echo $ORACLE_HOME

$RUN_BASE/EBSapps/10.1.2

$ which opatch

$RUN_BASE/EBSapps/10.1.2/OPatch/opatch

$ cd /aetnas46/oracmprod/R122patch_IFSPGRND/CPU_April22/OH

$ unzip p32922089_101232_LINUX.zip

$ cd 32922089

$ opatch apply

... ...

Removing patch 26825525...

Applying patch 32922089...

OPatch succeeded.

$ opatch lsinventory

44 patches.

8. Run ETCC script again to confirm all required technology patches were applied.

9. Testing and and after signed-off, run FS_CLONE.

$ cd $ADMIN_SCRIPTS_HOME

$ ./adautocfg.sh

$ ./adstrtal.sh

Do a backup on file systems and then run fs_clone. 

$ adop phase=fs_clone

Additional notes:

An Oracle document shows Log4j vulnerabilities are mitigated by April 2022 CPU patches. Seems Oracle collected all remediations into this patch set. Here is the document (as of July 30 2022):

CVE-2021-4104/CVE-2022-23302/CVE-2022-23305/CVE-2022-23307 Advisory for Oracle E-Business Suite (Apache Log4j 1.x Vulnerabilities) (Doc ID 2858304.1)

SYMPTOMS

These vulnerabilities have various conditions of exploitability. For example, to be maliciously exploited vulnerability CVE-2022-23307 requires that the attacker be able to create a malicious log entry and either the attacker or a customer run the Chainsaw component on the targeted system. Because of these various conditions of exploitability and the way the Log4j components are used in Oracle products, the reported CVSS Base Scores for these vulnerabilities do not necessarily reflect the criticality of the issues in the context of their use in an Oracle product. These non-default configurations of Log4j are not used by Oracle E-Business Suite.

CVE-2021-4104 (CVSS 7.5) deserialization of untrusted data in JMSAppender

Vulnerable if: log4j configuration uses JMSAppender (not used by default)

CVE-2022-23302 (CVSS 8.8) deserialization of untrusted data in JMSSink

Vulnerable if: log4j configuration uses JMSSink (not used by default)

CVE-2022-23305 (CVSS 9.8) SQL injection in JDBCAppender

Vulnerable if: log4j configuration uses JDBCAppender (not used by default)

CVE-2022-23307 (CVSS 9.8) Chainsaw GUI logviewer (for log4j's XML output)

Vulnerable if: using chainsaw

PATCHES

The following patches are available to mitigate the Log4j related vulnerabilities. If you find Log4j in other components not listed here, file a service request against the respective components.

Core Oracle E-Business Suite Functionality

Apply the following patches:

• For Oracle E-Business Suite Release 12.2.x with WLS 10.3.6 and FMW 11.1.1.9:

o Apply EBS April 2022 CPU Patch 33782739.

o Apply WLS 10.3.6 overlay Patch 33796519, which is included in Patch 33570839, on top of the 10.3.6 January 2022 PSU. This patch is included in Patch 33946345 EBS REHOSTED: 33791826 WLS PATCH SET UPDATE 10.3.6.0.220419.

o Apply WLS 10.3.6 Smart Update V5 Patch 33845432,  which is also included in Patch 33570839 EBS RELEASE 12.2 CONSOLIDATED FMW FIXES FOR JAN 2022 and subsequent Critical Patch Updates (CPUs).

o Apply FMW 11.1.1.9 Patch 33960746 (to oracle_common).

The following occurrences of log4j jar files are modified by the aforementioned patches:

• FMW_Home/modules/com.bea.core.apache.log4j_1.2.13.jar

• FMW_Home/modules/com.bea.core.apache.log4j_1.1.0.0_1-2-15.jar

• FMW_Home/wlserver_10.3/server/lib/consoleapp/APP-INF/lib/log4j-1.2.17-16.jar

• FMW_Home/wlserver_10.3/server/lib/consoleapp/APP-INF/lib/log4j-1.2.8.jar

• FMW_Home/wlserver_10.3/server/lib/wlstestclient.ear/webapp.war/log4j-1.2.8.jar

• FMW_Home/patch_wls1036/backup/backup.jar/wlstestclient.ear/webapp.war/log4j-1.2.8.jar

• FMW_Home/oracle_common/sysman/archives/fmwctrl/app/em.ear

• FMW_Home/oracle_common/sysman/archives/applications/11_1_1_0_0_emcore.war

• FMW_Home/oracle_common/sysman/jlib/log4j-core.jar

Any occurrences of log4j jar files in these directories can be manually removed:

• All Log4j libraries under the following directories:

o /clone (such as …/EBSapps/comn/clone/)

o /adopclone (such as …/EBSapps/comn/adopclone_xxxxx/)

o /inventory (such as …/EBSapps/10.1.2/ccr/inventory/)

o /patch stage (such as .../bsu/cache_dir/ and .../.patch_storage/)

o FMW_Home/user_projects/domains/EBS_domain/servers/AdminServer/tmp/_WL_user/emcore/28c293/WEB-INF/lib/log4j-core.jar

Shut down the WLS server before removing this file. After a while, the library might come back again which is the patched library.

o Run the following commands to stub out the log4j.jar under the 10.1.2 Oracle home:

     $ cd <ORACLE_HOME>

     $ perl -e print "" > ./sysman/admin/emdrep/lib/log4j.jar

o Run the following commands to stub out the EAR file under the 10.1.2 Oracle home for Oracle E-Business Suite Release 12.2 and 10.1.3 Oracle home for Oracle E-Business Suite Release 12.1:

     $ cd <ORACLE_HOME>

     $ perl -e print "" > ./oc4j/j2ee/oc4j_applications/applications/isqlplus.ear

Our analysis of the following occurrences of log4j v1.x indicates that these can be considered false positives. These instances are false positives since they do not contain the vulnerable classes.

The following Log4j jar files are not impacted by the CVEs listed in this note since they do not contain the vulnerable classes:

• 10.1.2/sysman/jlib/log4j-core.jar

• 10.1.2/OPatch/ocm/ocm.zip/core.jar/payload.zip/log4j-core.jar

• 10.1.2/diagnostics/lib/ojdl-log4j.jar

• FMW_Home/Oracle_EBS-app1/ccr/lib/log4j-core.jar

• FMW_Home/Oracle_EBS-app1/oui/jlib/lib/log4j-core.jar

• FMW_Home/Oracle_OAMWebGate1/oui/jlib/jlib/log4j-core.jar

• FMW_Home/webtier/OPatch/ocm/lib/log4j-core.jar

• FMW_Home/webtier/OPatch/ocm/ocm.zip/log4j-core.jar

• FMW_Home/webtier/OPatch/ocm/ocm.zip/core.jar/payload.zip/log4j-core.jar

• FMW_Home/webtier/ccr/lib/log4j-core.jar

• FMW_Home/webtier/oui/jlib/jlib/log4j-core.jar

• FMW_Home/oracle_common/OPatch/ocm/lib/log4j-core.jar

• FMW_Home/oracle_common/OPatch/ocm/ocm.zip/log4j-core.jar

• FMW_Home/oracle_common/OPatch/ocm/ocm.zip/core.jar/payload.zip/log4j-core.jar

• FMW_Home/oracle_common/modules/oracle.odl_11.1.1/ojdl-log4j.jar

• DBhome/sysman/jlib/ocm/log4j-core.jar

• DBhome/oui/jlib/jlib/log4j-core.jar

• DBhome/oc4j/j2ee/home/applications/ascontrol.ear/ascontrol.war/log4j-core.jar

• DBHome/ccr/lib/log4j-core.jar

• DBHome/javavm/lib/dbhadoop12101.jar

Vulnerability NOTES: 

In another Oracle document ID 2827804.1 (CVE-2021-44228/CVE-2021-45046/CVE-2021-44832/CVE-2021-45105 Advisory for Oracle E-Business Suite (Apache log4j Vulnerabilities), the Core Oracle E-Business Suite Functionality section asks to apply EBS patch 33672402:R12.TXK.C (in early 2022) to mitigate the vulnerability in Oracle E-Business Suite 12.2 associated with 

CVE-2021-44228, 

CVE-2021-45046, 

CVE-2021-44832, 

CVE-2021-45105

It patches file $COMMON_TOP/java/lib/log4j_core.jar.

Add OACORE servers in R12.2

Oracle Doc ID 1905593.1 (Managing Configuration of Oracle HTTP Server and Web Application Services in Oracle E-Business Suite Release 12.2) gives steps on adding oacore process to the instance. See "4.4.1 Adding a new managed server".

Addition of managed servers needs to be done on the RUN file system when there is no active ADOP cycle. During the next adop prepare, the Configuration Change Detector identifies that the addition has been made and the managed servers are automatically synced up from the run file system to the patch file system. The synchronization also gets done when fs_clone is executed.

Most likely, adding CPU and memory to the server are needed to support additional oacore.

1. Backup $CONTEXT_FILE

2. Backup two conf files (in below path or similar. Many conf files resides in this folder)

$ cd  $IAS_ORACLE_HOME/instances/EBS_web_OHS1/config/OHS/EBS_web

mod_wl_ohs.conf

apps.conf

3. Check ADOP session

$ hostname

master_node.domain.com

$ echo $TWO_TASK

EBSDEV

$ adop -status

Enter the APPS password:

Connected.

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

ADOP (C.Delta.12)

Session Id: 5

Command: status

Output: $NE_BASE/EBSapps/log/adop/5/20220X08_115941/adzdshowstatus.out

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

Node Name       Node Type  Phase           Status          Started              Finished             Elapsed

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

2nd_node            slave    FS_CLONE        COMPLETED       2022/02/17 23:05:12  2022/02/18 00:01:37  0:56:25

3rd_node            slave     FS_CLONE        COMPLETED       2022/02/17 23:05:12  2022/02/17 23:59:02  0:53:50

naster_node      master    FS_CLONE        COMPLETED       2022/02/17 23:05:12  2022/02/17 23:39:21  0:34:09

4. Identify port numbers (and how many OACORE server(s) on each node)

$ grep -i oacore_server_ports $RUN_BASE/inst/apps/$CONTEXT_NAME/appl/admin/$CONTEXT_NAME.xml

<oacore_server_ports oa_var="s_oacore_server_ports">oacore_server1:7206</oacore_server_ports>

$ grep -i oacore_server_ports $PATCH_BASE/inst/apps/$CONTEXT_NAME/appl/admin/EBSDEV*.xml

<oacore_server_ports oa_var="s_oacore_server_ports">oacore_server1:7207</oacore_server_ports>

Port 7206 is used for RUN file system and 7207 is used for PATCH file system. Port 7208 is free and is chosen for the new oacore. Also oacore_server1 is running on master node and oacore_server2 is running on 2nd node. Name oacore_server3 as the new server.

5.  Add a new managed server. This will create a managed server and add a new entry to the context file for starting and stopping the new managed server via the adstrtal and adstpall scripts:

$ echo $FILE_EDITION

run

$ echo $APPL_TOP

/u02/app/EBSPROD/fs1/EBSapps/appl    

$ echo $CONTEXT_FILE

$ perl $AD_TOP/patch/115/bin/adProvisionEBS.pl \

        ebs-create-managedserver -contextfile=$CONTEXT_FILE \

        -managedsrvname=oacore_server3 -servicetype=oacore \

        -managedsrvport=7208 -logfile=$APPLRGF/TXK/addMS_oacoreserver3_2022.log

Enter the APPS Schema password:

Enter the WebLogic AdminServer password:

The logfile for this session is located at $APPLRGF/TXK/addMS_oacoreserver3_2022.log

ManagedServer oacore_server3 created successfully.

Note: use the real path and file name for $CONTEXT_FILE in above line

$  egrep -i "Error|Failed|Warning|ORA-" $APPLRGF/TXK/addMS_oacoreserver3_2022.log

Warning: An insecure protocol was used to connect to the

*** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS

Warning: An insecure protocol was used to connect to the

$ grep -i s_oacore_nodes $CONTEXT_FILE

       <oacore_nodes oa_var="s_oacore_nodes">master_node.aetna.com:7206</oacore_nodes>

$ grep -i oacore_server_ports $CONTEXT_FILE

  <oacore_server_ports oa_var="s_oacore_server_ports">oacore_server1:7206,oacore_server3:7208</oacore_server_ports>

By the way, it does not add anything to PATCH file system.

$ grep -i oacore_server_ports $PATCH_BASE/inst/apps/$CONTEXT_NAME/appl/admin/EBSDEV*.xml

<oacore_server_ports oa_var="s_oacore_server_ports">oacore_server1:7207</oacore_server_ports>

Now, starting oacore_server3 shall work:

sh $ADMIN_SCRIPTS_HOME/admanagedsrvctl.sh status oacore_server3

sh $ADMIN_SCRIPTS_HOME/admanagedsrvctl.sh start oacore_server3

sh $ADMIN_SCRIPTS_HOME/admanagedsrvctl.sh status oacore_server3

You are running admanagedsrvctl.sh version 120.14.12020000.12

Enter the WebLogic Admin password:

oacore_server3 is running.

6. Add details of the newly added managed servers into the OHS configuration files mod_wl_ohs.conf and apps.conf on the current node:

$ echo $CONTEXT_FILE

$ perl $FND_TOP/patch/115/bin/txkSetAppsConf.pl \

        -contextfile=$CONTEXT_FILE \

        -configoption=addMS -oacore=node_name.domain.com:7208

Log file shall say mod_wl_ohs.conf and apps.conf were generated successfully.

$ egrep -i "Error|Failed|Warning|ORA-" $APPLRGF/TXK/txkSetAppsConf_*.log

$ cd $ADMIN_SCRIPTS_HOME

$ adapcctl.sh stop

$ adopmnctl.sh status

$ adapcctl.sh start start

$ adopmnctl.sh status

Recycle all services:

$ adstpall.sh apps/appsPWD

$ adstrtal.sh apps/appsPWD

Then, log onto Weblogic console to see two oacore servers are running.

7. Repeat steps on 2nd web/forms node. Skip this on node dedicated for concurrent jobs. 

Before adding more oacore

$ grep -i oacore_server_ports $CONTEXT_FILE

   <oacore_server_ports oa_var="s_oacore_server_ports">oacore_server2:7206</oacore_server_ports>

After adding one more oacore

$ grep -i s_oacore_nodes $CONTEXT_FILE

      <oacore_nodes oa_var="s_oacore_nodes">master_node.aetna.com:7206</oacore_nodes>

Note: it points to master_node on the 2nd node config. This could be the reason for unbalanced number of connections.

$ grep -i oacore_server_ports $CONTEXT_FILE

  <oacore_server_ports oa_var="s_oacore_server_ports">oacore_server2:7206,oacore_server4:7208</oacore_server_ports>

Again, verify it does not do anything on PATCH file system.

$ grep -i oacore_server_ports $PATCH_BASE/inst/apps/$CONTEXT_NAME/appl/admin/EBSDEV*.xml

<oacore_server_ports oa_var="s_oacore_server_ports">oacore_server2:7207</oacore_server_ports>

8.  Summary on oacore name and port:

Node1   RUN  oacore_server1:7206,   oacore_server3:7208

Node2   RUN  oacore_server2:7206,   oacore_server4:7208

After an adop CUTOVER or FS_CLONE, the change will be copied to PATCH file system. Names will be the same but use different ports:

Node1   PATCH  oacore_server1:7207,   oacore_server3:7209

Node2   PATCH  oacore_server2:7207,   oacore_server4:7209

The original idea for adding more oacore servers to our R12.2.10 instance was to address the unbalance of connection loadings between oacore_server1 and oacore_server2. But adding more oacore servers still does not resolve the issue. oacore servers on master node still get much more connections. 

The benefit is that if one OACORE is down or frozen, we can recycle it while the 2nd one is still running. That could reduce downtime on end-users.

UPDATES in 2023:

After we opened a SR with Oracle Support on OACORE connection distribution uneven, Oracle engineer provided an action plan for us to add entries to apps.conf and mod_wl_ohs.conf files which will allow for load balancing of traffic to all OACOREs.

1. Take a backup of your EBS environment

2. Connect to master_node and add oacore_server2 and oacore_server4 to the configuration:

a. Open a terminal window and connect to master node

b. Switch to the apps user

c. Source the apps environment

d. Run the commands below:

$ echo $FILE_EDITION

run

$ echo $APPL_TOP

/u02/app/EBSPROD/fs2/EBSapps/appl     <== a CUSOVER was done after oacore was added 

$ perl $FND_TOP/patch/115/bin/txkSetAppsConf.pl \

-contextfile=<CONTEXT_FILE> \

-configoption=addMS \

-oacore=2nd_node.domain.com:7207

$ perl $FND_TOP/patch/115/bin/txkSetAppsConf.pl \

-contextfile=<CONTEXT_FILE> \

-configoption=addMS \

-oacore=2nd_node.domain.com:7209

e. Restart OHS by running adapcctl.sh

3. Connect to 2nd_node and add oacore_server3 to the configuration:

a. Open a terminal window and connect to 2nd_node

b. Switch to the apps user

c. Source the apps environment

d. Run the below command:

$ perl $FND_TOP/patch/115/bin/txkSetAppsConf.pl \

-contextfile=<CONTEXT_FILE> \

-configoption=addMS \

-oacore=master_node.domain.com:7209

e. Restart OHS by running adapcctl.sh

4. Direct your load balancer team to stop sending web requests to your CONCURRENT MANAGER ONLY node.

5. Retest for the issue

The txkSetAppsConf.pl command should be run after adding apps tier nodes to your EBS environment. This is mentioned in the excerpt below from Doc ID 1383621.1 :

"If any of the managed servers from the newly added node are desired to be part of the cluster configuration on the current node, run the following command to add details of these managed servers into the OHS configuration files mod_wl_ohs.conf and apps.conf on the current node:..."

Before performing action plan from the SR (on master_node), entries in two .conf files: 

$ cd $IAS_ORACLE_HOME/instances/EBS_web_OHS1/config/OHS/EBS_web

$ grep 7209 apps.conf

BalancerMember http://master_node.domain.com:7209/OA_HTML/media

BalancerMember http://master_node.domain.com:7209/OA_HTML/classes

BalancerMember http://master_node.domain.com:7209

$ grep 7209 mod_wl_ohs.conf

WebLogicCluster master_node.domain.com:7207,master_node.domain.com:7209

$ grep 7207 apps.conf

BalancerMember http://master_node.domain.com:7207/OA_HTML/media

BalancerMember http://master_node.domain.com:7207/OA_HTML/classes

BalancerMember http://master_node.domain.com:7207

$ grep 7207 mod_wl_ohs.conf

WebLogicCluster master_node.domain.com:7207,master_node.domain.com:7209

After performing action plan from the SR (on master_node), all entries are balanced with two nodes.

$ grep 7209 apps.conf

   BalancerMember http://master_node.domain.com:7209/OA_HTML/media

   BalancerMember http://2nd_node.domain.com:7209/OA_HTML/media

   BalancerMember http://master_node.domain.com:7209/OA_HTML/classes

   BalancerMember http://2nd_node.domain.com:7209/OA_HTML/classes

   BalancerMember http://master_node.domain.com:7209

   BalancerMember http://2nd_node.domain.com:7209

$ grep 7209 mod_wl_ohs.conf

WebLogicCluster master_node.domain.com:7207,master_node.domain.com:7209,2nd_node.domain.com:7207,2nd_node.domain.com:7209

$ grep 7207 apps.conf

   BalancerMember http://master_node.domain.com:7207/OA_HTML/media

   BalancerMember http://2nd_node.domain.com:7207/OA_HTML/media

   BalancerMember http://master_node.domain.com:7207/OA_HTML/classes

   BalancerMember http://2nd_node.domain.com:7207/OA_HTML/classes

   BalancerMember http://master_node.domain.com:7207

   BalancerMember http://2nd_node.domain.com:7207

$ grep 7207 mod_wl_ohs.conf

WebLogicCluster master_node.domain.com:7207,master_node.domain.com:7209,2nd_node.domain.com:7207,2nd_node.domain.com:7209

Very similar entries in .conf files in 2nd_node, after action plan was performed on 2nd_node.