Oracle 9.2 connection reset problem

[erlend.bjorge]

Title: Melding

Hi there!

 

We get a SQLException saying 'connection reset', it seems that the "connection"

is suddenly dead for us, and when we try again running the method ('click' on the

link that access the database again) again it's work just fine. It's happend in a web application

in production. There are about 10 people using the web application, that query

and update the database all the time of the day.

 

We haven't experience that we have run out of connections, just that this connection reset

problem now and then. Same behaviour working with a Toad tool like called DbVisualizer using

JDBC as the connection, if I don't use the tool for over 15-30 min I guess, same message

from Oracle as we get using SQLMap.

 

In test environment, we haven't experienced this problem, but that don't mean it's not

a problem there, maybe we just haven't been seen it, since we haven't done full scale

testing there. So it's only in production we have this problem it seems, where our Resin server

with the web application and Oracle db server are two different DMZ,  could that have

something to do with it ?

 

Anyway we have this kind of setup of the datasource. After we introduced the

Pool.Ping configuration, it didn't helped that either..

 

    <settings
        cacheModelsEnabled="true"
        enhancementEnabled="true"
        lazyLoadingEnabled="true"
        maxRequests="256"
        maxSessions="128"
        maxTransactions="32"
        useStatementNamespaces="false"/>

 

    <transactionManager type="JDBC">
        <dataSource type="SIMPLE">
            <property name="JDBC.Driver" value="${driver}"/>
            <property name="JDBC.ConnectionURL" value="${url}"/>
            <property name="JDBC.Username" value="${username}"/>
            <property name="JDBC.Password" value="${password}"/>
            <property name="Pool.MaximumActiveConnections" value="30"/>
            <property name="Pool.MaximumIdleConnections" value="1"/>           Should we expand that one ???
            <property name="Pool.MaximumWait" value="1000"/>
            <property name="Pool.PingQuery" value="select * from dual"/>
            <property name="Pool.PingEnabled" value="true"/>
            <property name="Pool.PingConnectionsOlderThan" value="1"/>
            <property name="Pool.PingConnectionsNotUsedFor" value="1"/>
            <property name="Pool.ValidationQuery" value="select 1 from dual"/>
            <property name="Pool.LogAbandoned" value="true"/>
            <property name="Pool.RemoveAbandoned" value="true"/>
            <property name="Pool.RemoveAbandonedTimeout" value="50000"/>
        </dataSource>
    </transactionManager>

Shouldn't this Pool configuration work ?? = keeping the connections up to date ??

We have tried to tune different values forth and back without any 100% result.

 

--

 

When I run some test methods from my IDE, haven't experienced this problem,

and in debug mode I get these kind of feedback that's the Pool stuff seems to work.

 

DEBUG 13:39:36,186 [main] com.ibatis.common.jdbc.SimpleDataSource - Checked out connection 32550604 from pool.
DEBUG 13:39:36,186 [main] com.ibatis.common.jdbc.SimpleDataSource - Testing connection 32550604...
DEBUG 13:39:36,196 [main] com.ibatis.common.jdbc.SimpleDataSource - Connection 32550604 is GOOD!
.....

DEBUG 13:39:36,266 [main] com.ibatis.common.jdbc.SimpleDataSource - Testing connection 32550604...
DEBUG 13:39:36,266 [main] com.ibatis.common.jdbc.SimpleDataSource - Connection 32550604 is GOOD!
DEBUG 13:39:36,266 [main] com.ibatis.common.jdbc.SimpleDataSource - Returned connection 32550604 to pool.

 

Any clues anyone ? what we can do about this ?

 

 

Thanks for any help!

 

 

Best regards,

Erlend Bjørge