JBoss 3.2.0RC2 & Oracle9iR2 connection pooling

[andrewdem]

Hi ALL!
I've configured Jboss for Oracle connection pool. Pool works fine, but when I make shutdown/startup for Oracle DB
Jboss do not reconnect to Oracle DBMS (not establish new connections and not invalidate old).
When I try to get connection from datasource I can see error "Not connected to Oracle". Restarting Jboss solves this problem.
How can I configure oracle-service.xml that Jboss may reestablish Oracle connections after DB shutdown/startup?
Jboss 3.2.0RC3, jdk 1.4.1_02, Oracle 9i, Linux RedHat 7.3
The same problem in JBoss3.0.6
May be problem it this
(org/jboss/resource/adapter/jdbc/LocalManagedConnection.java)

/**
* Describe connectionError method here.
*
* @param e a SQLException value
* @todo Figure out when connectionError should be called, and uncomment it.
*/
void connectionError(SQLException e)
{
/* I'm commenting this out for now until I have a better understanding of
just when
such an error event should be propagated. Should this method be uncom
mented, many
methods that rely on eg integrity checks by provoking a SQLException "
duplicate key"
will fail.

ConnectionEvent ce = new ConnectionEvent(this, ConnectionEvent.CONNECTION_
ERROR_OCCURRED, e);
Collection copy = null;
synchronized(cels)
{
copy = new ArrayList(cels);
}
for (Iterator i = copy.iterator(); i.hasNext(); )
{
ConnectionEventListener cel = (ConnectionEventListener)i.next();
cel.connectionErrorOccurred(ce);
}
*/
}



--
Best regards,
Andrey Demchenko.

[davidjencks]

As you have noticed, the best solution is to implement an Oracle specific error handler. If you would like to contribute this, I will enthusiastically commit it.

Otherwise, I suggest using the flush operation on the pool when the db goes down.

[andrewdem]

Ok. I will try to correct this problem and inform you when done.

--
Best regards,
Andrey Demchenko.

[andrewdem]

The code bellow solves my problem
(correct connector/src/main/org/jboss/resource/adapter/jdbc/BaseWrapperConnection.jpp)
[I] void connectionError(SQLException e)
{

String error_text = (e.getMessage()).toUpperCase();

/* Check oracle specific errors for broadcasting connectionerror
ORA-00600 - Internal oracle error
ORA-00028 - session has been killed
ORA-01014 - Oracle shutdown in progres
ORA-01033 - Oracle initialization or shutdown in progress
TNS- - Net8 messages
socket - for control socket error
*/
if ((error_text.indexOf("ORA-00600") > -1) || (error_text.indexOf("ORA-00028") > -1) ||
(error_text.indexOf("ORA-01014") > -1) || (error_text.indexOf("ORA-01033") > -1) ||
(error_text.indexOf("ORA-01034") > -1) || (error_text.indexOf("TNS-") > -1) ||
(error_text.indexOf("SOCKET") > -1) || (error_text.indexOf("BROKEN PIPE") > -1))
{
broadcastConnectionError(e);
}
}
[/I]

I do not test all errors, but when database shutdown/start
old connections are destroing (only when try to get connection from datasource) and new connetions establishes.

--
Best regards,
Andrey Demchenko.

[davidjencks]

Thanks!

I've made a generic framework for this and put your code into an Oracle specific class. You specify the class name of the ExceptionSorter in the deployment descriptor config property ExceptionSorterClassName. The one I made for your code is org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter.

I haven't thought yet how to modify the xsl so this can be used with the *-ds.xml configuration.

Could you please check that this works as you expect? i will then port to jb4 and probably 3.0.x.

[andrewdem]

Server works 4 days without problems. I have added check for 3 additional Oracle errors.

[I] void connectionError(SQLException e)
{
String error_text = (e.getMessage()).toUpperCase();

/* Check oracle specific errors for broadcasting connectionerror
ORA-00600 - Internal oracle error
ORA-00028 - session has been killed
ORA-01014 - Oracle shutdown in progres
ORA-01033 - Oracle initialization or shutdown in progress
ORA-03111 - break received on communication channel
ORA-03113 - end-of-file on communication channel
ORA-03114 - not connected to ORACLE
TNS- - Net8 messages
socket - for control socket error
*/
if ((error_text.indexOf("ORA-00600") > -1) || (error_text.indexOf("ORA-00028") > -1) ||
(error_text.indexOf("ORA-01014") > -1) || (error_text.indexOf("ORA-01033") > -1) ||
(error_text.indexOf("ORA-01034") > -1) || (error_text.indexOf("TNS-") > -1) ||
(error_text.indexOf("ORA-03111") > -1) || (error_text.indexOf("ORA-03113") > -1) ||
(error_text.indexOf("ORA-03114") > -1) ||
(error_text.indexOf("SOCKET") > -1) || (error_text.indexOf("BROKEN PIPE") > -1))
{
broadcastConnectionError(e);
}
}
[/I]

--
Best regards,
Andrey Demchenko.

[davidjencks]

I've added these three more errors and tried again to commit the version I came up with. (maybe I forgot to actually commit??)

There is no documentation for ORA-01034.

Assuming that this time the commit worked, could you please check that my version works properly with Oracle?

thanks
david jencks

[andrewdem]

ORA-01034 ORACLE not available (may be Oracle not started)
I will try to checkout jboss from CVS and test it.

--
Best regards,
Andrey Demchenko.

[andrewdem]

Hi,
I have been tested JBoss 3.2.0RC4 for OracleExceptionSorter
and unfortunately has some troubles (Pool not started).
1) I set this in oracle-service.xml
[I] <config-property name="ExceptionSorterClassName" type="java.lang.String">org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</config-property>[/I]
2) And this is a system.log
===========================
[I]
2003-03-20 10:34:23,736 DEBUG [org.jboss.resource.connectionmanager.RARDeployment] setting property: ExceptionSorterClassName to value org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter
2003-03-20 10:34:23,737 WARN [org.jboss.resource.connectionmanager.RARDeployment] The class 'class org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory' has no setter for config property 'ExceptionSorterClassName'
2003-03-20 10:34:23,737 ERROR [org.jboss.resource.connectionmanager.RARDeployment] Starting failed
java.lang.IllegalArgumentException: The class 'class org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory' has no setter for config property 'ExceptionSorterClassName'
at org.jboss.resource.connectionmanager.RARDeployment.setManagedConnectionFactoryAttribute(RARDeployment.java:584)
at org.jboss.resource.connectionmanager.RARDeployment.setMcfProperties(RARDeployment.java:732)
at org.jboss.resource.connectionmanager.RARDeployment.startService(RARDeployment.java:543)
at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)[\I]

===========================
Can you fix this?

--
Best regards,
Andrey Demchenko.

[davidjencks]

OK, I think I finally fixed this (this time I actually tested it a little bit).

In your oracle-ds.xml (or oracle-xa-ds.xml) include

<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.DummyExceptionSorter</exception-sorter-class-name>


(you'll need to update your 3.2 source and recompile all of jboss 3.2)

Thanks
david jencks