Код ошибки 3136

At times, some users complained about connection timeout, and you saw error messages ORA-3136 in sqlnet.log as such:

Fatal NI connect error 12170.
    ns main err code: 12535
    ns secondary err code: 12606
    nt OS err code: 0
VERSION INFORMATION:
...
TNS-12535 TNS:operation timed out
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=X.X.X.X)(PORT=XXX))
    nt secondary err code: 0
  Tracing not turned on.
WARNING: inbound connection timed out (ORA-3136)


If you got the ORA-3136 accompanied with TNS-12535 and ORA-12170. It’s possibly caused by two factors, one is network traffic, the other is database heavy loading.

Luckily, the sqlnet.log logged the client connection data containing IP address for you to trace back the network condition at that time. Then, you can compare network response time during peak windows and off-peak windows to judge whether the network condition was normal or not.

Solution to ORA-3136

Heavy loading of a database cannot be easily solved in one second, but we can mitigate the complaining by increase INBOUND_CONNECT_TIMEOUT_LISTENER, the inbound timeout limit (in seconds).

Here are the steps:

Add Parameter to listener.ora

The default value of INBOUND_CONNECT_TIMEOUT_LISTENER is 60 seconds, we can raise the value to 160 seconds.

Single-instance Databases

We should add the parameter at oracle-level.

$ echo "INBOUND_CONNECT_TIMEOUT_LISTENER = 160" >> $ORACLE_HOME/network/admin/listener.ora

RAC Database

We should add the parameter at grid-level on all nodes.

$ echo "INBOUND_CONNECT_TIMEOUT_LISTENER = 160" >> $GRID_HOME/network/admin/listener.ora

Add Parameter to sqlnet.ora

$ echo "SQLNET.INBOUND_CONNECT_TIMEOUT = 180" >> $ORACLE_HOME/network/admin/sqlnet.ora


The value in sqlnet.ora should be slightly larger than the value in listener.ora, because database authentication needs extra time to do.

Restart the listener

$ lsnrctl stop
$ lsnrctl start


RAC Database

$ srvctl stop listener
$ srvctl start listener


Validate the Result

Then, we can validate the new setting by using telnet, the telnet connection will test open port 1521 and be timed out after 160 seconds, which is 2 minutes and 40 seconds:

$ date; telnet 10.10.10.10 1521; date
Thu Oct 11 11:06:20 CST 2012
Trying 192.168.0.21...
Connected to primary01.example.com (10.10.10.10).
Escape character is '^]'.
Connection closed by foreign host.
Thu Oct 11 11:09:00 CST 2012

The IP address above can be replaced with yours.

Notice that, the higher value you set, the higher security risk you take, it may be taken as a possible leak to DOS (Denial of Service) attack.

20180626 2018-06-26 11:18 [серьезно] имя хоста: zjhz-bjpaasb3, имя экземпляра: bjpaasb3, ожидая события: библиотека блокировки кэша, среднее число на второе: 1257, инвентаризационные данные

День база данных SMS оповещения, вдруг смотрите есть так много библиотека блокировки кэша, а затем в базу данных, просмотреть базу данных журнала.

После нахождения журнала, то Baidu нашел общий обложку блога, проблема та же, что охватывает следующий общий блог.

Недавно новая онлайн-база данных Oracle10Gr2 продолжала после ошибки в файле б / у Alert (Alert.log):

В то же время в SQLNet.log записывается следующая ошибка:

Это ошибка, связанная с сетевым соединением, и на металлике приведено следующее решение:

1.set INBOUND_CONNECT_TIMEOUT_<listenername>=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

Здесь база данных и слушатель я думаю, что не нужна, и наша перезарядка — это лист.

После модификации он наблюдался некоторое время, в настоящее время это нормально.

О параметрах SQLNET.INBOUND_CONNECT_TIMEOUT, Oracle предложил изменить параметры, чтобы избежать отказа в обслуживании.

Цитировать абзац Oracle Documentation Описание следующим образом:

SQLNET.INBOUND_CONNECT_TIMEOUT Purpose
Use the SQLNET.INBOUND_CONNECT_TIMEOUT parameter to specify the time, in seconds, for a client to connect with the database server and provide the necessary authentication information.

If the client fails to establish a connection and complete authentication in the time specified, then the database server terminates the connection. In addition, the database server logs the IP address of the client and an ORA-12170: TNS:Connect timeout occurred error message to the sqlnet.log file. The client receives either an ORA-12547: TNS:lost contact or an ORA-12637: Packet receive failed error message.

Without this parameter, a client connection to the database server can stay open indefinitely without authentication. Connections without authentication can introduce possible denial-of-service attacks, whereby malicious clients attempt to flood database servers with connect requests that consume resources.

To protect both the database server and the listener, Oracle Corporation recommends setting this parameter in combination with the INBOUND_CONNECT_TIMEOUT_listener_name parameter in the listener.ora file. When specifying values for these parameters, consider the following recommendations:

Set both parameters to an initial low value. 
Set the value of the INBOUND_CONNECT_TIMEOUT_listener_name parameter to a lower value than the SQLNET.INBOUND_CONNECT_TIMEOUT parameter. 
For example, you can set INBOUND_CONNECT_TIMEOUT_listener_name to 2 seconds and INBOUND_CONNECT_TIMEOUT parameter to 3 seconds. If clients are unable to complete connections within the specified time due to system or network delays that are normal for the particular environment, then increment the time as needed.

See Also: 
Oracle9i Net Services Administrator’s Guide for information about configuring these parameters

Default
None

Example
SQLNET.INBOUND_CONNECT_TIMEOUT=3

Ниже вы восстановите эту ошибку в тестовой среде.

О SQLNET.INBOUND_CONNECT_TIMEOUT SQLNET.ORA параметр, который представляет ожидания для времени тайм-аута аутентификации пользователя в секундах, значение по умолчанию составляет 60 секунд, если тайм-аут аутентификации пользователя, журнал сервера alert.log дисплей сообщение об ошибке «ВНИМАНИЕ: входящее подключение истекло из (ORA-3136)», которые появляются sqlnet.log TNS-12535: TNS: операции истекло сообщение об ошибке.

Просмотреть значение inbound_connect_timeout

Просмотр настроек SQLNET.INBOUND_CONNECT_TIMEOUT, как правило, следующий $ ORACLE_HOME администратора, файл / сеть / вид sqlnet.ora параметр.

LSNRCTL> show

The following operations are available after show

An asterisk (*) denotes a modifier or extended command:

rawmode                              displaymode                          

rules                                trc_file                             

trc_directory                        trc_level                            

log_file                             log_directory                        

log_status                           current_listener                     

inbound_connect_timeout              startup_waittime                     

snmp_visible                         save_config_on_stop                  

dynamic_registration                 enable_global_dynamic_endpoint       

oracle_home                          pid                                  

connection_rate_limit                valid_node_checking_registration     

registration_invited_nodes           registration_excluded_nodes          

LSNRCTL> show inbound_connect_timeout

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

LISTENER parameter «inbound_connect_timeout» set to 60

The command completed successfully

Установите значение sqlnet.inbound_connect_timeout

Сначала мы устанавливаем SQLNET.INBOUND_CONNECT_TIMEOUT 30 секунд, этот параметр изменяется в силу немедленно, не делать какие-либо другие операции.

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

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

SQLNET.INBOUND_CONNECT_TIMEOUT=30

Теперь имитировать тайм-аут входа пользователя

[[email protected] ~]$ sqlplus /@ORADB

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 24 09:30:25 2018

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

ERROR:

ORA-01017: invalid username/password; logon denied

ENTER-имя пользователя: ……………………………………. 60 секунд

Наблюдается в журнале Oracle Backstage

[[email protected] trace]$ tail -f alert_oradb.log

Thu May 24 09:31:25 2018

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

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: 24-MAY-2018 09:31:25

  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=127.0.0.1)(PORT=17263))

WARNING: inbound connection timed out (ORA-3136)

Внутри вы увидите предупреждение о тревоге: ошибка входящего соединения (ORA-3136) (ORA-3136).

подводить итоги:

Oracle 11g Пароль Задержка Аутентификация — это новая функция, привлеченная на 11G

В Oracle 11G, чтобы улучшить безопасность, Oracle представляет новые функции «проверки задержки пароля». Роль этой особенности заключается в том, чтоЕсли пользователь вводит неверный попыток ввода пароля для входа в систему, так и с увеличением числа ошибок авторизации, аутентификации Войти перед каждым будет увеличиваться, для того, чтобы замедлить атаку может попытаться повторить базу паролей.

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

Если вы столкнулись с такого рода проблемы, вы можете закрыть эту функцию с помощью Event 28401, устранить этот тип воздействия, следующая команда устанавливает измененные настройки в файле параметров:

ALTER SYSTEM SET EVENT =

 ‘28401 TRACE NAME CONTEXT FOREVER, LEVEL 1’ SCOPE = SPFILE;

Такая проблема очень типичен AWR показал появился следующий отчет, первый в ТОП 5, вы можете увидеть значительное ожидание Библиотека Cache блокировки, следующие примеры из реальной ситуации 11.2.0.3.0 версии:

В таких случаях модель времени — модели времени будут отображаться следующие индикаторы, где соединения Управление вызовами Прошедшее время занимает основную БД Время, которое непосредственно обозначенное создания соединения с базой данных:

Такие проблемы в 11g Oracle, является общей и определяются на MOS можно найти запись: High «библиотеку блокировки кэша» Время ожидания из — за недопустимую попытку входа (1309738.1) В дополнении Oracle 11g открыл случай проверку пароля При обновлении с Oracle 10g, вам нужны специальные вещи, чтобы быть осторожным, и может контролировать эту функцию инициализации параметров sec_case_sensitive_logon.

Hello,

I am applying hourly differential backup to the backup server from production with the following command. This command makes the database on standby server into read only mode.

RESTORE DATABASE ARSYSTEM FROM DISK = ‘E:SQL backup from productionsql_full_backup’

WITH  MOVE ‘arsystem’ TO

‘d:ardataarsystem.mdf’ ,
MOVE ‘arsystem_log’ TO ‘D:ARLOGARsystem’ ,
STANDBY = ‘E:SQL backup from productionSQL daily diff back up’

Now I want to run a command which will put the database in write mode. I have created a job which would make the datbase Write mode. This job runs successfully sometimes and fails sometimes. I need to ensure that the job always succeeds. When it fails, how do I troubleshoot and what is the possible fix?

Thanks in advance.

The error message is

Cannot apply the backup on device ‘E:SQL backup from productionSQL daily diff back up’ to database ‘ARSYSTEM’. [SQLSTATE 42000] (Error 3136)  RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.

The steps for the job are as follows with the failing step highlighted in bold. 

copy /y «\172.31.9.12Remedy BackupbackupSQL backupsql_full_backup» «E:SQL backup from productionsql_full_backup»

copy /y «\172.31.9.12Remedy BackupbackupSQL backupSQL daily diff back up» «E:SQL backup from productionSQL daily diff back up»

xp_cmdshell ‘net stop «bmc remedy action request system server»‘

exec rp_kill_db_processes ‘ARSYSTEM’

RESTORE DATABASE ARSYSTEM

FROM DISK = ‘E:SQL backup from productionsql_full_backup’

WITH

MOVE ‘arsystem’ TO ‘d:ardataarsystem.mdf’ ,

MOVE ‘arsystem_log’ TO ‘D:ARLOGARsystem’ ,

NORECOVERY

Failing step

RESTORE DATABASE ARSYSTEM

FROM DISK = ‘E:SQL backup from productionSQL daily diff back up’

WITH

MOVE ‘arsystem’ TO ‘d:ardataarsystem.mdf’ ,

MOVE ‘arsystem_log’ TO ‘D:ARLOGARsystem’ ,

RECOVERY

xp_cmdshell ‘del /f «E:SQL backup from productionsql_full_backup»‘

xp_cmdshell ‘del /f «E:SQL backup from productionsql daily diff back up»‘

xp_cmdshell ‘net start «bmc remedy action request system server»‘

I have scheduled the following  hourly diffential restore job too which never fails.

 RESTORE DATABASE ARSYSTEM FROM DISK = ‘E:SQL backup from productionsql_full_backup’

WITH  MOVE ‘arsystem’ TO

‘d:ardataarsystem.mdf’ ,
MOVE ‘arsystem_log’ TO ‘D:ARLOGARsystem’ ,
STANDBY = ‘E:SQL backup from productionSQL daily diff back up’
EXEC MASTER..XP_CMDSHELL ‘del /f «E:SQL backup from productionSQL daily diff back up»‘

Few days back we get an issue Inbound connection timed out (ORA-3136) in alert log. To troubleshoot this we need to dig into sqlnet.ora file and the alert log. 

The following four points are very important to troubleshoot this issue: 

• Check the alert log file and check from where the connection comes.
• Check the listener is up & running.
• Ping the server,make sure tnsping is working.
• Increase inbound_connect_timeout 

Regarding this we need to checked the value of “inbound_connect_timeout”.

LSNRCTL> show inbound_connect_timeout

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

LISTENER parameter “inbound_connect_timeout” set to 120

The command completed successfully

Here we can see that time is set to 120 which is ok.

The following would be the most likely reasons for this error :

1.Server gets a connection request from a malicious client which is not supposed to connect to the database.  In this case the error thrown would be the expected and desirable behaviour. You can get the client address for which the error was thrown in the sqlnet.log file that is local to the database.

2.The server receives a valid client connection request but the client takes a long time to authenticate more than the defined timeout.

3.The DB server is heavily loaded due to which it cannot finish the client logon within the timeout specified.

By default, the SQLNET.INBOUND_CONNECT_TIMEOUT is set to 60 seconds.

You can change the setting by adding the parameters SQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_<listener name>  to the $ORACLE_HOME/network/admin/sqlnet.ora file on the database server.

Setting the above parameters to a value of 0 implies an infinite time out.

Alternatively, you can use the lsnrctl command and issue the following:

LSNRCTL> set inbound_connect_timeout=<value>

Before opting for the above parameter changes, confirm that any firewall activity or Network Address Transalation (NAT) that may be occuring beween the client and and the database are not the cause of latency which is exceeding the timeout threshold.

 To identify the listener name and ORACLE_HOME and sqlnet.ora file we can use the below command.

[oracle@racdbq1.dcb.ichotels.com] ps -eaf|grep tns

Posted by Pavan DBA on June 16, 2010

This entry was posted on June 16, 2010 at 2:00 PM and is filed under Networking with Oracle.
Tagged: handling ora-3136, inbound connection timeout, ora-3136. You can follow any responses to this entry through the RSS 2.0 feed.

You can leave a response, or trackback from your own site.

Доброе время суток, у меня нечто похожая проблема. На базе одного из магазинов при расчете товародвижения (руками), при сохранении результатов в базу вылетает диалог с ошибкой и ссылкой на лог файл во временной папочке следующего содержания:

В остальном все работает нормально, никаких глюков не замечено. Подскажите, в чем может быть дело? Архив с файлами из временной папочки, созданными в результате переноса и расчета товародвижения высылаю. СМ2000 1.27.2, Oracle 10.2.0.4.0.x64. Сервер супермага находится на отделльной машине x32 — c нее и делается расчет. Ранее все было нормально. На всякий слуйчай привожу последние строки alert’та старого, и специально полученного нового:

Старый:

И новый:

Troubleshooting Guide ORA-3136: WARNING Inbound Connection Timed Out

Troubleshooting guide for «ORA-3136  WARNING inbound connection timed out» seen in the alert log.

Troubleshooting Tips:

The «WARNING: inbound connection timed out (ORA-3136)» in the alert log indicates that the client was not able to complete  the  authentication process within the period of time specified by the parameter SQLNET.INBOUND_CONNECT_TIMEOUT.

You might also see the errors ORA-12170 or TNS-12535 in the sqlnet.log that is generated on the server.
Check $ORACLE_HOME/network/log for this file.  This entry should contain client address from which the timeout originated and may be helpful in determining how to troubleshoot the issue.  Some applications or JDBC thin driver applications may not have these details.  The sqlnet.log file is not generated  by default in 11g and newer.

From 10.2.0.1 onwards the default setting for the parameter SQLNET.INBOUND_CONNECT_TIMEOUT is 60 seconds.  If the client is not able to authenticate within 60 seconds, the warning would appear in the alert log and the client connection will be terminated.

Note: This timeout restriction was introduced to combat Denial of Service (DoS) attack whereby malicious clients attempt to flood database servers with connect requests that consumes resources.

The following are the most likely reasons for this error —

-Server gets a connection request from a malicious client which is not supposed to connect to the database.  In this case the error thrown would be the expected and desirable behavior. You can get the client address for which the error was thrown in the sqlnet.log file that is local to the database.

-The server receives a valid client connection request but the client takes a long time to authenticate more than the default 60 seconds.

-The DB server is heavily loaded due to which it cannot finish the client logon within the timeout specified.

To understand what is causing this issue, following checks can be done:

The default value of 60 seconds is good enough in most conditions for the database server to authenticate a client connection. If it is taking longer, then it’s worth checking the following items before implementing the workaround:

1.Check whether local connection on the database server is successful & quick.
2.If local connections are quick ,then check for underlying network delay with the help of your network administrator.
3.Check whether your Database performance has degraded in anyway.
4.Check alert log for any critical errors for eg, ORA-600 or ORA-7445 and get them  resolved first.
These critical errors might have triggered the slowness of the database server.

It is often necessary to increase the values for INBOUND CONNECT TIMEOUT at  both the listener and the database in order to resolve this issue.    It is usually advisable to set the database (sqlnet.ora) value slightly higher than the listener (listener.ora).    The authentication process is more demanding for the database than the listener.

To set these parameters to use values higher than the default of 60 seconds, follow these instructions and restart the listener.There is no need to restart Oracle:

Edit the server side sqlnet.ora file and add this parameter:

SQLNET.INBOUND_CONNECT_TIMEOUT=<n>  Where <n> is the value in seconds.

E.g.:

SQLNET.INBOUND_CONNECT_TIMEOUT = 120

Edit the listener.ora file and add this parameter:

INBOUND_CONNECT_TIMEOUT_<listenername> = <n>  Again, where <n> is the timeout value in seconds.

For example if the listener name is LISTENER then use:

INBOUND_CONNECT_TIMEOUT_LISTENER = 110

From Oracle version 10.2.0.1 onwards the default value of INBOUND_CONNECT_TIMEOUT_<listenername> is 60 seconds. For previous releases it is zero or OFF by default.

How to check whether inbound timeout is active for the listener and database server:

For example,  INBOUND_CONNECT_TIMEOUT_<listener_name> =110

You can check whether the parameter is active or not by simply doing telnet to the listener port.
$ telnet <database server IP> <listener port>
for eg.

$ telnet 123.23.23.23 1521

The telnet session should disconnect after 110 seconds which indicates that the inbound connection timeout for the listener is active.

Alternatively, check at the LSNRCTL prompt using:

LSNRCTL>set current_listener <listener_name>
LSNRCTL>show inbound_connect_timeout

To check whether database server SQLNET.INBOUND_CONNECT_TIMEOUT is active:
Eg.

SQLNET.INBOUND_CONNECT_TIMEOUT=120

a. For Dedicated server setup, enable the support level sqlnet server tracing will show the timeout value as below:

niotns: Enabling CTO, value=120000 (milliseconds) <== 120 seconds
niotns: Not enabling dead connection detection.
niotns: listener bequeathed shadow coming to life…

b. For shared Server setup,
$ telnet <database server IP> <dispatcher port>
Example.

$ telnet 123.23.23.23  51658

The telnet session should disconnect after 120 seconds which indicates that the sqlnet.inbound_connect_timeout is active.

Reference metalink Doc ID 465043.1