Friday, June 27, 2008

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.


8 comments:

Anonymous said...

Great blog, Chintan.
I'm posting here for your previous blog "Insert/Extract SOAP Header", I got similar issue as the one comment in the blog. I checked the opmn log on the server and found following.
Can you check? TIA.
<2008-07-07 15:57:00,744> INFO> default.collaxa.cube.compiler> validating "Z:\SOABASIC\10.1.3.1\OracleAS_2\bpel\domains\default\tmp\.bpel_InsertHeaderVariables_1.0_c0f0a15dab2970032ad97dee51e7014a.tmp\InsertHeaderVariables.bpel" ...
08/07/07 15:57:02 Z:\SOABASIC\10.1.3.1\OracleAS_2\bpel\domains\default\tmp\.bpel_InsertHeaderVariables_1.0_c0f0a15dab2970032ad97dee51e7014a.tmp\BPEL-INF\src\bpel\insertheadervariables\BPEL_BIN.java:1240: '}' expected.
08/07/07 15:57:02 "Variable_Username"
08/07/07 15:57:02 ^
08/07/07 15:57:02 Z:\SOABASIC\10.1.3.1\OracleAS_2\bpel\domains\default\tmp\.bpel_InsertHeaderVariables_1.0_c0f0a15dab2970032ad97dee51e7014a.tmp\BPEL-INF\src\bpel\insertheadervariables\BPEL_BIN.java:1409: Class or interface declaration expected.
08/07/07 15:57:02 }
08/07/07 15:57:02 ^
08/07/07 15:57:02 2 errors
<2008-07-07 15:57:02,900> ERROR> default.collaxa.cube.engine.deployment> CubeProcessFactory::generateProcessClass>

Process "InsertHeaderVariables" (revision "1.0") compilation failed.
<2008-07-07 15:57:02,900> ERROR> default.collaxa.cube.engine.deployment> CubeProcessLoader::create>
ORABPEL-01005

Failed to compile classes.
Failed to compile the generated BPEL classes for "InsertHeaderVariables".

at com.collaxa.cube.lang.compiler.CubeProcessor.compileGeneratedClasses(CubeProcessor.java:972)

Anonymous said...

I resolved the issue by just passing in one Variable.
Now another question:
If I secure the BPEL web service(ExtractBPELHeaderProject), then I can't retrieve the fields from the SOAP header.
Seems like these two can't be done at the same time.
Do you have any advice?
TIA

من said...
This comment has been removed by the author.
Anonymous said...

Dear Chintan,

I am writing to you to ask you help me to resolve my problem in ODI.

I use Oracle Directory Integration Platform and provisioning service to synchronization of users from ODI to DB and vice versa .but it does not work properly, ODI cannot sync with DB. and below lines are shown in log files.

Updating the Status..
Setting Event Success Count : 0
Setting Event Failure Count : 0
Propagation Status : SUCCESS
Last change Key Set to:2800
Profile status Update - SUCCESS
Cleaned/Closed Readers and Writers
Initialization - Starting for Mode Bootstrap
Prov Reader - Initialize : Instantiating oracle.ldap.odip.prov.AppBootstrapEventReader
Provisioning Failure Retry Limit for App is : 1
Status Attribute in OID:orcluserapplnprovstatus;rasaApp_rasaapp
Default Subscription Status Attribute:orcluserapplnprovstatus;rasaapp
Timestamp attribute 20080713064905z
LDAP URL : (srv-metasearc.padl.local:636 cn=odisrv+orclhostname=srv-metasearc,cn=registered instances,cn=directory integration platform,cn=products,cn=oraclecontext
Specifying binary attributes: mpegvideo objectguid objectsid guid usercertificate orclodipcondirlastappliedchgnum
Connecting in SSL
LDAP Connection success
AppBootstrap searchFilter : (&(objectclass=orcluserv2)(|(orcluserapplnprovstatus;rasaApp_rasaapp=PROVISIONING_FAILURE)(orcluserapplnprovstatus;rasaApp_rasaapp=PENDING_UPGRADE)(orcluserapplnprovstatus;rasaApp_rasaapp=PROVISIONING_NOT_REQUIRED)(!(orcluserapplnprovstatus;rasaApp_rasaapp=*))))
Search Time : 0
App Bootstrap Search Successful for application : 'null'
Intialized the App Bootstrap Event Reader.
Prov Reader - Initialized
Prov Writer - Initialize : Instantiating oracle.ldap.odip.prov.LDAPEventWriter
LDAP URL : (srv-metasearc.padl.local:636 cn=odisrv+orclhostname=srv-metasearc,cn=registered instances,cn=directory integration platform,cn=products,cn=oraclecontext
Specifying binary attributes: mpegvideo objectguid objectsid guid usercertificate orclodipcondirlastappliedchgnum
Connecting in SSL
LDAP Connection success
Retreived Factory from Profile..
Prov Writer - Initialized ..
Initialization - Ending ..
Event Propagation - Begin..
More Data Found..
Reader finds some data...
More Data Found..
Getting Users for the BootStrap cases..
More Data Found..
Processing User - cn=orcladmin
UserCreateTimeStamp 20080713064639z
Application TimeStamp 20080713120917z
Upgrade Timestamp 20080713064905z
Current status : null
Upgrade Case..
Need to Generate Upgrade Event
Current User FailCount : 0
[fine] Inside Generate Events..
[fine] Object Type identified as : USER
[fine] LDAP Changetype : ADD
[fine] getObjTypeRules:Processing Event Defn (0) - ,Object Type:ENTRY
[fine] getObjTypeRules:Processing Event Defn (1) - ,Object Type:USER
[fine] getObjTypeRules:Matched Object Type :USER
[fine] getObjTypeRules:# Event Rules:0
[fine] EventEngine: No ObjectType Rules for :USER - changeType : ADD
[fine] EventEngine: # Generated Events : 0
More Data Found..
Processing User - cn=PUBLIC
UserCreateTimeStamp 20080713064639z
Application TimeStamp 20080713120917z
Upgrade Timestamp 20080713064905z
Current status : null
Upgrade Case..
Need to Generate Upgrade Event
Current User FailCount : 0
[fine] Inside Generate Events..
[fine] Object Type identified as : USER
[fine] LDAP Changetype : ADD
[fine] getObjTypeRules:Processing Event Defn (0) - ,Object Type:ENTRY
[fine] getObjTypeRules:Processing Event Defn (1) - ,Object Type:USER
[fine] getObjTypeRules:Matched Object Type :USER
[fine] getObjTypeRules:# Event Rules:0
[fine] EventEngine: No ObjectType Rules for :USER - changeType : ADD
[fine] EventEngine: # Generated Events : 0
More Data Found..
Processing User - cn=User1
UserCreateTimeStamp 20080713124312z
Application TimeStamp 20080713120917z
Upgrade Timestamp 20080713064905z
Current status : null
Current User FailCount : 0
[fine] Inside Generate Events..
[fine] Object Type identified as : USER
[fine] LDAP Changetype : ADD
[fine] getObjTypeRules:Processing Event Defn (0) - ,Object Type:ENTRY
[fine] getObjTypeRules:Processing Event Defn (1) - ,Object Type:USER
[fine] getObjTypeRules:Matched Object Type :USER
[fine] getObjTypeRules:# Event Rules:0
[fine] EventEngine: No ObjectType Rules for :USER - changeType : ADD
[fine] EventEngine: # Generated Events : 0
App bootstrap events generated successfully. Number of events: 0
Events Read from Source : 0

Events to write 0
Process Event Status for 0
Not calling status processor
Event Propagation - End ..
Updating the Status..
Cleaned/Closed Readers and Writers

I am thankful for your great consideration in advanced. I look forward to hearing from you as soon as possible.

Anonymous said...

Hi Abhishek,
I am looking for a solution where i want to catch a 'Null' Value or 'empty string' in reply output and show an error or show a message. You can find my question in forum for the full scenario here:

http://forums.oracle.com/forums/message.jspa?messageID=2718034#2718034

I would really appreciate if you are able to help me, i can also email you my project if you want.

Many thanks mate
Deepak/

Chintan Shah said...

Why in the hell my name would be Abhishek?

Anonymous said...

Lol Sorry, i sent the same message to Abhishek Saurabh as well. I forgot to change the name.

My mistake. Any idea about the problem i am facing?

Thanks Mate
Deepak

Anonymous said...

Hi ,

In my scenerios if the order doesnt cme for 3-4 days then the BPEL becomes stale and we have to retired and activate it once in 3 days

Can u help us.

Rgds
Niraj