Understanding JDBC Connections From the eBusiness Middle Tier

 

In this article I will describe the basics of configuring and monitoring JDBC connections between the eBusiness Web Tier and the Database, then cover some common issues to help identify root causes for both Release 11i and Release 12 of eBusiness Suite.


11i Architecture:


Brief overview


In general, whenever a functional page requires data from the database, it makes a call to a lower code level (the Java layer in the Application Object Library, also known as AOL/J) which handles the database connectivity.  The AOL/J code provides a JDBC connections to the database through the Database Connection Pool.


You should note that the Java Connection Pool mechanism for eBiz is completely provided by eBiz code and does not use the mechanisms provided to pool connections through Java language directly, nor through the Application Server configuration.


Configuring JDBC connection pooling


The JDBC connection pool is created using the settings in the DBC file. The name, location and contents of this DBC file is controlled through AutoConfig. To modify any of the JDBC connection pool parameters, you should therefore use the techniques described in the following document to ensure changes are maintained in a supportable and consistent way:



The "s_dbc_file_name" variable in the CONTEXT.xml file provides the dbc filename and is located in the $FND_TOP/admin/< INSTANCE>_<HOST> directory.


JDBC connection pool parameters and their use are covered in the following documents:



When considering any changes, you should also take into account that every JVM has its own JDBC connection pool. For example, if you have one Web Node with three OACoreGroup JVMs plus one XmlSvcsGrp JVM configured, then you will have a total of four JDBC connection pools with connections to your eBiz database


Monitoring the JDBC connection pool


It is always a good idea to understand how your environment looks when things are going well, to give you a baseline to compare against if you need to investigate any issues. 


You will most certainly need to review the JDBC connection data if you are experiencing issues.



Monitoring JDBC Connections through Oracle Applications Manager (OAM)

Login to OAM directly or via the "System Administration" responsibility.


  1. Select the "JServ Usage" under the Monitoring section in OAM
  2. 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 Oracle Support are assisting your investigation.
  3. If there are any non zero values for "Potentially Leaked" then click on this number to drill down into the details
  4. Select "Yes" for the "Filter by Potentially Leaked" option and click "Go" button
  5. Click "Show all details" to display the Java Thread dump for all the potentially leaks Java connections

mz_jdbc_oam:


The "old" way of gathering this data was to use the URL http://host.domain:port/OA_HTML/jsp/fnd/AoljDbcPoolStatus.jsp but this will only give data for the one JVM you happen to connect to, so may not be so useful in multi-JVM environments.


Run SQL scripts to monitor database connections


Using SQL scripts will not give so much information as OAM, but can be used to provide useful summary information on a periodic basis. For example you may wish to include the information from the SQL below as part of your baseline data:


REM
REM START OF SQL
REM
REM Connections by machine and instance
select s.machine, s.username, s.module, s.inst_id, count(*) how_many
from (select distinct PROGRAM, PADDR, machine, username, module, inst_id from gV$SESSION) s,
 gv$process p
where s.paddr = p.addr
and p.inst_id = s.inst_id
group by s.machine,s.username, s.module, s.inst_id
/
REM
REM END OF SQL
REM


NOTE - when looking at V$SESSION, the STATUS of JDBC connections tend to show as INACTIVE, this is normal and does not indicate a specific problem


Where Could It Go Wrong?


Issues with the JDBC connection pool tend to be of a nature whereby the number of database connections increase over time and do not seem to be released again. If this continues unchecked, then you may find the database will run out of sessions/processes and/or the Middle Tier JVM will run out of connections or memory.


A "quick fix" would normally be to restart Apache, but the underlying cause would need to be investigated further.


Issues can potentially occur at five different levels:



  1. Core Java Code
  2. AOL/J JDBC Code
  3. OA Framework
  4. Functional Code
  5. External Influences

I'll discuss each of these areas next.



Core Java code


Although eBiz does not use native Java connection pooling methods, we do rely on the underlying Java APIs generally.   Any issues at this level will generally require the latest Java version to be installed


If you need to upgrade your Java version, see:



AOL/J JDBC code


As this is the code that handles JDBC connection, it is often the first area to be blamed, but the JDBC connection pool can only drop database connections where the calling application has released the JDBC connection it holds in the pool, so it often turns out to be an issue higher up the code stack.


Number of JDBC connections increase after ATG Rup 5 because jdbc parameters are lower case (Metalink Note 459072.1) describes a known issue with Apps 11i.


It is prudent to be on the latest JDBC driver patch, but should have at least applied one of the following patches:-



The latest JDBC patch can be found in:



You should note that the JDBC driver version has no relation to the Database version, as it is installed on the eBiz Middle Tier.    For example, the latest version of JDBC drivers provided by patch 4899697 (9.2.0.8) is the same patch for all RDBMS versions.


OA Framework (OAF)


OA Framework calls AOL/J when it needs a database connection, and it is up to OAF to release any such connection when it has finished with it. There is an added complexity, in that OAF also has its own pooling mechanism for the OAF Pages, which is the "Application Module pool" (AM pool).  This means that although a user may have finished with a page, the page and its associated database connection are retained for a period of time.


The AM pool is controlled by profile options, which are described in :-



Issues at this code level would tend to be either:



1. Issue with AM Pooling


You can test the effect of disabling AM pooling by setting the profile option "FND: Application Module Pool Enabled" to "No".  Use this technique with caution if considering this for a production environment.


2. Specific bug where database connection is not released.


This would generally require patching.


Functional Code


Issues at this code level would tend to be a specific bug where a connection is not released.


External influences


Firewall timeouts are known to potentially cause an issue for JDBC connections.  For details, see:



Although this note is for Apps 11i, the technical issue can also apply to Release 12 as well


Configuring eBiz to minimize JDBC connections


If investigating issues with JDBC connections increasing, it may be useful to minimise the database connections as much as possible by de-tuning the JDBC pool. This may reduce end user performance, so should be used with caution if considering this for a production environment.


To do so, you need to do both these steps:



1. Disable Application Module (AM) Pooling


This is necessary as the AM objects hold a JDBC connection whilst they are alive, even if they are not currently used.


Set the profile option "FND: Application Module Pool Enabled" (AMPOOL_ENABLED) at SITE level to a value of "No"

2. Set the JDBC connection pool parameters to release connections:


    FND_JDBC_BUFFER_DECAY_INTERVAL=180
    FND_JDBC_BUFFER_MIN=0
    FND_JDBC_BUFFER_MAX=0
    FND_JDBC_BUFFER_DECAY_SIZE=50


Conclusion


Identifying issues with JDBC connections can sometimes be a frustrating process, as the investigations may need to consider multiple failure points and complex architectures. I hope this article has given you a better understanding of JDBC Pooling and where to start looking for issues.


References



Related


Comments:

Hi Mike,

How we can monitor java leaks in R12 Instance. From SQL Extensions i can find memory consumption of each session, but I can't see leaks. Can you please let me navigation to find leaks.

Regards,
Phani.K

Posted by Phani K on September 21, 2007 at 02:01 PM PDT #

Thanks for pointing out my ommision..... in R12 it is no longer in OAM but you can see similar information using "System Administration" responsibility --> Diagnostics --> AOL/J Database Connection Pool Status

Posted by Mike Shaw on September 23, 2007 at 09:08 PM PDT #

Mike,
Thanks for update.
Regards,
Phani.K

Posted by Phani K on September 26, 2007 at 11:11 AM PDT #

Hi Mike,

You specify in your article that in V$SESSION, STATUS of JDBC connections being INACTIVE is not a problem condition. Is this always the case ? We're seeing that INACTIVE JDBC connections from previous days get stacked up and increase over a period of time. Should this be treated as a problem condition or be treated as a normal one ? I'd appreciate your inputs on this.

Thanks,
Rakesh.

Posted by Rakesh Tripathi on October 17, 2007 at 10:29 PM PDT #

JDBC connections being INACTIVE in itself is not a problem. If connections are always building up and do not drop down again when activity reduces, this may indicate an issue (particularly if you already did the steps in "Configuring eBiz to minimize JDBC connections")

There are a couple of things you can do towards verifying this as a problem. 1. Check the LAST_CALL_ET from gV$SESSION for these connections. Connections should be reused over a period of time. 2. Check the "fnd_jdbc" parameters in your DBC file. There is an issue whereby if the parameters are in lower case then they are ignored (Patch for bug 6318531 should fix it) so ensure these parameters are all in UPPER case.

Hope that helps

Posted by Mike Shaw on October 18, 2007 at 06:51 PM PDT #

Mike, Thanks for the information and details. I'll check the things that you've specified.

Best Regards,
Rakesh.

Posted by Rakesh Tripathi on October 22, 2007 at 02:48 AM PDT #

Hi,

Note 164317.1 gives information about 9i JDBC driver. Is there a note or patch which provides the latest 10g JDBC driver ?

- Vikram

Posted by Vikram Das on July 16, 2008 at 04:48 AM PDT #

Hello Vikram

Note 164317.1 shows the latest certified JDBC version for the eBiz middle tier. The JDBC driver version is not tied to a database version.

At the time of writing the "Oracle9i JDBC Drivers Release 9.2.0.8" drivers are the latest certified version.

Hope this is clear

Posted by Mike Shaw on July 17, 2008 at 01:38 AM PDT #

hi Mike,

I m facing a problem of JDBC connection for home page keep increasing over the period of time & dont release at alll for Login Page. causing Error like "oracle.apps.fnd.common.PoolException: Not able to create database connection" & all users fail with INternal Server Errro. Pls suggest solution.

thanks.
Nilesh

Posted by Nilesh on July 08, 2010 at 08:56 AM PDT #

Hello Nilesh,

There can be a variety of issues, so please raise a Service Request(SR) through My Oracle Support so we can investigate the issue in the context of your specific environment

Feel free to ping me the SR number

regards

Mike

Posted by Mike Shaw on July 08, 2010 at 04:17 PM PDT #

Hi,

I setup four oacore jvms on R12.0.6 but timely one of the four JVMs starts gathering Full GC continously leaving application hanging for ALL users. I doubt that all the connection load is handled by only this one JVM. How can i find whether the user load evenly distributed among all jvms?

Regards

Posted by Lardy on November 30, 2010 at 09:58 PM PST #

Hello there,

We can increase logging to see what is going on, but would doubt there would be a problem with the user load distribution. The best way forward is to raise a Service Request with ATG support so we can take a look in more detail and advise the diagnostic steps to suit

regards

Mike Shaw

Posted by Mike Shaw on November 30, 2010 at 11:01 PM PST #

Hi Steve,

We need to upgrade our JDBC version from 10.2.0.3 to 10.2.0.4 (EBS R12).

Could you please guide in getting this done?

Thanks,
Krishnamoorthy. R

Posted by krishnamoorthy R on January 17, 2011 at 05:58 PM PST #

Hello Krishnamoorthy. R

JDBC patches need to be included in an ATG patch in order to be installed in eBiz

The best plan is for you to raise a Service Request so we can verify the issue you are experiancing and verify your versions, in order to provide the best solution for your specific circumstances

regards

Mike Shaw

Posted by Mike Shaw on January 17, 2011 at 11:00 PM PST #

Hello,

From grid we can get only limited information about the client process like OS User Name and Host for JDBC Thin Client sessions. Aslo on the application detail for JDBC Thin Client sessions grid gives only Current SQL Command & Previous SQL, it is not giving the Current Action details. With these details we can not find out the application user id and module from which this jdbc connection is coming from(like from which form or concurrent program this connection is coming from). If there is a database lock by JDBC Thin Client, it is very difficut to find out application user name and the module where the connection is coming from. Is there any way we can get this information?

Thanks,
Guna

Posted by Guna M on October 13, 2011 at 03:42 AM PDT #

Mike,

Is it possible to identify the application user id and the module name(like form name or concurrent program name) for the JDBC Thin Client session which has a database lock?

Thanks,
Guna

Posted by Guna Muthusamy on October 13, 2011 at 03:45 AM PDT #

Hello Guna,

It is generally tricky to tie down a JDBC connection to an eBiz user, due to the way JDBC connection pooling works.

I don't have any prepackaged SQL for you, but you could give the following a try (once you have identified the RDBMS session that is blocking)

select machine, inst_id, sid, serial#, status, process,
sql_id, module, last_call_et, client_identifier,
blocking_session_status, blocking_session
from gV$SESSION
where program = 'JDBC Thin Client'
order by machine, last_call_et;

Hope this helps,

Mike

Posted by Mike Shaw on November 03, 2011 at 03:48 AM PDT #

Hi
Can anyone provide a guide that i can use to establish a connection to oracle ebs using jdbc. How can i configure jdbc/oracle ebs to work appropiately.

Thanks

Posted by guest on December 16, 2012 at 08:48 PM PST #

Hello,

Not sure the context in which you need to connect to eBiz, but would recommend you review "Oracle E-Business Suite Software Development Kit for Java (includes AppsDataSource, Java Authentication and Authorization Service, session management) Readme - Patch 13882058" (Doc ID 974949.1)

Hope this helps

Mike

Posted by Mike Shaw on December 17, 2012 at 06:59 AM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
4
5
6
7
8
9
10
11
12
13
14
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today