Thanks for the question, Ravikanth.
Asked: August 16, 2021 - 7:24 am UTC
Last updated: August 17, 2021 - 3:46 am UTC
Version: Oracle Database 12c Enterprise Edition Release
Viewed 1000+ times
You Asked
Hello,
We keep getting connection closed error with following in error stack.
Could you please let me know who we could address this. We do not have any more details in the error stack. Is there any replay setting we have to tweak? This happens few times each month and the frequency is increasing these days.
Caused by: java.sql.SQLRecoverableException: The connection is closed
at oracle.ucp.jdbc.proxy.NonReplayableConnectionProxy.pre(NonReplayableConnectionProxy.java:198) ~[ucp-18.3.0.0.jar:18.3.0.0.0]
Thanks
and Connor said...
Try the following, taken from MOS note 1642867.1
To maintain the connections alive:
1. Set ENABLE=BROKEN parameter in the DESCRIPTION clause in the connection descriptor. For example:
jdbc:oracle:thin:@(DESCRIPTION=(ENABLE=BROKEN)(ADDRESS=(PROTOCOL=tcp)(PORT=<PORT>)(HOST=<HOSTNAME>))(CONNECT_DATA=(SERVICE_NAME=<SERVICE NAME>)))
2. And also set SQLNET.EXPIRE_TIME=10 in the database sqlnet.ora. This will enable Dead Connection Detection (DCD).
DCD is a keepalive mechanism. To summarize what DCD does:
* When a connection is established on the database, DCD is initiated.
* SQL*Net reads the SQLNET.EXPIRE_TIME parameter in the SQLNET.ORA file.
* Based on the value of this parameter, SQL*Net sets a timer to generate an "alarm".
* When this timer expires, SQL*Net sends a "probe" packet to the client.
* This is an empty SQL*Net packet; it doesn't contain data, but does create data traffic on the underlying protocol.
* If the client end of the connection is still active, the probe is discarded, and the timer mechanism is reset.
* If the client has terminated abnormally, the server will receive an error from the send call issued for the probe, and SQL*Net on the server will signal the operating system to release the connection's resources.
The (ENABLE=BROKEN) clause will work in conjunction with the server-side sqlnet.expire_time=10 , by enabling the JDBC driver to respond to the client probes sent by the server-side when the sqlnet.expire_time is reached. This will provide regular traffic on the sockets and prevent them from timing out.
Is this answer out of date? If it is, please let us know via a Comment