TNS-12535: TNS:operation timed out AND TNS-00505: Operation timed out

My one of the client recently implemented firewall for security purpose, it was successful activity. When all the application server connected to database server, below mentioned error logged into an alert log file frequently. It was clear clue, firewall messed up with the client-server connections.

It was the problem with Firewall “idle session timeout” value. If the client-server connection idle for more than defined value then it was forcefully dropped by firewall.
Error log:

Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
Time: 13-APR-2016 19:26:50
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
 ns secondary err code: 12560
 nt main err code: 505 
TNS-00505: Operation timed out
 nt secondary err code: 110
 nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.xxx.xxx.xxx)(PORT=xxxx))

Work around for TNS-12535 and TNS-00505:

We can remove or increase the firewall setting for maximum idle time. but it’s not recommended, because who know how much time client-server connection going to be idle.

So, append following parameter to RDBMS_HOME/network/admin/sqlnet.ora file on the database server. Resulting, database server check application server every <n> minutes to see if it is up by sending packets. So the connections are found as active by firewall and they will not terminated forcefully.

SQLNET.EXPIRE_TIME=n
Where <n> is a non-zero value set in minutes.

Reference: Doc ID 1628949.1

Your suggestions and comments are highly appreciated, if any.

Thank you. 🙂

Leave a Reply

%d bloggers like this: