eBS 11i: Generating custom library

During an e-Business suite cross-platform migration, not all custom .plx get generated automatically using adadmin.

To take care of the the extra .plx, left behind once adadmin as completed, proceed as follow, for example to generate 2 days older .plx files:

for i in `find $AU_TOP/resource -name "*.plx" -mtime +2 -print`;
  filename=$(basename "$i")
  echo "Processing $prefix"
  $ORACLE_HOME/bin/f60gen module=$AU_TOP/resource/${prefix}.pll userid=APPS/<apps> output_file=$AU_TOP/resource/${prefix}.plx module_type=library compile_all=special


Then check that all libraries .plx in $AU_TOP/resources are all generated, looking at their timestamp.


eBS 11i: Enabling Forms Runtime Diagnostics Trace

To enable forms diagnostics with e-business 11i, in a situation where forms does not open, for example after a platform migration, proceed as follow:

First check that value for the ICX:Forms launcher profile option, probably with the ids specified below. Correct as you go, depending the version and the environment history:

SELECT profile_option_value
FROM fnd_profile_option_values
WHERE level_id            = 10001
  AND profile_option_id   = 3769
  AND level_value         = 0


Then update the profile option, at the site level, for convenience.

UPDATE fnd_profile_option_values
SET profile_option_value = 'http://<ebs_url>/dev60cgi/f60cgi?record=all'||CHR(38)||'log=record.log'
WHERE level_id           = 10001
 AND profile_option_id   = 3769
 AND level_value         = 0


Then launch a forms and check that there is a record.log trace file in $ORACLE_HOME/forms60/log

Once your troubleshooting time is done, revert the profile option to the original value.



eBS 11i: ORA-01422 in SYSTEM.AD_DDL line 2280

While troubleshooting a conversion of e-Business suite 11i from AIX to Solaris, I noticed a few ORA-1422 errors for

sqlplus -s APPS/***** @<appl_top>/ad/11.5.0/patch/115/sql/adctxpkg.sql &systempwd CTXSYS &un_apps

PL/SQL procedure successfully completed.

ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYSTEM.AD_DDL", line 2280
ORA-06512: at line 215


The root cause looked like a corruption of the FND_ORACLE_USERID table, most likely a few years old datafix or user error, with the following query probably improperly returning more than one record.

select * from fnd_oracle_userid where install_group_num=1 and read_only_flag='U'


With no time to troubleshoot further, I bypassed the error by discarding the wrong records with:

update fnd_oracle_userid set read_only_flag='Z' where oracle_id between 20043 and 20047;

eBS and RAC: Creating cluster database services

It is a good practice to create dedicate cluster services for eBS environments running on RAC:

export LDB=ebsdb

srvctl add service -d ${LDB} -s ${LDB}_forms  -r ${LDB}1,${LDB}2
srvctl add service -d ${LDB} -s ${LDB}_ssa    -r ${LDB}1,${LDB}2
srvctl add service -d ${LDB} -s ${LDB}_batch  -r ${LDB}1,${LDB}2
srvctl add service -d ${LDB} -s ${LDB}_batcha -r ${LDB}1 -a ${LDB}2
srvctl add service -d ${LDB} -s ${LDB}_batchb -r ${LDB}2 -a ${LDB}1

srvctl start service -d ${LDB} -s ${LDB}_forms
srvctl start service -d ${LDB} -s ${LDB}_ssa
srvctl start service -d ${LDB} -s ${LDB}_batch
srvctl start service -d ${LDB} -s ${LDB}_batcha
srvctl start service -d ${LDB} -s ${LDB}_batchb

SOA 12c: Finding flow and instances names and titles

To identify via SQL the latest flow and instance titles, run the following query:

SELECT f.flow_id
FROM   sca_flow_instance         f
      ,sca_flow_to_cpst          c
WHERE  c.flow_id                 = f.flow_id
       f.flow_id      DESC
      ,c.composite_id DESC

Also including the composite type:

SELECT f.flow_id
FROM   sca_flow_instance         f
      ,sca_flow_to_cpst          c
      ,sca_entity                r
WHERE  c.flow_id                 = f.flow_id
  AND  c.composite_sca_entity_id = r.id
       f.flow_id      DESC
       c.composite_id DESC