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

7

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

Ошибка «Неустранимая ошибка подключения NI 12170» в журнале предупреждений

Ошибка «Неустранимая ошибка подключения NI 12170» в журнале предупреждений

Fatal NI connect error 12170.

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))

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

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

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

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

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

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

SQL> select * from v$version;

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.

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]$ 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 Механизм также использует аналогичную стратегию для проверки.

-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

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

После этого повторно 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 "Проблема в основном вызвана методом взаимодействия с базой данных с длинным подключением, который не следует рассматривать как проблему с ошибкой в ​​строгом смысле слова. Если это какое-то системное приложение с трехуровневой архитектурой, вы можете рассмотреть возможность использования пула соединений для динамического распределения ресурсов для решения проблемы. легкость.

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

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

Fatal NI connect error 12170.

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 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))

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

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

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

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.

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

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

[[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)

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

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.

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.

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.

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:

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.

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

выше: 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 :

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

— 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:

-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

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=10.xxx.yy.117)(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

These time out related messages are mostly informational in nature. The messages indicate the specified client connection (identified by the ‘Client address:’ details) has experienced a time out. The ‘nt secondary err code’ identifies the underlying network transport, such as (TCP/IP) timeout limits after a client has abnormally terminated the database connection.

The ‘nt secondary err code’ translates to underlying network transport timeouts for the following Operating Systems:

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)

Description: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

The reason the messages are written to the alert log is related to the use of the new 11g Automatic Diagnostic Repository (ADR) feature being enabled by default. See (Doc ID 454927.1).

SOLUTION

— Search the corresponding text version of the listener log located on the database server for the corresponding client connection referenced by the Client address details referenced in the alert log message.

For the message incident below you would search the listener log for the ‘Client address’ string:

The search of the listener log should find the most recent connection before the time reference displayed in the alert log message, e.g. ’22-JAN-2011 21:48:23′.

-Corresponding listener log entry:

22-JAN-2011 21:20:12 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=AMN11264.us.oracle.com)(CID=(PROGRAM=D:\app\mcassady\product\11.2.0\dbhome_1\bin\sqlplus.exe)(HOST=mcassady-lap)(USER=mca
ssady))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.xxx.yy.117)(PORT=1092)) * establish * AMN11264.us.oracle.com * 0

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=10.xxx.yy.117)(PORT=1092))

Note the time of the client corresponding client connection(s) in the listener log. Here you may find a particular client, set of clients or particular applications that are improperly disconnecting causing the timeout errors to be raised and recorder in the database alert log.

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

You may choose to revert from the new Automatic Diagnostic Repository (ADR) method to prevent the Oracle Net diagnostic details from being written to the alert log(s) by setting the following Oracle Net configuration parameters:

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

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

— 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:

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

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)

In this Document

APPLIES TO:

SYMPTOMS

The following error is reported in the database alert log.

***Note the "Client address" is posted within the error stack in this case.

Fatal NI connect error 12170.

VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.2.0.3.0 — Production
Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.2.0.3.0 — Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.2.0.3.0 — Production
Time: 22-FEB-2014 12:45:09
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=121.23.142.141)(PORT=45679))

CHANGES

No changes are necessary, but may have recently upgraded the database to 11g release 1 or higher, or installed a new Oracle11g database and they are now visible in the alert log.
Note: Prior to 11gR1 these same ‘Fatal NI connect error 12170’ are written to the sqlnet.log. This document describes a problem that arises when a firewall exists between the client and the database server.

CAUSE

We can search the listener log covering the same time period using this search criteria.

(HOST=121.23.142.141)(PORT=45679)

The 11g listener log in text format is located here:

Again, this is the client’s IP address and the unique ephemeral port assigned to the client for this connection.

In this case, we find that this connection was established at the listener at this timestamp:

22-FEB-2014 10:42:10 * (CONNECT_DATA=(SID=test)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=121.23.142.141)(PORT=45679)) * establish * test* 0 .

Compare this to the event in the alert.log with special attention to the timestamp.
The connection was dropped by the instance at 22-FEB-2014 12:45:09 or roughly 2 hours later.

Time: 22-FEB-2014 12:45:09
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=121.23.142.141)(PORT=45679))

The ‘nt secondary err code’ identifies the underlying network transport, such as (TCP/IP) timeout limit. In the current case 60 identifies Windows underlying transport layer.

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 : "nt secondary err code: 238"
AIX: "nt secondary err code: 78"
Solaris: "nt secondary err code: 145"

The alert.log message indicates that a connection was terminated AFTER it was established to the instance. In this case, it was terminated 2 hours and 3 minutes after the listener handed the connection to the database.

This would indicate an issue with a firewall where a maximum idle time setting is in place.

The connection would not necessarily be "idle". This issue can arise during a long running query or when using JDBC Thin connection pooling. If there is no data ‘on the wire’ for lengthy

periods of time for any reason, the firewall might terminate the connection.

SOLUTION

The non-Oracle solution would be to remove or increase the firewall setting for maximum idle time. In cases where this is not feasible, Oracle offers the following suggestion:

The following parameter, set at the **RDBMS_HOME/network/admin/sqlnet.ora, can resolve this kind of problem. DCD or SQLNET.EXPIRE_TIME can mimic data transmission between the server and the client during long periods of idle time.

SQLNET.EXPIRE_TIME=n Where <n> is a non-zero value set in minutes.

Once this change is in place, there is NO need to restart the listener or the database. The change will be in place for all newly spawned server processes following the change.

Be aware that connections that were established prior to this setting would not be affected by this change. Therefore, you may continue to experience some timeouts until all remote connection are established with this setting in place.

See the following : Note 257650.1 Resolving Problems with Connection Idle Timeout With Firewall

**In an installation that includes GRID, this parameter should be set in the RDBMS_HOME/network/admin/sqlnet.ora file. This would be the default location for sqlnet.ora file parameters referenced by the instance.

Please consider your business requirement for allowing connections to remain or appear ‘idle’ before implementing these suggestions and note that this is a workaround which, on some occasions, may not overpass all the network timeouts.

REFERENCES

NOTE:257650.1 — Resolving Problems with Connection Idle Timeout With Firewall
NOTE:1286376.1 — Fatal NI Connect Error 12170, ‘TNS-12535: TNS:operation timed out’ Reported in 11g Alert Log

Объяснение обнаружения обрыва соединения (DCD) (идентификатор документа 151972.1)

Решение проблем с тайм-аутом простоя подключения с помощью брандмауэра (идентификатор документа 257650.1)

In this Document

APPLIES TO:

PURPOSE

This article describes about the connection idle time-out issues that occur while a firewall or load balancer is used to monitor and control TCP traffic between clients (such as application servers / iAS components) and Oracle Databases.

SCOPE

This article is primarily intended for Application/Database Administrators and Network Administrators who would like to understand and try to workaround connection "IDLE TIMEOUT" issues in a firewall or F5 load balancer environment.

DETAILS

An Overview

Firewalls (FW) has become common in today’s networking to protect the network environment. The firewall recognizes the TCP protocol and it records the client server socket end-points. Also, FW recognize the TCP connection closure, and then will release the resources allocated for recording the opening connection. For every end-point pairs , the firewall must also allocate some resources(may be small).

When the client or server closes the communication it sends TCP FIN type packet, this is a normal socket closure. However, it is not uncommon that the client server communication abruptly ending without closing the end points properly by sending FIN packet, for example, when the client or server crashed, power down or a network error which prevents sending the closure packet to the other end. In that cases, the firewall will not know that the end-points will no longer use the opened channel. As a passive intermediary, it had no way to determine if the endpoints are still active. As is it not possible to maintain resources forever, and also, it is a security threat keeping a port open for undefined time. So, firewall imposes a BLACKOUT on those connections that stay idle for a predefined amount of time.

Initially FW were designed to protect the application servers, network and then to protect client/server connection. With these in mind, a time-out in terms of hours (1 hour is the default for most FW) is reasonable. With the advent of more complex security schemes, FW are not only between client and server, but also between different application servers ( intranet, demilitarized zone (DMZ) , and such) and database servers. So, the horizon of 1 hour idle time for communication between servers maybe not be appropriate.

Idle connections can be expected from an application server. There is the case of J2EE using pooled JDBC connections. The pool usually returns the first available connection to the requester, so the first connections of the pool list are the most likely to be active. The last one, which are at the end of the list, are only used at peak loads, and most of the time it will be inactive.

Other cases are the connections established from a HTTP Server, either SQL connections from mod_plsql, or AJP connections from mod_oc4j.

Blackout

One of the inconvenience of theses blackout, is that they are passive. None of the endpoints will be notified that the communication was banned . Only when the client or server tries to contact its peer, it comes to know that the peer end is no more active and the communication has already been broken.

The worst of all scenarios are the so called "passive listeners" . They will never know. Because, passive listeners are those processes at an endpoint that are simply waiting for commands to arrive from the other end. A typical example of this are the backend database server processes, which are reading from the socket looking new SQL statements to execute , and after the request is answered, they return to their passive state. When a blackout occurs, they will stay forever in this reading state, unless some of the following techniques are applied.

Resolving problems with connection idle time-out

TCP KeepAlive

Ensure TCP KeepAlive is set appropriately for your environment. Refer to your OS documentation for details.

DCD for DataBase Servers

For database connections, one of the endpoints is a passive listener, either is a dedicated process or a dispatcher process. If the connection becomes blacked out , this backend will never know that client cannot send any more requests, and then will lock important resources as database sessions, locks , and at least , a file descriptor used for maintaining the socket.

A solution is to make this backend "not so" passive, using the DCD (dead connection detection) to figure out if the communication is still possible.

Simply, set in the $ORACLE_HOME/network/admin/sqlnet.ora, in the server side SQLNET.EXPIRE_TIME=10 (10 minutes, for example). With this parameter in place, after 10 minutes of inactivity, the server send a small 10 bytes probe packet to the client. If this packet is not acknowledge, the connection will be closed and the associated resources will be released.

There are two benefits with this DCD
1. If the SQLNET.EXPIRE_TIME is less than the FW connection idle time-out, then the firewall *may* consider this packet as activity, and the idle time-out (firewall blackout) will never happen until both the client and the server processes are alive.

2. If the SQLNET.EXPIRE_TIME (let’s say a little bit higher) than the FW idle limit, then , as soon as the blackout happens , the RDBMS will know and will close the connection.

The first case is recommended when the connection comes from another application server , and the second makes sense for client applications.

DCD works at the application level and also works on top of TCP/IP protocol. If you have set the SQLNET.EXPIRE_TIME=10 then do not expect that the connections will be flagged as dead exactly after 10 minutes of the blackout or network outage. Please seeNote:151972.1 "Dead Connection Detection (DCD) Explained" for details on DCD. The TCP timeout and TCP retransmission values also adds to this time.

AJP Connections

It is not a default behavior in 9.0.2, but if Patch 2862660 is installed, the connection between and OHS server process and the J2EE can be maintained for more than a single request. If the parameter Oc4jConnTimeout is set, the OHS will maintain the connection for at least that time. The problem is that the child process may became inactive before that time-out occurs, and then the connection will remain open. While the child process is inactive, the connection will be idle, and there is chance to be blackout by the FW.

If this happens, the first thing that the child will do is to close it when it becomes active. But at this time, the TCP socket closing cannot be completed, due the blackout. Although the http child process can simply ignore the closing failure and continue the creation of a new connection, the passive listener at the
j2ee side (the worker thread) will be hook without a chance for the resources to be released.

To solve this , the Patch 3151686 must be installed and the java-option
-Dajp.keepalive=true
must be enabled.

After this, the blackout detection will rely on the TCP KeepAlive provided by the operating system.

As DCD , this process consist in send probes -empty packages- when a socket had been inactive for a period of time. If there is no response, the socket will be closed, and then, even the passive listener, will receive and exception or signal to let him know that the no further communication is possible.

CONCLUSION

As the firewalls extend their functionality , and are now are placed in between application servers, some tuning and parameter adjusting must be made to overcome the default rules established for client/server communications. However, remember that a firewall idle timeout setting is simply the way this product functions and any changes to it should be made with agreement between all parties involved.

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

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

Пользователи не могут подключиться к базе. Обычно при этом они получают ошибки: 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

Fatal NI connect error 12170

I got ” Fatal NI connect error 12170 ” error in Oracle.

Details of error are as follows.

This error is related with the firewall exists between the client and the database server.

The fatal ni connect error 12170 is related with the ORA-12170 error, you can read the related post as follows.

To solve this error, reconfigure one or all of the parameters SQLNET.INBOUND_CONNECT_TIMEOUT, SQLNET.SEND_TIMEOUT, SQLNET.RECV_TIMEOUT in sqlnet.ora to larger values.

Add the following line to the sqlnet.ora file on the server.

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

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

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:

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