How to check if your EBS system is on AD/TXK Delta 13

SELECT bug_number, creation_date
FROM   ad_bugs
WHERE  bug_number IN (
  -- AD/TXK Delta 13 driver patch numbers
  '35163625','35163283','35163924','35162879'
)
ORDER BY creation_date DESC;

If no rows are returned, you are not on AD/TXK Delta 13, yet

Where does an Oracle EBS 12.2 appserver save logs from the concurrent worker processes?

Look in the directory $ADOP_LOG_HOME

In here, every session will create its own subfolder. In my case

10  11  2  3  4  5  6  7  8  9

In my case, I had to enter the folder named after session 11.

In here you will find folders named according to exection time, for example

20240916_135516

Inside this folder, you will find folders named according to action, for example "prepare", "cutover", or "apply".

In my case, step inside the "apply" directory and you will find a folder named after your appserver.

Finally, you will find a folder named according to the patch number, for example

36876222_N

with a log directory underneath it.

So the path $ADOP_LOG_HOME/11/20240916_135516/apply/oric-ebsapp01/36876222_N/log is the complete path to your my log directory for the session I am looking for.

What does it mean to run autoconfig on an EBS DB tier?

In a nutshell, it means running adautocfg.sh on the database server.

This is what I did when the listener.ora was missing on one of my EBS servers:

1. Login as the oracle software owner on your server
2. Remove softlink in $TNS_ADMIN called "listener.ora" which points to $TNS_ADMIN/mydb_myserver/listener.ora

cd $TNS_ADMIN
rm listener.ora

3. Create new listener.ora directly under $TNS_ADMIN

cd $TNS_ADMIN
vi listener.ora
-- add the following --
cdb =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = minserver.skead.no)(PORT = 1521))
    )
  )

SID_LIST_cdb =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME= /orasw/oracle/product/19_21)
      (SID_NAME = cdb)
    )
  )


USE_SID_AS_SERVICE_cdb = ON
STARTUP_WAIT_TIME_cdb = 0
CONNECT_TIMEOUT_cdb = 10
TRACE_LEVEL_cdb = ADMIN

LOG_DIRECTORY_cdb = /orasw/oracle/product/19_21/network/admin
LOG_FILE_cdb = cdb
TRACE_DIRECTORY_cdb = /orasw/oracle/product/19_21/network/admin
TRACE_FILE_cdb = cdb
ADMIN_RESTRICTIONS_cdb = ON
SUBSCRIBE_FOR_NODE_DOWN_EVENT_cdb = OFF

# added parameters for bug# 9286476
LOG_STATUS_cdb  =  ON
INBOUND_CONNECT_TIMEOUT_cdb = 60

# ADR is only applicable for 11gDB
DIAG_ADR_ENABLED_cdb  = ON
ADR_BASE_cdb = /orasw/oracle/product/19_21/admin/mydb_myserver

#IFILE=/orasw/oracle/product/19_21/network/admin/mydb_myserver/listener_ifile.ora

4. Run autoconfig

cd $ORACLE_HOME/appsutil/scripts/mydb_myserver/
./adautocfg.sh
Enter the APPS user password:

Result: autoconfig succeeds.

5. Create a new softlink under $TNS_ADMIN

cd $TNS_ADMIN
ln -s mindb_minserver/listener.ora listener.ora

Solution to ERRORMSG: Invalid SYSTEM database user credential when running txkPostPDBCreationTasks.pl as a part of an EBS database upgrade

When upgrading an EBS database from version 12c to 19c, you have to convert to the mulitenant architechture.

Oracle supplies script for this entire operation as outlined in Doc ID 580629.1 "Interoperability Notes: Oracle E-Business Suite Release 12.1 with Oracle Database 19c"

When reaching section 6 "Run the post PDB script" you are supposed to run the txkPostPDBCreationTasks.pl script which updates the PDB information.

I found that this script is essential and it must run to completion. It is restartable, so if it fails for some reason, you can fix the root cause and rerun the script.

Here is the error I ran into. The logfile shows it, and it's simple to understand:

==============================
Inside searchFileContents()...
==============================
log_file: /u01/app/oracle/product/19x00_231017/appsutil/log/TXK_POST_PDB_Fri_Jan_26_19_15_34_2024/validate_system_password.log
pattern: ERROR
================
Pattern found...
================
EXIT STATUS: 1
Invalid SYSTEM database user credentials.
LOG FILE: /u01/app/oracle/product/19x00_231017/appsutil/log/TXK_POST_PDB_Fri_Jan_26_19_15_34_2024/validate_system_password.log.
*******FATAL ERROR*******
PROGRAM : (/u01/app/oracle/product/19x00_231017/appsutil/bin/txkPostPDBCreationTasks.pl)
TIME    : Fri Jan 26 19:18:47 2024
FUNCTION: main::validateSystemSchemaCredentials [ Level 1 ]
ERRORMSG: Invalid SYSTEM database user credentials.
*******FATAL ERROR*******
PROGRAM : (/u01/app/oracle/product/19x00_231017/appsutil/bin/txkPostPDBCreationTasks.pl)
TIME    : Fri Jan 26 19:18:47 2024
FUNCTION: main::validateSystemSchemaCredentials [ Level 1 ]
ERRORMSG: Invalid SYSTEM database user credentials.

So the system password was not set correctly. Note that this is the only error in the logfile. All other tests had passed!

Since this is a non-EBS password, it can be set manually with the alter user statement, which I did.

Log on to the cdb:

export ORACLE_SID=cdb
sqlplus / as sysdba
alter user system identified by by mysecretpassword container=all;

Test your connection, preferrably from the apptier. Log on to the PDB, in this case EBSTEST, using the system account:

sqlplus system@EBSTEST
Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> show user
USER is "SYSTEM"

So the password is now confirmed to be correct.

You can now rerun the script

export ORACLE_SID=cdb

# source the environment
cd $ORACLE_HOME/appsutil
. ./txkSetCfgCDB.env dboraclehome=<your 19c OH path here>

perl $ORACLE_HOME/appsutil/bin/txkPostPDBCreationTasks.pl -dboraclehome=<your 19c OH path here> \
-outdir=<your 19c OH path here>/appsutil/log -cdbsid=cdb -pdbsid=EBSTEST \
-appsuser=<APPSUSER> -dbport=<LISTENING PORT> -servicetype=onpremise

Why is it essential that the script runs to completion, correctly without errors?

First of all, it updates important tables in the APPS schema. During an upgrade to 19c, the UTL_FILE_DIRS are no longer found in the database init.ora file or the spfile. It is saved in the tables v$parameter and v$parameter2.

select name, value from v$parameter where name = 'utl_file_dir';

and 

select name, value from v$parameter where name = 'utl_file_dir';

should yield the same output as the text file found in your $ORACLE_HOME/dbs/EBSTEST_utlfiledir.txt file.

In my case the

$ORACLE_HOME/dbs/EBSTEST_utlfiledir.txt

showed

/sw/oracle/product/temp/EBSTEST
/usr/tmp/EBSTEST
/<oracle_19c_home>/appsutil/outbound/EBSTEST_<myservername>

Second, it creates two environment files in your $ORACLE_HOME that can be used to source the cdb and the PDB environments,respectively. The are called

cdb_<yourservername>.env

EBSTEST_<yourservername>.env

Third, it creates your database server's context file, $ORACLE_HOME/appsutil/EBS_<yourservername>.xml

Without all of the above correctly done, you cannot proceed with running autoconfig on your apptier. It will keep failing until you fix the database-part of your migration.

How to find installed patches through SQL in an EBS database

During an EBS upgrade, I had to check whether or not a specific patch, 8796558, still needed to be applied.
Turned out is no longer relevant; it has been rolled up in cummulative patch sets. See listing below.

set lines 200
col patch_name format a30
 
SELECT DISTINCT a.bug_number,e.patch_name, e.patch_type, TRUNC(c.end_date) applied_Date
FROM ad_bugs a, ad_patch_run_bugs b, ad_patch_runs c, ad_patch_drivers d , ad_applied_patches e
WHERE
a.bug_id = b.bug_id AND
b.patch_run_id = c.patch_run_id AND
c.patch_driver_id = d.patch_driver_id AND
d.applied_patch_id = e.applied_patch_id AND
a.bug_number in
('8796558')
ORDER BY 2 DESC;
 
BUG_NUMBER                     PATCH_NAME                     PATCH_TYPE                     APPLIED_DATE
------------------------------ ------------------------------ ------------------------------ ------------
8796558                        22644544                       PATCH-SET                      04-SEP-19  
8796558                        21236633                       PATCH-SET                      27-MAI-16  
8796558                        19030202                       PATCH-SET                      15-DES-15  
8796558                        17774755                       PATCH-SET                      11-OKT-14  
 
22644544 --> Oracle E-Business Suite Release 12.1.3+ Recommended Patch Collection 5 [RPC5]
21236633 --> Oracle E-Business Suite Release 12.1.3+ Recommended Patch Collection 4 [RPC4]
19030202 --> Oracle E-Business Suite Release 12.1.3+ Recommended Patch Collection 2 [RPC2]
17774755 --> Oracle E-Business Suite Release 12.1.3+ Recommended Patch Collection 1 [RPC1]

Workaround for error adstpall.sh: too few arguments specified when stopping the EBS 12.2 server processes

ebs@ebsserver1.oric.no 35642926]$ $ADMIN_SCRIPTS_HOME/adstpall.sh –skipNM -skipAdmin
 
You are running adstpall.sh version 120.22.12020000.7
 
 
Enter the WebLogic Server password:
adstpall.sh: too few arguments specified.
 
USAGE:  adstpall.sh  [-skipNM] [-skipAdmin] [-nothreading]
        adstpall.sh  -secureapps [-skipNM] [-skipAdmin]
        adstpall.sh -nodbchk [-skipNM] [-skipAdmin]
        adstpall.sh -mode=allnodes
 
 
adstpall.sh: exiting with status 1

Reason: You have copied the text directly from step 5 in the documentation

Turns out the docs use a different dash character than the expected one. The one used in the documentation is most likely copied from MS word or similar, which is interpreted differently than the regular dash character:

Incorrect: –skipNM
Correct:   -skipNM

In other words, the documented command below is incorrect:

$ $ADMIN_SCRIPTS_HOME/adstpall.sh –skipNM -skipAdmin

Workaround: execute it with the correct dash

$ $ADMIN_SCRIPTS_HOME/adstpall.sh -skipNM -skipAdmin

Thanks to Marco DeDecker from Oracle Netherlands for finding and pointing out this error.

Syntax for applying and removing a patch on the WLS server Home in an EBS 12.2 installation

The bsu (or "Smart update") utility:

cd $MW_HOME/utils/bsu
bsu.sh -version
Oracle Smart Update. Version: 3.3.0.0

Here's an example on how to install a patch:

./bsu.sh -remove -patch_download_dir=$MW_HOME/utils/bsu/cache_dir -patchlist=1LRI -prod_dir=$MW_HOME/wlserver_10.3 -verbose -log=install_1LRI.log

Here's an example on how to remove a patch:

./bsu.sh -install -patch_download_dir=$MW_HOME/utils/bsu/cache_dir -patchlist=E7HI -prod_dir=$MW_HOME/wlserver_10.3 -verbose -log=install_E7HI.log

How to use strace to figure out what files are being accessed by a shell script

I had a situation where an ebs-supplied script adstrtal.sh would not start - it kept throwing error

ORA-12541: TNS:no listener

Although $TNS_ADMIN was correctly set, and sqlplus and tnsping would confirm that the database was open and the listener was up, accepting connections on the desired port.

The sqlnet.log file created in the same directory from which I executed adstrtal.sh displayed the connection being attempted:

Fatal NI connect error 12541, connecting to:
 (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=EBS32.oric.no)(CID=(PROGRAM=sqlplus)(HOST=oric-ebsapp-utv.oric.no)(USER=ebs)))
 (ADDRESS=(PROTOCOL=TCP)(HOST=162.20.5.225)(PORT=1521)))

We are not using default port 1521, but a different port.

I then used strace to find the source of the error, like this:

strace -f -o /tmp/strace.out ./adstrtal.sh apps/****

When going through the /tmp/strace.out file, I was pointed in the right direction:

openat(AT_FDCWD, "$INST_TOP/ora/10.1.3/network/admin/EBS32_oric-ebsapp-utv_ifile.ora", O_RDONLY|O_LARGEFILE) = -1 ENOENT (No such file or directory)

Turns out that adstrtal.sh was looking for a tnsnames.ora entry not in the tnsnames.ora in $TNS_ADMIN ($INST_TOP/ora/10.1.2/network/admin) but rather in $INST_TOP/ora/10.1.3/network/admin.

As soon as I had added the correct tnsnames.ora entry, the adstrtall.sh script worked.

What are EBS snapshots?

I found this info in the document Oracle® Applications Maintenance Utilities Release 12.1 Part No. E13676-02 There are two types of snapshots: APPL_TOP snapshotsand global snapshots.

An APPL_TOP snapshot lists patches and versions of files in the APPL_TOP.

A global snapshot lists patches and latest versions of files in the entire Applications system (that is, across all APPL_TOPs).

Both APPL_TOP snapshots and global snapshots may be either current view snapshots or named view snapshots.

A current view snapshot is created once and updated when appropriate to maintain a consistent view.

A partial view snapshot allows you to synchronize only selected files from a current view.

A named view snapshot is a copy of the current view snapshot at a particular time (not necessarily the latest current view snapshot), and is not updated.

Getting ORA-01722 when running adstats.sql after an EBS database upgrade to 19c

An old error from 2015 surfaced today, when we were trying to upgrade a 12.1 EBS database to 19c.

The problem occured during the running of adstats.sql, which should be executed at the end of the installation procedure, right before the database is converted to a PDB in a multitenant architecture:

sqlplus / as sysdba @adstats.sql apps
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 23 16:18:26 2023
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
Koblet til:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
Tilkoblet.
--------------------------------------------------
--- adstats.sql started at 2023-02-23 16:18:26 ---
Checking for the DB version and collecting statistics ...
declare
*
Error on line 1:
ORA-01722: invalid number
ORA-06512: on line 9

The solution is the same as in "ORA-01722 when running ausy1120.sql during preparation of new target database for EBS":

Modify the adstats.sql script slightly and correct a typo. In an editor, change the statement

select
  to_number(substr(version,1,instr(version,'.')))
  into :dbver
  from v$instance
  where rownum=1;

to

select
  to_number(substr(version,1,instr(version,'.')-1))
  into :dbver
  from v$instance
  where rownum=1;

Then rerun the script.

How to avoid error Perl lib version (5.28.1) doesn't match executable version (5.36.0)

When trying to use clone.pl to clone the latest version of the Oracle 19c database, the following error stopped me from proceeding:

/sw/oracle/product/19.18/clone/bin/clone.pl: 
Perl lib version (5.28.1) doesn't match executable '/sw/oracle/product/19.18/perl/bin/perl' 
version (5.36.0) at /sw/oracle/product/19.17/perl/lib/5.28.1/x86_64-linux-thread-multi/Config.pm 
line 62.

Turns out that that the error occured at the following point in my code:

export NEW_ORACLE_HOME=$ORACLE_BASE/product/19.18
export PRE_ORACLE_HOME=$ORACLE_BASE/product/19.17
export NEW_ORACLE_VERSION=19C_230117
su - oracle <<!
cd $NEW_ORACLE_HOME/clone/bin
perl clone.pl ORACLE_HOME=$NEW_ORACLE_HOME ORACLE_HOME_NAME=$NEW_ORACLE_VERSION ORACLE_BASE=$ORACLE_BASE OSDBA_GROUP=dba
!

Cause:
The .bash_profile of the oracle user contained a variable commonly used in EBS database environments:

export PERL5LIB=/sw/oracle/product/19.17/perl/lib/5.28.1:/sw/oracle/product/19.17/perl/lib/site_perl/5.28.1:/sw/oracle/product/19.17/appsutil/perl

and when trying to run the clone.pl program from the new ORACLE_HOME, perl was unable to find the matching libraries.

Solution:
unset the PERL5LIB before running the clone.pl program:

export NEW_ORACLE_HOME=$ORACLE_BASE/product/19.18
export PRE_ORACLE_HOME=$ORACLE_BASE/product/19.17
export NEW_ORACLE_VERSION=19C_230117
su - oracle <<!
cd $NEW_ORACLE_HOME/clone/bin
unset PERL5LIB
perl clone.pl ORACLE_HOME=$NEW_ORACLE_HOME ORACLE_HOME_NAME=$NEW_ORACLE_VERSION ORACLE_BASE=$ORACLE_BASE OSDBA_GROUP=dba
!

What is the ifile directive sometimes used in tnsnames.ora?

My customer is using Oracle E-Business Suite, and during a so-called "autoconfig", some files in the Oracle RDBMS home on the database server is overwritten.

Oracle eBS actually warns the DBA against changing these files manually. Here is an example from the $TNS_ADMIN/tnsnames.ora:

###############################################################
#
# Do not edit settings in this file manually. They are managed
# automatically and will be overwritten when AutoConfig runs.
# For more information about AutoConfig, refer to the Oracle
# E-Business Suite Setup Guide.
###############################################################

Since we are using multitenant architecture, and also use Golden Gate to extract data, we need to tnsnames.ora entries for the Golden Gate setup:

One entry for the pluggable database

One entry for the container database

After the initial setup, before the Golden Gate installation was ready, the $TNS_ADMIN/tnsnames.ora contained only the first of these two entries:

ebsref=
        (DESCRIPTION=
            (ADDRESS=(PROTOCOL=tcp)(HOST=ebs-ref-db01.oric.no)(PORT=1537))
            (CONNECT_DATA=
                (SERVICE_NAME=ebs_ebsref)
                (INSTANCE_NAME=cdb)
            )
        )

This entry is fine for the first of our two required connections. But there was no one supporting a direct connection to the root container, in my case called "cdb".
The listener process already supports a service called "cdb":

Services Summary...
Service "cdb" has 1 instance(s).
  Instance "cdb", status READY, has 1 handler(s) for this service...
Service "ebsref" has 1 instance(s).
  Instance "ebsref", status UNKNOWN, has 1 handler(s) for this service...

But we cannot add the necessary Golden Gate configuration unless we add another entry in the tnsnames.ora file.

How to do this, when we are not supposed to edit the tnsnames.ora because it eventually will be overwritten by autoconfig?

The answer was actually already in the tnsnames.ora file.

Oracle has included an example of how to use an include file in the tnsnames.ora file, so we can add our entries according to our needs. At the bottom of the file, you can see this string:

IFILE=/u01/oracle/sw/19c/network/admin/ebsref_ebs-ref-db01/ebsref_ebs-ref-ref-db01_ifile.ora

The file does not exist, so you need to create it:

vi /u01/oracle/sw/19c/network/admin/ebsref_ebs-ref-db01/ebsref_ebs-ref-ref-db01_ifile.ora

Add the following:

cdb = (DESCRIPTION=
          (ADDRESS=(PROTOCOL=tcp)(HOST=ebs-ref-db01.skead.no)(PORT=1537))
                (CONNECT_DATA= (SERVICE_NAME=cdb)
                (INSTANCE_NAME=cdb))
      )

And you've solved your problem, the connect identifier "cdb" can now be used by Golden Gate connections to the container database. Burleson Consulting has an article about ifile that actually points out eBS as the very reason for the ifile directive:

According to some Oracle experts, the ifile facility was designed for the init.ora file and ifile is not supported for the listener.ora, sqlnet.ora and protocol.ora files, but Oracle eBusiness Suite (EBS) release 11 uses ifile in their parameter files, ostensibly for reasons of customizations. With a ifile, you can leave the original file intact and not wonder what was added later.

How to solve "Can't locate TXK/ARGS.pm in @INC (you may need to install the TXK::ARGS module)" when running txkChkPDBCompatability.pl

Background:

As a part of converting your non-CDB Oracle database to a pluggable database, you are supposed to run the perl script txkChkPDBCompatability.pl

Problem:

The script fails with:

Can't locate TXK/ARGS.pm in @INC (you may need to install the TXK::ARGS module) (@INC contains: $ORACLE_HOME/perl/lib/5.28.1/x86_64-linux-thread-multi $ORACLE_HOME/perl/lib/5.28.1 $ORACLE_HOME/perl/lib/site_perl/5.28.1/x86_64-linux-thread-multi $ORACLE_HOME/perl/lib/site_perl/5.28.1 $ORACLE_HOME/perl/lib/site_perl/5.28.1/x86_64-linux-thread-multi /$ORACLE_HOME/perl/lib/site_perl/5.28.1 $ORACLE_HOME/perl/lib/5.28.1/x86_64-linux-thread-multi $ORACLE_HOME/perl/lib/5.28.1) at ./txkChkPDBCompatability.pl line 61.
BEGIN failed--compilation aborted at ./txkChkPDBCompatability.pl line 61.

Cause:

The PERL5LIB environment variable is not complete. You need to add the location of the ADK and TXK directories, which in turn contains several .pm files.
These folders can be found in $ORACLE_HOME/appsutil/perl

Solution:

Add the $ORACLE_HOME/appsutil/perl to your existing PERL5LIB variable:

export PERL5LIB=$PERL5LIB:$ORACLE_HOME/appsutil/perl

Thanks to the author of Punitoracledba for providing the solution after lots of troubleshooting.

To see the note used to convert a non-cdb EBS database to a pluggable database, go to Doc ID 2552181.1 at Oracle Support

How to check the code level currently in use in an EBS database

To find the E-Business Suite and AD/TXK patch levels, log into the EBS container and use the following two queries:

col abbreviation format a20
col name format a40
col codelevel format a20
select  ABBREVIATION, NAME, codelevel FROM apps.AD_TRACKABLE_ENTITIES where abbreviation in ('txk','ad')

ABBREVIATION         NAME                                     CODELEVEL
-------------------- ---------------------------------------- --------------------
ad                   Applications DBA                         C.11
txk                  Oracle Applications Technology Stack     C.11

select release_name from apps.FND_PRODUCT_GROUPS;

RELEASE_NAME
------------
12.2.9

Workaround for ORA-65011 in an upgraded EBS database running multitenant

If you are converting a non-cdb EBS database to a pdb in a multitenant setup, and your non-cdb had a name that used lower case ("pdb1" instead of "PDB1") the different configurationscripts that you will run during the upgrade will set the hidden parameter _pdb_name_case_sensitive to TRUE.

If you try to connect to your pdb as you normally would, you will see this error:

SYS@cdb SQL>  alter session set container=pdb1;
ERROR:
ORA-65011: Pluggable database PDB1 does not exist.

Check the setting of _pdb_name_case_sensitive:

SYS@cdb SQL> show parameter pdb

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_pdb_name_case_sensitive             boolean     TRUE

To connect, enclose your pdb name with double quotation marks:

SYS@cdb SQL> alter session set container="pdb1";

Session altered.

SYS@cdb SQL> show con_name

CON_NAME
------------------------------
pdb1
SYS@cdb SQL> show con_id

CON_ID
------------------------------
3

The note ORA-65011: Pluggable Database Does Not Exist (Doc ID 2642230.1) from Oracle Support confirms this finding.

How to solve "FAILED: file csrrsreg.sql on worker " when applying RPC5 to an EBS 12.1.3 installation

During a patching of EBS 12.1.3 to RPC5, I encountered an unexpected problem.

A while into the patching, the patching procedure failed with an error message like this:

Deferred: file csrrsreg.sql on worker  1 for product csr username CSR.               (Deferment number 1 for this job)
 Assigned: file csrrsreg.sql on worker  1 for product csr username CSR.
   FAILED: file csrrsreg.sql on worker  1 for product csr username CSR.
 Deferred: file csrrsreg.sql on worker  1 for product csr username CSR.               (Deferment number 2 for this job)
 Assigned: file csrrsreg.sql on worker  1 for product csr username CSR.
   FAILED: file csrrsreg.sql on worker  1 for product csr username CSR.

ATTENTION: All workers either have failed or are waiting:

           FAILED: file csrrsreg.sql on worker  1.

ATTENTION: Please fix the above failed worker(s) so the manager can continue.

Open another ssh session to the server.

Check the logfile for the worker:

cd $APPL_TOP/admin/$ORACLE_SID/log
ls -la adwork*.log

-rw-r--r-- 1 ebsadm dba 123777231 Jun  5 12:58 adwork001.log
-rw-r--r-- 1 ebsadm dba 115207185 Jun  5 12:58 adwork002.log
-rw-r--r-- 1 ebsadm dba 119484153 Jun  5 12:58 adwork003.log
-rw-r--r-- 1 ebsadm dba 119051681 Jun  5 12:57 adwork004.log

Check the logfile for worker 1:

vi adwork001.log

The content could look something like this:

Creating the XML Schema Directory
CSR Product Top is 

PL/SQL procedure successfully completed.

Registing Rules XML Schema Document
ERROR:
ORA-06502: PL/SQL: numeric or value error: host bind array too small
ORA-06512: at line 1

Your workers are now waiting.
You can see their status by using the utility "adctrl".
Open another ssh session.
Execute adctrl, and answer the questions along the way.
At the end, you'll see a menu like this:

AD Controller Menu
     ---------------------------------------------------

     1.    Show worker status

     2.    Tell worker to restart a failed job

     3.    Tell worker to quit

     4.    Tell manager that a worker failed its job

     5.    Tell manager that a worker acknowledges quit

     6.    Restart a worker on the current machine

     7.    Exit

Enter your choice [1] : 1

        Control
Worker  Code      Context            Filename                    Status
------  --------  -----------------  --------------------------  --------------
     1  Run       AutoPatch R120     csrrsreg.sql                FAILED
     2  Run       AutoPatch R120                                 Wait
     3  Run       AutoPatch R120                                 Wait
     4  Run       AutoPatch R120                                 Wait

The solution to this particular error can be found in the note 1263895.1 "Advanced Scheduler Error:' ORA-06502: PL/SQL: numeric or value error: host bind array too small' In CSRRSREG.sql When Upgrading To 12.1.3".
You need to pull up the file csrrsreg.sql in a text editor and change

dbms_output.put_line('Registering Scheduler Rules XML Schema failed with an error ' || SQLERRM);

to

dbms_output.put_line(SUBSTR(('Registering Scheduler Rules XML Schema failed with an error ' || SQLERRM), 1, 250));

The file is placed in the patch stage area 22644544/csr/patch/115/sql, but it is copied to $APPL_TOP/csr/12.0.0/patch/115/sql during installation.
So I changed the file in both places, in case of a rerun.

Then, according to note 1263895.1, run the $APPL_TOP/csr/12.0.0/patch/115/sql/csrrsreg.sql manually.
Open the file first, and read the instructions. You need the passowrds for APPS and CSR, as well as the value for $CSR_TOP.
Run it and supply the parameters as they are requested by the script:
$1 = 'APPS'
$2 = password for APPS
$3 = 'CSR'
$4 = password for CSR
$5 = value for $CSR_TOP

sqlplus /nolog @/u01/app/apps/apps_st/appl/csr/12.0.0/patch/115/sql/csrrsreg.sql

Go back to your session where you are running adctrl.
Restart the worker process:

AD Controller Menu
     ---------------------------------------------------

     1.    Show worker status

     2.    Tell worker to restart a failed job

     3.    Tell worker to quit

     4.    Tell manager that a worker failed its job

     5.    Tell manager that a worker acknowledges quit

     6.    Restart a worker on the current machine

     7.    Exit

Enter your choice [1] : 2

Enter the worker number(s)/range(s) or 'all' for all workers,
or press [Return] to go back to the menu : 1

Status changed to 'Fixed, restart' for worker 1.

You should now check the worker status again:

Review the messages above, then press [Return] to continue.

                    AD Controller Menu
     ---------------------------------------------------

     1.    Show worker status

     2.    Tell worker to restart a failed job

     3.    Tell worker to quit

     4.    Tell manager that a worker failed its job

     5.    Tell manager that a worker acknowledges quit

     6.    Restart a worker on the current machine

     7.    Exit

Enter your choice [1] : 1

        Control
Worker  Code      Context            Filename                    Status
------  --------  -----------------  --------------------------  --------------
     1  Run       AutoPatch R120     pa_inv_exception_rpt_tbl_t  Running
     2  Run       AutoPatch R120     pa_perid_profile_tbl_typ.x  Assigned
     3  Run       AutoPatch R120                                 Wait
     4  Run       AutoPatch R120                                 Wait

Review the messages above, then press [Return] to continue.

Enter your choice [1] : 1


        Control
Worker  Code      Context            Filename                    Status
------  --------  -----------------  --------------------------  --------------
     1  Run       AutoPatch R120     BEN_REP_ACTIONS_TAB.xdf     Running
     2  Run       AutoPatch R120     BEN_REP_ARCHIVE_TAB.xdf     Running
     3  Run       AutoPatch R120     BEN_DPNT_SICVRD_GT_TAB.xdf  Running
     4  Run       AutoPatch R120     BEN_ACA_CBR_TAB.xdf         Running

The workers are proceeding as before.
If you check your initial ssh session where you run the patching, things should move along.

What is the admsi.pl utility that is used during EBS patching?

Thanks to the blog post by Atul Kumar for explain this.
It's a tool for generating instance-specific patch installation instructions.

This tool was refered to in the beginning of a readme-file for a particular patch. The readme-file stated:

Instructions For Applying This Patch
Execute the following command to generate your instance specific installation instructions Source the Applications environment file Run the Patch Application Assistant by entering "admsi.pl".

A bit further down in the same readme-file:

Apply The Patch

For 12.0.X / 12.1.X / pre-upgrade patches (using adpatch), you must shut down all Application tier services before performing the tasks in this section. For 12.2.X patches (using adop), you can perform the tasks in this section without shutting down the Application tier services. Apply patch [required] This patch contains the following unified driver file to be applied with AutoPatch: u28389569.drv (This is the unified driver)

$ perl $AD_TOP/bin/admsi.pl -patch_top=/software/EBS/28389569
Gathering Information..
Enter the APPS user password:
Logfile for this session is located at admsi.log
Generating installation instructions for patch 28389569..
Updating database....
install_28389569.html generated successfully
install_28389569.txt generated successfully

The generated files will contain the exact patching instructions.

A list of eBS users

To view the users that are installed as a part of an eBS installation, use the following query:

SELECT EBS.ORACLE_USERNAME,U.PROFILE 
FROM APPS.FND_ORACLE_USERID EBS JOIN DBA_USERS U
ON (EBS.ORACLE_USERNAME = U.USERNAME);

How to change passwords for eBS schemas in an Oracle database

In an Oracle database supporting eBS, there are a number of schemas that supports the different modules in eBS. Everything is installed and must be maintained, regardless if the module is in use or not.
If you want to change the passwords for these accounts, you need to do that through the utility AFPASSWD or the deprecated FNDCPASS utility.

Requirements:

* The environment variable TWO_TASK must be set on the eBS application server
* The SYSTEM password for your eBS database
* The current APPS password

I strongly recommend using the same password for all eBS schemas.
Unfortunately, you cannot use special characters in the passwords, so you need to compensate this by making them at least 12-20 characters lang.

To change the passwords, use the following procedure:

1. logon to your eBS application server
2. change user to the owner of the software installation
3. shut down all eBS processes
4. take backup of FND_USER and FND_ORACLE_USERID tables (you can use CTAS for this)

connect apps/oldpassword
create table FND_USER_BUP AS SELECT * FROM FND_USER;
create table FND_ORACLE_USERID_BUP AS SELECT * FROM FND_ORACLE_USERID;

5.

cd $FND_TOP/bin

6. Change the password for all type 3 passwords (meaning all eBS schemas except APPS, APPLSYS,APPLSYSPUB)

./AFPASSWD -c APPS@$TWO_TASK -a

7. Change the passwords for type 2 schema password

./AFPASSWD -c APPS@$TWO_TASK -s APPS

8. Change the password for type 2 schema password APPLSYS

./AFPASSWD -c APPS@$TWO_TASK -s APPLSYS 

9. Change the password for type 2 schema password for APPLSYSPUB. Note that the APPLSYSPUB password must be uppercase, even if you have enabled case sensitive passwords

./AFPASSWD -c APPS@$TWO_TASK -o APPLSYSPUB

10. Update the s_gwyuid_pass variable in the AutoConfig context file to reflect the new password

vi $CONTEXT_FILE

Change from

<oa_user type="GWYUID">
            <username oa_var="s_gwyuid_user">APPLSYSPUB</username>
            <password oa_var="s_gwyuid_pass">PUB</password>
         </oa_user>

to

<oa_user type="GWYUID">
            <username oa_var="s_gwyuid_user">APPLSYSPUB</username>
            <password oa_var="s_gwyuid_pass">YOURNEWSECRETCOMPLICATEDPASSWORD</password>
         </oa_user>

11. verify that the users now have a new password:

sqlplus /nolog
SQL> connect APPS/YOURNEWSECRETCOMPLICATEDPASSWORD
Connected.
SQL> show user
USER is "APPS"

12. start your eBS application processes

Here is a query that will extract the description of each schema, and when the password was last changed:

SELECT ORACLE_USERNAME,LAST_UPDATE_DATE, DESCRIPTION
FROM FND_ORACLE_USERID
WHERE ORACLE_USERNAME IN ('APPS','APPLSYSPUB','APPLSYS','AR','GMO','PFT')
ORDER BY LAST_UPDATE_DATE;

Output:

ORACLE_USERNAME	LAST_UPDATE_DATE	DESCRIPTION
AR	15.12.2016 16:28:45	Oracle Receivables Account
GMO	15.12.2016 16:28:47	Oracle Manufacturing Execution System for Process Manufacturing Account
PFT	15.12.2016 16:28:47	Oracle Profitability Manager Account
APPLSYS	15.12.2016 16:30:34	Application Object Library Account
APPS	15.12.2016 16:30:34	APPS #1 Account
APPLSYSPUB	15.12.2016 16:31:09	Application Object Library Public Account

For a complete list, leave out the WHERE-clause.

ORA-01722 when running ausy1120.sql during preparation of new target database for EBS

If you are following note Doc ID 741818.1 "Export/import process for 12.0 or 12.1 using 11gR1 or 11gR2" and run into the follwing problem when running the ausy1120.sql script:

sqlplus system/*** @ausy1120.sql

SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 2 14:19:31 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option


PL/SQL procedure successfully completed.

declare
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 5

the solution is to modify the script slightly and correct a typo.
Open the ausy1120.sql file in an editor and change

select
  to_number(substr(version,1,instr(version,'.')))
  into :dbver
  from v$instance
  where rownum=1;

to

select
  to_number(substr(version,1,instr(version,'.')-1))
  into :dbver
  from v$instance
  where rownum=1;

Then rerun the script. It should finish almost immediately and the output should be similar to:

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

--------------------------------------------------------------------------------
--- ausy1120 started at 02-NOV-2015 14:40:04 ---


'---AUSY1120COMPLETEDAT'||TO_CHAR(SYSDATE,'DD-MON-YYYYHH24:MI:SS')||'----'
--------------------------------------------------------------------------------
--- ausy1120 completed at 02-NOV-2015 14:40:04 ----

Commit complete.

The same error seems to occur when running the aujv1120.sql and the aumsc1120.sql as well. The solution is the same for all three.