Refreshing Connection Pool via MBeans

You can easily refresh the connection pool via Enterprise Manager. If you look at MBean Browser (for more information please look at my earlier blog on JMX http://chintanblog.blogspot.com/2007/12/it-is-all-about-jmx-i-worked-on-jmx.html, it is very comprehensive blog) you can search for your Connection Pool it provides management interface to refresh the connection pool as well. I believe EM internally must be using MBean interface.

Anyways, here is programmatic way of accessing MBean related to Connection pool and doing refresh using MBean client API.

To get Mbean Name, you can look at MBean Browser as displayed here. You can also see it supports testConnection, refreshConnection, etc.. methods.

You can see Mbean name and method name in the Mbean browser, you invoke here if you want also.


Here is the code to execute this method using Java API:

Here MBean name and method name I have got it from System MBean Browser. This code works great and does invoke the refreshConnectionPool method.


Testing the connection pool refresh

To replicate the scenario where connection pool get broken and remain broken without calling my refresh method was quite tricky to figure out. I tried multiple different scenarios and here is the one which worked great and as expected.

Here is the use case and steps I came up with:

1) Start database and create connection pool and data source
2) Test the connection pool using Mbean browser "testConnection" method
Expected Result: it should work without any isuse.
3) Stop database and Start the database
4) Test the connection pool using Mbean browser "testConnection" method
Expected Result: it should not work, and should show error (at least for a while) because of stale connection
5) Stop database and start the database
6) Run refresh Mbean Client routine
7) Test the connection pool using Mbean browser "testConnection" method
Expected Result: it should work without any issue.

Execution of the usecase:

Step 1) Start database and create connection pool and data source:
I created connection pool called TestPool which points to Test Schema

Step 2) Test the connection pool using Mbean browser "testConnection" method:
As show below it was successful.

Step 3) Stop database and Start the database

Step 4) Test the connection pool using Mbean browser "testConnection" method
As we already know, testConnection method failed with four different error message each time we invoke test connection method. After these four messages, connection was successful.

Exception occurred testing connection. Exception: java.sql.SQLException: No more data to read from socket.
Exception occurred testing connection. Exception: java.sql.SQLException: OALL8 is in inconsistent state.
Exception occurred testing connection. Exception: java.sql.SQLException: Io exception: Software caused connection abort: socket write error.
Exception occurred testing connection. Exception: java.sql.SQLException: Closed Connection.

---

---

---

Step 5) Stop database and start the database

Step 6) Run refresh Mbean Client routine

Step 7) Test the connection pool using Mbean browser "testConnection" method

This time it just works fine...


Code for MBean Client can be downloaded from here.