Fatal ni connect error 12170 oracle что это

ORA-12170/TNS-12535 Timeout Errors while attempting to Connect To Remote Database Using SQL*Plus or Any other tool. (Doc ID 1392646.1)

Last updated on AUGUST 02, 2021

Applies to:

Symptoms

SQLPLUS and TNSPING command fails with the TNS-12535 error:

Verified the issue by the client sqlnet.log and trace file Oracle Net Client trace file, ‘sqlnet_3640_1.trc’

1. Client sqlnet.log

Fatal NI connect error 12170.

VERSION INFORMATION:
TNS for 32-bit Windows: Version 10.2.0.1.0 — Production
Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 10.2.0.1.0 — Production
Time: 05-JAN-2012 12:56:25
Tracing to file: C:oracleproduct10.2.0client_1NETWORKtracesqlnet_3640_1.trc
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: 60
nt OS err code: 0
Client address:

2. Client sqlnet trace (sqlnet_3640_1.trc)

If try SQLPLUS and TNSPING from the database server using listener it works fine.

Changes

No changes, This is a new installation of 11.2.

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

Error ora 12170 tns connect timeout

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.

Источник

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

ORA-12170

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.
• Hardware or software firewall
• Poor quality of network
• Network card interface (NIC) failure
• Anti-Virus software
  • Detected suspicious packets and blocked them.
  • Was scanning the whole operating system.
• Intrusion Prevention System (IPS)
• Intrusion Detection System (IDS)
• Proxy Server

Solutions

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

The most common mistake is that you didn’t open the port 1521 on firewall. That caused ORA-12170. To open port on firewall of the database server, you may refer to these posts:

On Linux:

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.

Источник

Error ora 12170 tns connect timeout

Пользователи не могут подключиться к базе. Обычно при этом они получают ошибки: ORA-12547: TNS:lost contact или ORA-12637: Packet receive failed.

В sqlnet.log на сервере сообщения об ошибке ORA-12170: TNS:Connect timeout. Еще для версий 10g и выше, в alert.log могут быть сообщения WARNING: inbound connection timed out (ORA-3136).

VERSION INFORMATION:
TNS for 64-bit Windows: Version 10.2.0.4.0 — Production
Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 10.2.0.4.0 — Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 10.2.0.4.0 — Production
Time: 13-FEB-2013 13:47:12
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: 60
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.77.116.47)(PORT=2971))
***********************************************************************

Для разных ОС, параметр ‘nt secondary err code‘ может быть разным

For the Solaris system: nt secondary err code: 145:
ETIMEDOUT 145 /* Connection timed out */
For the Linux operating system: nt secondary err code: 110
ETIMEDOUT 110 Connection timed out
For the HP-UX system: nt secondary err code: 238:
ETIMEDOUT 238 /* Connection timed out */
For AIX: nt secondary err code: 78:
ETIMEDOUT 78 /* Connection timed out */
For Windows based platforms: nt secondary err code: 60 (which translates to Winsock Error: 10060)

Выдержка из документации

ORA-12170: TNS:Connect timeout occurred
Cause: The server shut down 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; or this may indicate that a malicious client is trying to cause a Denial of Service attack on the server.
Action: If the error occurred because of a slow network or system, reconfigure one or all of the parameters SQLNET.INBOUND_CONNECT_TIMEOUT, SQLNET.SEND_TIMEOUT, SQLNET.RECV_TIMEOUT in sqlnet.ora to larger values. If a malicious client is suspected, use the address in sqlnet.log to identify the source and restrict access. Note that logged addresses may not be reliable as they can be forged (e.g. in TCP/IP).

Смысл этой ошибки в том что соединение не может быть установлено в течение отведенного интервала времени. А вот причин по которым это происходит может быть великое множество.

Как видно, основная рекомендация — увеличить параметры SQLNET.INBOUND_CONNECT_TIMEOUT, SQLNET.SEND_TIMEOUT и SQLNET.RECV_TIMEOUT. Можно попробовать сделать это, но это может не помочь. Поэтому лучше попробовать разобраться в корне проблемы.

Несколько основных причин ошибки и способы их решения

1) Серверные ресурсы перегружены.

Проверить насколько загружен сервер (процессор, диски, сеть). Выявить причину утечки ресурсов и устранить её. Большая загрузка сети может косвенно указывать на DoS. Если вы обнаружили высокую нагрузку сервера, но она оказалась полезной — то это указывает на нехватку мощности сервера и пора задуматься об его обновлении или замене.
©Bobrovsky Dmitry
2) Проблемы сети.
©Bobrovsky Dmitry
Если есть возможность проверить сетевой канал от клиента до сервера с помощью специализированых аппаратных или программных средств. Если нет, то можно запуститесь ping на некоторое время и посмотреть, какое время отклика и нет ли потерь пакетов.

3) База данных и Listener не функционируют.

Проверить что сама база данных и Listener запущены и работают нормально, что к ним можно подключиться локально или с других компьютеров сети.
Dmitry Bobrovsky
4) Проблемы из-за антивируса или firewall.
Dmitry Bobrovsky
Отключить или даже полностью деинсталировать антивирус или firewall.

5) Проблемы с DNS.

Либо прописать соответствующие записи в файл host либо во всех конфигурационных файлах oracle net использовать вместо имен — ip-адреса.
Запись fatal ni connect error 12170 ns main err code 12535 впервые появилась Dmitry Bobrovsky Blog

Источник

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 Oracle12cBaseappproduct12.1.0dbhome_1NETWORKADMINtnsnames.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 6879763 shared pool simulator bug fixed by patch for unpublished Bug 6966286 see Note 563149.1

Unpublished Bug 7039896 workaround parameter _enable_shared_pool_durations=false see Note 7039896.8

Other approaches to avoid the problem

Источник

Fatal NI connect error 12170.

  VERSION INFORMATION:

        TNS for Linux: Version 11.2.0.4.0 — Production

        Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.4.0 — Production

        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 — Production

  Time: 08-MAY-2017 10:24:32

  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=10.176.172.44)(PORT=55353))

[[email protected] admin]$ oerr ora 12170

12170, 00000, «TNS:Connect timeout occurred»

// *Cause:  The server shut down 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; or this may

// indicate that a malicious client is trying to cause a Denial of Service

// attack on the server.

// *Action: If the error occurred because of a slow network or system,

// reconfigure one or all of the parameters SQLNET.INBOUND_CONNECT_TIMEOUT,

// SQLNET.SEND_TIMEOUT, SQLNET.RECV_TIMEOUT in sqlnet.ora to larger values.

// If a malicious client is suspected, use the address in sqlnet.log to

// identify the source and restrict access. Note that logged addresses may

// not be reliable as they can be forged (e.g. in TCP/IP).

[[email protected] admin]$

Решение:

listener.ora

INBOUND_CONNECT_TIMEOUT_LISTENER = 0

DIAG_ADR_ENABLED_LISTENER = OFF

sqlnet.ora

DIAG_ADR_ENABLED = OFF

SQLNET.INBOUND_CONNECT_TIMEOUT =0

После этого повторноreloadКонфигурация прослушивателя или перезапустите прослушиватель.

Регулярно проверяйте базу данныхalert logИнформация — это наш важный метод работы при ежедневном обслуживании, проверке и устранении неисправностей базы данных. Системы баз данных, «работающие с болезнью» и «оперирующие с травмами», часто являются главными убийцами «небольших болезней, ведущих к смерти». Так называемые «меры предосторожности до того, как они произойдут», требуют, чтобы администраторы баз данных начинали с повседневных тривиальных вопросов, всегда понимали работу системы и решали их как можно скорее.

Эта статья в основном знакомит с тем, как автор используетOracle 11gR2Проблемы, которые возникли во время проверки журнала во время процесса, хотя они не были решены удовлетворительно. Запишите его и оставьте, чтобы друзья могли проверить.

1, Описание проблемы

Автор использует набор сред разработки, версия базы данных11gR2, Дополнительный номер версии:11.2.0.4。

SQL> select * from v$version;

BANNER

———————————————————————————

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 — 64bit Production

PL/SQL Release 11.2.0.4.0 — Production

CORE    11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 — Production

NLSRTL Version 11.2.0.4.0 – Production

Осмотрите базу данныхalert logВ процессе были обнаружены сообщения об ошибках.

Tue May 19 23:04:55 2015

*************************************

Fatal NI connect error 12170.

  VERSION INFORMATION:

        TNS for Linux: Version 11.2.0.4.0 — Production

        Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.4.0 — Production

        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 — Production

  Time: 19-MAY-2015 23:04:55

  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=172.xx.xx.xx)(PORT=50741))

Один и тот же тип ошибки неоднократно появляется в журнале, и частота появления10Вокруг разница каждый раз бываетHostсоответствоватьIPАдрес другой.

2, анализ проблемы

Этот тип ошибки11gR2Часто появляется в версии. Эта проблема часто возникала в некоторых производственных системах до автора. Текущая разрабатываемая архитектура системы является относительно традиционной и типичной.CSАрхитектурный подход. Клиентское настольное приложение — это многофункциональное клиентское программное обеспечение, и вся бизнес-логика находится на клиенте. Клиент подключается напрямую к базе данных.

Этот архитектурный подход является более традиционным, и в отрасли уже много лет обсуждают недостатки этого подхода. Только с точки зрения базы данных такой архитектурный подход означает больше соединений с базой данных и более частые структуры доступа.

использоватьIPАдрес, мы можем войти от слушателяlistener.logВы можете найти этоIPКогда было подключено адресное соединение?

[[email protected] trace]$ pwd

/u01/app/diag/tnslsnr/localhost/listener/trace

[[email protected] trace]$ cat listener.log | grep 172. xx.xx.xx

19-MAY-2015 13:51:10 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=visvim))(SERVICE_NAME=sicsdb)) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.xx.xx.xx)(PORT=50741)) * establish * sicsdb * 0

ОтMOSСогласно отзывам об информации, этот тип сообщения об ошибке является нормальным.OracleРабочий механизм. Когда клиентский процессClient ProcessС серверным процессомServer ProcessПосле того, как соединение установлено, эти двое сформировали отношения «жизнь и смерть» (собственный режим соединения). Если клиент не инициирует прерывание илиServer ProcessБыть ненормальнымkill。

В реальной операционной среде это идеальное состояние часто нарушается. в случаеClient ProcessПросто сохраните соединение, не выполняйте инструкцию, сеанс вidleстатус. Это соединение легко прерывается устройствами сетевого уровня, такими как брандмауэры.

вOracle11gR2Если нет подключения в течение длительного времениServer ProcessОтрезанный внешней силой,OracleАвтоматически запишет информацию в виде сообщения об ошибке наalert logКак напоминание. в11R1Версия, в которую будет записана эта информацияsqlnet.logв.

3, Меры по решению проблем

индукцияMOSИ различные методы в сети, есть примерно две стратегии, соответственно, использующиеDCDИ отключитьADR。

DCDПолное имяDead Connection Detection, Проверка на основе активного обнаруженияOracleЗомби-клиентский процессClient ProcessСтратегия. КонфигурацияDCDКлюч в том, чтобы установитьsqlnet.expire_timeПараметры вSQL NetВ рамках системыOracleОтдаст всеClient ProcessОтправьте пакеты сетевой связи для определенияClientСтоит ли выжить.

Именно благодаря этой пакетной связи брандмауэр может думать, что сетевое соединение все еще на месте.activeСтатус, действие по принудительному отключению выполняться не будет. Есть похожие механизмыLinuxВверхtcp keep liveМеханизм также использует аналогичную стратегию для проверки.

[[email protected] trace]$ cd /u01/app/oracle/network/admin/

[[email protected] admin]$ ls -l

total 16

-rw-r—r—. 1 oracle oinstall  343 Sep  2  2014 listener.ora

drwxr-xr-x. 2 oracle oinstall 4096 Jun 16  2014 samples

-rw-r—r—. 1 oracle oinstall  381 Dec 17  2012 shrept.lst

-rw-r—r—. 1 oracle oinstall    0 Sep  2  2014 sqlnet.ora

-rw-r——. 1 oracle oinstall  308 Sep  5  2014 tnsnames.ora

[[email protected] admin]$ cat sqlnet.ora

[[email protected] admin]$ cat sqlnet.ora

sqlnet.expire_time=10

Другой способ тожеOracleРекомендуется закрыть11gизADRмеханизм.ADR（Automatic Diagnostic Repository)ДаOracleКомпоненты инструмента для автоматической диагностики и автоматических напоминаний.OracleПодумайте, что если пользователю не нужноSQL NetПрименение в компонентахADR, Вы можетеsqlnet.oraКонфигурация закрыта.

[[email protected] admin]$ cat sqlnet.ora

sqlnet.expire_time=10

DIAG_ADR_ENABLED = OFF

DIAG_ADR_ENABLED_LISTENER=OFF

После этого повторноreloadНастройте прослушиватель или перезапустите его.

[[email protected] admin]$ lsnrctl reload

LSNRCTL for Linux: Version 11.2.0.4.0 — Production on 21-MAY-2015 10:13:34

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

4,в заключение

база данных»Fatal NI connect error 12170«Проблема в основном вызвана методом взаимодействия с базой данных с длинным подключением, который не следует рассматривать как проблему с ошибкой в ​​строгом смысле слова. Если это какое-то системное приложение с трехуровневой архитектурой, вы можете рассмотреть возможность использования пула соединений для динамического распределения ресурсов для решения проблемы. легкость.

Блог Deep Blue:http://blog.csdn.net/huangyanlong/article/details/46372849

Проверьте журнал аварийных сигналов базы данных и найдите ошибку:Fatal NI connect error 12170Сообщить об ошибке

Журнал тревог выглядит следующим образом:

***********************************************************************

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: 26-OCT-2014 06:05:44

  Tracing not turned on.

  Tns error struct:

    ns main err code: 12535

  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: 26-OCT-2014 06:05:44

  Tracing not turned on.

  Tns error struct:

    ns main err code: 12535

TNS-12535: TNS:operation timed out

    ns secondary err code: 12606

    nt main err code: 0

    nt secondary err code: 0

    nt OS err code: 0

  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.53.105.20)(PORT=19164))

  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: 26-OCT-2014 06:05:44

  Tracing not turned on.

  Tns error struct:

    ns main err code: 12535

TNS-12535: TNS:operation timed out

    ns secondary err code: 12606

    nt main err code: 0

    nt secondary err code: 0

    nt OS err code: 0

  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.53.105.20)(PORT=19166))

TNS-12535: TNS:operation timed out

    ns secondary err code: 12606

    nt main err code: 0

    nt secondary err code: 0

    nt OS err code: 0

  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.53.105.20)(PORT=19165))

Sun Oct 26 06:05:50 2014

Successfully onlined Undo Tablespace 2.

Verifying file header compatibility for 11g tablespace encryption..

Verifying 11g file header compatibility for tablespace encryption completed

***********************************************************************

[Решение]

[[email protected] ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 — Production on 02-MAR-2015 12:34:22

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

————————

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.1.0 — Production

Start Date                02-MAR-2015 09:45:49

Uptime                    0 days 2 hr. 48 min. 33 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/11.2.0/grid/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/node1/listener/alert/log.xml

Listening Endpoints Summary…

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.53.105.20)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.53.105.24)(PORT=1521)))

Services Summary…

Service «+ASM» has 1 instance(s).

  Instance «+ASM1», status READY, has 1 handler(s) for this service…

Service «xcky» has 1 instance(s).

  Instance «xcky1», status READY, has 1 handler(s) for this service…

Service «xckyXDB» has 1 instance(s).

  Instance «xcky1», status READY, has 1 handler(s) for this service…

The command completed successfully

[[email protected] ~]$ cd /u01/11.2.0/grid/network/admin/

[[email protected] admin]$ ls

endpoints_listener.ora     listener.ora            samples     sqlnet.ora

listener1410255PM1539.bak  listener.ora.bak.node1  shrept.lst

[[email protected] admin]# chmod 775 listener.ora

[[email protected] admin]# chmod 775 sqlnet.ora

—подтвердитьoracleДоступ пользователей к файлам конфигурации

[[email protected] admin]$ vi listener.ora

—Добавить параметрыINBOUND_CONNECT_TIMEOUT_LISTENER = 0

—Добавить параметрыDIAG_ADR_ENABLED_LISTENER = OFF

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent

LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

INBOUND_CONNECT_TIMEOUT_LISTENER = 0

DIAG_ADR_ENABLED_LISTENER = OFF

~

~

[[email protected] admin]$ vi sqlnet.ora

—Добавить параметрыDIAG_ADR_ENABLED = OFF

—Добавить параметрыSQLNET.INBOUND_CONNECT_TIMEOUT =0

# sqlnet.ora.node1 Network Configuration File: /u01/11.2.0/grid/network/admin/sqlnet.ora.node1

# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /u01/app/oracle

DIAG_ADR_ENABLED = OFF

SQLNET.INBOUND_CONNECT_TIMEOUT =0

~

~

[Официальный документ]

Взгляните на описание в официальном документе, а именно:

ORA-12170: TNS:Connect timeout occurred

Cause: The client failed to establish a connection and complete authentication in the time specified by the SQLNET.INBOUND_CONNECT_TIMEOUT parameter in the sqlnet.ora file. This error may be a result of network or system delays, or it may indicate that a malicious client is trying to cause a denial-of-service attack on the database server.

See Also:

«Configuring the Listener and the Oracle Database To Limit Resource Consumption By Unauthorized Users» further information about setting the SQLNET.INBOUND_CONNECT_TIMEOUT parameter

Action: If the error occurred due to system or network delays that are normal for the particular environment, then perform these steps:

Turn on tracing to determine where clients are timing out.

See Also:

«Tracing Error Information for Oracle Net Services»

Reconfigure the SQLNET.INBOUND_CONNECT_TIMEOUT parameter in sqlnet.ora to a larger value.

If you suspect a malicious client, then perform these steps:

Locate the IP address of the client in the sqlnet.log file on the database server to identify the source.

For example, the following sqlnet.log excerpt shows a client IP address of 10.10.150.35.

Fatal NI connect error 12170.

  VERSION INFORMATION:

        TNS for Solaris: Version 10.1.0.2.0

        Oracle Bequeath NT Protocol Adapter for Solaris: Version 10.1.0.2.0

        TCP/IP NT Protocol Adapter for Solaris: Version 10.1.0.2.0

  Time: 03-JUL-2002 13:51:12

  Tracing to file: /ora/trace/svr_13279.trc

  Tns error struct:

    nr err code: 0

    ns main err code: 12637

    TNS-12637: Packet receive failed

    ns secondary err code: 12604

    nt main err code: 0

    nt secondary err code: 0

    nt OS err code: 0

  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.10.150.35)(PORT=52996))

Beware that an IP address can be forged.

If the time out occurs before the IP address can be retrieved by the database server, then enable listener tracing to determine the client that made the request.

See Also:

Tracing Error Information for Oracle Net Services

Restrict access to the client. For example, you can configure parameters for access rights in the sqlnet.ora file.

See Also:

«Configuring Database Access Control»

[Объединение процессов]

выше:10GЧто касается содержания официального документа, вы можете ознакомиться с официальным документом, в котором даются предложения:Reconfigure the SQLNET.INBOUND_CONNECT_TIMEOUT parameter in sqlnet.ora to a larger value.

Затем поищите официальные документы дляSQLNET.INBOUND_CONNECT_TIMEOUTОписание, как показано ниже:

Как и выше, эта проблема возникает из-за того, что соединение с базой данных не удалось успешно завершить, и результат соединения задерживается. В официальных документах нет четкого способа справиться с этим, только некоторые предложения по настройке.

Впоследствии я поискал в Интернете аналогичные методы обработки ошибок (есть информация, что эта ошибка может бытьMOSНайдено в документе) в сочетании с официальным документом у меня есть предварительное пониманиеORA-12170Ошибка из-заAutomatic Diagnostic Repositoryсредний Oracle Net diagnosticЭто открытое состояние, так что ошибка задержки подключения получается и записывается в журнал аварийных сигналов.

опубликовано пользователями сетиMOSСсылка на документ, как показано ниже:

To revert to Oracle Net Server tracing/logging, set following parameter in the server’s sqlnet.ora :

DIAG_ADR_ENABLED = OFF

Also, to back out the ADR diag for the Listener component, set following parameter in the server’s listener.ora:

DIAG_ADR_ENABLED_<listenername> = OFF

   — Where the <listenername> would be replaced with the actual name of the configured listener(s) in the listener.ora configuration file.  For example, if the listener name is ‘LISTENER’, the parameter would read:

DIAG_ADR_ENABLED_LISTENER = OFF

-Reload or restart the TNS Listener for the parameter change to take effect.

опубликовано пользователями сетиMetalinkПриведенное выше решение

1. set INBOUND_CONNECT_TIMEOUT_=0 in listener.ora

2. set SQLNET.INBOUND_CONNECT_TIMEOUT = 0 in sqlnet.ora of server.

3. stop and start both listener and database.

4. Now try to connect to DB and observe the behaviour

Неустранимая ошибка NI Connect 12170, «TNS-12535: TNS: превышено время ожидания операции». Сообщается в журнале предупреждений 11g (идентификатор документа 1286376.1)

In this Document

APPLIES TO:

Oracle Net Services — Version 11.1.0.6 to 12.1.0.2 [Release 11.1 to 12.1]

Information in this document applies to any platform.

TNS-12170, ORA-12170, TNS-12535, TNS-00505 alert.log

SYMPTOMS

CHANGES

CAUSE

SOLUTION

See the following for more information and a potential solution where a firewall may be causing this issue:   Note:1628949.1   Alert Log Errors: 12170 TNS-12535/TNS-00505: Operation Timed Out

REFERENCES

NOTE:151972.1 — Dead Connection Detection (DCD) Explained

NOTE:454927.1 — Using and Disabling the Automatic Diagnostic Repository (ADR) with Oracle Net for 11g

NOTE:1628949.1 — Alert Log Errors: 12170 TNS-12535/TNS-00505: Operation Timed Out

Ошибки журнала предупреждений: 12170 TNS-12535 / TNS-00505: время ожидания операции истекло (идентификатор документа 1628949.1)

***Checked for relevance on 08-APR-2016***

                        DEAD CONNECTION DETECTION 
                        =========================

OVERVIEW 
-------- 
 
Dead Connection Detection (DCD) is a feature of SQL*Net 2.1 and later, including
Oracle Net8 and Oracle NET. DCD detects when a partner in a SQL*Net V2 client/server
or server/server connection has terminated unexpectedly, and flags the dead session
so PMON can release the resources associated with it.
 
DCD is intended primarily for environments in which clients power down their 
systems without disconnecting from their Oracle sessions, a problem
characteristic of networks with PC clients.

DCD is initiated on the server when a connection is established. At this 
time SQL*Net reads the SQL*Net parameter files and sets a timer to generate an 
alarm.  The timer interval is set by providing a non-zero value in minutes for 
the SQLNET.EXPIRE_TIME parameter in the sqlnet.ora file.

When the timer expires, SQL*Net on the server sends a "probe" packet to the 
client. (In the case of a database link, the destination of the link
constitutes the server side of the connection.)  The probe is essentially an 
empty SQL*Net packet and does not represent any form of SQL*Net level data, 
but it creates data traffic on the underlying protocol. 
 
If the client end of the connection is still active, the probe is discarded, 
and the timer mechanism is reset.  If the client has terminated abnormally, 
the server will receive an error from the send call issued for the probe, and 
SQL*Net on the server will signal the operating system to release the 
connection's resources. 
 
On Unix servers, the sqlnet.ora file must be in either $TNS_ADMIN or 
$ORACLE_HOME/network/admin. Neither /etc nor /var/opt/oracle alone is valid. 
 
It should be also be noted that in SQL*Net 2.1.x, an active orphan process 
(one processing a query, for example) will not be killed until the query 
completes. In SQL*Net 2.2, orphaned resources will be released regardless of 
activity.

This is a server feature only.  The client may be running any supported 
SQL*Net V2 release.
 
 
THE FUNCTION OF THE PROTOCOL STACK 
---------------------------------- 
 
While Dead Connection Detection is set at the SQL*Net level, it relies heavily
on the underlying protocol stack for it's successful execution. For example,
you might set SQLNET.EXPIRE_TIME=1 in the sqlnet.ora file, but it is unlikely
that an orphaned server process will be cleaned up immediately upon expiration
of that interval. 
 
TCP/IP, for example, is a connection-oriented protocol, and as such, the 
protocol will implement some level of packet timeout and retransmission in an 
effort to guarantee the safe and sequenced order of data packets. If a timely 
acknowledgement is not received in response to the probe packet, the TCP/IP 
stack will retransmit the packet some number of times before timing out. After
TCP/IP gives up, then SQL*Net receives notification that the probe failed.
 
The time that it takes TCP/IP to timeout is dependent on the TCP/IP stack, and
timeouts of many minutes are entirely common.  This has been an area of concern
for many customers, as many retransmissions at the protocol layer causes what
could be a significant lag between the expiration of the DCD interval and the
time when the orphaned process is actually killed. 
 
The easiest way to determine if the protocol stack is causing such a delay 
involves testing different DCD intervals.


TESTING THE PROTOCOL STACK 
--------------------------
Set the SQLNET.EXPIRE_TIME parameter to 1 minute and note the time required to
clean up an orphaned server process.  Then set SQLNET.EXPIRE_TIME to 5 minutes
and again observe the time required to clean up the shadow. If the TCP/IP
timeout is the reason the server resources do not get released, the time to
clean up the shadow should increase by about 4 minutes.

If the TCP/IP retransmission timeout is indeed the problem, the Operating 
System kernel can be tuned to reduce the interval for and number of packet 
retransmissions (on many Unix platforms, the file 
/usr/include/netinet/tcp_timer.h contains the configuration parameters). 
 
Reducing the interval and number of retransmissions may impact other system 
components, since in effect you are shrinking the window allowed for
connections to process data, possibly resulting in inadvertent loss of 
connections during periods of heavy system load.  Slower connections from
remote sites may be impacted by this change.
 
Kernel parameters that may affect retransmission include but are not limited 
to TCP_TTL, TCPTV_PERSMIN, TCPTV_MAX, and TCP_LINGERTIME. 
 
*** To avoid disrupting other system processes, it is important to contact the 
appropriate vendor for assistance in tuning the operating system kernel or 
protocol stack. *** 
 
 
MONITORING DEAD CONNECTION DETECTION 
------------------------------------ 
The best way to determine if DCD is enabled and functioning properly is to 
generate a server trace and search the file for the DCD probe packet. To 
generate a server trace, set TRACE_LEVEL_SERVER=16 and 
TRACE_DIRECTORY_SERVER=<path> in sqlnet.ora on the server (note the location
of the sqlnet.ora file).  The resulting trace file will have a filename of
svr_<PID>.trc and will be located in the specified directory. 
 

Is DCD Enabled? 
--------------- 
For pre-Oracle8i versions, enable level 16 SQL*Net server tracing and search
the resultant server trace file for an entry like the following: 

  osntns: Enabling dead connection detection (1 min) 
 
The timer interval listed should match the value of SQLNET.EXPIRE_TIME.

For Oracle8i onwards, you should see the following:

  nstimini: entry 
  nstimig: entry 
  nstimig: normal exit 
  nstimini: initializing NLTM in asynchronous mode 
  nstimini: normal exit 
  nstimstart: entry

 
Is DCD Working? 
---------------
Search the server trace file for DCD probe packets. They will appear in the
form of empty data packets, as follows: 
 
  nstimexp: entry 
  nstimexp: timer expired at 05-OCT-95 12:15:05 
  nsdo: entry 
  nsdo: cid=0, opcode=67, *bl=0, *what=1, uflgs=0x2, cflgs=0x3 
  nsdo: nsctx: state=8, flg=0x621c, mvd=0 
  nsdo: gtn=93, gtc=93, ptn=10, ptc=2048 
  nsdoacts: entry 
  nsdofls: entry 
  nsdofls: DATA flags: 0x0 
  nsdofls: sending NSPTDA packet 
  nspsend: entry 
  nspsend: plen=10, type=6 
  nttwr: entry 
  nttwr: socket 4 had bytes written=10 
  nttwr: exit 
  nspsend: 10 bytes to transport 
  nspsend:packet dump 
  nspsend:00 0A 00 00 06 00 00 00  |........| 
  nspsend:00 00 00 00 00 00 00 00  |........| 
  nspsend: normal exit 
  nsdofls: exit (0) 
  nsdoacts: flushing transport 
  nttctl: entry 
  nsdoacts: normal exit 
  nsdo: normal exit 
  nstimexp: normal exit

The entry:

  nspsend:00 0A 00 00 06 00 00 00  |........| 
  nspsend:00 00 00 00 00 00 00 00  |........| 
 
represents the probe packet.  Note that DCD packets are 10 bytes long when they
are issued to the protocol stack. Once the protocol header and trailer bytes
for the underlying protocols have been added, the packet could be approximately
70 bytes long.
 
If DCD is enabled, you will see these probe packets written to the trace file
when the timer expires.  If the server is a UNIX system, it might be useful to
establish a connection and tail the trace file: 

  tail -f svr_<PID>.trc 

The time elapsed after each probe packet is written to the server trace should 
match the SQLNET.EXPIRE_TIME value.

Note: from version 9.2.0.4.0 onwards, DCD probe packets are no longer traced in
SQL*Net trace files, however DCD packets can be observed using other forms of
tracing, such as network sniffer tracing.


KNOWN PROBLEMS OR LIMITATIONS 
----------------------------- 
- Of the few reported problems, perhaps the most significant is DCD's poor 
performance on Windows NT.  Dead connections are cleaned up only when the 
server is rebooted and the database is restarted.  Exactly how well DCD works 
on NT depends on the client's protocol implementation. SQL*Net v2.3 has 
improved the performance over earlier releases. 
 
  This has been logged as port-specific Bug#303578. 

 
- On SCO Unix, a problem was reported in which server processes spin, consuming
large amounts of CPU, once the DCD timer expires. The problem is due to improper
signal handling and can be eliminated by disabling DCD.
 
  This is port-specific Bug#293264

- Orphaned resources are not released if only the client application is 
terminated. Only after the client PC has been rebooted does DCD release these 
resources. For example, if a Windows application is killed yet Windows remains
running, the probe packet may be received and discarded as if the connection is
still active.  As it currently stands, it appears that DCD detects dead client
machines, but not dead client processes.
 
  This is logged as generic Bug#280848. 
 
- The SQL*Net V2 implementation on MVS does not use the generic DCD mechanism,
and therefore the SQLNET.EXPIRE_TIME parameter does not apply. The KEEPALIVE
function of IBM's TCP/IP is used instead. This was implemented prior to
development of DCD. 
 
  This is documented in port-specific Bug#301318. 
 
- DCD relies heavily on issuing probe packets during any phase of the connection.
This is not be possible with some protocols which run half-duplex. Hence, DCD is
not enabled on protocols like APPC/LU6.2. 
 
  This is not a bug, but is rather the intended design. 
 
- Local connections using BEQ protocol adapters are not supported with DCD.  
Local connections using the IPC protocol adapters are supported with DCD.

-BUG#1388806 : On Windows NT, DCD FAILS AFTER 16 CONNECTIONS

 
A FINAL NOTE...
--------------
On most OS'es (even more recent versions of Windows) if a process exits 
abnormally or is killed by an administrator, the OS will still gracefully 
clean up resources associated with that process including the network
connection(s).  It will tell the server on the other end that it is closing 
the network connection. DCD is still useful for times when there are problems 
with the physical network (e.g. ethernet cable falls off the machine) or if
the OS kernel panics and crashes (e.g. blue screen of death) before it can
close the network connections.  It may have another side benefit with certain
load balancing hardware, that may prematurely abort connections it thinks have
been idle too long, by sending a dummy packet to the client periodically.
 
Under no circumstances should you rely 100% on Dead Connection Detection.  
It was developed to handle clients that have abnormally exited. Clients should
always exit their applications gracefully. It is the responsibility of the
application developer to make this possible. DCD is intended only to clean up
after abnormal events.
 
DCD is much more resource-intensive than similar mechanisms at the protocol 
level, so if you depend on DCD to clean up all dead processes, that will put 
an undue load on the server. 
 
Clearly it is advantageous to exit applications cleanly in the first place.


REFERENCES
---------- Note:395505.1 How to Check if Dead Connection Detection (DCD) is Enabled in 9i and 10g Note:438923.1 How To Track Dead Connection Detection(DCD) Mechanism Without Enabling Any Client/Server Network Tracing

Ora 12170 что за ошибка

Пользователи не могут подключиться к базе. Обычно при этом они получают ошибки: ORA-12547: TNS:lost contact или ORA-12637: Packet receive failed.

В sqlnet.log на сервере сообщения об ошибке ORA-12170: TNS:Connect timeout. Еще для версий 10g и выше, в alert.log могут быть сообщения WARNING: inbound connection timed out (ORA-3136).

VERSION INFORMATION:
TNS for 64-bit Windows: Version 10.2.0.4.0 — Production
Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 10.2.0.4.0 — Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 10.2.0.4.0 — Production
Time: 13-FEB-2013 13:47:12
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: 60
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.77.116.47)(PORT=2971))
***********************************************************************

Для разных ОС, параметр ‘nt secondary err code‘ может быть разным

For the Solaris system: nt secondary err code: 145:
ETIMEDOUT 145 /* Connection timed out */
For the Linux operating system: nt secondary err code: 110
ETIMEDOUT 110 Connection timed out
For the HP-UX system: nt secondary err code: 238:
ETIMEDOUT 238 /* Connection timed out */
For AIX: nt secondary err code: 78:
ETIMEDOUT 78 /* Connection timed out */
For Windows based platforms: nt secondary err code: 60 (which translates to Winsock Error: 10060)

Выдержка из документации

ORA-12170: TNS:Connect timeout occurred
Cause: The server shut down 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; or this may indicate that a malicious client is trying to cause a Denial of Service attack on the server.
Action: If the error occurred because of a slow network or system, reconfigure one or all of the parameters SQLNET.INBOUND_CONNECT_TIMEOUT, SQLNET.SEND_TIMEOUT, SQLNET.RECV_TIMEOUT in sqlnet.ora to larger values. If a malicious client is suspected, use the address in sqlnet.log to identify the source and restrict access. Note that logged addresses may not be reliable as they can be forged (e.g. in TCP/IP).

Смысл этой ошибки в том что соединение не может быть установлено в течение отведенного интервала времени. А вот причин по которым это происходит может быть великое множество.

Как видно, основная рекомендация — увеличить параметры SQLNET.INBOUND_CONNECT_TIMEOUT, SQLNET.SEND_TIMEOUT и SQLNET.RECV_TIMEOUT. Можно попробовать сделать это, но это может не помочь. Поэтому лучше попробовать разобраться в корне проблемы.

Несколько основных причин ошибки и способы их решения

1) Серверные ресурсы перегружены.

Проверить насколько загружен сервер (процессор, диски, сеть). Выявить причину утечки ресурсов и устранить её. Большая загрузка сети может косвенно указывать на DoS. Если вы обнаружили высокую нагрузку сервера, но она оказалась полезной — то это указывает на нехватку мощности сервера и пора задуматься об его обновлении или замене.
©Bobrovsky Dmitry
2) Проблемы сети.
©Bobrovsky Dmitry
Если есть возможность проверить сетевой канал от клиента до сервера с помощью специализированых аппаратных или программных средств. Если нет, то можно запуститесь ping на некоторое время и посмотреть, какое время отклика и нет ли потерь пакетов.

3) База данных и Listener не функционируют.

Проверить что сама база данных и Listener запущены и работают нормально, что к ним можно подключиться локально или с других компьютеров сети.
Dmitry Bobrovsky
4) Проблемы из-за антивируса или firewall.
Dmitry Bobrovsky
Отключить или даже полностью деинсталировать антивирус или firewall.

5) Проблемы с DNS.

Либо прописать соответствующие записи в файл host либо во всех конфигурационных файлах oracle net использовать вместо имен — ip-адреса.
Запись fatal ni connect error 12170 ns main err code 12535 впервые появилась Dmitry Bobrovsky Blog

Источник

ORA-12170/TNS-12535 Timeout Errors while attempting to Connect To Remote Database Using SQL*Plus or Any other tool. (Doc ID 1392646.1)

Last updated on AUGUST 02, 2021

Applies to:

Symptoms

SQLPLUS and TNSPING command fails with the TNS-12535 error:

Verified the issue by the client sqlnet.log and trace file Oracle Net Client trace file, ‘sqlnet_3640_1.trc’

1. Client sqlnet.log

Fatal NI connect error 12170.

VERSION INFORMATION:
TNS for 32-bit Windows: Version 10.2.0.1.0 — Production
Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 10.2.0.1.0 — Production
Time: 05-JAN-2012 12:56:25
Tracing to file: C:oracleproduct10.2.0client_1NETWORKtracesqlnet_3640_1.trc
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: 60
nt OS err code: 0
Client address:

2. Client sqlnet trace (sqlnet_3640_1.trc)

If try SQLPLUS and TNSPING from the database server using listener it works fine.

Changes

No changes, This is a new installation of 11.2.

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

Versions 11, 12 and Newer Databases: Fatal NI Connect Error 12170, ‘TNS-12535: TNS:operation timed out’ Reported in Alert Log (Doc ID 1286376.1)

Last updated on DECEMBER 28, 2021

Applies to:

Oracle Net Services — Version 11.1.0.6 to 12.2.0.1 [Release 11.1 to 12.2]
Oracle Database — Enterprise Edition — Version 19.12.0.0.0 to 19.12.0.0.0 [Release 19]
Information in this document applies to any platform.
TNS-12170, ORA-12170, TNS-12535, TNS-00505 alert.log

Symptoms

nt secondary err code: 110 В Monitoring of the 11g database Alert log(s) may show frequent timeout related messages such as:

— On Oracle Solaris:

Fatal NI connect error 12170.

VERSION INFORMATION:
TNS for Solaris: Version 11.2.0.1.0 — Production
Oracle Bequeath NT Protocol Adapter for Solaris: Version 11.2.0.1.0 — Production
TCP/IP NT Protocol Adapter for Solaris: Version 11.2.0.1.0 — Production
Time: 22-JAN-2011 21:48:23
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: 145
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=xx.yyy.zz.abc)(PORT=1092))

———
The «nt secondary err code» will be different based on the operating system.

Linux x86 or Linux x86-64: «nt secondary err code: 110»
HP-UX Server: «nt secondary err code: 238»
AIX: «nt secondary err code: 78»

Changes

No changes are necessary, but may have recently upgraded the database to 11g release 1 or higher, or installed a new Oracle11g database.

Note: Prior to 11gR1 these same ‘Fatal NI connect error 12170’ are written to the sqlnet.log

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

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

ORA-12170

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.
• Hardware or software firewall
• Poor quality of network
• Network card interface (NIC) failure
• Anti-Virus software
  • Detected suspicious packets and blocked them.
  • Was scanning the whole operating system.
• Intrusion Prevention System (IPS)
• Intrusion Detection System (IDS)
• Proxy Server

Solutions

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

The most common mistake is that you didn’t open the port 1521 on firewall. That caused ORA-12170. To open port on firewall of the database server, you may refer to these posts:

On Linux:

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 что за ошибка

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.

Источник