Upgrade R12.1.1 to R12.1.3

I followed Doc ID 1080973.1 in below steps to apply patch 9239090 for upgrading R12.1.1 to R12.1.3 on an Oracle 11.2.0.3.0 database.

Current system status:

SQL> select * from ad_bugs where bug_number in ('9239089', '9239090', '9239095', '9817770', '9966055');
only 9239089 was applied.

$ $IAS_ORACLE_HOME/Apache/Apache/bin/httpd -v
Server version: Oracle-Application-Server-10g/10.1.3.5.0 Oracle-HTTP-Server
Server built:   Jul 21 2009 11:12:22

$ $ORACLE_HOME/bin/frmcmp_batch|grep Forms| grep Version
Forms 10.1 (Form Compiler) Version 10.1.2.3.0 (Production)

$ cat $FORMS_WEB_CONFIG_FILE|grep sun_plugin_version| cut -c 1-35
sun_plugin_version=1.6.0_06

Pre steps in database (to avoid headaches during the upgrading):

1. Setup two database parameters:
_disable_fast_validate=TRUE       <-- this parameter shall be removed after upgrade completed.
pga_aggregate_target=2G

2. Follow Doc ID 1112223.1 to grant XDBADMIN the right role to avoid ORA-31000 error with csrrsreg.sql.
SQL> show user
apps
SQL> select * from USER_ROLE_PRIVS;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
----------------------------------- ------------------------------------------------- ------- ------ ---
APPS                           AQ_ADMINISTRATOR_ROLE      YES YES NO
APPS                           CTXAPP                                     NO  YES NO
APPS                           JAVASYSPRIV                            NO  YES NO
APPS                           RESOURCE                                NO  YES NO
APPS                           XDBADMIN                                  NO  YES NO

3. Follow Doc ID 1357760.1 & 1081227.1 to avoid autoconfig error from jtfictx.sh
DECLARE
*
ERROR at line 1:
ORA-29874: warning in the execution of ODCIINDEXALTER routine
ORA-29960: line 1,
DRG-10595: ALTER INDEX JTF_AMV_ITEMS_URL_CTX failed
DRG-10758: index owner does not have the privilege to use file or URL datastore
ORA-06512: at "SYS.DBMS_SQL", line 1199
ORA-06512: at line 96
Fixes:
SQL> create role apps_ctx_role;
SQL> grant apps_ctx_role to apps;
SQL> exec ctxsys.ctx_adm.set_parameter('file_access_role', 'apps_ctx_role');
SQL> grant apps_ctx_role to JTF;     -- Grant it to the index owners
SQL> grant apps_ctx_role to AMV;
SQL> grant apps_ctx_role to APPLSYS;
SQL> grant apps_ctx_role to HR;
SQL> grant apps_ctx_role to IBC;
SQL> grant apps_ctx_role to OKC;

4. Follow Doc ID 1551854.1 & 847687.1 to avoid error from EGOSILDU.sql
DECLARE
*
ERROR at line 1:
ORA-02149: Specified partition does not exist
ORA-06512: at line 408

SQL> SELECT ATTR_GROUP_ID
     FROM EGO_FND_DSC_FLX_CTX_EXT WHERE APPLICATION_ID = 431
      AND DESCRIPTIVE_FLEXFIELD_NAME = 'EGO_ITEMMGMT_GROUP'
      AND DESCRIPTIVE_FLEX_CONTEXT_CODE = 'ItemDetailDesc';

ATTR_GROUP_ID
-------------------------
            2

SQL> SELECT 1 FROM EGO_MTL_SY_ITEMS_EXT_TL WHERE ATTR_GROUP_ID = 2
AND ROWNUM = 1;
no rows selected

SQL> SELECT 1 FROM EGO_MTL_SY_ITEMS_EXT_TL WHERE ATTR_GROUP_ID = 2
AND ROWNUM = 1;
no rows selected

Fixes: Open a SR with Oracle Support to get a datafix script from them. And run it as sysdba:
  SQL> @Partition_Fix_Script_Ver2.sql

Notes:   After I used $AD_TOP/sql/adutconf.sql to confirm module EGO is inactive, I let the failed worker to quit and then skipped EGOSILDU.sql in my Dev instance. Seems ignoring the error worked fine.

5. Make sure account OLAPSYS is not locked.
SQL> select username, account_status from dba_users like '%OLAP%';

If locked, it will hit errors in Doc ID 1054417.1:
ERROR at line 1:
ORA-33292: Insufficient permissions to access analytic workspace APPS.ODPCODE
using the specified access mode.
ORA-06512: at "SYS.DBMS_AW", line 67
ORA-06512: at "SYS.DBMS_AW", line 131
ORA-06512: at "SYS.DBMS_AW", line 984
ORA-06512: at "SYS.DBMS_AW", line 937
ORA-33262: Analytic workspace ODPCODE does not exist.     
ORA-06512: at "SYS.DBMS_AW", line 908
ORA-06512: at "APPS.MSD_AW_LOADER", line 10

Pre steps on Apps side:

1. If any schema owner password (such as HR) was changed by Sql*plus abnormally (not by FNDCPASS), the upgrade will fail because adpatch would not have the right password for that module. I had to tell all workers to quit and then "kill" adpatch process on OS level because adpatch kept remembering the bad password. After I used FNDCPASS to change the wrong password, I re-ran adpatch from the beginning and it worked.
2. Apply patch 10257394 (Doc ID 1286779.1) to avoid error with  IEX_SCORE_COMP_DET_U1
(or, copy iexscore.lct from patch .zip file and manually replace the old one)

Apply the patch:

$ unzip 6 .zip files (for patch 9239090) to same folder /path/to/R1213CD
($ unzip xxxX.zip -d /path/to/R1213CD)
$ adadmin   to enable maintenance mode
$ cd /path/to/R1213CD/9239090   <== make folder 9239090 10GB free and rw permissions
$ adpatch                                        <== it takes 3 or more hours
Then, optional step: start apps to do a quick check out.

Post steps:

$ cd 9239095
$ adpatch

$ cd 9817770
$ adpatch

$ cd 9966055
$ adpatch

$ cd $AD_TOP/bin
$ perl admkappsutil.pl       ==> then copy appsutil.zip file to DB server.

$ adconfig.sh on DB tier
$ adautocfg.sh on each apps tiers

disable maintenance mode
start apps services

remove _disable_fast_validate=TRUE

NOTES: In a R12.1.1 instance, a lot of database sessions kept in INACTIVE for a long time (15+ days) and never got closed. So, the number of db sessions increased daily and exceeded easily database parameter PROCESSES (3000). We had to recycle Apps services each two weeks. Modules AR and GL are used most in this instance and most idle sessions are from "e::bes:oracle.apps.icx.security.session.created" by JDBC Thin Client. We tried and did not find a fix to this problem. But, after the instance was upgraded to R12.1.3 and its database was upgraded to 12c, the idle session problem went away surprisingly.