Files holding EBS R12.1 configuration info

In addition to $CONTEXT_FILE, $APPL_TOP/customXXXX.env, and other standard files, such as appsweb.cfg for Forms, there are other files hold configuration data for the customization of EBS instance. Good understanding where are those files helps instance refresh and troubleshooting.

======================================

Files shall be in $FND_TOP/admin/template/custom

======================================

1. $FND_TOP/admin/template/custom/rwbuilder_conf_1012.tmp
This file is used by autoconfig to generate file $INST_TOP/ora/10.1.2/reports/conf/rwbuilder.conf. See Doc ID 1322704.1
NOTES:  When creating a custom folder, there is a risk that a patch may later bring a newer version of template file.  AutoConfig may fail with conflicts error because the patch only copies the template files to upper (standard) folder. In this case, the template file in custom folder has to be re-created.

For example, a concurrent job completed with Error :
REP-0069: Internal error
REP-57054: In-process job terminated:Finished successfully but output is voided

To correct this, a workaround on setting up a value in rwbuilder.conf file as follows:
(a) Create a custom directory under $FND_TOP/admin/template:
      $ mkdir $FND_TOP/admin/template/custom
(b) Copy the file $FND_TOP/admin/template/rwbuilder_conf_1012.tmp to this custom directory.
(c) Change line in file rwbuilder_conf_1012.tmp in custom directory from
      <property name="cacheSize" value="0"/>
     to
     <property name="cacheSize" value="50"/>
(d) Save it and then run autoconfig. rwbuilder.conf will get generated and kept with new content.

2. $FND_TOP/admin/template/ssl_conf_1013.tmp
This file is used to generate $INST_TOP/ora/10.1.3/Apache/Apache/conf/ssl.conf (same as $ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf/ssl.conf)

For example, use it to enable TLS1.0 (Doc ID 1937646.1).

Also use one line to rotate ssl_engine_log file (daily):
Replace
   SSLLog      %s_logs_dir%/ora/10.1.3/Apache/ssl_engine_log
with
   SSLLog   "|%s_weboh_oh%/Apache/Apache/bin/rotatelogs %s_logs_dir%/ora/10.1.3/Apache/ssl_engine_log %s_ohs_log_rotation_time%"

3. $FND_TOP/admin/template/httpd_conf_1013.tmp
This file is used to generate file $INST_TOP/ora/10.1.3/Apache/Apache/conf/httpd.conf

Add lines to this file to call a shell script .sh file on OS by a custom URL ebsSiteName.domain.com/CUST/bin for a custom webpage or button on a Form.
Find the true path by "grep -w s_at $CONTEXT_FILE" for s_at and replace it in below lines

  <IfModule mod_alias.c>
    ScriptAlias /cgi-bin/ "%s_weboh_oh%/Apache/Apache/cgi-bin/"
    ScriptAlias /CUST/bin/ "%s_at%/cust/html/bin/"   <== to define a directory on file system
    #
    # "%s_weboh_oh%/Apache/Apache/cgi-bin" should be changed to whatever your ScriptAliased
    # CGI directory exists, if you have that configured.
    #
    <Directory "%s_weboh_oh%/Apache/Apache/cgi-bin">
      ... ... ...
    </Directory>

    <Directory "%s_at%/cust/html/bin">                    <== add those lines. (replace s_at)
        AllowOverride None
        Options None
        Order allow,deny
        Allow from all
    </Directory>
  </IfModule>

4. $FND_TOP/admin/template/oracle_apache_conf_1013.tmp
This file is used to generate file $INST_TOP/ora/10.1.3/Apache/Apache/conf/oracle_apache.conf

For example, add one line:
include "%s_weboh_oh%/Apache/modplsql/conf/plsql.conf"    

NOTES:  Script adtmplreport.sh can find template file for Apache configuration files.
 $ cd  $AD_TOP/bin
 $ adtmplreport.sh contextfile=$CONTEXT_FILE target=$INST_TOP/ora/10.1.3/Apache/Apache/conf/ssl.conf
 It will generate a log file showing which template file is for ssl.conf.

Other files, such as
- forms_server_xml_1013.tmp
- orion_web_xml_1013.tmp

==========================================
AUTOCONFIG does not modify below files, but read them
==========================================

5. $IAS_ORACLE_HOME/Apache/modplsql/conf/dads.conf
Add below lines to this DAD file to hold the password for custom PLSQL webpages.

<Location /pls/EBSQA>
SetHandler pls_handler
Order allow,deny
Allow from All
AllowOverride None
PlsqlDatabaseUsername apps
PlsqlDatabasePassword appsPWD
plsqlDatabaseConnectString dbServerName.domain.com:1562:EBSQA
PlsqlAuthenticationMode Basic
PlsqlDefaultPage fnd_web.ping
</Location>

6. Files in $INST_TOP/certs/Apache folder. 

They are ssl certificates if ssl is enabled for the site.

7. $APPL_TOP/admin/adsign.txt and $APPL_TOP/admin/adkeystore.dat

They are used for Java Signing.

8. $FND_TOP/fndenv.env

 $CUSTOM_TOP will be defined in this file.

… … … 

# Call devenv.env to set up development environment

        . ${FND_TOP}/${APPLUSR}/devenv.env

#Begin Customizations
MYGL_TOP=$APPL_TOP/mygl;   export MYGL_TOP
#End Customizations

===============================

AUTOCONFIG modify and re-create them

===============================

9. $INST_TOP/ora/10.1.2/forms/server/default.env
This file holds custom Forms location.
… … …
#Begin Customizations
MYGL_TOP=$APPL_TOP/mygl
#End Customizations

Building an Index ONLINE

Creating an index on a busy table may fail when user's DML is using the table (in 11G database).

SQL> CREATE INDEX owner.index_IDX1 ON owner.table_name(col1, clm_id, col2) TABLESPACE tablespace_name;
    *
ERROR at line 1:
 ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Specify ONLINE to indicate that DML operations on the table will be allowed during creation of the index. Oracle will create an extra log table to keep track of any data changes without locking the table. There are restrictions on Online Index Building. One of them is that parallel DML is not supported during online index building. Oracle Database returns an error, if you specify ONLINE and then issue parallel DML statements.

Besides, other options, such as PARALLEL and NOLOGGING, will speed up the index creation.

SQL> CREATE INDEX owner.index_IDX1 ON owner.table_name(col1, clm_id, col2) TABLESPACE tablespace_name PARALLEL ( DEGREE 4 ) nologging online;
Index created.

With parallel, Oracle may automatically spawn parallel executions for every single SELECT statement. After that, the two options can be turned off by below statement:

SQL> alter index owner.index_IDX1 logging noparallel;

Extra notes: statement to turn on monitoring index usage (usually shall exclude PK from monitoring):

SQL> alter index owner.index_IDX1 monitoring usage ;

Apply EBS R12.1 CPU patches (October 2015)

Apps CPU (Critical Patch Update) patches involve different areas. Normally CPU patches are applied at the same time to EBS apps and two ORACLE_HOMEs. Doc ID 2051000.1 has the instruction for October 2015 CPU patches.

1. Database patches

In my 12.1.0.2 database, database October 2015 PSU and other patches were applied. But most of them are for other purposes, and are not pre-requisites for EBS Oct 2015 CPU.
    Patch            Conflict patch
    21520444    Combo of 12.1.0.2.5 OJVM PSU and 12.1.0.2.5 DB PSU (Oct 2015)
    6880880      Latest OPatch
X 19382851     Already included in 12.1.0.2.5 PSU
X 19393542     Already included in 12.1.0.2.5 PSU
X 19627012     Already included in 12.1.0.2.5 PSU
X 19649152     Already included in 12.1.0.2.5 PSU
    20204035    12.1.0.2.0 version. No conflicts
    13542050    A mutex related hang with holder around 65534 (0xfffe)
    19855835    Upgrade slow when reorganizing large stats history tables
    20879889    Open cursor leak from DML on table with a materialized view log
    20807398    ORA-600 [kgl-hash-collision] with fix to bug 20465582 installed
    21091518    Extend fix of bug 18304693 to Partition Views

2. Patch 21845960 (CPU Oct2015 Fusion Middleware 10.1.3.5)

$ cd $INST_TOP/ora/10.1.3
$ . xxxx.env                           <== source the env file there
$ echo $ORACLE_HOME       <== make sure it points to 10.1.3 IAS_ORACLE_HOME
/path/to/apps/tech_st/10.1.3

$ unzip p21845960_101350_LINUX.zip
$ export OPATCH_PLATFORM_ID=46         <== need it on 64-bit server
$ cd 21845960
$ opatch napply               <== take about 15 minutes and roll back previously installed CPU patches

Notes:
(1) After 21845960 was applied, "opatch lsinventory | grep 21845960" will show nothing because it is a CPU patch.  But, below line shall show 21845942 (one of the included patches) was installed
$ opatch lsinventory | grep 21845942
(2) Without "export OPATCH_PLATFORM_ID=46" on 64-bit server, opatch exited with error message "NApply failed to apply the following patches "21815758". After that, I tried "opatch napply -skip_duplicate" (I used this option when applied database SPU patch), it says "The input directory could not processed". Then, I went to sub-folder 21815758 and used "opatch apply" to apply it successfully.

3. Patch 21103001
NOTES: This patch replaces CPU Oct2014 Fusion Middleware 10.1.2.3 patch 19434967. See Doc ID 437878.1 CHANGE LOG of Oct 16, 2015.

Start a new OS session or $ . .profile     <== run the main env file
$ echo $ORACLE_HOME      <== make sure it is the 10.1.2 ORACLE HOME
/path/to/apps/tech_st/10.1.2

(1) p14825718_10123_LINUX.zip  (asked by 21103001 README)
   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.
     - 6995251, 7121788 were APPLIED.

$ cd 14825718
$ opatch apply
Note1: README of Bundle Patch is not applicable to E-Business Suite Customers.
Note2: It may rollback some patches. If you get below message, answer "N" to continue:
If you continue, all patches will be rolled back and the new patch (14825718) will be installed.
Do you want to STOP?
Please respond Y|N > N

(2) p6640838_10106_Linux-x86-64.zip  (asked by 21103001 README)
This patch 6640838 only added missing components to OUI 10.1.0.6.0, not install a new OUI
Steps with screenshots: Doc ID 1301320.1 How to patch OUI for installing overlay patches on top of Forms Bundle Patch - 9593176

$ mv $ORACLE_HOME/OPatch $ORACLE_HOME/OPatch.pre_6640838
to reserve the higher version of opatch.

$ cd cd/Disk1/install
$ ./runInstaller -ignoreSysPrereqs

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

(3) p8551790_10123_LINUX.zip  (asked by 21103001 README)

$ cd 8551790
$ opatch apply

(4) p21103001_101232_LINUX.zip

$ cd 21103001
$ opatch apply             <== very quick
Note1: Post steps are not needed for EBS. See "Post Steps For Patch 19434967 Files Do Not Exist (Doc ID 1945012.1)"
Note2: If opatch version is older than 1.0.0.0.63, it may give a misleading error message:
The patch directory area must be a number.
ERROR: OPatch failed because of problems in patch area.

4. Run the new adgrants.sql as SYSDBA ( get it from 21507207/admin/adgrants.sql after unzip p21507207_12.1.0_R12_LINUX.zip). Without it, ADFIXUSER.sql will fail.

SQL> @adgrants.sql apps  ( <- lower case)
... ... ...
Start of Creating AD_JAR context
PL/SQL procedure successfully completed.
End of Creating AD_JAR context
Creating PL/SQL Package AD_DBMS_METADATA.
Package created.
Package body created.
End of Creating PL/SQL Package AD_DBMS_METADATA.
Creating PL/SQL Package AD_ZD_SYS
Package created.
Package body created.
End of Creating PL/SQL Package AD_ZD_SYS.
PL/SQL procedure successfully completed.
Commit complete.

5. Check pre-requisites on EBS apps

select * from ad_bugs where bug_number in (
'9239089',    -- R12.AD.B.Delta.3
'19273341',  -- R12.BNE.B.delta.4
'8919491',    -- R12.ATG_PF.B.Delta.3
'18936791',  -- R12.FWK.B.delta.4   see Don ID 1931412.1
'19393542'   -- 12.1.0.2 database patch. It will not show up here :)
);

6. Apply R12.1 Oct2015 CPU 21507207

In my system, I merged 19273341, 18936791, 21507207 together and applied them in one run by adpatch. It may take more than 1 hour on first node (around 18 minutes silence on afprgshrlog.sql !).

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.

Configure printer to work with EBS

This summary is not available. Please click here to view the post.