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.
Fatal NI connect error 12170. VERSION INFORMATION: TNS for Linux: Version 184.108.40.206.0 - Production Oracle Bequeath NT Protocol Adapter for Linux: Version 220.127.116.11.0 - Production TCP/IP NT Protocol Adapter for Linux: Version 18.104.22.168.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. 🙂