SQL to list all users having an responsibility in R12.1

Below script will list the users who has System Administrator responsibility. 

SELECT fuser.user_name           "User ID",
       fuser.description                   "User Name",
       frt.responsibility_name         "Responsibility Name",
       furgd.start_date                    "Start Date",
       furgd.end_date                     "End Date",
       fresp.responsibility_key      "Responsibility Key",
       fapp.application_short_name "Application Short Name"
  FROM fnd_user_resp_groups_direct furgd,
       fnd_user                             fuser,
       fnd_responsibility               fresp,
       fnd_responsibility_tl          frt,
       fnd_application                  fapp,
       fnd_application_tl              fat
 WHERE furgd.user_id = fuser.user_id
   AND furgd.responsibility_id = frt.responsibility_id
   AND fresp.responsibility_id = frt.responsibility_id
   AND fapp.application_id = fat.application_id
   AND fresp.application_id = fat.application_id
   AND frt.language = USERENV('LANG')
   AND UPPER(frt.responsibility_name) = 'SYSTEM ADMINISTRATOR'
   AND (furgd.end_date IS NULL OR furgd.end_date >= TRUNC(SYSDATE))

   AND (fuser.end_date IS NULL OR fuser.end_date >= TRUNC(SYSDATE))  -- Active users
 ORDER BY frt.responsibility_name;

Similarly, same query can find all responsibilities for a user:

SELECT fuser.user_name             "User ID",

       fuser.description                     "User Name",
       frt.responsibility_name          "Responsibility Name",
       furgd.start_date                      "Start Date",
       furgd.end_date                       "End Date",
       fresp.responsibility_key         "Responsibility Key",
       fapp.application_short_name "Application Short Name"
  FROM fnd_user_resp_groups_direct furgd,
       fnd_user                        fuser,
       fnd_responsibility         fresp,
       fnd_responsibility_tl     frt,
       fnd_application             fapp,
       fnd_application_tl         fat
 WHERE furgd.user_id = fuser.user_id
   AND furgd.responsibility_id = frt.responsibility_id
   AND fresp.responsibility_id = frt.responsibility_id
   AND fapp.application_id = fat.application_id
   AND fresp.application_id = fat.application_id
   AND frt.language = USERENV('LANG')

   AND UPPER(fuser.user_name) = UPPER('&Enter_User_Name')

   -- AND UPPER(frt.responsibility_name) = 'SYSTEM ADMINISTRATOR'

   AND (furgd.end_date IS NULL OR furgd.end_date >= TRUNC(SYSDATE))
 ORDER BY frt.responsibility_name;

Upgrade JDK to JDK 7 in EBS R12.1

There are three parts in upgrading JDK (Java Development Kit) on server for EBS R12.1.3. The document I followed on this upgrade is Doc ID 1467892.1 (Using JDK 7.0 Latest Update with Oracle E-Business Suite Release 12.0 and 12.1).

First, check current JDK/Java version is old (Doc ID 468311.1) :
$ sh -c "`awk -F= '$1 ~ /^JSERVJAVA.*$/ {print $2}' $ADMIN_SCRIPTS_HOME/java.sh` -version;"
java version "1.6.0_17"
Java(TM) SE Runtime Environment (build 1.6.0_17-b04)
Java HotSpot(TM) Server VM (build 14.3-b01, mixed mode)

Note client JRE version is a different thing:
$ cat $FORMS_WEB_CONFIG_FILE|grep sun_plugin_version| cut -c 1-35
sun_plugin_version=1.8.0_152

1) Upgrading to JDK 7.0 on Application Tier 10.1.3 Oracle Home

Upgrading to JDK 7.0 requires Oracle Application Server 10.1.3.5 or higher for the web tier.
By default when R12.1.1 was installed, Web Tier got 10.1.3.4. To upgrade it to 10.1.3.5 follow Doc ID 454811.1 (Upgrading to the Latest OracleAS 10g 10.1.3.x Patch Set in Oracle E-Business Suite Release 12).

1. Download JDK 7.0
I downloaded JDK 1.7.0_231 from a link in Doc ID 1439822.1 (Java SE Downloads on My Oracle Support Knowledge).
Oracle JDK 7 Update 231 - Patch 29657331 (32 bit): p29657331_170231_LINUX.zip => jdk-7u231-linux-i586.tar.gz

2. Before upgrading Application Tier nodes to JDK 7.0,  apply pre-patches.

    Patch 17309237  (prerequisite: '17932167' -- R12.TXK.B.delta.3)

    Patch 16545472  ('9239089' -- R12.AD.B.delta.3, '8919491' -- R12.ATG_PF.B.delta.3)
    Patch 16496713:R12.POS.B  (If Payables is used, it is needed & it's safe to apply it)

Below query returns 3 rows for me, indicating their prerequisites were installed
SQL> select * from ad_bugs where bug_number in (
'16545472',

'17309237',

'16496713',

'17932167', -- R12.TXK.B.delta.3 

'9239089',   -- R12.AD.B.delta.3
'8919491',   -- R12.ATG_PF.B.delta.3 
'19671435', -- RHEL7 may need it. Not for JDK upgrade.
'19863797'  -- RHEL7 may need it. Not for JDK upgrade.
);

-- stop apps services
$ cd $ADMIN_SCRIPTS_HOME
$ ./adstpall.sh apps/apps
$ ./adadmin

$ cd /u01/app/patchJDK7          <= where all .zip files located
$ -- unzip p16545472_R12.OAM.B_R12_GENERIC.zip
$ cd 16545472
$ ls
$ adpatch

$ cd ..
$ -- unzip p17309237_R12.TXK.B_R12_GENERIC.zip
$ cd 17309237
$ ls
$ adpatch

3. Replace JDK in EBS 10.1.3 HOME
$ echo $IAS_ORACLE_HOME
point to /path/apps/tech_st/10.1.3

$ cd ..
$ -- unzip p29657331_170231_LINUX.zip
$ tar vzxfp jdk-7u231-linux-i586.tar.gz

-- replace old JDK 1.6 Home used by Oracle E-Business Suite R12.1
$ cd $IAS_ORACLE_HOME/appsutil
$ mv jdk jdk_old_for_7
$ mv /u01/app/patchJDK7/jdk1.7.0_231 jdk
$ ls -ald jdk*        <= if jdk64 exists, rename it.

$ cd $IAS_ORACLE_HOME/jre
$ mv 1.4.2 1.4.2_old_for_7
$ ls -al $IAS_ORACLE_HOME/jre

-- copy 5 EBS font files to the new JDK
$ ls -al $FND_TOP/resource/ALB*.ttf
$ ls $IAS_ORACLE_HOME/appsutil/jdk/jre/lib/fonts
$ cp -p $FND_TOP/resource/ALB*.ttf $IAS_ORACLE_HOME/appsutil/jdk/jre/lib/fonts/.
$ ls $IAS_ORACLE_HOME/appsutil/jdk/jre/lib/fonts

2) Upgrade to JDK 7.0 in OracleAS 10.1.2 Oracle_Home

1. apply pre patch
$ echo $ORACLE_HOME
point to /path/apps/tech_st/10.1.2

== patch 12848228
$ cd /u01/app/patchJDK7
$ opatch lsinventory
$ opatch lsinventory | grep 5659594    -- show 5659594 was installed before

$ -- unzip p12848228_10123_GENERIC.zip
$ cd 12848228
$ opatch apply

2. Replace old JDK 1.4.2 Home     
$ cd ..
$ tar vzxfp jdk-7u231-linux-i586.tar.gz
$ cd $ORACLE_HOME
$ mv jdk jdk_old_for_7
$ mv /u01/app/patchJDK7/jdk1.7.0_231 jdk
$ mv $ORACLE_HOME/jre/1.4.2 $ORACLE_HOME/jre/1.4.2_old_for_7

$ ls -ald $ORACLE_HOME/jdk*    <= if jdk64 exists, rename it
$ ls -al $ORACLE_HOME/jre

Note: upto this step, "opatch lsinventory" (opatch version 1.0.0.0.63) gets warning on liboraInstaller.so
   
3. Apply 5 patches to 10.1.2 Home in below sequence:
== patch 16271876
$ cd /u01/app/patchJDK7
$ -- unzip p16271876_10123_LINUX.zip
$ cd 16271876
$ opatch apply

$ cd $ORACLE_HOME/lib/stubs
$ ln -s libjvm-1.7-stub.so libjvm.so
$ ls -al libjvm.so

== patch 17907988
$ cd /u01/app/patchJDK7
$ -- unzip p17907988_10123_LINUX.zip
$ cd 17907988
$ opatch apply   <-- it rolls back subset patch 7121788

$ chmod +x $ORACLE_HOME/bin/genshlib
$ ls -al $ORACLE_HOME/bin/genshlib

== patch 17653437
$ cd ..
$ -- unzip p17653437_10123_LINUX.zip
$ cd 17653437
$ ls
$ opatch apply
      <-- it rolls back conflicting patch 6995251 (answer: N (not STOP). Doc ID 1921974.1 )

$ cd $ORACLE_HOME/forms/lib
$ make -f ins_forms.mk sharedlib install       <= Rebuild Forms executables

== patch 17645157
$ cd /u01/app/patchJDK7
$ -- unzip p17645157_10123_LINUX.zip
$ cd 17645157
$ opatch apply

$ cd $ORACLE_HOME/reports/lib
$ make -f ins_reports.mk install                 <= Rebuild Reports executables

== patch 16241466
$ cd /u01/app/patchJDK7
$ -- unzip p16241466_10123_LINUX.zip
$ cd 16241466
$ opatch apply   <-- It rolls back subset patch 8551790

$ opatch lsinventory   -- confirm 6 new patches installed

4. Re-generate Oracle E-Business Suite Forms and Reports
$ adadmin --> 1 --> 2 & 3
   AND disable maintenance mode

The following Oracle Forms objects did not generate successfully:
igi     forms/US        IGIRRMSC.fmx
I ignored it. (Doc ID 2206725.1: Ignore the issue if you do not use Public Sector Financials International. It will not affect your system.)

5. Verify
$ $ADJVAPRG -version
java version "1.7.0_231"
Java(TM) SE Runtime Environment (build 1.7.0_231-b08)
Java HotSpot(TM) Server VM (build 24.231-b08, mixed mode)

$ $AFJVAPRG -version
java version "1.7.0_231"
Java(TM) SE Runtime Environment (build 1.7.0_231-b08)
Java HotSpot(TM) Server VM (build 24.231-b08, mixed mode)

$ cd $ADMIN_SCRIPT_HOME
- start EBS services

3) Upgrade to JRE 7.0 on Database Tier Node
This step is needed if Oracle database version is below 11gR2. This upgrade is independent to the JDK upgrade on the Oracle E-Business Suite application tier.

Download Latest JRE 7.0 Update. Note: Download the 32-bit JRE only, not the Java SE Development Kit (JDK)

To replace existing JRE:
$ cd $ORACLE_HOME/appsutil
$ mv jre jre_old
$ mv jre1.7.0_231 jre

TROUBLESHOOTING:

1. After all above steps, script adstpall.sh got Timed-out error on three processes OAFM, FORMS, and OACORE
$ ./adstpall.sh apps/appPWD
You are running adstpall.sh version 120.10.12010000.4
The logfile for this session is located at $LOG_HOME/appl/admin/log/adstpall.log
Executing service control script: $ADMIN_SCRIPTS_HOME/adoafmctl.sh stop
script returned:
****************************************************
ERROR : Timed out( 100000 ): Interrupted Exception
You are running adoafmctl.sh version 120.8
Stopping OPMN managed OAFM OC4J instance ...
****************************************************

The error in $LOG_HOME/ora/10.1.3/opmn/default_group~oafm~default_group~1.log
--------
20/01/29 16:22:07 Stop process
--------
Error: Could not connect to the remote server. Please check if the server is down or the client is using invalid host, ORMI port or password to connect: <no message>oracle.oc4j.security.ExchangingEncryptor.getEncryptedValue(ExchangingEncryptor.java:161)
com.evermind.server.rmi.RMIProtocol$SecureCredentials.send(RMIProtocol.java:278)
com.evermind.server.rmi.RMIProtocol.sendCredentials(RMIProtocol.java:95)
oracle.oc4j.rmi.ClientRmiTransport.connectToServer(ClientRmiTransport.java:92)
oracle.oc4j.rmi.ClientSocketRmiTransport.connectToServer(ClientSocketRmiTransport.java:69)
com.evermind.server.rmi.RMIClientConnection.connect(RMIClientConnection.java:765)
com.evermind.server.rmi.RMIClientConnection.sendLookupRequest(RMIClientConnection.java:247)
com.evermind.server.rmi.RMIClientConnection.lookup(RMIClientConnection.java:231)
com.evermind.server.rmi.RMIClient.lookup(RMIClient.java:302)
com.evermind.server.rmi.RMIClientContext.lookup(RMIClientContext.java:59)
com.evermind.client.orion.Oc4jAdminConsole.executeCommand(Oc4jAdminConsole.java:138)
com.evermind.client.orion.Oc4jAdminConsole.main(Oc4jAdminConsole.java:31)
caused by: oracle.oc4j.security.KeyExchange.getSecretKey(KeyExchange.java:136)
oracle.oc4j.security.ExchangingEncryptor.getEncryptedValue(ExchangingEncryptor.java:152)
com.evermind.server.rmi.RMIProtocol$SecureCredentials.send(RMIProtocol.java:278)
com.evermind.server.rmi.RMIProtocol.sendCredentials(RMIProtocol.java:95)
oracle.oc4j.rmi.ClientRmiTransport.connectToServer(ClientRmiTransport.java:92)
oracle.oc4j.rmi.ClientSocketRmiTransport.connectToServer(ClientSocketRmiTransport.java:69)
com.evermind.server.rmi.RMIClientConnection.connect(RMIClientConnection.java:765)
com.evermind.server.rmi.RMIClientConnection.sendLookupRequest(RMIClientConnection.java:247)
com.evermind.server.rmi.RMIClientConnection.lookup(RMIClientConnection.java:231)
com.evermind.server.rmi.RMIClient.lookup(RMIClient.java:302)
com.evermind.server.rmi.RMIClientContext.lookup(RMIClientContext.java:59)
com.evermind.client.orion.Oc4jAdminConsole.executeCommand(Oc4jAdminConsole.java:138)
com.evermind.client.orion.Oc4jAdminConsole.main(Oc4jAdminConsole.java:31)
caused by: Unsupported secret key algorithm: DES

The fix is to follow Doc ID 2353710.1:
1) Copy opmn_xml_1013.tmp from $FND_TOP/admin/template to $FND_TOP/admin/template/custom
2) Modify 6 lines in $FND_TOP/admin/template/custom/opmn_xml_1013.tmp by attaching "-Djdk.crypto.KeyAgreement.legacyKDF=true" to the end of each line for oafm_jvm, forms_jvm, oacore_jvm.
3) Run autoconfig.
After that, confirm file opmn/xml is updated with new entries:
$ grep legacyKDF $ORA_CONFIG_HOME/10.1.3/opmn/conf/opmn.xml

2. After above, I still have Timed-out problem with stopping FORMS. I looked into further by running below line to deploy EAR files and it failed with exact error as in Doc ID 1399491.1 (but no similar error in my formsstd.err file). The fix is to apply patch 12965674 to 10.1.3 ORACLE_HOME.

$ $FND_TOP/bin/txkrun.pl -script=CfgOC4JApp -applicationname=forms -oc4jpass=welcome123 -runautoconfig=No

MESSAGES:
Command error: <rc> = 52224, <command> = $IAS_ORACLE_HOME/opmn/bin/opmnctl stopproc  instancename=forms
STACK TRACE
        TXK::Error::abort('TXK::Error','HASH(0x8bf2334)').......

3. If EBS installation is a 32bit build while JDK is 64bit, opatch may give error:
Java HotSpot(TM) 64-Bit Server VM warning: You have loaded library
/PATH/apps/tech_st/10.1.2/oui/lib/linux/liboraInstaller.so which might have disabled stack guard.
The VM will try to fix the stack guard now.
It's highly recommended that you fix the library with 'execstack -c <libfile>', or link it with '-z noexecstack'. java.lang.UnsatisfiedLinkError: /PATH/apps/tech_st/10.1.2/oui/lib/linux/liboraInstaller.so:
/PATH/apps/tech_st/10.1.2/oui/lib/linux/liboraInstaller.so: wrong ELF class: ELFCLASS32 

Another way to download Java files from Oracle Support site: click on Patches &Updates tab. Download the right version JDK. For example, JDK 1.7 Update 231 on Linux 64bit.

It may returns multiple rows.
 . JDK 7 update 231 is the one that you would need for JDK
 . Server JRE 7 is the JRE used on server side
 . JRE 7 update 231 is the regular JRE version of Java used for applications on client side.

References:
1. Using JDK 7.0 Latest Update with Oracle E-Business Suite Release 12.0 and 12.1 (Doc ID 1467892.1)
2. Using the Latest JDK 7.0 Update with Oracle E-Business Suite Release 12.2 (Doc ID 1530033.1)
    It today says EBS R12.2.2 or higher is certified with "JDK 7.0 Update 9 or higher" (1.7.0_09).
3. All Java SE Downloads on MOS (Doc ID 1439822.1).
4. E-WL: How to Upgrade the Java JDK Version for WebLogic 12c (Doc ID 2168514.1)

Find Responsibilities that can run a concurrent program

Below query tells which R12 responsibilities can run a particular concurrent program, such as "Purge Signon Audit data":

SELECT
    frt.responsibility_name,
    frg.request_group_name,
    frgu.request_unit_type,
    frgu.request_unit_id,
    fcpt.user_concurrent_program_name
FROM
    fnd_responsibility           fr,
    fnd_responsibility_tl        frt,
    fnd_request_groups           frg,
    fnd_request_group_units      frgu,
    fnd_concurrent_programs_tl   fcpt
WHERE
    frt.responsibility_id = fr.responsibility_id
    AND frg.request_group_id = fr.request_group_id
    AND frgu.request_group_id = frg.request_group_id
    AND fcpt.concurrent_program_id = frgu.request_unit_id
    AND frt.language = userenv('LANG')
    AND fcpt.language = userenv('LANG')
    AND fcpt.user_concurrent_program_name = 'Purge Signon Audit data'
ORDER BY 1,    2,    3,    4;

Concurrent job hangs and stays at "Running" forever

To make all R12.1 services run on a single node, instead of on multiple apps-tier nodes, I followed 4 steps:

1. Edited $CONTEXT_FILE to make all 5 variables "enabled":

Root Service Group : s_root_status
Web Entry Point Services : s_web_entry_status
Web Application Services : s_web_applications_status
Batch Processing Services : s_batch_status
Other Service Group : s_other_service_group_status

2. Ran autoconfig on the single node 

3. Start all services, including concurrent managers on the single node. 

4. Keep all services down in all other nodes.

Everything worked without a problem. But, when I ran a concurrent request, such as "Active Users", the job stays hung and frozen forever.  Below query show it reached the database:

SQL> SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a, apps.fnd_concurrent_processes b, v$process c, v$session d
WHERE a.controlling_manager = b.concurrent_process_id AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid AND a.request_id = &Request_ID AND a.phase_code = 'R';

REQUEST_ID  SID    SERIAL#  OSUSER  PROCESS  SPID

----------------- ------- -------------- ----------- --------------- -------
33925067         2345   40605         ebsdev      18208          42991822

And, database server has its process  

$ ps -ef | grep 42991822

 oracle 42991822        1   0 16:31:07      -  0:00 oracleEBSDEV (LOCAL=NO)

After I cancelled the request from GUI Forms, the Standard Manager got crashed and posted a very misleading error in Manager's log: 

12-MON-20XX 21:55:43

Request  : 33925067
Priority : 50
Program  : 0/20641
State    : T

12-MON-20XX 21:55:43
Attempting process termination for process 17331 on node nodeName
12-MAR-2021 21:55:43 - Could not submit job to kill request session 33925067:

No such process
An error occured in client-side routine afpsmckp for Service Manager FNDSM_NodeName_EBSDEV.  The routine returned code 1. Check for preceding errors and as well as the service manager log file for further details."

12-MON-20xx 21:57:44 - Could not submit job to kill concurrent process 260751: Oracle error 100: ORA-01403: no data found has been detected in FND_CONC_RAC_UTILS.SUBMIT_MANAGER_KILL_SESSION.
Found dead process: spid=(17331), cpid=(260751), ORA pid=(74), manager=(0/0)

The real problem is exactly as described in Doc ID 737445.1 (R12 Concurrent Requests Run Forever, rwrun Errors with REP-50125) and the fix is to delete file $ORACLE_HOME/reports/conf/rwnetwork.conf.  Only below line will give the true error in file areport.trc.

$ $INST_TOP/ora/10.1.2/bin/appsrwrun.sh userid=apps/appsPWD mode=character report=$FND_TOP/reports/US/FNDSCURS.rdf \
batch=yes destype=file desname=./areport.out desformat=$FND_TOP/reports/HPL pagesize=132x66 traceopts=trace_all tracefile=areport.trc tracemode=trace_replace 

By the way, to totally remove a node from multiple nodes structure, run two steps BEFORE above 4 steps:

a) SQL>  EXEC FND_CONC_CLONE.SETUP_CLEAN 

b) Run autoconfig on database server.

Then, below two table shall have no data:

SQL> select * from fnd_concurrent_queues;

SQL> select * from fnd_concurrent_processes;

And below two tables shall have only one row:

SQL> select * from fnd_nodes;

SQL> select * from fnd_conflicts_domain;

How to start Background Engines

Login as a Sysadmin, Site Map => Work Item Metrics (under Workflow)

If you see Background Engines is not Up, start it manually. To start "Workflow Background Process": On the top right corner: Submit Request For >> Background Engines, click on Go

Schedule Background Engines with the following parameters (in R12.1):

Then, the job status can be viewed from GUI forms: Request => View. To learn more on scheduling this job, see Performance Degradation when the Workflow Background Process is Running (Doc ID 743338.1).

Background Engine icon on OAM page may still show red Down status, even "Workflow Background Process" ran successfully. The job has to be repetitive (so that they run over and over). If it just runs once, the icon will change back to red X. 

Same way with jobs "Purge Obsolete Workflow Runtime Data" and "Workflow Control Queue Cleanup" to make two other icons stay in green on OAM page.