Java Mailer Setup Diagnostic Test

Source $APPL_TOP/$APPLFENV on the Current Manager node assigned to the Workflow Mailer.
unzip ATGSuppJavaMailerSetup12.zip
./ATGSuppJavaMailerSetup12.sh
It needs following parameters to pass
1)apps user
2)apps user password and following
3)user paswd
PARAMETER_ID PARAMETER_NAME VALUE
------------ --------------- -------------------------
10018 ACCOUNT oracle
10033 INBOUND_SERVER smtp.corp.mycompany.com
10043 OUTBOUND_SERVER smtp.xyz.mycompany1.net
10053 REPLYTO oracle@mycompany.com

Use the following script to get above listed parameters
sql script:
col PARAMETER_NAME format a15
col VALUE format a25
select p.parameter_id,
p.parameter_name,
v.parameter_value value
from fnd_svc_comp_param_vals_v v,
fnd_svc_comp_params_b p,
fnd_svc_components c
where c.component_type = 'WF_MAILER'
and v.component_id = c.component_id
and v.parameter_id = p.parameter_id
and p.parameter_name in ('OUTBOUND_SERVER', 'INBOUND_SERVER', 'ACCOUNT', 'REPLYTO')
order by p.parameter_name;

Ref: Oracle Workflow ATG Support: R12 Java Mailer Setup Diagnostic Test [ID 748421.1]

Unable to connect Discoverer Admin using sysadmin

1. You need to create a folder called SECURE under your ORACLE folder:

eg. make a directory like c:\oracle\secure

2. Copy the DBC File from the E-Business Suite database and place it in the secure directory. It will be located in the FND_SECURE directory.

3. Select the Environment tab and create new System Variables

FND_SECURE =\secure in step 1 above: eg. c:\oracle\secure

4. Update your %ORACLE_HOME%/network/admin/tnsnames.ora to include an entry for the E-Business Suite database. The database name must match the two_task entry in the dbc file.

5. You may have to rename the dbc file so that it matches the name by which you are referring to the database in your TNS Names file

6. Make sure that TNSPING works and that you can get a connection to the database

7. You should be able to log in ok now

Ref:Connecting to an E-Business Suite EUL Using Any Discoverer Component Fails With "Authentication Failed" or "ORA-01017: invalid username/password;logon denied" [ID 364665.1]

Script to monitor jdbc connections

set pause off
REM JDBC Scripts used to clear JDBC INACTIVE connections:

REM Script1: It will capture the complete JDBC Connection repot:

spool prod_jdbc_update_jan12_11.log
select count(*),
substr(machine, 6, instr(machine,'.')-6) machine, process, module from gv$session where program like 'JDBC%'
group by machine, process, module
order by 1 asc ;

prompt 'executing Script2: Capture the all INACTIVE sessions list:...'

set lines 200
set head off
column machine format a35
column logon_time format a19
column spid format a10
column process format a10
column module format a18
column action format a10

select '------------------------------'||to_char(sysdate,'mm-dd-yyyy hh24:mi:ss')||'-------------------------------------' Time from dual;
set head on
set pagesize 1000
SELECT
s.sid||','||s.serial# sid_ser,
s.process
,substr(s.machine, 6, instr(s.machine,'.')-6) machine,
s.program,
s.MODULE,
to_char(s.logon_time,'mm-dd-yy hh24:mi:ss') Logon_Time,
round(s.last_call_et/3600) Last_Call_ET
FROM gv$session s, gv$process p
WHERE s.module like 'JDBC%'
and s.paddr = p.addr
and s.username is not null
AND s.module = s.program
and s.inst_id=p.inst_id
and s.status='INACTIVE'
and s.type != 'BACKGROUND'
ORDER BY round(last_call_et),logon_time, s.process,s.machine, s.program, s.MODULE;

prompt 'executing Script3: It will capture the JDBC Connections list older than 12 hours:..'

set lines 200
set head off
column machine format a35
column logon_time format a19
column spid format a10
column process format a10
column module format a18
column action format a10
select '------------------------------'||to_char(sysdate,'mm-dd-yyyy hh24:mi:ss')||'-------------------------------------' Time from dual;
select 'Following is the JDBC Connections list older than 12 hours , are killed' from dual;
set head on
set pagesize 1000
SELECT
s.sid||','||s.serial# sid_ser,
s.process
,substr(s.machine, 6, instr(s.machine,'.')-6) machine,
s.program,
s.MODULE,
to_char(s.logon_time,'mm-dd-yy hh24:mi:ss') Logon_Time,
round(s.last_call_et/3600) Last_Call_ET
FROM gv$session s, gv$process p
WHERE s.module like 'JDBC%'
and s.paddr = p.addr
and s.username is not null
AND s.module = s.program
and s.inst_id=p.inst_id
and s.status='INACTIVE'
and s.type != 'BACKGROUND'
and s.last_call_et/3600 > 12
ORDER BY round(last_call_et),logon_time, s.process,s.machine, s.program, s.MODULE;

prompt 'executing Script 4 : It will capture the list of JDBC Connection list and spool to /tmp/db_prod_kill_jdbc_sessions.lst which has the list to clear sid,serial#...'

set head off
set feedback off
spool /tmp/db_prod_kill_jdbc_sessions.lst

select 'alter system kill session '''||s.sid||','||s.serial#||''';'
FROM gV$SESSION s , gV$PROCESS p
WHERE s.paddr = p.addr
AND s.module = s.program
and s.inst_id=p.inst_id
and s.username IS NOT NULL
and s.type != 'BACKGROUND'
AND round(s.last_call_et/3600) > 12
and s.module like 'JDBC%'
and s.status='INACTIVE';

spool off

prompt 'executing Script5: It will capture the list of OS processes corresponding to JDBC Connection list (script 4) and spool to /tmp/jdbc_inactive_session.sh ..'


spool /tmp/jdbc_inactive_session.sh
SELECT ' kill -7 '||s.process
FROM gV$SESSION s ,gV$PROCESS p
WHERE s.paddr = p.addr
AND s.module = s.program
and s.inst_id=p.inst_id
and s.username IS NOT NULL
and s.username is not null
and s.type != 'BACKGROUND'
AND round(s.last_call_et/3600) > 12
and s.module like 'JDBC%'
and s.status='INACTIVE';
spool off

exit

The users that fails do not have access to the Business Area (BA) used by the worksheet

Cause
The users that fails do not have access to the Business Area (BA) used by the worksheet.
Solution
In Discoverer Administrator :
Use Tools / Security pull down menu.
Search for user(s) that fail, move required BA(s) from Available to Selected.
Select OK button (or Apply and then OK button)

or

Refer Note : Unable To Use EULAPI to Grant Privilege to Multiple Responsibilities [ID 430945.1]
Syntax Eg:$ORACLE_HOME/bin/eulapi -CONNECT eul4_us/eul4_us@PRDN -GRANT_PRIVILEGE -ROLE "All INV Super User" -PRIVILEGE all_user_privs -log /tmp/eul_14.log

Query for Appliation Products Installed

col application_short_name format a23
col APPLICATION_NAME format a27
col PATCH_LEVEL format a15
select fav.application_short_name
,fav.application_name
,fpi.patch_level
,decode(fpi.status,'N','None','I','Installed','S','Shared')
from fnd_product_installations fpi
,fnd_application_vl fav
where fav.application_id=fpi.application_id
order by 1;

how to know oracle forms patchset

- Login to the application
- Navigate to Help > About Oracle Applications

Eg:
Forms Server
----------------------------------------
Oracle Forms Version : 6.0.8.27.0

use the following note to know the corresponding Developer 6i Patchset
Note: 125767.1 - Upgrading Developer 6i with Oracle Applications 11i
Note: 232313.1 - Information on Previous Versions of Developer 6i Patchsets
Eg:
Forms: 6.0.8.27.x --> Developer 6i Patch 18
Forms: 6.0.8.26.x --> Developer 6i Patch 17
Forms: 6.0.8.25.x --> Developer 6i Patch 16
Forms: 6.0.8.24.x --> Developer 6i Patch 15

ORA-12152 "Unable To Send Break Message" on Connection

When trying to log on to the database from a client when using a Cisco GSS and a CSM (Content Switch Module) the connection fails to complete or fails before receiving any data with a network related error:
ORA-12152: TNS:unable to send break message

Solution:
To resolve such an issue, make sure all switches or firewalls are set to the highest possible value and test. Once all connections are successful, the idle time limit can be tuned downward to a value that won't cause an interruption. This type of configuration would normally be handled by the Network Administrator of a system so this information should be passed on to them for actioning.

Ref:ORA-12152 "Unable To Send Break Message" on Connection [ID 363638.1]

Check if product is implemented/installed

select fa.APPLICATION_SHORT_NAME, fpi.PATCH_LEVEL, DECODE(fpi.STATUS, 'I','Installed',
'S','Shared', 'N', 'Inactive', fpi.STATUS) Status , fpi.DB_STATUS
from fnd_product_installations fpi,FND_APPLICATION fa
where fpi.APPLICATION_ID in (
select APPLICATION_ID from FND_APPLICATION where APPLICATION_SHORT_NAME like '')
and fa.APPLICATION_ID=fpi.APPLICATION_ID;

or

a) Source the applications environment file for Admin node
b) cd $AD_TOP/sql/
c) Execute:
sqlplus apps/ @adutconf.sql

Ref:How to check if certain Oracle Applications product/module is implemented? [ID 443699.1]

FRM-92150 Web Client Too New

Solution:

1. Open JInitiator from the Control Panel.
2. Open the "Cache" Tab.
3. Click the button "Clear JAR Cache".
4. Re-run the Form.
or
1. Open Java from the Control Panel.
2. Open press button "settings" on the general tabpage.
3. Click the button "delete files" to delete the cached jarfiles.
4. Re-run the Form.

Ref:Troubleshooting: FRM-92150 Web Client Too New [ID 416429.1]

A Guide to Configure, Maintain & Troubleshoot JDBC Buffers in Oracle Application

A Guide to Configure, Maintain & Troubleshoot JDBC Buffers in Oracle Application

What Is a JDBC Driver ?
=======================

JDBC technology is an API (included in both J2SE and J2EE releases) that provides cross-DBMS connectivity to a wide range
of SQL databases and access to other tabular data sources, such as spreadsheets or flat files. The JDBC API is the industry standard for database-independent connectivity between the Java programming language and a wide range of databases. The JDBC API provides a call-level API for SQL-based database access.

The JDBC API makes it possible to do 3 things:

* Establish a connection with a database or access any tabular data source
* Send SQL statements
* Process the results

How the Connections are made :
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

* For the JDBC Thin Driver:
Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:@", "", "");
where, is a string of the form :: or a SQL*net name-value pair.
Get these values from the tnsnames.ora.

Where are the JDBC settings in Applications ?
=====================================

They are made in the DBC file(s) in $FND_TOP/secure. There can be two dbc files instance_sid.domain and instance_sid.
The values in the DBC file will override any values set in SSP_INIT.txt, but you should not put these values in ssp_init.txt anymore. The file 'ssp_init.txt' is now only used for iProcurement specific settings.

How the JDBC pool behaves with the following Parameter set:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

FND_MAX_JDBC_CONNECTIONS=100
FND_JDBC_BUFFER_MIN=5
FND_JDBC_BUFFER_MAX=25%
FND_JDBC_BUFFER_DECAY_INTERVAL=60
FND_JDBC_BUFFER_DECAY_SIZE=1
FND_JDBC_USABLE_CHECK=true
FND_JDBC_CONTEXT_CHECK=true
FND_JDBC_PLSQL_RESET=false

Note We are changing the default values for the parameter FND_JDBC_MAX_CONNECTIONS to 100 in Bug:3186367.

When Apache starts, it will create a buffer of 5 JDBC connections (FND_JDBC_BUFFER_MIN=5) plus a few more as java
dependent products initialize. As users log-in, those 5 connections will get used and more connections will be created to
replenish the buffer. These connections will increase as needed up to the limit of 100 (FND_MAX_JDBC_CONNECTIONS).
If the buffer decay interval is too high then the pool cleanup thread will not kick in often enough.
The default value is 60 seconds. If the interval is too high and the decay size is too small then insufficient number of
connections will be cleaned each time the cleanup thread sweeps the pool. The default for decay size is 1.

You may want to up the decay size and lower the interval to make the pool cleanup thread more aggressive.
I suggest you the following:
FND_JDBC_BUFFER_DECAY_INTERVAL = 60
FND_JDBC_BUFFER_DECAY_SIZE = 1
As users log off, the JDBC connections will die off at a rate of 1 every 60 seconds
(FND_JDBC_BUFFER_DECAY_SIZE=1, FND_JDBC_BUFFER_DECAY_INTERVAL=60), but this decay won't start until you have reached 25 JDBC connections (25% of 100 where FND_JDBC_BUFFER_MAX=25% and FND_MAX_JDBC_CONNECTIONS=100).
The decay will slowly reduce the number of inactive JDBC connections down to FND_JDBC_BUFFER_MIN=5, with the least
recently used ones being removed first.

Note that each of these parameters are PER JVM.
If your jserv.conf has 3 groups of 1 JVM each (OACoreGroup, DiscoGroup, XmlSvcsGrp) for a total of 3 JVMs,
each with a FND_JDBC_BUFFER_MAX of 25 for a total of 75.

Recent Changes
==============

Bug: 1467681 states that The parameter used to configure the high end of the buffer range, FND_JDBC_BUFFER_MAX replaces the deprecated connection pool parameter FND_UNUSED_CONNECTION_TIMEOUT. The main difference in functionality is that the NEW Algorithm removes connections based on the size of the buffer, whereas the OLD Algorithm removed them based on How long they had been available.

Rather than timing out a JDBC connection we check the size of the JDBC buffer and compare it to FND_JDBC_BUFFER_DECAY_SIZE every FND_JDBC_BUFFER_DECAY_INTERVAL.
If it has Not yet reached this size then the connection is left.

The other deprecated connection pool parameter, FND_IN_USE_CONNECTION_TIMEOUT, was replaced some time ago by an internal mechanism that determines whether to remove locked connections based on whether the borrowing thread is still alive, instead of on a set expiration time.

More specifically 11.5.10 (or ATG_PF.H) do not include any change of algorithm moving leaked connections to available pool. The changes where performed at applications level and not at ATG level. There was, a code sweep to find offending code where there was a request for a connection to the pool but the connection was never returned so each product team fixed each
situation adding code to return the connection to the pool.

In case you find any, note down the specification of reusing the leaked connections. If there are such cases, you should find out what module is doing that and log a tar with that specific product team.

The only valid case where this might happen is in OA Framework where they preallocate jdbc connections for the AM pool.

+ Keep in mind even when the
> Total number of AOLJ JDBC connections reaches FND_MAX_JDBC_CONNECTIONS and any new
connections cannot be created Leaked Connections are Not reused.
> Garbage collection runs No leaked connections are released to the available pool.

What are the Implications of the Change
=================================

The key parameter of the above Section is always FND_JDBC_MAX_CONNECTIONS.
In older versions of the apps, the default value for this used to be unreasonably high. So, first thing I would always do would be to reset the max value to something more reasonable.
You can check/reset it in your AutoConfig xml file - the parameter is called s_fnd_max_jdbc_connections.
More recent installs have a reasonable value here (in the hundreds rather than the billions).
The BUFFER_MIN and BUFFER_MAX parameter control the no. of "free" connections that are maintained in the pool.
Less than BUFFER_MIN, and some new ones get opened up. More than BUFFER_MAX, and some get closed. That is entirely separate from the total no. of connections that are in the pool at any point - in a busy system, you might have several hundred in use at once.
Total no. of connections in the pool = connections in use + connections free

BUFFER_DECAY_INTERVAL and BUFFER_DECAY_SIZE are pretty much self-explanatory - every BUFFER_DECAY_INTERVAL, some process wakes up and decides whether or not there are any connections to close - if there are, it will close them, up to BUFFER_DECAY_SIZE at a time.

Also any of these will Not be affected much by how/whether users log out. In the course of any given self-service user session, you will be picking a random JDBC connection out of the pool each time you want to access the database. Any given session, of a reasonable length, might use dozens of different JDBC connections over time. The JDBC connection pool, neither knows nor cares whether a given user is still logged in - it only cares how many different user sessions need database access right now.

Another useful parameter is FND_JDBC_USABLE_CHECK.
Set it to TRUE if you want to check whether each connection is working OK before you give it to a user.
Can be very helpful in a RAC environment, say, if one of your instances goes down.

How to Monitor JDBC Connections through Oracle Applications Manager (OAM) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



Login to OAM directly or via the "System Administration" responsibility.
Select the "JServ Usage" under the Monitoring section in OAM
Click the "Expand all" link to list the Servers and all the JServ processes for OACoregroup
This shows memory usage, connections (including "Potentially Leaked") and Application Module information
You can click the "Add to support cart" to capture this page

If there are any non zero values for "Potentially Leaked" then click on this number to drill down into the details
Select "Yes" for the "Filter by Potentially Leaked" option and click "Go" button
Then click "Show all details" to display the Java Thread dump for all the potentially leaks Java connections
This screen does not have the option to add to support cart, so instead use the Browser facility to "Save as" web page

Repeat these steps all of the JVM with potentially leaked connections (or at least three)
Then upload the summary page as well as the web pages you have saved to Oracle Support for review.

A Stray point to keep in mind is sometimes Not all connections shown in 'Red' are leak, although it shows up in red.
Eventually they get released once the root AM for the transaction is released.

Performance Tuning
==================

The perception is that the current JDBC communications work well when they are co-located with the server, but not well over the WAN. So the frequent questions asked are :

- How do you architect the application and communications to be the most performant over the WAN or LAN?
- What should you avoid and what should you take advantage of when considering JDBC communications in terms of Best
Practices?

For Apps deployments, the JDBC thin driver will be deployed in the Apps middle-tier which should always be collocated with the data server. It is strongly discouraged to introduce a WAN between the App tier and the data server.
I assume you are referring to the one or two modules which require the JDBC driver on the desktop rather than the general
middle-tier. ATG development have worked closely with the JDBC team to improve performance of the driver as well as
minimize round-trips. The 10g driver uses bundled calls, and this reduces round-trips by a factor of 2 overall and a factor of 3 for some of the SQL calls. The 10g driver will be certified soon with Apps.

Currently all you need to do is make sure you are on the latest JDBC driver. Refer to Note: 164317.1.

What is a Connection Leak ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~

A connection is considered "leaked" when the thread that requested the connection has died. But, there could be the case that this thread might have spawn another thread and passed the connection to it.
However, that doesn't mean that the connection is really dead. It is quite possible that the new spawned thread is still really performing some database activity and, since there is no way to determine that, we do not reclaim the connections.

It is the responsibility of the calling thread to return the connection back to the pool once the DB activity has ceased. So, with this premise in mind, you will find fixes for JDBC connection leaks for almost any module.

There are 3 main Reasons Why JDBC connections often do Not get Released -~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

a) The JDBC connection pool is retaining (unused) connections longer than is wanted
b) The application is creating to many connections, either because of the work it is doing (number of users) or because it is Not closing connections once it is finished with it. In this case, when users are logging out the connections are not decreasing, however it is also possible that there is a Concurrent Job running that could be taking up JDBC connections.

c) Some kind of issue with the JDBC drivers. May be worth considering upgrading the JDBC drivers anyway.

To prove if option (a) is an issue is relatively quick and easy to do (but will require iAS to be bounced on each middle tier) and I recommend doing this as soon as possible. You need to tune Applications towards minimizing DB connections instead of outright performance. This is likely to have a small negative effect on user perceived performance, but would not expect a major drop in performance. It is a small risk you need to take into account when deciding to implement this suggestion.

Make the following changes (or add entries if not there) in all the DBC files of each middle tier.

FND_JDBC_BUFFER_MIN=0
FND_JDBC_BUFFER_MAX =100
FND_JDBC_BUFFER_DECAY_INTERVAL=30
FND_JDBC_BUFFER_DECAY_SIZE=200

In profile options, change "FND: Application Module Pool Enabled " to a value of "No"
You will then need to bounce at least iAS on each middle tier machine for these changes to take effect.

Another small reduction in initial DB connections can be made by changing the "zone.properties" file for each middle tier machine, you can comment out these two lines in the "Startup Servlets" section :-

servlets.startup=oracle.apps.ecx.oxta.TransportAgent
servlets.startup=oracle.apps.fnd.tcf.SocketServer

If the above changes makes no difference, I believe we would need to start considering what applications you are using to
identify any applications that may be retaining DB connections as per suggestion (b) listed above.

To try and trace this problem I would make the following suggestions:-

Enable logging of the JDBC connection pool. In the Jserv.properties file, add the following

wrapper.bin.parameters=-DAFLOG_ENABLED=TRUE
wrapper.bin.parameters=-DAFLOG_LEVEL=STATEMENT
wrapper.bin.parameters=-DAFLOG_MODULE=fnd.security.DBConnObjPool,fnd.security.DBConnObj
wrapper.bin.parameters=-DAFLOG_FILENAME=/tmp/pool.log

Bounce Apache and monitor progress. Also periodically Capture the information from AoljDbcPoolStatus.jsp and save to a
file so that you can see this output later.

Now, a different story is if there is a module which is leaking connections (JDBC Connections shown in RED in
AoljDbcPoolStatus.jsp). If for performance reasons you want to tune up the JDBC connection pool in order to free resources in the DB server and have the connections decreased, you can use this note as a guideline to achieve the desired configuration.

If it becomes clear that the DB connections are being eaten by the JDBC connections then having more JVM wont actually
help this situation, although it may well help once the connection issue has been resolved.

+ Keep in mind that according to Bug 3729725, the connections that are grabbed from WFContext are not an item to worry
about for connection leaks (so in other words these are shown in "red" text in AOLJPOOL but this is a bug as these are not dead connections)

Actually you can change
FND_JDBC_BUFFER_MAX=100
to
FND_JDBC_BUFFER_MAX=0
which may make it easier to identify actual connections.
Also note down the stack generated by AoljDbcPoolStatus.jsp over 15 Min's intervals

If you see the connections disappearing, 2 things might be happening :

1) Bug #4452532 ( Locked connections being used by other threads. This is Not a new feature).
2) For some reason the JVM is dying and when it restarts, it creates a new pool.
In order to confirm, please check the new CLIENT_PROCESSID system property. In this case, the value should be different.

Debug Guide
=============

First thing to be clarified is a phrase often used to describe a JDBC problem.
They say "spinning JDBC connections" but the problem actually often is that the maximum sessions are reached rather than the server being at 100% CPU, which is what I associate the word "spinning" with.
So please make sure whether the Middle Tier (or Database) server is at 100% CPU, or whether CPU is normal, but one of the middle tiers has a large number of JDBC connections that never decreases ?

It is obviously important we understand the exact symptoms .Most of the time it is also unclear what the JDBC connections are, so in order to get some additional information.

Please run the following script every 10 minutes for one hour and note down the output from each run.

REM START OF SQL
set pagesize 132
set linesize 100
column inst_id format 9999
column machine format A15
column program format A25
column module format a35
column how_many format 99999999

spool list_sessions.txt

select to_char(sysdate, 'DD-MON-RR HH24:MM:SS') when_run from dual
/
select inst_id, machine, program, module, count(*) how_many from gv$session
group by inst_id, machine, program, module
/
select count(*) from v$session;
/
select count(*) from v$session where status='INACTIVE';
/
select count(*) from v$session where status='ACTIVE';
/
select machine, osuser, program, count(*) from v$session group by machine, osuser, program order by 4 desc;
/
select count(status) Count, status, machine, program from v$session where program like '%JDBC%' group by status, machine,
program;
/
select count(status) Count, status, machine, module from v$session where program = 'JDBC Thin Client' group by status,
machine, module;
/
select serial#, substr(program,1,20) program, status, to_char(logon_time,'DD-MON-YY HH24:SS') Login_Time,
to_char(sysdate-last_call_et/86400,'DD-MON-YY HH24:SS') Last_Activity
from v$session where program like 'JDBC%' order by 4;
/

spool off
REM END OF SQL

+ Keep in mind Regarding connections,
The INACTIVE status of the JDBC connections is normal, so this in itself does not mean anything.

Some Preventive Measures During Problem Times
=========================================

1. Implement a strategy to minimize the JDBC connections
------------------------------------------------------

If JDBC connections are being retained in the pool then this will ensure connections are dropped as soon as the application has finished with it. Apart from ensuring minimum DB connections it will also help to identify if there is a JDBC connection leak

a) Disable AM Pool
Change the following profile options from "Yes" to a value of "No" at SITE level
FND: Application Module Connection Pool Enabled
FND: Application Module Pool Enabled

b) De tune JDBC connection pool
Do through Autoconfig (or manually update DBC file) on all Middle Tier servers
FND_JDBC_BUFFER_DECAY_INTERVAL=120
FND_JDBC_BUFFER_MIN=0
FND_JDBC_BUFFER_MAX=0
FND_MAX_JDBC_CONNECTIONS=256
FND_JDBC_USABLE_CHECK=true
FND_JDBC_BUFFER_DECAY_SIZE=5

Note : "FND_JDBC_USABLE_CHECK=true" is preferred for RAC, as discussed in note 294652.1

2. Update TCP Parameters
-------------------------

Make sure the current TCP settings are as recommended by Oracles tcpset.sh script and also OS vendor web site

Parameter Recommended value
tcp_ip_abort_interval 60,000
tcp_keepalive_interval 900,000
tcp_rexmit_interval_initial 1500

3. Take Care of jserv.properties "security" settings
--------------------------------------------------

Make sure the current settings does not allow more connections to the JVM than JDBC connections, which is not best practice. Changing security.backlog, in particular, can lead to user connections hanging.
Maintain default settings
security.maxConnections=256
# security.backlog=5

4. Check ApJServRetryAttempts parameter in Jserv.conf
-------------------------------------------------------

This setting will delay any recovery of a dead JVM by mod_oprocmgr. If a JVM is not responding in 4.5 minutes (default setting) then tuning should be implemented to resolve this, rather than allowing 45 minutes for no response.
Maintain default settings
ApJServRetryAttempts 3

5. Disable JVM Distributed Caching
----------------------------------

If some JVMs out of many are not servicing requests and generating "java.lang.NoClassDefFoundError" errors. Disabling Distributed JVM caching would eliminate this from being the cause of the problem.

Disabling Distributed JVM Cache is achieved by changing "LONG_RUNNING_JVM=" from "true" to "false" in the jserv.properties. This is controlled by AutoConfig parameter "s_long_running_jvm"

6. Prevent wastage of database connections on the system by setting the profile option
'FND: Application Module Pool Minimum Available Size' value to 0 (which is the default).

After doing the above monitor the progress in terms of connection utilization. Also keep collecting the following information from system periodically:

column module heading "Module Name" format a48;
column machine heading "Machine Name" format a15;
column process heading "Process ID" format a10;
column inst_id heading "Instance ID" format 99;
prompt
prompt Connection Usage Per Module and process
select to_char(sysdate, 'dd-mon-yyyy hh24:mi') Time from dual
/
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select count(*), machine, process, module from gv$session
where program like 'JDBC%' group by machine, process, module order by 1 asc
/

7. For processes that show up with the highest counts in query result above, Log in to the JVM machine and run

"kill -3 jvmid".
where jvmid is the process seen in query output above.
The output goes to OACoreGroup.*.stdout file. Post those files to Oracle Support.

8. If there seems to be a lot of blocked sessions in the database. Check 2/3 locked/blocked sessions to ascertain the user, sql and row locked.

You can use utllockt.sql ( Note:166534.1 describes this in more detail ) as the starting point.
Post the output from utllockt.sql in addition to the SQL/row lock information for 2/3 sessions to Oracle Support.

Before Engaging Oracle Support
===============================

+ Enable response time in access_log file
To enable the response time in the access_log file add the following entry to httpd.conf This format is required when using "rotatelogs" which is enabled by default
Note - This change is not AutoConfig enabled, so may need to be placed in a custom block or the change re-applied after
running AutoConfig : LogFormat "%h %l %u %t \"%r\" %>s %b %T"

+ At lunchtime and also at the end of the day, collect new set of log files listed below as an "information pack" of data
to analyze The following files from the $IAS_ORACLE_HOME/Apache/Apache/logs directory

a) access_log.
By default rotatelogs is enabled, so there is a separate log file for every 24 hours.
You need to identify which log file relates to the current time period

b) error_log
The following files from the $IAS_ORACLE_HOME/Apache/Jserv/logs directory ** for all the files in this section, once
you have uploaded these files, please archive or remove them, so we get a new log file for the next incident

c) mod_jserv.log
d) jserv.log

Note - It seems jserv.log does not exist as JServ logging needs to be enabled by setting the AutoConfig variable "s_oacorelog" variable to a value of "true" and re-running AutoConfig

e) All files from the $IAS_ORACLE_HOME/Apache/Jserv/logs/jvm directory
These will have file names of the form ..stdout|stderr
For example OACoreGroup.0.stderr
Note - please rotate these log files every 24 hours to ensure only relevant information is gathered

f) Alert.log file from RDBMS server
g) Hourly reports from the Statspack output for the time period concerned

+ Provide exact Java Version With the latest versions of AutoConfig for Unix platforms, the jserv.properties file
"wrapper.bin=" entry will point to a java.sh script
(normally $ORACLE_HOME/../iAS/Apache/Apache/bin/java.sh), which was implemented in order to allow the capture of the stdout and stderr output. Refer to this java.sh file to find the explicit path to the java binary, which is referred to as JSERVJAVA in the script. Run the following command to identify the version of Java being used :-
/java -version

You can use the following script to automate the above step
sh -c "`awk -F= '$1 ~ /^JSERVJAVA.*$/ {print $2}' \
$ORACLE_HOME/../iAS/Apache/Apache/bin/java.sh` -version;"

+ Provide the current JDBC driver version in the system through the output from the following SQL :

REM
REM START OF SQL
REM
set echo on
set timing on
set feedback on
set pagesize 132
set linesize 80
REM check ad_bugs for any of these patches
select bug_number, decode(bug_number,
'3043762','JDBC drivers 8.1.7.3',
'2969248','JDBC drivers 9.2.0.2',
'3080729','JDBC drivers 9.2.0.4 (OCT-2003)',
'3423613','JDBC drivers 9.2.0.4 (MAR-2004)',
'3585217','JDBC drivers 9.2.0.4 (MAY-2004)',
'3882116','JDBC drivers 9.2.0.5 (OCT-2004)',
'3966003','JDBC drivers 9.2.0.5 (OCT-2004)',
'3981178','JDBC drivers 9.2.0.5 (NOV-2004)',
'4090504','JDBC drivers 9.2.0.5 (JAN-2005)',
'4201222','JDBC drivers 9.2.0.6 (MAY-2005)') Patch_description
from ad_bugs
where bug_number in
( '3043762','2969248','3080729','3423613','3585217','3882116',
'3966003','3981178','4090504','4201222')
order by 2;
REM
REM END OF SQL
REM

Ref:A Guide to Configure, Maintain & Troubleshoot JDBC Buffers in Oracle Application [ID 364704.1]

code to identify the duplicate datafiles

select max(substr(file_name,instr(file_name,'/',-1),length(file_name))) from dba_data_files where TABLESPACE_NAME='&tablespace_name';

Few more useful Notes

Adding and Deleting Oracle RAC Nodes for Oracle E-Business Suite Release 11i [ID 1134763.1
Adding and Deleting Oracle RAC Nodes for Oracle E-Business Suite Release 12 [ID 1134753.1
Target Node/Queue Unavailable Seen After Adding New Node [ID 391458.1]
How to Add a New Node to The Application Tier, and Make It a Web Server Node Only [ID 464018.1]
Adcfgclone Fails with RC-40201 at appsTier While Adding a New Node [ID 876359.1]
RAC Assurance Support Team: RAC and Oracle Clusterware Starter Kit and Best Practices (Generic) [ID 810394.1]
ASM Technical Best Practices [ID 265633.1]




Note: 388577.1 - Using Oracle 10g Release 2 Real Application Clusters and Automatic Storage Management with Oracle E-Business Suite Release 12
Note: 783044.1 - Using Oracle 11g Release 1 (11.1.0.6) Real Application Clusters and Automatic Storage Management with Oracle E-Business Suite Release 12
Note: 466649.1 - Using Oracle 11g Release 1 Real Application Clusters and Automatic Storage Management with Oracle E-Business Suite Release 12
Note: 745759.1 - Oracle E-Business Suite and Oracle Real Application Clusters Documentation Roadmap

PCP:
Note: 388495.1 - How to Set Up Parallel Concurrent Processing (PCP) in Apps 11i?
Note: 602899.1 - Some More Facts On How to Activate Parallel Concurrent Processing
Note: 271090.1 - Parallel Concurrent Processing Failover/Failback Expectations
Note: 752604.1 - Failover Does Not Occur To The Secondary Node While The Primary Node Is Up
Note: 729883.1 - How to Create a Second OPP Concurrent Manager in a Node Different Than The Primary Node