ORA-12535 is the same as TNS-12535, they all indicate that the connection between the client and the listener has been timed out.
First, let’s see how we reproduce TNS-12535.
C:\Users\ed>tnsping compdb
TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0 - Production on 22-JUL-2014 19:03:21
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
C:\oracle\app\client\ed\product\12.1.0\client_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primary01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = compdb)))
TNS-12535: TNS:operation timed out
Causes
TNS-12535 means the connect identifier can be found in tnsnames.ora and the hostname is resolvable by DNS or /etc/hosts, but somehow network problems make the connection timed out.
There could be several causes of TNS-12535 that I listed below.
- The hostname in the connect descriptor of compdb points to a wrong IP address.
- The port specified in the connect descriptor of compdb points to a wrong one.
- The port specified in the connect descriptor of compdb is not opened on firewall of the database server.
- DNS or /etc/hosts points to a wrong IP address while resolving domain name.
- Any other anti-virus software on clients.
- The server has been shutdown, there’s no way to go.
Solutions
- Inspect the content of tnsnames.ora closely once again to make sure there’s no any typo or mistake.
- Check your DNS, /etc/hosts or C:\Windows\System32\drivers\etc\hosts file to make sure the IP mapping is correct. Additionally, make sure the format of IP mapping is correct.
- Open the port 1521.
- For firewalld or iptables, you may refer to: Open Port 1521 on Linux for Database Server.
- For Windows platform, you may refer to: How to Open Port 1521 on Windows Server.
- Revert any changes of security policy on network appliances including IPS, IDS and firewall. I have ever met a case related to IPS.
- If the server is down, then recover server’s availability.
- Usually, TNS-12535 accompanies with ORA-3136 (or ORA-03136), you may consider to raise timeout period on the server.
For more connection troubleshooting, you may refer to Oracle 19c Net Services Administrator’s Guide: 15 Testing Connections.
These days everything goes to the cloud or it has been collocated somewhere in a shared infrastructure. In this post I’ll talk about sessions being disconnected from your databases, firewalls and dead connection detection.
Changes
We moved number of 11g databases from one data centre to another.
Symptoms
Now probably many of you have seen the following error in your database alertlog «TNS-12535: TNS:operation timed out» or if you haven’t you will definitely see it some day.
Consider the following error from database alert log:
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.3.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
Time: 12-MAR-2015 10:28:08
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.168.0.10)(PORT=49831))
Thu Mar 12 10:28:09 2015
Now this error indicate timing issues between the server and the client. It’s important to mention that those errors are RESULTANT, they are informational and not the actual cause of the disconnect. Although this error might happen for number of reasons it is commonly associated with firewalls or slow networks.
Troubleshooting
The best way to understand what’s happening is to build a histogram of the duration of the sessions. In particular we want to understand whether disconnects are sporadic and random or they follow a specific pattern.
To do so you need to parse the listener log and locate the following line from the above example:
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.10)(PORT=49831))
Since the port is random you might not get same record or if you do it might be days apart.
Here’s what I found in the listener:
12-MAR-2015 08:16:52 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)(CID=(PROGRAM=app)(HOST=apps01)(USER=scott))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.10)(PORT=49831)) * establish * ORCL * 0
In other words — at 8:16 the user scott established connection from host 192.168.0.10.
Now if you compare both records you’ll get the duration of the session:
Established: 12-MAR-2015 08:16:52
Disconnected: Thu Mar 12 10:28:09 2015
Here are couple of other examples:
alertlog:
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.10)(PORT=20620))
Thu Mar 12 10:31:20 2015
listener.log:
12-MAR-2015 08:20:04 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)(CID=(PROGRAM=app)(HOST=apps01)(USER=scott))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.10)(PORT=20620)) * establish * ORCL * 0
alertlog:
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.10)(PORT=48157))
Thu Mar 12 10:37:51 2015
listener.log:
12-MAR-2015 08:26:36 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)(CID=(PROGRAM=app)(HOST=apps01)(USER=scott))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.10)(PORT=48157)) * establish * ORCL * 0
alertlog:
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.11)(PORT=42618))
Tue Mar 10 19:09:09 2015
listener.log
10-MAR-2015 16:57:54 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=ORCL1)(SERVER=DEDICATED)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.11)(PORT=42618)) * establish * ORCL1 * 0
As you may have noticed the errors follow very strict pattern — each one gets disconnect exactly 2hrs 11mins after it has been established.
Cause
Given the repetitive behaviour of the issue and that it happened for multiple databases and application servers we can conclude that’s definitely a firewall issue.
The firewall recognizes the TCP protocol and keeps a record of established connections and it also recognizes TCP connection closure packets (TCP FIN type packet). However sometimes the client may abruptly end communication without closing the end points properly by sending FIN packet in which case the firewall will not know that the end-points will no longer use the opened channel. To resolve this problem firewall imposes a BLACKOUT on those connections that stay idle for a predefined amount of time.
The only issues with BLACKOUT is that neither or the sides will be notified.
In our case the firewall will disconnect IDLE sessions after around 2hrs of inactivity.
Solution
The solution for database server is to use Dead Connection Detection (DCD) feature. DCD detects when a connection has terminated unexpectedly and flags the dead session so PMON can release the resources associated with it.
DCD sets a timer when a session is initiated and when the timer expires SQL*Net on the server sends a small 10 bytes probe packet to the client to make sure connection is still active. If the client has terminated unexpectedly the server will get an error and the connection will be closed and the associated resources will be released. If the connection is still active then the probe packet is discarded and the timer is reset.
To enable DCD you need to set SQLNET.EXPIRE_TIME in sqlnet.ora of you RDBMS home!
cat >> $ORACLE_HOME/network/admin/sqlnet.ora
SQLNET.EXPIRE_TIME=10
This will set the timer to 10 minutes. Remember that sessions need to reconnect for the change to take place it won’t work for existing connections.
Firewalls become smarter and they can now inspect packages even deeper. Make sure the following settings are also disabled:
- SQLNet fixup protocol
- Deep Packet Inspection (DPI)
- SQLNet packet inspection
- SQL Fixup
I had similar issue with Dataguard already, read more here:
Smart Firewalls
How to test Dead Connection Detection
You might want to test or make sure DCD really works. You’ve got multiple options here — Oracle SQL client trace, Oracle SQL Server Trace, Sniff the network with packet analyzer OR using strace to trace the server process. I used strace since I had access to the database server and it was non intrusive.
-
Establish a connection to the database through SQL*Net
-
Find the processes number for your session:
SQL> select SPID from v$process where ADDR in (select PADDR from v$session where username='SVE');
SPID
------------------------
62761
- Trace the process:
[oracle@dbsrv ~]$ strace -tt -f -p 62761
Process 62761 attached - interrupt to quit
11:36:58.158348 --- SIGALRM (Alarm clock) @ 0 (0) ---
11:36:58.158485 rt_sigprocmask(SIG_BLOCK, [], NULL, 8) = 0
....
11:46:58.240065 --- SIGALRM (Alarm clock) @ 0 (0) ---
11:46:58.240211 rt_sigprocmask(SIG_BLOCK, [], NULL, 8) = 0
...
11:46:58.331063 write(20, "\0\n\0\0\6\20\0\0\0\0", 10) = 10
...
What I did was to attach to the process, simulate some activity at 11:36 and then leave the session IDLE. Then 10 minutes later the server process sent an empty packet to the client to check if the connection is still alive.
Conclusion
Errors in alertlog disappeared after I enabled the DCD.
Make sure to enable DCD if you host your databases in a shared infrastructure or there are firewalls between your database and application servers.
References
How to Check if Dead Connection Detection (DCD) is Enabled in 9i ,10g and 11g (Doc ID 395505.1)
Alert Log Errors: 12170 TNS-12535/TNS-00505: Operation Timed Out (Doc ID 1628949.1)
Resolving Problems with Connection Idle Timeout With Firewall (Doc ID 257650.1)
Dead Connection Detection (DCD) Explained (Doc ID 151972.1)
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. 🙂
Проблемы с listener ORA-12535: TNS:operation timed out в Oracle 9i
Одной из причин таких ошибок в часы пик может быть то, что листенер всего-навсего не успевает обработать поступающие запросы на подключение.
Например, когда одновременно запускаются очень много клиентских джобов, которые все в один момент обрушиваются на листенер, листенер успевает обработать только первые поступившие запросы, а остальные падают с ошибкой ORA-12535.
Для борьбы с ними можно попробовать следующие варианты:
1) Параметр INBOUND_CONNECT_TIMEOUT
Можно попробовать установить параметр в файле listener.ora: INBOUND_CONNECT_TIMEOUT_
= 600
Этот параметр задает кол-во секунд, в течение которых должна быть завершена обработка запроса клиента. Если за указанное время листенер не успевает обработать запрос клиента на подключение, он выдает ошибку ORA-12535 и обрывает соединение с клиентом.
Это аналог параметра CONNECT_TIMEOUT, который является устаревшим в версии 9i.
Еще можно попробовать в файле sqlnet.ora добавить параметр:
SQLNET.INBOUND_CONNECT_TIMEOUT = 700
2) Параметр QUEUESIZEС помощью этого параметра можно установить кол-во запросов, которые может обрабатывать листенер одновременно.
LISTENER_TESTDB =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = myhost)(Port = 2483)(QUEUESIZE=200))
)
SID_LIST_LISTENER_TESTDB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TESTDB)
(ORACLE_HOME = /testdb/u01/app/oracle/product/9.2.0.8.0)
(SID_NAME = TESTDB)
)
)
3) Поднятие дополнительных листенеров
Установка параметров INBOUND_CONNECT_TIMEOUT и QUEUESIZE не всегда может помочь, так как листенер работает с той же скоростью, а те запросы, которые ожидают подключения к базе, всего-навсего будут дольше удерживаться, пока не рассосется очередь. Но если очередь все-таки не рассосется за указанное время, клиенты опять упадут с ошибкой ORA-12535.
Чтоб решить проблему с производительностью раз и навсегда можно поднять дополнительные листенеры и настроить tns на стороне клиента, чтоб, если не отвечает первый листенер, запрос шел на второй, третий листенеры.
И так, на сервере поднимаем 2 листенера на 2 разных портах 2483 и 2484.
В файле listener.ora прописываем:
LISTENER_TESTDB =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = myhost)(Port = 2484))
)
SID_LIST_LISTENER_TESTDB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TESTDB)
(ORACLE_HOME = /testdb/u01/app/oracle/product/9.2.0.8.0)
(SID_NAME = TESTDB)
)
)
LISTENER_TESTDB2 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = myhost)(Port = 2483))
)
SID_LIST_LISTENER_TESTDB2 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TESTDB2)
(ORACLE_HOME = /testdb/u01/app/oracle/product/9.2.0.8.0)
(SID_NAME = TESTDB2)
)
)
Запускаем листенеры:
oracle@myhost $ lsnrctl
LSNRCTL> start LISTENER_TESTDB
LSNRCTL> start LISTENER_TESTDB2
Теперь нужно на стороне клиента прописать в tnsnames.ora:
TESTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(FAILOVER = ON)
(LOAD_BALANCE = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 2483))
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 2484))
)
(CONNECT_DATA = (SERVICE_NAME = TESTDB))
)
Теперь можно в $ORACLE_HOME/network/log посмотреть размеры лог файлов каждого листенера. Они будут расти по мере обработки запросов на подключение.
oracle@myhost $ ls -la *.log
May 6, 2020
Sometimes You can get “ORA-12535: TNS:operation timed out ” error.
ORA-12535: TNS operation timed out
Details of error are as follows.
ORA-12535: TNS:operation timed out Cause: The requested operation could not be completed within the time out period. Action: Look at the documentation on the secondary errors for possible remedy. See SQLNET.LOG to find secondary error if not provided explicitly. Turn on tracing to gather more information. [oracle@msdbadm01 admin]$ tnsping MSDB TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 06-MAY-2020 13:02:57 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: /ggateb01/oracle_client/product/11.2.0/client_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.63.34)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MSDB))) TNS-12535: TNS:operation timed out [oracle@msdbadm01 admin]$
ORA-12535
This ORA-12535 error is related with firewall or listener timeout.
If firewall is open, then disable it as follows.
service iptables stop chkconfig iptables off service ip6tables stop chkconfig ip6tables off
You can disable firewall in Redhat with below script.
systemctl stop firewalld systemctl disable firewalld
TNS operation timed out
Or Check your IP Adress, Port if they are incorrect, you can get this error. To solve this error, fix IP adress and port.
Or
TNS-12535 and ORA-03135: connection lost contact error are related with the Network traffic and High loaded database operations.
To solve this error, add the following parameter to the SQLNET.ORA file ( under $ORACLE_HOME/network/admin/ ).
SQLNET.INBOUND_CONNECT_TIMEOUT = 180 INBOUND_CONNECT_TIMEOUT_LISTENER = 120
then restart the Listener as follows.
$ srvctl stop listener $ srvctl start listener
or
$ lsnrctl stop listener $ lsnrctl start listener
Or Check database server if it is up or not.
Do you want to learn Oracle Database for Beginners, then Click and read the following articles.
Oracle Database Tutorials for Beginners ( Junior Oracle DBA )