Ora 12170 tns connect timeout occurred что делать

oracle tutorial webinars

Error ORA-12170: “TNS: Connect Timeout Occurred”

There are several possible causes for error ORA-12170. They include: problems with the firewall, when the database is down, when the listener is down, if the sqlnet.ora parm is invalid, among other possible causes.

To fix the error, the user should consider all possible causes and their corresponding solutions on how to get rid of the error. The solution to error ORA-12170 will emerge through process of elimination.

Check to make sure there is no firewall in place between the client and server. If there is, disable the firewall and try again

If the error occurred because there was a delay in the system or network, turn on tracing to determine exactly where clients are timing out.

It may be the case that the Oracle service is running on an IP address while the host is configured to another IP address. To determine the IP address of the Oracle service, issue an lsnrctl status command. Check the address that is reported. To determine the IP address of the host, issue an ipconfig or ifconfig. This is dependent on your operation system; use ipconfig for Windows OS and ifconfig for Linux. To avoid seeing error ORA-12170, use a static IP address rather than a DHCP for assigning an IP address of the host.

In one case, the server may have shut down because the connection establishment or communication with a client did not complete in an allotted time interval. This is probably due to delays within the system or the network, or it may possibly be a client that is trying to maliciously attack by causing a Denial of Service attack on the server.

If the error is occurring because of a slow network or system, you may fix the error by reconfiguring the following parameters in sqlnet.ora to larger values:

SQLNET.INBOUND_CONNECT_TIMEOUT, SQLNET.SEND_TIMEOUT, SQLNET.RECV_TIMEOUT

If the error is occurring due to a malicious attack via a Denial of Service attack, you may use the sqlnet.log to identify the source of attack and restrict the attacker’s access. However, users should be aware that logged addresses might not be entirely reliable. To find your sqlnet.ora file, go to your $ORACLE_HOME/network/admin directory. If the timeout occurs before the IP address can be retrieved by the database server, enable listener tracing to determine the client who made the request.

If you continue to experience problems, contact your business objects administrator or database administrator with the error message information.

Русские Блоги

После десяти лет разработки осталась только эта архитектурная система! >>>

После успешной установки ORACLE 11g в Red Hat Enterprise Linux Server версии 6.7, после настройки TNS на клиенте, проверьте, может ли он подключиться к серверу блока данных, результатом будет ошибка: ORA-12170: TNS: время ожидания соединения

1: Сначала проверьте, можно ли пинговать сеть, как показано ниже, сеть разблокирована.

2: Проверьте конфигурацию TNS (с конфигурацией TNS проблем нет)

GSP =
(DESCRIPTION =
(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.1.81)(PORT = 1521))
(CONNECT_DATA=
(SERVER = DEDICATED)
(SERVICE_NAME = esbdb)
)
)
3: Проверьте, запущена ли служба мониторинга сервера

LSNRCTL for Linux: Version 10.2.0.1.0 — Production on 14-DEC-2012 15:51:13

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 — Production
Start Date 14-DEC-2012 13:15:28
Uptime 0 days 2 hr. 35 min. 45 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /database/oracle/product/dbhome/network/admin/listener.ora
Listener Log File /database/oracle/product/dbhome/network/log/listener.log
Listening Endpoints Summary.
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=wgods)(PORT=1521)))
Services Summary.
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service.
Service "gsp" has 2 instance(s).
Instance "gsp", status UNKNOWN, has 1 handler(s) for this service.
Instance "gsp", status READY, has 1 handler(s) for this service.
Service "gspXDB" has 1 instance(s).
Instance "gsp", status READY, has 1 handler(s) for this service.
Service "gsp_XPT" has 1 instance(s).
Instance "gsp", status READY, has 1 handler(s) for this service.
The command completed successfully

4: Используйте команду tnsping, чтобы проверить и сообщить TNS-12535: TNS: Превышено время ожидания операции. На данный момент мы можем быть уверены, что это проблема межсетевого экрана.

Утилита TNS Ping для 32-битной Windows: версия 11.2.0.1.0-Production от 14-декабря-2012 15:47:15

Файл используемых параметров:
E:\app\kerry\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

Адаптер EZCONNECT был использован для разрешения псевдонимов
Попробуйте подключиться (DESCRIPTION = (CONNECT_DATA = (SERVICE_NAME =)) (ADDRESS = (PROTOCOL = TCP) (HOST = 172.20.32.79) (PORT = 1521)))
TNS-12535: TNS: Превышено время ожидания операции

Для проблемы брандмауэра у нас может быть два решения:

1: Отключите брандмауэр (это решение не очень хорошее, отключение брандмауэра принесет много угроз безопасности)

]# service iptables stop
Flushing firewall rules: [ OK ]
Setting chains to policy ACCEPT: filter [ OK ]
Unloading iptables modules: [ OK ]

2: изменить iptables, открыть порт 1521 и разрешить подключение к порту 1521

2.1 Отредактируйте файл iptables и добавьте запись -A RH-Firewall-1-INPUT -p tcp -m state —state NEW -m tcp —dport 1521 -j ACCEPT.
[[email protected] sysconfig]# vi iptables

# Generated by iptables-save v1.3.5 on Fri Dec 14 17:03:58 2012
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [1749:243629]
:RH-Firewall-1-INPUT — [0:0]
-A INPUT -j RH-Firewall-1-INPUT
-A FORWARD -j RH-Firewall-1-INPUT
-A RH-Firewall-1-INPUT -i lo -j ACCEPT
-A RH-Firewall-1-INPUT -p icmp -m icmp —icmp-type any -j ACCEPT
-A RH-Firewall-1-INPUT -p esp -j ACCEPT
-A RH-Firewall-1-INPUT -p ah -j ACCEPT
-A RH-Firewall-1-INPUT -d 224.0.0.251 -p udp -m udp —dport 5353 -j ACCEPT
-A RH-Firewall-1-INPUT -p udp -m udp —dport 631 -j ACCEPT
-A RH-Firewall-1-INPUT -p tcp -m tcp —dport 631 -j ACCEPT
-A RH-Firewall-1-INPUT -m state —state RELATED,ESTABLISHED -j ACCEPT
-A RH-Firewall-1-INPUT -p tcp -m state —state NEW -m tcp —dport 21 -j ACCEPT
-A RH-Firewall-1-INPUT -p tcp -m state —state NEW -m tcp —dport 25 -j ACCEPT
-A RH-Firewall-1-INPUT -p tcp -m state —state NEW -m tcp —dport 22 -j ACCEPT
-A RH-Firewall-1-INPUT -p tcp -m state —state NEW -m tcp —dport 23 -j ACCEPT
-A RH-Firewall-1-INPUT -p tcp -m state —state NEW -m tcp —dport 1521 -j ACCEPT
-A RH-Firewall-1-INPUT -j REJECT —reject-with icmp-host-prohibited
COMMIT
# Completed on Fri Dec 14 17:03:58 2012

"iptables" 24L, 1212C written

2.2 Перезапустите сервис iptables
[[email protected] sysconfig]# service iptables restart
Flushing firewall rules: [ OK ]
Setting chains to policy ACCEPT: filter [ OK ]
Unloading iptables modules: [ OK ]
Applying iptables firewall rules: [ OK ]
Loading additional iptables modules: ip_conntrack_netbios_ns ip_conntrack_ftp [ OK ]

2.3 Сохраните новые правила, чтобы правила конфигурации не стали недействительными после перезапуска компьютера в следующий раз.

[[email protected] sysconfig]# service iptables save
Saving firewall rules to /etc/sysconfig/iptables: [ OK ]

2.4 Проверьте, открыт ли порт 1521 и разрешите соединение (см. Красную часть)
[[email protected] sysconfig]# iptables -L -n
Chain INPUT (policy ACCEPT)
target prot opt source destination
RH-Firewall-1-INPUT all — 0.0.0.0/0 0.0.0.0/0

Chain FORWARD (policy ACCEPT)
target prot opt source destination
RH-Firewall-1-INPUT all — 0.0.0.0/0 0.0.0.0/0

Chain OUTPUT (policy ACCEPT)
target prot opt source destination

Chain RH-Firewall-1-INPUT (2 references)
target prot opt source destination
ACCEPT all — 0.0.0.0/0 0.0.0.0/0
ACCEPT icmp — 0.0.0.0/0 0.0.0.0/0 icmp type 255
ACCEPT esp — 0.0.0.0/0 0.0.0.0/0
ACCEPT ah — 0.0.0.0/0 0.0.0.0/0
ACCEPT udp — 0.0.0.0/0 224.0.0.251 udp dpt:5353
ACCEPT udp — 0.0.0.0/0 0.0.0.0/0 udp dpt:631
ACCEPT tcp — 0.0.0.0/0 0.0.0.0/0 tcp dpt:631
ACCEPT all — 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED
ACCEPT tcp — 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:21
ACCEPT tcp — 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:25
ACCEPT tcp — 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:22
ACCEPT tcp — 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:23
ACCEPT tcp — 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:1521
REJECT all — 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited
[[email protected] sysconfig]#

Читать:

Как объединить словари в python

Используйте PL / SQL Developer для подключения к базе данных от клиента и решения проблемы.

How to Resolve ORA-12170: TNS:Connect timeout occurred

At some points, the client sides received intermittent ORA-12170 repeatedly:

ORA-12170: TNS:Connect timeout occurred

Meanwhile, the server side did not receive any errors either in listener.log or alert.log .

ORA-12170 mean that the failed connections did not reach the listener due to network problem. Most likely, they’re blocked by security rules.

Sometimes, there’s a time-out, but what would cause the network time out? What factors that blocked the packets of clients in the pathway to database. Here I made a list for the possibilities of ORA-12170:

TNS Names
- Wrong IP address in TNS entry, which cannot be reachable in your local area network. There maybe a duplicate entry in your tnsnames.ora .
- Wrong port in TNS entry, which denies all connections by firewall.
- Detected suspicious packets and blocked them.
- Was scanning the whole operating system.
Solutions

The first step, you need to make sure everything you provided is correct like I said earlier, which includes checking tnsnames.ora .

If the firewall is on network appliance, you should ask your network administrator for help.

In our case, we found an IPS applied new rules recently that could caused the problem eventually. To revert the configuration, we rolled back the policy of that IPS. No more ORA-12170.

Other Factors

By the way, if the network appliance really needs more considerable time to complete the validation, you can raise the inbound connect time value of the listener, which is also the solution to ORA-03136 Inbound Connection Timed Out.

Such cases of ORA-12170 happened on the client sides. The management usually misunderstood as a database problem, and ask DBA for resolving it. But the database was healthy during incidents, this reminds us that not all ORA errors are thrown by the database.

ORA-12170: TNS:Connect timeout occurred

I was trying to connect to the database here in my laptop using Oracle Toad but I kept on having this error:

ORA-12170: TNS:Connect timeout occurred

What are the possible reasons why I kept on having this error?

I accessed the same database yesterday and was able to accessed it.

9 Answers 9

[Gathering the answers in the comments]

The problem is that the Oracle service is running on a IP address, and the host is configured with another IP address.

To see the IP address of the Oracle service, issue an lsnrctl status command and check the address reported (in this case is 127.0.0.1, the localhost):

To see the host IP address, issue the ipconfig (under windows) or ifconfig (under linux) command.

Howewer, in my installation, the Oracle service does not work if set on localhost address, I must set the real host IP address (for example 192.168.10.X).

To avoid this problem in the future, do not use DHCP for assigning an IP address of the host, but use a static one.

It is because of conflicting SID. For example, in your Oracle12cBase\app\product\12.1.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora file, connection description for ORCL is this:

And, you are trying to connect using the connection string using same SID but different IP, username/password, like this:

To resolve this, make changes in the tnsnames.ora file:

Check the FIREWALL, to allow the connection at the server from your client. By allowing Domain network or create rule.

Issue because connection establishment or communication with a client failed to complete within the allotted time interval. This may be a result of network or system delays.

TROUBLESHOOTING STEPS (Doc ID 730066.1)

Connection Timeout errors ORA-3135 and ORA-3136 A connection timeout error can be issued when an attempt to connect to the database does not complete its connection and authentication phases within the time period allowed by the following: SQLNET.INBOUND_CONNECT_TIMEOUT and/or INBOUND_CONNECT_TIMEOUT_ server-side parameters.

Starting with Oracle 10.2, the default for these parameters is 60 seconds where in previous releases it was 0, meaning no timeout.

On a timeout, the client program will receive the ORA-3135 (or possibly TNS-3135) error:

ORA-3135 connection lost contact

and the database will log the ORA-3136 error in its alert.log:

. Sat May 10 02:21:38 2008 WARNING: inbound connection timed out (ORA-3136) .
- Authentication SQL
When a database session is in the authentication phase, it will issue a sequence of SQL statements. The authentication is not complete until all these are parsed, executed, fetched completely. Some of the SQL statements in this list e.g. on 10.2 are:

NOTE: The list of SQL above is not complete and does not represent the ordering of the authentication SQL . Differences may also exist from release to release.
- Hangs during Authentication
The above SQL statements need to be Parsed, Executed and Fetched as happens for all SQL inside an Oracle Database. It follows that any problem encountered during these phases which appears as a hang or severe slow performance may result in a timeout.

Symptoms of such hangs will be seen by the authenticating session as waits for: • cursor: pin S wait on X • latch: row cache objects • row cache lock Other types of wait events are possible; this list may not be complete.

The issue here is that the authenticating session is blocked waiting to get a shared resource which is held by another session inside the database. That blocker session is itself occupied in a long-running activity (or its own hang) which prevents it from releasing the shared resource needed by the authenticating session in a timely fashion. This results in the timeout being eventually reported to the authenticating session.
- Troubleshooting of Authentication hangs
In such situations, we need to find out the blocker process holding the shared resource needed by the authenticating session in order to see what is happening to it.

Typical diagnostics used in such cases are the following:
1. Three consecutive systemstate dumps at level 266 during the time that one or more authenticating sessions are blocked. It is likely that the blocking session will have caused timeouts to more than one connection attempt. Hence, systemstate dumps can be useful even when the time needed to generate them exceeds the period of a single timeout e.g. 60 sec:
- ASH reports covering e.g. 10-15 minutes of a time period during which several timeout errors were seen.
- If possible, Two consecutive queries on V$LATCHHOLDER view for the case where the shared resource being waited for is a latch. select * from v$latchholder; The systemstate dumps should help in identifying the blocker session. Level 266 will show us in what code it is executing which may help in locating any existing bug as the root cause.
Examples of issues which can result in Authentication hangs
Unpublished Bug 7039896 workaround parameter _enable_shared_pool_durations=false see Note 7039896.8