Oracle11gr2的tnsnam.ora新增TRANSPORT_CONNECT_TIMEOUT

11.2TNSNAMES.ORATRANSPORT_CONNECT_TIMEOUT

有的时候配置TNSNAMES.ORA出错在所难免，而等待超时失败的时候十分痛苦，这一分钟的TCP超时会显得很漫长，尤其是WINDOWS环境下的SQLPLUSW工具，在这一分种内完全没有任何的响应。

现在11.2允许用户配置TNSNAMES.ORA的时候指定TRANSPORT_CONNECT_TIMEOUT参数，用来指定TCP超时的等待时间。

注意，配置这个参数要求11.2的客户端：

[oracle@bjtest admin]$ more tnsnames.ora

TEST =

(DESCRIPTION =

(ADDRESS_LIST =

  (ADDRESS = (PROTOCOL = TCP)(HOST = 172.26.3.23)(PORT = 1521))

)

(CONNECT_DATA =

  (SERVICE_NAME = TESTA)

  (SERVER = DEDICATED)

)

)

TEST112_SHARE =

(DESCRIPTION =

(ADDRESS_LIST =

  (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.230)(PORT = 1521))

)

(CONNECT_DATA =

  (SERVICE_NAME = TEST112XDB)

  (SERVER = SHARED)

)

)

下面通过11.2的sqlplus连接配置的TEST服务名：

[oracle@bjtest ~]$ sqlplus /nolog

SQL*Plus: Release11.2.0.1.0 Production on星期三5月5 06:29:59 2010

Copyright (c) 1982, 2009, [****](). All rights reserved.

SQL> set time on

06:30:07 SQL> conn test/test@test

ERROR:

ORA-12170: TNS:连接超时

06:31:14 SQL>

很明显连接超时需要等待一分钟的时间。

同样通过tnsping命令也可以看到相似的结果：

[oracle@bjtest admin]$ tnsping test

TNS Ping Utility for Linux: Version11.2.0.1.0 – Production on 05-5月-2010 06:30:44

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

已使用的参数文件:

已使用TNSNAMES适配器来解析别名

尝试连接(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.26.3.23)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = TESTA) (SERVER = DEDICATED)))

TNS-12535: TNS:操作超时

[oracle@bjtest admin]$ date

Wed May 5 06:31:48 CST 2010

下面修改TEST服务名的配置，添加TRANSPORT_CONNECT_TIMEOUT参数：

[oracle@bjtest admin]$ vi tnsnames.ora

TEST =

(DESCRIPTION =

(TRANSPORT_CONNECT_TIMEOUT = 5)

(ADDRESS_LIST =

  (ADDRESS = (PROTOCOL = TCP)(HOST = 172.26.3.23)(PORT = 1521))

)

(CONNECT_DATA =

  (SERVICE_NAME = TESTA)

  (SERVER = DEDICATED)

)

)

TEST112_SHARE =

(DESCRIPTION =

(ADDRESS_LIST =

  (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.230)(PORT = 1521))

)

(CONNECT_DATA =

  (SERVICE_NAME = TEST112XDB)

  (SERVER = SHARED)

)

)

~

"tnsnames.ora"53L,1086Cwritten

再次通过SQLPLUS连接：

06:32:52 SQL> conn test/test@test

ERROR:

ORA-12170: TNS:连接超时

06:33:05 SQL>

这次很短的时间很就超时报错并返回结果了。

执行tnsping的结果观察的更明显一些：

[oracle@bjtest admin]$ tnsping test

TNS Ping Utility for Linux: Version11.2.0.1.0 – Production on 05-5月-2010 06:33:16

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

已使用的参数文件:

已使用TNSNAMES适配器来解析别名

尝试连接(DESCRIPTION = (TRANSPORT_CONNECT_TIMEOUT = 5) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.26.3.23)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = TESTA) (SERVER = DEDICATED)))

TNS-12535: TNS:操作超时

[oracle@bjtest admin]$ date

Wed May 5 06:33:24 CST 2010