oracle rac遭遇ora-12170

现象：客户端配置了tnsname指向rac两个节点的VIP地址，tnsping tnsname正常，使用sqlplus连接则会出现偶发的ora-12170报错（同时观察网络状态一切正常）。

以前遇到过：
WARNING: inbound connection timed out (ORA-3136)
sqlnet.log：Fatal NI connect error 12170.
通过：
1.set INBOUND_CONNECT_TIMEOUT_=0 in listener.ora
2. set SQLNET.INBOUND_CONNECT_TIMEOUT = 0 in sqlnet.ora of server.
3. stop and start both listener and database.
4. Now try to connect to DB and observe the behaviour
可解决问题，但我这次碰到的显然不是这样的问题

在metalink中找到Doc ID:453544.1，它描述的问题跟我遇到的十分像，
---------------------------------------------------
Cause：
Client -----------&gt NAT firewall ----------&gt  RAC

The problem lies with the network design. Client can access RAC only via RAC's external IP address, because the connection goes via NAT firewall/router.
As part of RAC configuration, the connections may be re-directed among available nodes to have loadbalancing feature. RAC DB sends the internal IP/hostname in the redirected packet. When the client tries to connect using this internal IP address, it can not connect and thus errors out.
You will see the error if the redirection happends to other nodes. Connection will be successful if no redirection happends.
---------------------------------------------------

之后通过其Solution，解决了ora-12170问题。

解决方法：
1.设置rac每个节点的系统参数LOCAL_LISTENER
node1
-----
SQL>alter system set local_listener='(address=(protocol=tcp)(host=node1)(port=1521))' sid='ocrl1'

node2
-----
SQL>alter system set local_listener='(address=(protocol=tcp)(host=node2)(port=1521))' sid='ocrl2'

这个有点像以前遇到的ora-12545问题的解决方法，不同的是host=node1（注意是：hostname，我使用了VIP hostname）

2.检查listener状态
$ lsnrctl services
Service "oracle" has 2 instance(s).
  Instance "oracle1", status READY, has 3 handler(s) for this service...
    Handler(s):
       "DEDICATED" established:0 refused:0 state:ready
             REMOTE SERVER
             (ADDRESS=(PROTOCOL=TCP)(HOST=node1)(PORT=1521))
       "DEDICATED" established:0 refused:0 state:ready
             LOCAL SERVER
  Instance "oracle2", status READY, has 2 handler(s) for this service...
    Handler(s):
       "DEDICATED" established:0 refused:0 state:ready
            REMOTE SERVER
            (ADDRESS=(PROTOCOL=TCP)(HOST=node2)(PORT=1521))

3.client端tns
RAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oracle)
      (FAILOVER_MODE =
         (TYPE = SELECT)
         (METHOD = BASIC)
      )
    )
  )

4.确保client端能解析服务器hostname（设置不同平台下的hosts文件）

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17997/viewspace-617020/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17997/viewspace-617020/