Tuesday, July 1, 2014

ORA-03113: end-of-file on communication channel

There could be several reasons for this error message returned to a session - for example, session is killed; network disconnection occurred, database is shutdown etc. Sometimes none of this happens but still end users complain about having disconnected from the database with this error message; time to time, and one of the very common reasons for this is firewall.

If our client (or application server) and database server has a firewall between them, firewall may have the functionality to drop a connection if firewall does not sense any activity on that connection for a specified about of time. This functionality of dropping the connection may vary from manufacturer to manufacturer and model to model of firewall; that how firewall would monitor the connection and what would make firewall to kill the connection.

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

No comments:

Post a Comment