Upgrade database of EBS R12.1.3 to Oracle 12c (12.1.0.2)

Steps to upgrade EBS database from 11.2.0.2 to 12.1.0.2:

0. Make sure pre-requisites for patches of Step 2 were applied:
select * from ad_bugs where bug_number in (
'8502056',    -- R12.AD.B.DELTA.2.            for 12923944
'7651091',    -- R12.ATG_PF.B.DELTA.2.   for 12923944
'6636108',    -- AutoConfig patch (Doc ID 387859.1, Section 3.2)
'8919489',    -- AutoConfig patch (Doc ID 387859.1, Section 3.1)
'9239089',    -- R12.AD.B.DELTA.3        for 16289505
'8919491'     -- R12.ATG_PF.B.delta.3   for 16289505
-- '18039691'  -- AFTER DB upgrade.    for 8796558 on WMS
);

1. Pre-step - Apply patch 11791600:R12.AR.B to address error " Failed on compiling library ARXRWAPP.pll " from applying below 5 patches in Step 2. This could be optional to some environments.

2.  Apply 5 required patches by "Interoperability Notes EBS 12.0 or 12.1 with RDBMS 12cR1 (Doc ID 1524398.1)".
 p12923944_R12.FND.B_R12_LINUX.zip
 p13473483_R12.IBE.B_R12_GENERIC.zip  (for iStore. no pre-requisite)
 p16289505_12.1.0_R12_LINUX.zip
 p19558309_R12.AR.B_R12_LINUX.zip
 p8796558_R12.WMS.B_R12_LINUX.zip

NOTES:
(1) Verified that instance has all pre-requisites, including RPC patches for R12.1.3. In my instance, no RPC patch was applied before and so patch 18843706 is not needed.
SQL> select * from ad_bugs where bug_number in (
  '17774755', --    RPC1
  '19030202', --    RPC2
  '20203366'  --    RPC3);
"For the 12.1.1 instance, there is no reason to apply that patch since you can not apply a RPC patch unless you are on 12.1.3". See Doc ID 1563799.1 (FAQ: E-Business Suite Financials Recommended Patch Collection - RPC ) for more on RPC patches.
UPDATES in April 2018:
      21236633 -- R12.1.3+ RPC4. See Doc ID 2053709.1
      22644544 -- R12.1.3+ RPC5. See Doc ID 2152266.1
(2) Also verified ORACLE_HOME patch was applied before.
In my instance, patch 6400501 was applied to 10.1.2 Oracle Home on apps server.
(3) It works fine for me to merge above 6 patches together and then apply it at one time. 
(4) After 6 patches are applied and before doing database 12c upgrade in next Step 3, start Apps services to do a quick check-out. All shall work.

3. DBAs upgrade database from 11.2.0.2 to 12.1.0.2 (see patch list in Doc ID 761570.1 appendix A) and also apply July2015 PSU patch (Doc ID 21150768.8).
      Patch           Conflict patch
     17694377   12.1.0.2 Patchset (files p17694377_121020_platform_1of8.zip,
                                                          p17694377_121020_platform_2of8.zip,
                                                          p17694377_121020_platform_7of8.zip)
     21150768   Combo of 12.1.0.2.4 OJVM PSU and 12.1.0.2.4 DB PSU (Jul 2015)
     6880880     Latest OPatch
X 19382851     Already included in 12.1.0.2.4 PSU
X 19393542     Already included in 12.1.0.2.4 PSU
X 19627012     Already included in 12.1.0.2.4 PSU
X 19649152     Already included in 12.1.0.2.4 PSU
    20204035    12.1.0.2.0 version. No conflicts

After all, it includes the patch numbers for the 12.1.0.2 release itself, the one-offs required for R12.1.3 EBS and ones that are included in the July PSU

$ $ORACLE_HOME/OPatch/opatch lsinventory | grep applied
 Patch 21068507 : applied on Wed Jul 29 11:22:52 EDT 2015 (part of patch 21150768)
 Patch 20204035 : applied on Wed Jul 29 10:40:05 EDT 2015
 Patch 20831110 : applied on Tue Jul 28 16:04:35 EDT 2015  (part of patch 21150768)

After dbupgdiag.sql script as part of pre 12c upgrade steps, it may report invalid objects in SYS and SYSTEM schemas and utlrp.sql did not compile them.
Invalid views V$LOCKS_WITH_COLLISIONS ,V$LOCK_ACTIVITY & V$LOCK_ELEMENT are only for Oracle 6 compatibility and can be dropped if invalid after migration. Follow the below document for dropping them: Doc ID 837667.1 (Invalid SYS Objects Found On 9.2 Before Upgrade To 10.2.0.1). For invalid package system.OSS, follow Doc ID 1087534.1 (Package Body OSS and OSSADMIN In SYSTEM Schema Are Invalid After Database Upgrade).

Also, dropped MGDSYS schema in 11.2.0.3 as pre upgrade step. After post-upgrade step adconfig.sh on database server, created MGDSYS which helped compile package WMS_EPC_PVT body from invalid to valid. (Before MGDSYS was re-created, compiling WMS_EPC_PVT got same error as described in Doc ID 2198041.1. But after re-creation of MGDSYS, the compiling worked without EBS patch 19666945 even WMS is Inactive status, not installed, in the instance).

Invalid packages QPR_MAINTAIN_AW (Doc ID 1136003.1) and ZPB_BUILD_METADATA (Doc ID 1951646.1) can be ignored, and sys.V$KXSBD can be dropped in 12c if it exists.

NOTES: Patch Set Updates (PSU) are cumulative patches that include both the security fixes and priority fixes.  The key with PSUs is they are minor version upgrades (e.g., 12.1.0.2.0 to 12.1.0.2.4). Once a PSU is applied, only PSUs can be applied in future quarters until the database is upgraded to a new base version.

4. Follow Doc ID 1524398.1 to do the next steps

Get scripts/files ready for DBA tasks:
 (step 21)  cp -p $APPL_TOP/admin/adgrants.sql $APPLPTMP/.
 (step 22)  cp -p $AD_TOP/patch/115/sql/adctxprv.sql $APPLPTMP/.
 (step 25)  cp -p $APPL_TOP/admin/adstats.sql $APPLPTMP/.
 (step 27)  Generate new file appsutil.zip for DB server   (Doc ID 387859.1, Step 3.1 B)
                $ cd $AD_TOP/bin
                $ perl admkappsutil.pl
                $ cp $INST_TOP/admin/out/appsutil.zip $APPLPTMP/.

 Step 24: Validate Workflow ruleset
       $ cd  $FND_TOP/patch/115/sql
       $ sqlplus apps/appPWD @wfaqupfix.sql APPLSYS APPS

 Step 26.  Deregister the current database server.
       Note: it is not necessary, if " select * from fnd_nodes; " output is fine!
       SQL> exec fnd_conc_clone.setup_clean;

Step 27. Run adconfig.sh on database server

Step 29: Apply post-upgrade WMS patch 18039691   -- Seems optional (if WMS module is not used)

 Step 32: Re-create grants and synonyms
       $ adadmin
       --> 4 --> 2.

Step 35  Log on to Oracle E-Business Suite with the "System Administrator" responsibility.
    Click Requests > Run > Single Request and the OK button. Enter the following parameters:
    Request Name = Workflow Directory Services User/Role Validation
    Batch Size = 10000
    Fix dangling users = Yes
    Add missing user/role assignments = Yes
    Update WHO columns in WF tables = No
Click "OK" and "Submit".
Notes: it may take more than one hour! 

NOTE: After all above patches and steps, it got error with db login issue on running concurrent reports cross database link (defined by ILM archiving & purging tool). I had to apply apps patch16946854 to resolve the errors:
REP-0167: Database connect strings are not permitted in USERID parameter. Use TNSnames alias to specify the database.
REP-0501: Unable to connect to the specified database.
ORA-01017: invalid username/password; logon denied

Extra step: e-Business Suite CPU patches:
Apply e-business suite July 2015 CPU patch 20953340 (Doc ID 2013117.1):

1. apply pre-requisite patch 19273341 R12.BNE.B.delta.4
2. run SQL> @adgrants.sql apps
as SYSDBA  (newer version is /20953340/admin/adgrants.sql)
3. apply CPU patch 20953340
Note: for this patch, "adpatch" will run autoconfig during the patching process. All shall be smooth.

I merged 19273341, 20953340 and 16946854 (for db login fix), and then applied it successfully on EBS instances, after the database 12c upgrade and database Jul 2015 PSU patching.

UPDATES in February 2016:
1) During the busiest time in the month, 12.1.0.2 database got hang and no login was allowed.  There were many processes waiting on “LGWR worker group ordering”. It did not give ORA- errors described in Doc ID 1957710.1 (but DEADLOCK might be seen if a system state dump was performed). We applied the workaround "_use_single_log_writer"=TRUE to disable a performance feature in 12c for using multiple log writers.

2) After database upgrade to 12.1.0.2, " AR XLAACCUP module: Accounting Program " concurrent job failed very quick with  error:
An internal error occurred.  Please inform your system administrator or support representative that:
An internal error has occurred in the program xla_ae_lines_pkg.AccountingReversal.  ORA-01555: snapshot too old: rollback segment number 28

The fix is EBS patch 19591608.  We tried workaround in 2009636.1 to set temp_undo_enabled = FALSE. But it did not fix the problem. Then set it back to TRUE before applying patch 19591608.

3) If 12.1.0.2 database for EBS R12.1 gets below UTL_HTTP error, apply 10gAS Oct2015 CUP patch 21845960 to 10.1.3.5 ORACLE_HOME (for Apache) as the fix (see more detail).
SQL> SELECT utl_http.request('https://sitename.domain.com:443', NULL ,'file:/path/to/wallet', NULL) from dual;
SELECT utl_http.request('https://sitename.domain.com:443', NULL ,'file:/path/to/wallet', NULL) from dual
       *
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-29048: Unexpected message received.
ORA-06512: at "SYS.UTL_HTTP", line 1491
ORA-06512: at line 1
See here for setting up Wallet file on database host

4) We also applied R12.1 patch 21950151 to address the performance issue on " ARRGTA: Journal Entries Report ". Note this patch seems having impact on Process Lockboxes report (ARLPLB).

5) After database was upgraded to 12.1.0.2, and database PSU and apps CPU were applied, EBS R12.1.3 got an Import Rules issue. Patch  21149920, which changes only 3 XLA packages that are related to Import functionality, was applied. It fixed the issue.