Ошибка oracle 12170

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:

select value$ from props$ where name = 'GLOBAL_DB_NAME'

select privilege#,level from sysauth$ connect by grantee#=prior privilege# 
and privilege#>0 start with grantee#=:1 and privilege#>0

select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME'),
SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'SERVICE_NAME'), 
INSTANCE_NUMBER, STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN') 
from v$instance where INSTANCE_NAME=SYS_CONTEXT('USERENV', 'INSTANCE_NAME')

select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0

ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERICA' NLS_CURRENCY= '$'
NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CHARACTERS= '.,' NLS_CALENDAR= 'GREGORIAN'
NLS_DATE_FORMAT= 'DD-MON-RR' NLS_DATE_LANGUAGE= 'AMERICAN' NLS_SORT= 'BINARY' TIME_ZONE= '+02:00'
NLS_COMP= 'BINARY' NLS_DUAL_CURRENCY= '$' NLS_TIME_FORMAT= 'HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT=
'DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT= 'HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT=
'DD-MON-RR HH.MI.SSXFF AM TZR'

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:
      $ sqlplus -prelim '/ as sysdba' 

       oradebug setmypid 
       oradebug unlimit 
       oradebug dump systemstate 266 
       ...wait 90 seconds 
       oradebug dump systemstate 266 
       ...wait 90 seconds 
       oradebug dump systemstate 266 
       quit
  • 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

In some cases, it may be possible to avoid problems with Authentication SQL by pinning such statements in the Shared Pool soon after the instance is started and they are freshly loaded. You can use the following artcile to advise on this:
Document 726780.1 How to Pin a Cursor in the Shared Pool using DBMS_SHARED_POOL.KEEP

Pinning will prevent them from being flushed out due to inactivity and aging and will therefore prevent them for needing to be reloaded in the future i.e. needing to be reparsed and becoming susceptible to Authentication hang issues.

3 / 3 / 0

Регистрация: 19.08.2010

Сообщений: 104

1

27.08.2010, 13:37. Показов 62442. Ответов 7


Студворк — интернет-сервис помощи студентам

Всем привет!
База 10.2 установлена на вирт. машину Vmware. Сеть настроена — машины видны в обоих направлениях (от виртуальной к реальной и наоборот). Все прекрасно пингуется без задержек. Экземпляр и листнер запущены. Настройки базы при установке были стандартные. Пытаюсь законектиться к демобазе scott (scott разблокирован).

Но при попытке добавления TNS до базы с реальной машины — не достучаться, пишет:

Connecting…ORA-12170: TNS:Connect timeout occurred
The test did not succeed.

Есть предположения почему не пускает?



0



648 / 77 / 0

Регистрация: 23.01.2010

Сообщений: 193

27.08.2010, 14:04

2

tnsping походит?
firewall не включен?



1



3 / 3 / 0

Регистрация: 19.08.2010

Сообщений: 104

27.08.2010, 14:16

 [ТС]

3

Цитата
Сообщение от dlinnbly
Посмотреть сообщение

tnsping походит?
firewall не включен?

не, фаерволов нет
и tnsping не проходит

C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN>tnsping orcl

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 — Production on 27-AUG-2010 14:13:07

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
c:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = BOLT)(PORT = 1521)) (CONNECT_DATA = (SID = orcl) (SERVER =
DEDICATED)))
TNS-12535: TNS:operation timed out



0



648 / 77 / 0

Регистрация: 23.01.2010

Сообщений: 193

27.08.2010, 14:34

4

Выложите tnsnames.ora с клиента и listener.ora с сервера

Добавлено через 6 минут
BOLT вообще отвечает на ping и telnet по 1521 порту?

Добавлено через 49 секунд
BOLT вообще отвечает на ping и telnet по 1521 порту?



1



edrёn_baton

3 / 3 / 0

Регистрация: 19.08.2010

Сообщений: 104

27.08.2010, 14:37

 [ТС]

5

Выложите tnsnames.ora с клиента и listener.ora с сервера

listener.ora

Bash
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
  )
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = BOLT.localdomain)(PORT = 1521))
    )
  )

tnsnames.ora

Bash
1
2
3
4
5
6
7
8
9
10
11
# TNSNAMES.ORA Network Configuration File: D:\oracle\ora92\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
 
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = BOLT)(PORT = 1521))
    (CONNECT_DATA =
      (SID = orcl)
      (SERVER = DEDICATED)
    )
  )



0



648 / 77 / 0

Регистрация: 23.01.2010

Сообщений: 193

27.08.2010, 15:45

6

Сервер BOLT вообще отвечает на ping и telnet по 1521 порту?



0



3 / 3 / 0

Регистрация: 19.08.2010

Сообщений: 104

27.08.2010, 15:51

 [ТС]

7

Цитата
Сообщение от dlinnbly
Посмотреть сообщение

tnsping походит?
firewall не включен?

Стыдно признаться… но дело было в брандмауере гостевой ОС.
Сейчас всё работает.
Прошу прощения.



0



105 / 75 / 0

Регистрация: 29.06.2009

Сообщений: 328

27.08.2010, 16:17

8

Классное имя для sid — BOLT
Себе тоже сделаю



0



Oracle databases are widely used around the world, and like any other software, they can run into issues. One such issue is the ORA-12170 error, which occurs when a client is unable to establish a connection to the Oracle database server within the specified time period. In this guide, we will provide a comprehensive explanation and step-by-step solution to resolve the ORA-12170 error.

Table of Contents

  • Understanding the ORA-12170 Error
  • Common Causes of ORA-12170 Error
  • Step-by-Step Solution to Fix ORA-12170 Error
  • Step 1: Verify the Basic Network Connectivity
  • Step 2: Check the Listener Configuration
  • Step 3: Validate the SQL*Net Configuration
  • Step 4: Test the Connection with TNSPING
  • Step 5: Adjust the Connection Timeout
  • FAQ

Understanding the ORA-12170 Error

The ORA-12170 error occurs when a client fails to establish a connection to the Oracle database server within the specified time period. The error message looks like this:

ORA-12170: TNS: Connect timeout occurred

This error can be caused by several factors, such as network issues, incorrect listener configuration, or an overly restrictive firewall.

Common Causes of ORA-12170 Error

Some of the common causes of the ORA-12170 error include:

  1. Network connectivity issues between the client and the database server
  2. An incorrect or missing listener configuration
  3. Invalid SQL*Net configuration in the client’s tnsnames.ora file
  4. Insufficient connection timeout value
  5. Firewall restrictions between the client and the database server

Step-by-Step Solution to Fix ORA-12170 Error

Follow these steps to resolve the ORA-12170 error:

Step 1: Verify the Basic Network Connectivity

First, ensure that the client can connect to the database server over the network. You can use the ping command to test the basic network connectivity.

ping <database_server_hostname/IP>

If the ping command fails, check your network configuration and ensure that the client and the database server can communicate with each other.

Step 2: Check the Listener Configuration

Ensure that the Oracle listener is running on the database server and is configured correctly. You can use the lsnrctl status command on the server to check the listener’s status.

lsnrctl status

If the listener is not running, start it using the lsnrctl start command.

lsnrctl start

If the listener is running but is not configured correctly, edit the listener.ora file on the server and update the configuration as needed.

Step 3: Validate the SQL*Net Configuration

On the client side, check the tnsnames.ora file for the correct SQL*Net configuration. Ensure that the hostname, port, and service name in the configuration match the listener configuration on the database server.

Here’s a sample tnsnames.ora configuration:

MYDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <database_server_hostname/IP>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydb)
    )
  )

Step 4: Test the Connection with TNSPING

Use the tnsping utility on the client to test the connection to the Oracle database server.

tnsping MYDB

If the tnsping test fails, review the previous steps to ensure that the network connectivity, listener configuration, and SQL*Net configuration are correct.

Step 5: Adjust the Connection Timeout

If the tnsping test is successful but you still encounter the ORA-12170 error, consider increasing the connection timeout value. You can do this by adding the TIMEOUT parameter to the CONNECT_DATA section of the tnsnames.ora file.

MYDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <database_server_hostname/IP>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydb)
      (TIMEOUT = 5000)
    )
  )

In the example above, the connection timeout is set to 5000 milliseconds (5 seconds).

FAQ

What is the default connection timeout value for Oracle databases?

By default, the connection timeout value for Oracle databases is set to 60 seconds.

How can I check the current connection timeout value?

You can check the current connection timeout value in the tnsnames.ora file on the client side. Look for the TIMEOUT parameter in the CONNECT_DATA section.

Can firewall restrictions cause the ORA-12170 error?

Yes, firewall restrictions between the client and the database server can cause the ORA-12170 error. Ensure that the necessary ports (usually 1521 for Oracle databases) are open on the firewall.

Can I change the connection timeout value on the server side?

Yes, you can change the connection timeout value on the server side by editing the listener.ora file and adding the INBOUND_CONNECT_TIMEOUT parameter in the SID_DESC section.

How can I troubleshoot network issues between the client and the database server?

You can use various network troubleshooting tools, such as ping, traceroute, or telnet, to identify network connectivity issues between the client and the database server.

Learn more about Oracle Listener

Oracle SQL*Net Configuration

Oracle TNSPING Utility

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:

  • Server is Down
  • Sometimes, the network is unable to send your packets to the destination because the database server is down.

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

  • How to Open Port 1521 on Linux Server
  • How to Open Port 1521 on Windows Server

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


***********************************************************************
Fatal NI connect error 12170.

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 на некоторое время и посмотреть, какое время отклика и нет ли потерь пакетов.

ping server1 -t

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

  • Ошибка oracle 03113
  • Ошибка ora 01019
  • Ошибка ora 01652 невозможно увеличить временный сегмент
  • Ошибка oracle 01652
  • Ошибка ora 06553 pls 306