双机负载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\

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。