Monitoring JDBC connection leak on a multi node R12 environment

Posted on May 1, 2008. Filed under: Apps Technology Stack, R12 | Tags: , , , , |

To understand this, first review this blog by SCHAN

Monitoring the JDBC connection pool”

So i had a requirement where i needed to know my jdbc connection leaks, monitor its pattern and identify offending code…. when i started , all i knew was ‘over a period of time , all my jdbc connections would be exhausted’ and eventually will get a error message

“java.lang.RuntimeException: Null JDBC Connection returned from connection pool”

So you read the above link and tried it from OAM , surprise surprise , it works on 11i and not in R12 . So what do you do on a R12 environment (as of 12.0.4)

you try http://host.domain:port/OA_HTML/jsp/fnd/AoljDbcPoolStatus.jsp , but in a multi node environment where you use load balancer , you cannot do it . It simply doesnt work.

Here is are two work arounds , 2nd one is better than 1st

1) workaround#1
Since you have R12 , i assume you already have JDK 1.5 (i had 1.5.0_14)

With jdk 1.5 , you get an amazing tool called as jconsole . This is a gui tool , so make sure your DISPLAY is set properly (linux env) and invoke jconsole -> attach to your java process -> go to MBeans -> oacore -> oracle.apps.fnd.management -> JDBCConnectionPoolStatistics . Voila ! you see the Leaked connections and also the Leaked Connection stacks

So you can click on refresh button and see how these change with time and workload and build your own pattern.

Next step is identifying your root cause , you need the “Leaked Connection stacks” for this

(a) simply double click on the value for “Leaked Connection stacks”

or

(b) go to operations (tab on top center) , click on getLeakedConnectionStacks()

The only problem is , you cannot copy the stack messages to a text pad or notepad .

You solve this by Workaround # 2

2) workaround#2 …. assuming you are using oracle configurator with order management (else you wont get this issue anyway πŸ˜‰ )

(a) give yourselves a order entry responsibility , like Order Administrator , -> Go to Sales Orders -> Enter a Sample order -> click on line items , enter model number and click configurator , your configurator screen opens in a new window , Now in this new window , just edit the URL to say

http://host.domain:port/OA_HTML/jsp/fnd/AoljDbcPoolStatus.jsp

so make sure to point your BOM%CONFIGURATOR%URL% profile option to the host.domain that you want.

hope this helps.

Advertisements

Make a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

One Response to “Monitoring JDBC connection leak on a multi node R12 environment”

RSS Feed for OraclePitStop – Lets share some knowledge Comments RSS Feed

Hi! I was surfing and found your blog post… nice! I love your blog. πŸ™‚ Cheers! Sandra. R.


Where's The Comment Form?

Liked it here?
Why not try sites on the blogroll...

%d bloggers like this: