[ART.118.5053] [ADA.1.204] Oracle JDBC Socket read timed out error

, ,
1

Hi wm community,

We are getting the following error in our Production system randomly around once a month for the last 3 months:

[ART.117.4012] Adapter Runtime (Adapter Service): Unable to run adapter service. Error occurred when connecting to resource mbl.connections.loanIQ.common.adapters.jdbc:conn_loanIQ_common.
[ART.118.5053] Adapter Runtime (Connection): Unable to get a connection to resource mbl.connections.loanIQ.common.adapters.jdbc:conn_loanIQ_common.
[ADA.1.204] Cannot connect to the database with DataSource class "oracle.jdbc.pool.OracleDataSource".
IO Error: Socket read timed out

JDBC settings:

NO_TRANSACTION
oracle.jdbc.pool.OracleDataSource

Max Pool Size = 10
Block Timeout = 60000
Expire Timeout = 60000

ojdbc6.jar
Implementation-Version: 11.2.0.2.0

The error would happen, and then subsequent pollings would work again. This doesn’t seem to be connection pool related since that gives a different error:

[ART.118.5053] Adapter Runtime (Connection): Unable to get a connection to resource 
The pool doesn't have a connection available for the request.

This adapter only polls once every 5 minutes, there are no one else using this connection pool. There are no errors in the DB logs and it hasn’t exceeded any DB thresholds either.

I’ve found some similar threads on the Oracle forums also, but none had any reasonable solutions:
[URL]https://forums.oracle.com/forums/thread.jspa?threadID=1040247[/URL]
[URL]http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html[/URL]

Has anyone experienced similar issues or can offer some insight?

2

you only poll once every 5 min, and expire timeout is only 60 sec.
you may want to increase it to 10min.
if you have a firewall between the IS and DB, you may want to check that too, see if it’s cutting the connection.

You can also try to create a ping service to the DB, run more frequently (say, once every 30 sec) to keep the connection alive.

3

What is the min pool size setting? If not 0, set it to 0.

I agree with tongwang on increasing the idle session timeout (expiry). There is no point in having a pool of connections if all connections are being consistently closed within a short time frame.

The expire timeout setting should be just short of whatever the DB server-side has for client timeout (if any). I regularly see 2 hours as a timeout. If a connection has been idle for 2 hours, its generally good to close it. The key is striking a balance between holding idle connections too long and closing them too quickly. Generally, you want to error towards holding them longer than what you think you need. And hold them no longer than what the DB will allow you to–you want IS to close the connection, not the DB.

4

Yes the min pool size is 0 at the moment. We’ll contemplate increasing the expiry time and see how it goes. Thanks all

5

Hi There,

Pls. could you let me know if you have any solution for this. I am also having same issue.

Many thanks in advance.

6

Did you place the appropriate JARS? What is your connection pooling settings (screen shot)

When do you receive this error. Could you explain in-detail.?

7

Hi Mahesh,

Yes we have proper jars in place and this is intermittent connection issue. It happens for some seconds and then back to normal.

Error: [ART.118.5053] Adapter Runtime (Connection): Unable to get a connection to resource .
[ADA.1.204] Cannot connect to the database with DataSource class “oracle.jdbc.pool.OracleDataSource”.
IO Error: Socket read timed out

Enable Connection Pooling true
Minimum Pool Size 0
Maximum Pool Size 10
Pool Increment Size 1
Block Timeout (msec) 5000
Expire Timeout (msec) 10000
Startup Retry Count 1
Startup Backoff Timeout (sec) 5

Thanks

8

How often do you see these errors on your IS? Do you face the same errors on other IS’s?

Did you check with the oracle DBA about the error to check and see if there was a intermittent downtime or the network team?

What is the status in production?

9

Hi Gautam,

Did you go through this suggestion by Tong Wang? Of increasing the expire timeout.

In your case its just 10sec.(1000ms)

/Naidu

10

Yes please try increase the time out or negotiate with your DBA for trouble shoot testing!

HTH,
RMG

11

@All, Thanks for prompt response.

We have same problem across all IS irrespective of connecting to Exadata, Oracle, SQL server with different versions and Oracle support could not give any solution. I suspect it is in network. Yes I have plan to increase timeout which will overcome such errors however still it is like suppressing errors

Please can you suggest what is the ideal parameter setting for connection pool; specially what is for block timeout and expiry timeout.

Regards,
Gautam

12

There is no certain number but default is 1000 and you can tune it based on your needs and test the connection.

HTH,
RMG

13

Hi All,
PFB logs and connection settings of wM IS and we are facing this connectivity issue occasionally and its lasting 10mins after that IS comes to normal.
We are not sure why the error is happening in wM. But when we ask our DBA they are telling DB servers are okey.
Kindly help me to understand and solve the issue.

[SCC.0126.0119E] An error occurred while attempting to create connections for maxis.eai.system.intdb.atomic.intdb_cat_conn_01:intdb_cat_conn_01
[SCC.0126.0106E] An error occurred while attempting to retrieve a connection from maxis.eai.system.intdb.atomic.intdb_cat_conn_01:intdb_cat_conn_01
[ART.0114.1007E] Adapter Runtime: Error Logged. See Error log for details. Error: [ART.118.5053] Adapter Runtime (Connection): Unable to get a connection to resource maxis.eai.system.intdb.atomic.intdb_cat_conn_01:intdb_cat_conn_01.
[ADA.1.204] Cannot connect to the database with DataSource class “oracle.jdbc.pool.OracleDataSource”.
IO Error: Socket read timed out

[SCC.0126.0106E] An error occurred while attempting to retrieve a connection from maxis.eai.system.esb.atomic.eaidb_conn_01:eaidb_conn_01
[ART.0114.1007E] Adapter Runtime: Error Logged. See Error log for details. Error: [ART.118.5053] Adapter Runtime (Connection): Unable to get a connection to resource maxis.eai.system.esb.atomic.eaidb_conn_01:eaidb_conn_01.
[ADA.1.204] Cannot connect to the database with DataSource class “oracle.jdbc.pool.OracleDataSource”.
IO Error: The Network Adapter could not establish the connection

Enable Connection Pooling true
Minimum Pool Size 0
Maximum Pool Size 100
Pool Increment Size 1
Block Timeout (msec) 5000
Expire Timeout (msec) 10000
Startup Retry Count 1
Startup Backoff Timeout (sec) 5

14

For me the issue seems more on network connectivity. Get your network connectivity experts to resolve this issue or to get more details.

15

Yes definetely it sounds a network/firewall related or JDBC connection settings issue some things need to be sorted out with your internal team.

HTH,
RMG

16

Thanks RMG & Mahesh,

Kindly correct me if I am wrong, If network/firewall related issue it should happen all times right? But its happening very rarely. That too issue is there for 10 mins after that IS is okey.

So, what can be checked from network/firewall side? Can you please let me know, so that I will check with my network team.

Thanks in advance for your reply.

17

Share the below logs to your network team:

[SCC.0126.0119E] An error occurred while attempting to create connections for maxis.eai.system.intdb.atomic.intdb_cat_conn_01:intdb_cat_conn_01
[SCC.0126.0106E] An error occurred while attempting to retrieve a connection from maxis.eai.system.intdb.atomic.intdb_cat_conn_01:intdb_cat_conn_01
[ART.0114.1007E] Adapter Runtime: Error Logged. See Error log for details. Error: [ART.118.5053] Adapter Runtime (Connection): Unable to get a connection to resource maxis.eai.system.intdb.atomic.intdb_cat_conn_01:intdb_cat_conn_01.
[ADA.1.204] Cannot connect to the database with DataSource class “oracle.jdbc.pool.OracleDataSource”.
IO Error: Socket read timed out

[SCC.0126.0106E] An error occurred while attempting to retrieve a connection from maxis.eai.system.esb.atomic.eaidb_conn_01:eaidb_conn_01
[ART.0114.1007E] Adapter Runtime: Error Logged. See Error log for details. Error: [ART.118.5053] Adapter Runtime (Connection): Unable to get a connection to resource maxis.eai.system.esb.atomic.eaidb_conn_01:eaidb_conn_01.
[ADA.1.204] Cannot connect to the database with DataSource class “oracle.jdbc.pool.OracleDataSource”.
IO Error: The Network Adapter could not establish the connection

18

Yes please share the above errors with your DBA/network teams and resolve if they can help any way.

HTH,
RMG