"(17008) Closed Connection"

, ,
1

Hi All,

We are getting the below error when connecting to Oracle DB.

"[ADA.1.316] Cannot execute the SQL statement “{call procedure name( ?, ?, ?, ?)}”.
“(17008) Closed Connection”


Even i get the connection restarted twice on that server, and also killed all the sessions opened by the User by which i’m connecting to the Oracle DB.

I’m using min pool size =“1”. This is fixed and can’t be changed due to some business needs.

Can someone please provide me the solution for the above error. And what could be the reason for the error, with same connection parameters i could able to call different DB Instance.

Thanks

npbkwm

2

I find it hard to believe that a “business need” cares anything about the size of a connection pool. Connection pools are inherently a technical concern. The min pool size should be set to 0. Setting this to non-zero is typically more trouble than it is worth.

Can you share the rest of the connection pool settings? My suspicion is that the idle timeout is too low. It should be just shorter than the DB-side idle timeout setting, if one is set (check with your DBA). I see idle timeouts anywhere from about 5 minutes to a few hours. The key is having the adapter connection timeout before the DB kills the connection–that avoids stale connections.

If the idle time is short (say 10 seconds or less) and the min size is 1, you may be causing session thrashing on the DB. Connected, dropped, connected, dropped, etc.

3

Hi Reamon,

Thanks for your reply,
below are the con prpperties…

[TABLE=“width: 100%”]
[TR]
[TD=“class: oddrow, colspan: undefined”]Minimum Pool Size[/TD]
[TD=“class: oddrowdata-l, colspan: undefined”]1[/TD]
[/TR]
[TR]
[TD=“class: evenrow, colspan: undefined”]Maximum Pool Size[/TD]
[TD=“class: evenrowdata-l, colspan: undefined”]10[/TD]
[/TR]
[TR]
[TD=“class: oddrow, colspan: undefined”]Pool Increment Size[/TD]
[TD=“class: oddrowdata-l, colspan: undefined”]1[/TD]
[/TR]
[TR]
[TD=“class: evenrow, colspan: undefined”]Block Timeout (msec)[/TD]
[TD=“class: evenrowdata-l, colspan: undefined”]1000[/TD]
[/TR]
[TR]
[TD=“class: oddrow, colspan: undefined”]Expire Timeout (msec)[/TD]
[TD=“class: oddrowdata-l, colspan: undefined”]1000[/TD]
[/TR]
[TR]
[TD=“class: evenrow, colspan: undefined”]Startup Retry Count[/TD]
[TD=“class: evenrowdata-l, colspan: undefined”]0[/TD]
[/TR]
[TR]
[TD=“class: oddrow, colspan: undefined”]Startup Backoff Timeout (sec)

Thanks
npbkwm[/TD]
[TD=“class: oddrowdata-l, colspan: undefined”]10

[/TD]
[/TR]
[/TABLE]

4

Those are the default values used whenever a new JDBC connection is created. They are not good settings to use for typical needs.

I would recommend:

Minimum Pool Size = 0

Block Timeout (msec) = 30000 - 30 seconds for a thread to wait for a connection from the pool before giving up

Expire Timeout (msec) = 1800000 - 30 minutes for an idle connection to be closed by IS; based on your environment, this could be increased or may need to be decreased, depending upon the DB-side idle timeout value

Block and expire timeouts of 1 second are far too short for anything useful.

5

Thanks reamon,

I would follow the above pool properties you have given,

Thanks
npbkwm

6

Hi,

Sometime you will get these kinds of errors if you’re using ojdbc14.jar to do the batchInsert on byteArray or blob data.

Basically when jars are not compatible with the resources, it will throw an errors saying closed connection. try using updated jars like-ojdbc6.jar.

7

you mean going backward from -ojdbc14.jar to -ojdbc6.jar…

Which IS and DB versions are you talking about?

8

Hi,

Once I had the same problem in my project on IS 7.1.2 with ojdbc14.jar for DB Oracle 11g, Then i replaced this jar to
latest jar ojdbc6.jar

Latest jars for oracle 11g : Database 19c and 21c | Oracle