To enable DCD you need to set SQLNET.EXPIRE_TIME in sqlnet.ora of you RDBMS home!Ĭat > $ORACLE_HOME/network/admin/sqlnet.ora If the connection is still active then the probe packet is discarded and the timer is reset. If the client has terminated unexpectedly the server will get an error and the connection will be closed and the associated resources will be released. DCD detects when a connection has terminated unexpectedly and flags the dead session so PMON can release the resources associated with it.ĭCD sets a timer when a session is initiated and when the timer expires SQL*Net on the server sends a small 10 bytes probe packet to the client to make sure connection is still active. The solution for database server is to use Dead Connection Detection (DCD) feature.
In our case the firewall will disconnect IDLE sessions after around 2hrs of inactivity. The only issues with BLACKOUT is that neither or the sides will be notified. To resolve this problem firewall imposes a BLACKOUT on those connections that stay idle for a predefined amount of time. However sometimes the client may abruptly end communication without closing the end points properly by sending FIN packet in which case the firewall will not know that the end-points will no longer use the opened channel. The firewall recognizes the TCP protocol and keeps a record of established connections and it also recognizes TCP connection closure packets (TCP FIN type packet). Given the repetitive behaviour of the issue and that it happened for multiple databases and application servers we can conclude that's definitely a firewall issue.
Listener.log 1 16:57:54 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=_jdbc_)(USER=root))(SERVICE_NAME=ORCL1)(SERVER=DEDICATED)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.11)(PORT=42618)) * establish * ORCL1 * 0Īs you may have noticed the errors follow very strict pattern - each one gets disconnect exactly 2hrs 11mins after it has been established. Now if you compare both records you'll get the duration of the session:Īlertlog: Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.10)(PORT=20620)) In other words - at 8:16 the user scott established connection from host 192.168.0.10. Since the port is random you might not get same record or if you do it might be days apart. To do so you need to parse the listener log and locate the following line from the above example: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.10)(PORT=49831)) In particular we want to understand whether disconnects are sporadic and random or they follow a specific pattern. The best way to understand what's happening is to build a histogram of the duration of the sessions. Although this error might happen for number of reasons it is commonly associated with firewalls or slow networks. It's important to mention that those errors are RESULTANT, they are informational and not the actual cause of the disconnect. Now this error indicate timing issues between the server and the client. Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
TNS for Linux: Version 11.2.0.3.0 - Production Now probably many of you have seen the following error in your database alertlog "TNS-12535: TNS:operation timed out" or if you haven't you will definitely see it some day.Ĭonsider the following error from database alert log: Fatal NI connect error 12170. We moved number of 11g databases from one data centre to another. In this post I'll talk about sessions being disconnected from your databases, firewalls and dead connection detection. These days everything goes to the cloud or it has been collocated somewhere in a shared infrastructure.
0 kommentar(er)
