php访问oracle 12170,双机负载rac ORA-12170: TNS: 连接超时 问题

大家好，请教大家一个问题，在线等，非常着急，请大家赐教，谢谢各位，现在我把问题描述如下：

客户端连接串配置：

oracle.username=qport

oracle.password=qport_manager

oracle.driver=oracle.jdbc.driver.OracleDriver

oracle.url=jdbc\

racle\:thin\:@(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.3.30) (PORT = 1521))(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.3.31) (PORT = 1521))(LOAD_BALANCE = yes)(FAILOVER = ON))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl.idevelopment.info)(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180 )(DELAY = 5))))

最近两天出现如下错误：

ORA-12520: TNS:listener could not find available handler for requested type of

server

昨天开会讨论，对每个用户进行资源限制，所以对每个用户配置了资源文件，限制其连接数。

今天我检查发现监听服务发现实例2(ORCL2)上除了自身会话外，没有应用会话，说明节点2监听没有提供remote服务，我个人分析这也是导致ORA-12520错误的原因，因为客户端采用负载均衡配置，数据库分配到实例1(ORCL1)时正常，分配到实例2(ORCL2)时无法解析，导致ORA-12520错误。

我将节点2上监听重启后，发现两个实例都正常了，但从客户端连接时有时能连接上，有时出现如下错误：ORA-12170: TNS: 连接超时

客户端测试情况如下：

一、两个虚IP 不指定实例名

QPORT_ASM =

(DESCRIPTION =

(ADDRESS_LIST =

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

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

(LOAD_BALANCE = yes)

(FAILOVER = ON)

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl.idevelopment.info)

(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180 )(DELAY = 5))

)

)

结果：有时会出现ORA-12170: TNS: 连接超时

二、一个虚IP 指定实例名

qport2 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl.idevelopment.info)

(INSTANCE_NAME = orcl2)

)

)

qport1 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl.idevelopment.info)

(INSTANCE_NAME = orcl1)

)

)

结果：连接正常

服务器端

RAC1 监听、tnsname文件配置如下

SQL> show parameter local_list

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

local_listener                       string      LOCAL_LISTENERS

SQL> show parameter remote_listener

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

remote_listener                      string      LISTENERS_ORCL

[oracle@rac1 admin]$ cat listener.ora

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

# Generated by Oracle configuration tools.

PREFER_LEAST_LOADED_NODE = OFF

LISTENER_RAC1 =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.30)(PORT = 1521)(IP = FIRST))

)

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.20)(PORT = 1521)(IP = FIRST))

)

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

)

)

)

[oracle@rac1 admin]$ cat tnsnames.ora

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

# Generated by Oracle configuration tools.

ORCL_TAF =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))

(LOAD_BALANCE = yes)

(FAILOVER = ON)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ORCL_TAF.idevelopment.info)

(FAILOVER_MODE =

(TYPE = SELECT)

(METHOD = BASIC)

(RETRIES = 180)

(DELAY = 5)

)

)

)

LISTENERS_ORCL =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))

)

LOCAL_LISTENERS =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))

)

ORCL2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl.idevelopment.info)

(INSTANCE_NAME = orcl2)

)

)

ORCL1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl.idevelopment.info)

(INSTANCE_NAME = orcl1)

)

)

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))

(LOAD_BALANCE = yes)

(FAILOVER = ON)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl.idevelopment.info)

(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180 )(DELAY = 5))

)

)

RAC2 监听、tnsname文件配置如下

SQL> show parameter local_listener

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

local_listener                       string      LOCAL_LISTENERS

SQL> show parameter remote_listener

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

remote_listener                      string      LISTENERS_ORCL

[oracle@rac2 admin]$ cat listener.ora

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

# Generated by Oracle configuration tools.

PREFER_LEAST_LOADED_NODE = OFF

LISTENER_RAC2 =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.31)(PORT = 1521)(IP = FIRST))

)

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.21)(PORT = 1521)(IP = FIRST))

)

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

)

)

)

[oracle@rac2 admin]$ cat tnsnames.ora

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

# Generated by Oracle configuration tools.

ORCL_TAF =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))

(LOAD_BALANCE = yes)

(FAILOVER = ON)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ORCL_TAF.idevelopment.info)

(FAILOVER_MODE =

(TYPE = SELECT)

(METHOD = BASIC)

(RETRIES = 180)

(DELAY = 5)

)

)

)

LISTENERS_ORCL =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))

)

LOCAL_LISTENERS =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))

)

ORCL2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl.idevelopment.info)

(INSTANCE_NAME = orcl2)

)

)

ORCL1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl.idevelopment.info)

(INSTANCE_NAME = orcl1)

)

)

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))

(LOAD_BALANCE = yes)

(FAILOVER = ON)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl.idevelopment.info)

(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180 )(DELAY = 5))

)

)

应用连接串配置：oracle.url=jdbc\

racle\:thin\:@(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.3.30) (PORT = 1521))(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.3.31) (PORT = 1521))(LOAD_BALANCE = yes)(FAILOVER = ON))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl.idevelopment.info)(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180 )(DELAY = 5))))

在线等，请大家帮我分析一下出现ORA-12170: TNS: 连接超时 的原因，谢谢各位