Unable to get a connection to resource

, ,
1

Hello

We get these connection related errors in production for some DB operations, it happens occasionaly. At DB end , all seems ok as per DBA.

Below are the connection parameters -

Min Pool : 0
Max Pool : 10
Pool increment : 1
block timeout : 120000
expire timeout : 180000

Error :

  1. Error executing sql: delete from NPOKE_PV_CONTRACTS_EXTRACT where NSI_STATUS_FLAG =‘G’ on connection: PRIMAVERA (type: XA_TRANSACTION) com.wm.pkg.art.error.DetailedSystemException : [ART.117.4012] Adapter Runtime (Adapter Service): Unable to run adapter service. Error occurred when connecting to resource NpJDBCConnections.XA:PRIMAVERA. [ART.118.5053] Adapter Runtime (Connection): Unable to get a connection to resource NpJDBCConnections.XA:PRIMAVERA. [ADA.1.204] Cannot connect to the database with DataSource class “oracle.jdbc.xa.client.OracleXADataSource”. IO Error: Connection reset

  2. [ART.117.4012] Adapter Runtime (Adapter Service): Unable to run adapter service. Error occurred when connecting to resource NpJDBCConnections.LOCAL:PRIMAVERA99. [ART.118.5053] Adapter Runtime (Connection): Unable to get a connection to resource NpJDBCConnections.LOCAL:PRIMAVERA99. [ADA.1.204] Cannot connect to the database with DataSource class “oracle.jdbc.pool.OracleDataSource”. IO Error: Connection reset

At this point i can think of increasing the pool size to 20, any other steps / areas i need to check for?

Thanks in advance.

Regards
Shreyus

2

On the contrary, I will suggest to reduce the pool size to match corresponding db user’s max session setting in db.

3

Hello,

Try to set Min Pool : 1 and check if you are still facing the same issue.
Sometime back we faced similar issue and we set the Min Pool Value to “1” and errors were minimized not completely removed though.

Regards,
Syed Faraz Ahmed

4

Hi,

setting min pool size to 1 might be helpful for the test, but for long term operation it is not recommended.

When the connection gets stale it will not be closed by default and will block the complete pool upon IS restart.

More likely you are hitting a network issue here.
Can you check this with your network team?

Regards,
Holger

5

Definitely it will create stale connection at any point of time but for testing purpose we can use it.
Correct me if i am wrong, I read some time back that for XA_Transactions we should set min pool size as “1” it is a good practice?

Regards,
Syed Faraz Ahmed