Monday, July 20, 2015

ORA-03135: connection lost contact

This error may be accompanied by ORA-03113: end-of-file on communication channel. This actually means that your session has been dropped and there could be different reasons which might have caused this. Most commonly; a firewall between client and server could have caused this session/connection drop, if firewall senses some session to be idle for a longer period of time. This session drop timeout setting is done in the firewall and to deny its effect, SQLNET profile parameter “expire_time” could be used to keep the database sessions alive to avoid any connection drop by the firewall.

SQLNET.EXPIRE_TIME
To avoid the problem of "unwanted" disconnection, sqlnet.expire_time can be added in the sqlnet.ora file of database server. This parameter has a value in minutes and it would cause database server to send a packed to each client connect to it at an interval equal to the value of this parameter. This small packet sending would actually deceive firewall so that it considers it an active connection and does not drop it.

We can initially set it to a value of 10 minutes which can be alerted with coordination of network admin who can help to find out firewall settings. Add the following line in $ORACLE_HOME/network/admin/sqlnet.ora

############

SQLNET.EXPIRE_TIME = 10

############

Actual work of this parameter is to check the client sessions which might have exited but server process related to the session is still intact; so that this server process could also be terminated and resources could be released. For details about this parameter, please 
Click Here


3 comments:

Unknown said...

Thanks!

Sachin Saklani said...

I am having the same issue changed the EXPIRE_TIME to 10 but issue is not resolved on the production server.

Salman Ahmed Qureshi said...

Hi Sachin,
There could be multiple reasons for this error. There might be a possibility that your firewall has some issues, or network card. You would need to enable tracing at client side and server side and you may further involve Oracle support to analyze the trace files to find out the cause of session drop.