oracle ORA-12170: TNS:Connect 超时发生

ORA-12170: TNS:Connect timeout occurred

提问by Pseudonymous

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

我试图在我的笔记本电脑中使用 Oracle Toad 连接到数据库，但我一直遇到这个错误：

ORA-12170: TNS:Connect timeout occurred

ORA-12170: TNS:Connect 超时发生

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

我不断出现此错误的可能原因是什么？

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

我昨天访问了同一个数据库并且能够访问它。

采纳答案by Zac

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

问题是Oracle 服务运行在一个IP 地址上，而主机配置了另一个IP 地址。

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

要查看 Oracle 服务的 IP 地址，请发出lsnrctl status命令并检查报告的地址（在本例中为 127.0.0.1，本地主机）：

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

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

要查看主机 IP 地址，请发出ipconfig（在 windows 下）或ifconfig（在 linux 下）命令。

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

但是，在我的安装中，如果在 localhost 地址上设置Oracle 服务将不起作用，我必须设置真实的主机 IP 地址（例如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.

为避免将来出现此问题，请勿使用 DHCP 分配主机的 IP 地址，而应使用静态地址。

回答by Ashish Jain

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:

这是因为 SID 冲突。例如，在您的 Oracle12cBase\app\product\12.1.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora 文件中，ORCL 的连接描述是这样的：

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

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

并且，您正尝试使用使用相同 SID 但不同 IP、用户名/密码的连接字符串进行连接，如下所示：

sqlplus username/[email protected]:1521/orcl

sqlplus 用户名/密码@192.168.130.52:1521/orcl

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

要解决此问题，请在 tnsnames.ora 文件中进行更改：

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.130.52)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

回答by Vishal Tathe

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.

问题是因为连接建立或与客户端的通信未能在分配的时间间隔内完成。这可能是网络或系统延迟的结果。

回答by Fajar

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

检查防火墙，以允许您的客户端在服务器上进行连接。通过允许域网络或创建规则。

回答by Mohammad AL-Omari

open sqlnet.ora  

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.INBOUND_CONNECT_TIMEOUT=360
SQLNET.RECV_TIMEOUT=10
SQLNET.SEND_TIMEOUT=10

http://docs.oracle.com/cd/B19306_01/network.102/b14213/sqlnet.htm

http://docs.oracle.com/cd/B19306_01/network.102/b14213/sqlnet.htm

回答by Avatar Girase

I was getting the same error while connecting my "hr" user of ORCLPDB which is a pluggable database.

我在连接 ORCLPDB 的“hr”用户时遇到了同样的错误，ORCLPDB 是一个可插入的数据库。

First, get hostname and port number by typing a command lsnrctl statuson windows command prompt. In my case, it was 127.0.0.1 with port number as 1521

首先，通过lsnrctl status在 Windows 命令提示符下键入命令来获取主机名和端口号。就我而言，它是 127.0.0.1，端口号为 1521

Second, enter the below command with your hostname and port number:

其次，使用您的主机名和端口号输入以下命令：

sqlplus username/password@HostName:Port Number/PluggableDatabaseName.

For example:

例如：

sqlplus hr/[email protected]:1521/ORCLPDB.

回答by Tagar

TROUBLESHOOTING STEPS(Doc ID 730066.1)

故障排除步骤（文档 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_TIMEOUTand/or INBOUND_CONNECT_TIMEOUT_server-side parameters.

连接超时错误 ORA-3135 和 ORA-3136 当连接到数据库的尝试未在以下允许的时间段内完成其连接和身份验证阶段时，可能会发出连接超时错误： SQLNET.INBOUND_CONNECT_TIMEOUT和/或INBOUND_CONNECT_TIMEOUT_server侧参数。

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

从 Oracle 10.2 开始，这些参数的默认值为 60 秒，而在以前的版本中为 0，表示没有超时。

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

超时时，客户端程序将收到 ORA-3135（或可能是 TNS-3135）错误：

ORA-3135 connection lost contact

ORA-3135 连接失去联系

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

并且数据库将在它的 alert.log 中记录 ORA-3136 错误：

... Sat May 10 02:21:38 2008 WARNING: inbound connection timed out (ORA-3136) ...

... 2008 年 5 月 10 日星期六 02:21:38 警告：入站连接超时 (ORA-3136) ...

• Authentication SQL

• 身份验证 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:

当数据库会话处于身份验证阶段时，它将发出一系列 SQL 语句。直到所有这些都被完全解析、执行和获取，身份验证才完成。此列表中的一些 SQL 语句（例如 10.2）是：

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.

注意：以上 SQL 列表并不完整，不代表身份验证 SQL 的排序。不同版本之间也可能存在差异。

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

上述 SQL 语句需要像 Oracle 数据库中的所有 SQL 一样进行解析、执行和提取。因此，在这些阶段遇到的任何问题，表现为挂起或严重的性能下降，都可能导致超时。

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.

这种挂起的症状将被身份验证会话视为等待： ? 光标：引脚 S 等待 X ？闩锁：行缓存对象？行缓存锁 其他类型的等待事件也是可能的；此列表可能不完整。

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:

1. 在一个或多个身份验证会话被阻止期间，在级别 266 处连续三次系统状态转储。阻塞会话很可能会导致多次连接尝试超时。因此，即使生成它们所需的时间超过单个超时（例如 60 秒）的时间，系统状态转储也很有用：

      $ 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

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

• ASH 报告涵盖了例如 10-15 分钟的时间段，在该时间段内看到了几个超时错误。
• 如果可能，对 V$LATCHHOLDER 视图进行两次连续查询，以了解正在等待的共享资源是闩锁的情况。从 v$latchholder 中选择 *；系统状态转储应该有助于识别阻止程序会话。级别 266 将向我们展示它正在执行的代码，这可能有助于将任何现有错误定位为根本原因。

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

• 未发布的错误 6879763 共享池模拟器错误由未发布的错误 6966286 的补丁修复，请参阅注释 563149.1

• 未发布的错误 7039896 变通方法参数 _enable_shared_pool_durations=false 参见注释 7039896.8

• 避免该问题的其他方法

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

在某些情况下，通过在实例启动并重新加载后不久将此类语句固定在共享池中，可能可以避免身份验证 SQL 出现问题。您可以使用以下文章对此提供建议：文档 726780.1 如何使用 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.

固定将防止它们因不活动和老化而被刷新，因此将防止它们在未来需要重新加载，即需要重新解析并变得容易受到身份验证挂起问题的影响。