EBS R12.1 patching for JRE 1.8

To make EBS R12.1.3 work with JRE (Java Run-time Environment) 1.8, patches on apps and both ORACLE_HOMEs are needed. The main document is ID 393931.1 "Deploying JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite Release 12". I do not see this activity requires any database patches. My databases are in 11.2.0.2 or above.

1. Verify the versions and conditions
  (1) $ $IAS_ORACLE_HOME/Apache/Apache/bin/httpd -v
  Server version: Oracle-Application-Server-10g/10.1.3.5.0 Oracle-HTTP-Server
  Server built:   May 16 2013 15:47:41

  (2) $ $ORACLE_HOME/bin/frmcmp_batch|grep Forms| grep Version
  Forms 10.1 (Form Compiler) Version 10.1.2.3.0 (Production)
  (Because 10.1.2.3.0 patch 5983622 was was installed by Installer, how to confirm it is installed?)

  (3) Confirm first 3 patches were installed
  select * from ad_bugs where bug_number in
  ('4377566',  -- Step 2.1, 393931.1
  '8919489',   -- Step 2.3.2, 437878.1
  '14837539', -- Step 2.1.1 & Step 3.8, 437878.1
  '11776182' ); -- Step 2.1.2 Footnote7, 437878.1. very old? Skip it

2. Patch in 10.1.3 ORACLE_HOME ( Section 1)

Shutdown apps services (adstpall.sh apps/appsPWD) on all nodes and complete a backup.

Apply patch 19568561 (required):
  $ cd $INST_TOP/ora/10.1.3
  $ . xxxx_XXX.env               <== change the ORACLE_HOME to 10.1.3
  $ echo $ORACLE_HOME 
     /u02/app/EBSDev/apps/tech_st/10.1.3        <==  make sure it is 10.1.3!

  $ export OPATCH_PLATFORM_ID=46
     (to avoid error on 64-bit Linux:
     OPatch detects your platform as 226 while this patch 19568561 supports platforms: 46 Linux Intel)
  $ cd 19568561
  $ opatch apply

Update on 12/19/2015: Document 393931.1 was updated recently with recommendation of replacing this patch 19568561 with October 2015 CUP patch 21845960. I found patch 21845960 worked better and fixes issue with Firefox (version 43.0+).

3. Patches in 10.1.2 ORACLE_HOME (OracleAS 10g Patches. Follow Doc ID 437878.1) 
Start a new OS session, or $ . .profile
$ echo $ORACLE_HOME
   /u02/app/EBSDev/apps/tech_st/10.1.2      <== make sure it is 10.1.2

(1) p14825718_10105_LINUX.zip     (MLR patch or Bundle Patch. It is pre-requisite for patch 21103001 )
     Verify conditions are met to apply Patch 14825718 (Step 3.2 notes of Document 437878.1):
     12881480, 13808590, 14041415, 14262118, 14614795, 14577216, 9593176 were NOT applied
     while 6995251, 7121788 were APPLIED

$ cd 14825718
$ opatch apply

NOTES: It will roll back some patches that were previously applied. Accept that and do NOT stop it.
The patch's README is not applicable to E-Business Suite. Step 5 in below covers it after all Forms patches are applied. 

Install any patches that miss from the list of Step 2.1.2 in 10.1.2 ORACLE_HOME. I had to catch up 14 patches (well, even the document 437878.1 does not say "required").

(2) p6640838_10106_Linux-x86-64.zip     <== pre-requisite for patch 21103001
This patch 6640838 only adds missing components to OUI 10.1.0.6.0, and does not install a new OUI.
Installation steps with screenshots: How to patch OUI for installing overlay patches on top of Forms Bundle Patch - 9593176 (Doc ID 1301320.1)

$ mv $ORACLE_HOME/OPatch $ORACLE_HOME/OPatch.pre_6640838
to reserve the higher version of opatch.
$ export DISPLAY=xxx.xx.xxxx:0.0

$ cd cd/Disk1/install

$ Modify file oraparam.ini to include Linux 5     <== need this workaround on RHEL5
                    Linux=redhat-2.1AS,redhat-3,redhat-4,redhat-5
$ ./runInstaller
or
$ ./runInstaller -ignoreSysPrereqs   ==> follow Doc ID 1301320.1 to the finish line!

$ cd $ORACLE_HOME
$ mv OPatch OPatch_delete
$ mv OPatch.pre_6640838 OPatch

(3) p8551790_10123_LINUX.zip            <== pre-requisite for patch 21103001

$ cd 8551790
$ opatch apply

(4) p21103001_101232_LINUX.zip
 This patch replaces 19434967
 Pre-requisite: OUI componetes (patch 6640838) and patch 8551790.  Also references:    
        Post Steps For Patch 19434967 Files Do Not Exist (Doc ID 1945012.1)
        Font Changes In EBS Forms After Application Of CPU Patches (Doc ID 2005998.1)
Notes: if opatch version is lower than 1.0.0.0.63, it may give a misleading error: the patch directory 21103301 doesn't match the patch id.

Updates in June 2016: patch 21103001 is replaced by MLR patch 22698265 after JRE 1.8.0_92 was released (see Change Log of Doc 437878.1 and Doc. 393931.1). During the installation of patch 22698265, it will rollback patch 21103001 if it was applied. It has the same pre-requisites as 21103001 does.

$ cd 21103001
$ opatch apply

(5) p10152652_10123_LINUX.zip

pre-requisite: patch 7121788. Yes
verify: $ chmod +x $ORACLE_HOME/bin/genshlib

$ cd 10152652
$ opatch apply
$ sh ./patch.sh
... ... ... ... 
I skipped RSF (required support files) patches in Step 3.4 (and Step 3.5), because my database is in 11.2.0.2 and those patches seem for database 10g.

4. Apply EBS R12.1 Forms Interoperability Patch (Step 2.1.1, Step 3.8 of Doc ID 437878.1)

 SQL> select * from ad_bugs where bug_number = '14837539';  -- to confirm it was not installed yet

 $ adadmin    to enable maintenance mode
 $ cd 14837539
 $ adpatch

5. Post AS10g patching steps (Section 4 of Doc ID 437878.1)

$ cd $ORACLE_HOME/forms/lib
$ make -f ins_forms.mk install

$ cd $ORACLE_HOME/reports/lib
$ make -f ins_reports.mk install

$ adadmin
    ==> 1 Select Generate Applications Files
    ==> 4 Generate Product JAR Files  (No  - Do not opt to force the regeneration of all JAR files)

optional: verify the versions.
$ export DISPLAY=XXX.XXX.XXX:0.0
$ ORACLE_HOME/bin/frmcmp help=y
$ ORACLE_HOME/bin/rwrun ?|grep Release

UPDATES:
I highly recommend to run "adstrtal.sh" to start all services and check out web login and forms. At this time point, the startup script may deploy new EAR file. If any failure, you know it is not caused by new JRE version of next steps.

I had one instance that failed to launch forms after Step 10. There was a Time out error in adstrtal.log on "adformsctl.sh start" to deploy formsapp.ear file. After I recycled all services, adopmnctl.sh showed "OC4J: forms" status got changed from DOWN to ALIVE. But, GUI forms was still not popup. It took me many hours to find that the problem was not from the new JRE version. Actually, after I re-ran Step 10, I was able to launch EBS forms even though the errors from EAR deployment never got fixed.

6.  Apply the JRE Interoperability Patch 21624242:R12.TXK.B (Step 2.1). Now follow Doc ID 393931.1
SQL> select * from ad_bugs where bug_number = '4377566'; --confirm pre-patch 4377566 was applied

$ mv $FND_TOP/bin/txkSetPlugin.sh $FND_TOP/bin/txkSetPlugin-4377566.sh
$ cd 21624242
$ adpatch

7. AD Patch 17191279 - Enhanced Jar Signing for Oracle E-Business Suite (Doc ID 1591073.1)

(as sysdba) SQL> @adgrants.sql apps
Note: This script may give errors from dropping some objects. Ignore them.
If a newer version of adgrants.sql (e.g. the one by Oct 2015 CPU patch) has been executed before, this step can be skipped.

$ cd 17191279
$ adpatch

8. AD Patch 18312333 (see Doc ID 1591073.1)

$ cd 18312333
$ adpatch

9. Download JRE file (Step 2.2)
   Click on the hype link " JRE Parameter Settings " (in Step 2.2) to get the information table in
   Appendix D: Reference Information of this document.
  
   Patch 21045690: Oracle JDK 8 Update 51
     selected and downloaded this Window one for both Windows (32-bit) and Windows x64 (64-bit),
     although my EBS hosts use Linux OS:
     jre-8u51-windows-i586.exe     <== In Oct. 2015 I downloaded this latest one (32-bit)

   (or, http://www.oracle.com/technetwork/java/javase/downloads/jre8-downloads-2133155.html)
 
$ cp -p jre-8u51-windows-i586.exe $COMMON_TOP/webapps/oacore/util/jinitiator/j2se18051.exe
  
10. Run script on web tier only (Section 3 of Doc ID 393931.1 )
The document says "Run the $FND_TOP/bin/txkSetPlugin.sh script against the web node". But I did not meet problem after running it on CM node also.

 $ $FND_TOP/bin/txkSetPlugin.sh 18051
... ... ...
Updating XML context file with new J2SE parameters...
Updated J2SE plugin to jdk
Updated J2SE Version to 1.8.0_051
Updated J2SE Classid to CAFEEFAC-0018-0000-0051-ABCDEFFEDCBA
AutoConfig is configuring the Applications environment ... ...

If success, two variables shall get new values in $CONTEXT_FILE:
$ grep sun $CONTEXT_FILE
    <sun_plugin_ver oa_var="s_sun_plugin_ver">1.8.0_051</sun_plugin_ver>
    <sun_plugin_type oa_var="s_sun_plugin_type">jdk</sun_plugin_type>
    <sun_clsid oa_var="s_sun_clsid">CAFEEFAC-0018-0000-0051-ABCDEFFEDCBA</sun_clsid>

$ cat $FORMS_WEB_CONFIG_FILE|grep sun_plugin_version| cut -c 1-35      (see Doc. 468311.1)
sun_plugin_version=1.8.0_051

UPDATES in January 2016: after newer JRE 1.8.0_66 version became available, I downloaded it from patch 22286087 and ran below lines, instead. All worked the same as JRE 1.8.0_51 on both server and user's client.
$ cp -p jre-8u66-windows-i586.exe $COMMON_TOP/webapps/oacore/util/jinitiator/j2se18066.exe
$ $FND_TOP/bin/txkSetPlugin.sh 18066

11. Start apps services
 $ adadmin    to disable maintenance mode
 $ adstrtal.sh apps/PW
12. On client workstation's Java Console, add the URL (https://sitename.domain.com) to Exception list on Security tab. Without this, forms will get " Application Blocked by Java Security " popup.

Now, the EBS Forms shall fire up on a workstation where JRE 1.8.0_51 was installed (with 2 or 3 annoying popups).

TROUBLESHOOTING:

If the forms link has no response or Forms do not show up after clicks, clean Java cache and IE cache on the client machine before re-trying it. You may try it in Firefox and if you get " 404 Not Found  The requested URL /forms/frmservlet was not found on this server. " error, there might be a problem with EAR file deployment. Scary part! Workarounds: (a) Stop all services cleanly and try startup adstrtal.sh again. (b) In some of my cases, I had to re-run Step 5 or Step 10 to get forms fired up. (c) Manually deploy EAR file.

Normally, just keep all default options under Java Console. If Java Console log shows timed out or failure error, test some options under Advanced tab in Java. Also, on IE Tools => Manage add-ons, click the dropdown under Show (on the left) to select "All adds-on", then make sure Java add-ons are "Enabled". You shall see below similar two (or more):

NOTES:
(1). In some environment, users may not have permission to install software on their workstation. JRE 1.8.0_51+ (32-bit) shall have been installed on PC by Windows Admin before users can launch EBS Forms.
(2). Make sure TLS1.x is enabled. If TLS1.x is not enabled, enable it first. Otherwise, EBS Forms may not launch after JRE1.8 patches are applied.
(3). If users' client has higher than 1.8.0_51 (such as JRE 1.8.0_66) installed, EBS Forms will still work with the higher JRE class (with a couple of popups for confirmation). But it will not work with JRE 1.9.0_xx (a higher JRE family in the future).

Post steps:
Follow Doc ID 1591073.1 on Java signing (to get rid of Step 12 above).

Find the version of executable file and database package

1. When see below lines in request log, SQL statement could be used to find the executable name.
    -----------------------------------------------------------------------------
    Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
    MSCPLD module: Loader Worker With Direct Load Option
    +---------------------------------------------------------------------------+

Step1
SQL> select EXECUTABLE_NAME,EXECUTION_FILE_NAME,EXECUTION_FILE_PATH
 from fnd_executables
 where EXECUTABLE_NAME like '%MSCPLD%';

EXECUTABLE_NAME    EXECUTION_FILE_NAME        EXECUTION_FILE_PATH
MSCPLD                          MSCPLD.sh   

Step 2. Then, use "locate" to find the file:
$ locate MSCPLD.sh
 /u02/app/EBSDEV/apps/apps_st/appl/msc/12.0.0/bin/MSCPLD.sh

2. How to get the header file versions of all the header files for an executable in Unix

Example 1
$ cd $AP_TOP/bin
$ strings -a APXXTR |grep Header

Example 2, ARRGTA file:
$ strings -a $AR_TOP/bin/ARRGTA | grep '$Header'

SELECT /* $Header: fdffvs.lc 120.17.12010000.8 2010/05/07 22:42:10 tebarnes ship $ */ event_code, user_exit FROM fnd_flex_validation_events WHERE flex_value_set_id = :id ORDER BY event_code
SELECT /* $Header: fdffvs.lc 120.17.12010000.8 2010/05/07 22:42:10 tebarnes ship $ */ additional_where_clause FROM fnd_flex_validation_tables WHERE flex_value_set_id = :id
$Header: arrgta.opc 120.9 2006/06/27 17:16:49 rkader ship $
$Header: arjbal.lpc 120.2 2005/10/24 14:13:01 srivasud ship $
$Header: arjigl.lpc 120.18.12010000.6 2009/10/05 20:43:34 mraymond ship $
 .......

Notes: "strings" can also get Java file's version:
$ strings -a $JAVA_TOP/oracle/apps/frm/xdo/bne/utilities/FrmUploadModule.class |grep '$Header'
S$Header: FrmUploadModule.java 120.2.12010000.16 2014/05/09 05:25:46 jsiripur ship $

3. Use below statement to get database package version applied to the database:
SQL> select * from dba_source where name='XXXXX' and line=2;
Example:
select text from dba_source where name='AP_ACCTG_DATA_FIX_PKG' and line=2;
TEXT
/* $Header: apgdfals.pls 120.1.12010000.21 2013/09/05 13:06:08 pshivara ship $ */
/* $Header: apgdfalb.pls 120.1.12010000.68 2013/10/24 11:27:22 rseeta ship $ */

To check the code file version on file system, use
$ grep -i Header $AP_TOP/patch/115/sql/apgdfalb.pls

NOTE1: EBS utility adident (in unix, windows and other OS) can also be used to provide the file versions.
NOTE2: Before applying a patch, you may check if there a higher version of file in other patches:
How to find the Latest/Newest Version of an Oracle Receivables File (Doc ID 1584999.1)

Blank log or error "Function ncrorou returned error code 3010"

When trying to open the log or output files from EBS R12.1 Forms, suddenly get a totally blank html page or below error:

An error occurred while attempting to receive the output arguments of the remote procedure. Function ncrorou returned error code 3010. The Applications File Server process may have exited abnormally. Please contact your system administrator

I found the cause can be bad Temp tablespace in the database, over-size log file, or no disk space left in somewhere on the server.

Concurrent jobs that should run periodically

In a production environment, some concurrent jobs are necessary to keep the system running smoothly. Below list covers good candidates.

USER_CONCURRENT_PROGRAM_NAME        CONCURRENT_PROGRAM_NAME
Workflow Background Process                               FNDWFBG
Workflow Control Queue Cleanup                            FNDWFBES_CONTROL_QUEUE_CLEANU
Workflow Directory Services User/Role Validation     FNDWFDSURV
Purge Obsolete Workflow Runtime Data                  FNDWFPR
Purge Concurrent Request and/or Manager Data      FNDCPPUR (one on request logs)
Purge Concurrent Request and/or Manager Data      FNDCPPUR (one on manager logs)
Delete Diagnostic Statistics                                   DELDIAGSTAT
Purge Logs and Closed System Alerts                    FNDLGPRG
Purge Inactive Sessions                                    FNDDLTMP (delete data from table ICX_SESSIONS)
Request Set Gather Statistics Schemas (weekly run)  Gather Schema Statistics

One of the important ones is to collect statistics. A Request Set can be created for that by
Requests => Set => name it "Gather Statistics Schemas (weekly run)"
Then, Define Stages => Requests =>
Enter a Seq number and select seeded Program "Gather Schema Statistics" for each schema (AR, GL, XLA, APPS, APPLSYS, ...). Make sure the Owner is sysadmin, otherwise sysadmin may be unable to see it. If the parallel Degree 16 is not specified, it will take the default from the database (I saw 32 in a run).

If job "Purge Concurrent Request and/or Manager Data" is not scheduled to run regularly, table fnd_env_context may become very big. You may have to truncate the table as the only option of reducing its size.

References:
- What Are Concurrent Reports That Should Be Scheduled Periodically [ID 1066117.1] .
- Why is The "Purge Inactive Sessions" Concurrent Program Not Deleting Records From ICX_SESSIONS? [ID 1073768.1].

Below query find all scheduled concurrent requests (or CM requests scheduled by a user):

SELECT
    fr.responsibility_key responsibility,
    fcp.concurrent_program_name,
    fcpt.user_concurrent_program_name,
    decode(fcr.status_code,
        'A', 'Waiting',
        'B', 'Resuming',
        'C', 'Normal',
        'D', 'Cancelled',
        'E', 'Error',
        'F', 'Scheduled',
        'G', 'Warning',
        'H', 'On Hold',
        'I', 'Normal',
        'M', 'No Manager',
        'Q', 'Standby',
        'R', 'Normal',
        'S', 'Suspended',
        'T', 'Terminating',
        'U', 'Disabled',
        'W', 'Paused',
        'X', 'Terminated',
        'Z', 'Waiting') status,
    decode(fcr.phase_code,
        'C', 'Completed',
        'I', 'Inactive',
        'P', 'Pending',
        'R', 'Running') phase,
    fcr.request_id,
    fcr.parent_request_id,
    fu.user_name requestor,
    to_char(fcr.requested_start_date, 'MON-DD-YYYY HH24:MM:SS') START_DATE,
    fcr.completion_text
FROM
    fnd_concurrent_requests fcr,
    fnd_concurrent_programs fcp,
    fnd_concurrent_programs_tl fcpt,
    fnd_user fu,
    fnd_responsibility fr
WHERE
    --fcr.status_code in ('Q', 'I') and
    --fcr.hold_flag = 'N' and
    fcr.requested_start_date > sysdate and
    fu.user_id = fcr.requested_by and
    fcr.concurrent_program_id = fcp.concurrent_program_id and
    fcr.concurrent_program_id = fcpt.concurrent_program_id and
    fcr.responsibility_id = fr.responsibility_id
    -- and fu.user_name = 'UserName'
order by fcr.requested_start_date,  fcr.request_id;

SQL scripts show R12 Profile Options

Below query will find the value on Profile option(s). It is modified from a script in Doc ID 1159313.1 "Unable To Authenticate Session Error When Login to Different R12 Instance in Same IE Session". Exact profile name is in table fnd_profile_options_tl .

SELECT p.profile_option_name, fpot.user_profile_option_name NAME,
decode(v.level_id,
             10001,'SITE',
             10002, (select 'App:'||a.application_short_name
                            from fnd_application a
                          where a.application_id = v.level_value),
             10003, (select 'Resp:'||f.RESPONSIBILITY_KEY
                            from fnd_responsibility f
                          where f.responsibility_id = v.level_value),
             10004, (select 'User:'||u.user_name
                            from fnd_user u
                          where u.user_id = v.level_value),
             10005, (select 'Server:'||n.node_name
                            from fnd_nodes n
                          where n.node_id = v.level_value),
             10006, (select 'Org:'|| o.name
                            from hr_operating_units o
                          where o.organization_id = v.level_value),
             'NOT SET') PROF_LEVEL,
              nvl(v.profile_option_value,'NOT SET') profile_option_value, fpot.description
FROM
  fnd_profile_options p,
  fnd_profile_option_values v,
  fnd_profile_options_tl fpot
WHERE
p.profile_option_id = v.profile_option_id (+)
and p.application_id = v.application_id (+)
and p.profile_option_name = fpot.profile_option_name
-- and p.profile_option_name = 'FND_DIAGNOSTICS'
-- and fpot.user_profile_option_name like 'FND: D%'
and fpot.user_profile_option_name like 'Signon%'
order by 1, v.level_id;

A query to find ALL profile Option values (modified from a script in Doc ID 2026081.1):

select n.user_profile_option_name NAME,
       decode(v.level_id,
               10001, 'Site',
               10002, 'Application',
               10003, 'Responsibility',
               10004, 'User',
               10005, 'Server',
               10006, 'Organization',
               10007, 'ServResp',
               'UnDef') LEVEL_SET,
       decode(to_char(v.level_id),
              '10001', '',
              '10002', app.application_short_name,
              '10003', rsp.responsibility_key,
              '10004', usr.user_name,
              '10005', svr.node_name,
              '10006', org.name,
              '10007', 'depends=',
              v.level_id) "CONTEXT",
       v.profile_option_value VALUE,
       (select n.node_name
          from
               fnd_nodes n
         where
               n.node_id=level_value2) Server,
               decode(v.LEVEL_VALUE,
               -1, 'Default',
               rsp.responsibility_key) Resp,
               decode(LEVEL_VALUE_APPLICATION_ID,
               -1, 'Default',
               app.application_short_name) Application
  from fnd_profile_options p,
       fnd_profile_option_values v,
       fnd_profile_options_tl n,
       fnd_user usr,
       fnd_application app,
       fnd_responsibility rsp,
       fnd_nodes svr,
       hr_operating_units org
 where p.profile_option_id = v.profile_option_id (+)
   and p.profile_option_name = n.profile_option_name
  -- and (upper(v.profile_option_value) like '%HTTP%')   -- if you want to find the value, enable this line
  -- and p.profile_option_name like 'APPLICATIONS_HOME%'   -- if you want to find the name
   and    usr.user_id (+) = v.level_value
   and    rsp.application_id (+) = v.level_value_application_id
   and    rsp.responsibility_id (+) = v.level_value
   and    app.application_id (+) = v.level_value
   and    svr.node_id (+) = v.level_value
   and    org.organization_id (+) = v.level_value
 order by name, level_set;

A change in Profile option can make the EBS run very differently. Below script will find Profile options that were changed within one day.

 SELECT '''' || a.user_profile_option_name
       || ''' Was Updated with value '
       || b.profile_option_value mesg,
       (SELECT user_name
             FROM apps.fnd_user u
            WHERE u.user_id = b.last_updated_by) who , b.last_update_date, b.level_value
  FROM apps.fnd_profile_options_vl a,
       apps.fnd_profile_option_values b,
       apps.fnd_user c
 WHERE a.profile_option_id = b.profile_option_id
   AND b.last_updated_by = c.user_id
   AND (   b.last_update_date > SYSDATE - 1   -- <- put a different number here if needed
        OR b.creation_date > SYSDATE - 1
       );
NOTE: Not sure why, but this query is not accurate sometimes (when the value was changed to null).

NOTES: If you have to change a Profile option but the EBS webpage is not available, try FND_PROFILE package (for example):

declare value Boolean;

begin

value := FND_PROFILE.SAVE('SIGNON_PASSWORD_CASE','1','SITE');

end;

/