Oracle 11g Logo

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. 🙂

Oracle 11g Logo

TNS-01201: Listener cannot find executable string for SID string – Listener failed to start

Usually TNS-01201 listener error pop up when oracle can’t find its executable under ORACLE_HOME variable mentioned in listener.ora file. After investigating listener.ora file, Found same, ORACLE_HOME was set to wrong path. After setting proper path listener started successfully.

Have a look on error logs:

Error Log: While starting listener

[oracle@PR admin]$ lsnrctl start
...
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/PR/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PR.localdomain)(PORT=1521)))
TNS-01201: Listener cannot find executable /u01/oracle/product/11.2.0/dbhome_1/bin/oracle for SID RTS
Listener failed to start. See the error message(s) above...

Error Log: While tnsping

[oracle@PR admin]$ tnsping RTS
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = PR)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = RTS) (GLOBAL_NAME = RTS) (UR=A)))
TNS-12541: TNS:no listener

listener.ora file:

SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
   (GLOBAL_DBNAME = RTS)
   (ORACLE_HOME = /u01/oracle/product/11.2.0/dbhome_1)
   (SID_NAME = RTS)
  )
 )
LISTENER =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = PR)(PORT = 1521))
 )
ADR_BASE_LISTENER = /u01/app/oracle

Solution:

Checkout above RED highlighted lines, mentioned ADR_BASE_LISTENER path is different then ORACLE_HOME. So after applying changes, listener started successfully. As follows:

[oracle@PR admin]$ lsnrctl start
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/PR/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PR.localdomain)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=PR)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 29-AUG-2015 09:22:35
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/PR/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PR.localdomain)(PORT=1521)))
Services Summary...
Service "RTS" has 1 instance(s).
Instance "RTS", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

tnsping also successful to RTS SID

[oracle@PR admin]$ tnsping RTS
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = PR)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = RTS) (GLOBAL_NAME = RTS) (UR=A)))
OK (0 msec)

Thanks,

Stay Tune. 🙂