标签归档：ORA-12545

RAC中诡异的ORA-12545错误

1条回复

这个错误是本月18号项目上线时遇到的，项目上线后应用开始进行测试，发现总是时断时续出现ORA-12545: Connect failed because target host or object does not exist. 这个错误提示很简单，一开始觉得这个错误按道理不应该出现，因为应用和中间件配的连接串配置是我这边提供的，其中设计到主机名和vip的地方我都改成了IP地址，而且还用了负载均衡，其连接串如下示例： 

ORA10GTAF =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.23)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.24)(PORT = 1521))
     (LOAD_BALANCE = yes)
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = ora10gtaf)
       (FAILOVER_MODE =
         (BACKUP = ora10gtaf_preconnect)
         (TYPE = SELECT)
         (METHOD = PRECONNECT)
         (RETRIES = 180)
         (DELAY = 5)
       )
     )
   )

于是在Server端写了一个使用连接串方式每5秒连接一次数据的脚本去测试，结果等了30分钟，一次12545错误也没出现。由此开始怀疑应用和中间件与数据库之前的连接配置串有问题，或是他们擅自把我提供的连接配置串修改，然后去和应用的人核查，查完之后并未发现异常，无奈只能将前面在Server端每5秒连接一次数据的检查脚本放在一个Linux应用服务段测试，结果ORA-12545错误重现了，但并不是每次连接都出现ORA-12545错误，而是间歇性出现。后来仔细观察才发现，每次连接成功的都是连接到实例1，永远连接不上实例2上，然后为了能让其连上实例2上，我在连接串里面把1节点的连接地址及协议注销掉，只留节点2的连接信息，示例如下： 

ORA10GTAF =
   (DESCRIPTION =
   # (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.23)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.24)(PORT = 1521))
     (LOAD_BALANCE = yes)
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = ora10gtaf)
       (FAILOVER_MODE =
         (BACKUP = ora10gtaf_preconnect)
         (TYPE = SELECT)
         (METHOD = PRECONNECT)
         (RETRIES = 180)
         (DELAY = 5)
       )
     )
   )

然后再用那个每5秒连接数据库检测脚本去测试，结果这次连接测试全部失败，没有一次连接成功，然后就开始思考为何出现这种情况，考虑到第一次测试时能连接成功的总是实例1，从没有一次能连接上实例2，第二次单独去测试连接实例2就出现全部连接失败的问题，突然想到RAC的负载均衡问题，由此判断实例2负载很可能大于1实例（负载指每个实例的连接会话数和服务器压力大小）。在实例2的负载大于实例1前提下，当第二次去单独测试连接实例2时，由于RAC的负载均衡机制，每次都会将连接实例2的请求重定向到实例1上，那么连接请求重定向如果失败是不是就出现了上面的ORA-12545错误呢？想到这一点我立即查了一下连个节点的负载情况： 

SQL> select inst_id,count(*) from gv$session group by inst_id;
   INST_ID   COUNT(*)
---------- ----------
         1         30
         2         74

果然实例2的连接数远远大于实例1，刚好在处理的过程中在MOS上看到一篇于此类似的文章Client Connection to RAC Intermittently Fails-ORA-12545 TNS: Host or Object Does not Exist (Doc ID 364855.1)，看完解开了心中疑惑。 

Symptoms
When attempting to connect to a RAC service name, the connection intermittently fails with an ORA-12545 error:  TNS: Host or object doesn't exist.   The expected behavior is that the connection would be redirected to any of the listener in the cluster where the listener is running on the VIP.  This is normal under RAC Cluster node load balancing. 
However, when the server side listener endpoints are not correctly configured OR the client cannot resolve all forms of the VIP hostname, this error can get thrown.
 
Cause
Whe LOCAL_LISTENER is not set at all or is not set correctly, PMON can register both the VIP hostname and the physical hostname with the REMOTE_LISTENER.  This is incorrect in RAC but if it does occur, the client can intermittently be routed to a listener endpoint on either of these addresses.  If the client is unable to resolve the physical hostname or even the vip hostname, the connection will fail with ORA-12545.
Additionall, if the VIP hostname cannot be resolved, but the VIP numerical address is reachable; then this VIP numerical address should be used as the HOST value for LOCAL_LISTENER instead.
And important first set is to check the output for :  lsnrctl services <listener_name>
You should not see the physical hostname in the handler information for your RAC instances.   Here's an example of an incorrect lsnrctl services <listener_name> output for a 2-node RAC cluster:
------------------------------------
lsnrctl services oracle_rac_listener 
Service "oracle.oracle.com" has 2 instance(s). 
Instance "orcl1", status READY, has 2 handler(s) for this service... 
Handler(s): 
"DEDICATED" established:0 refused:0 state:ready 
REMOTE SERVER 
(ADDRESS=(PROTOCOL=TCP)(HOST=myphysicalhost1)(PORT=1521)) 
"DEDICATED" established:326 refused:0 state:ready 
LOCAL SERVER 
 
Instance "orcl2", status READY, has 1 handler(s) for this service... 
Handler(s): 
"DEDICATED" established:394371 refused:0 state:ready 
REMOTE SERVER 
(ADDRESS=(PROTOCOL=TCP)(HOST=myphysicalhost2)(PORT=1521))
 
Here's an example of what it ought to look like when LOCAL_LISTENER is configured correctly:
------------------------------------
lsnrctl services oracle_rac_listener 
Service "oracle.oracle.com" has 2 instance(s). 
Instance "orcl1", status READY, has 2 handler(s) for this service... 
Handler(s): 
"DEDICATED" established:0 refused:0 state:ready 
REMOTE SERVER 
(ADDRESS=(PROTOCOL=TCP)(HOST=myVIP_host1)(PORT=1521)) 
"DEDICATED" established:326 refused:0 state:ready 
LOCAL SERVER 
 
Instance "orcl2", status READY, has 1 handler(s) for this service... 
Handler(s): 
"DEDICATED" established:394371 refused:0 state:ready 
REMOTE SERVER 
(ADDRESS=(PROTOCOL=TCP)(HOST=myVIP_host2)(PORT=1521))
 
Solution
Log in with privileges to the instance and issue the following commands so that LOCAL_LISTENER is set correctly:
-----------------------------------
alter system set LOCAL_LISTENER="(address=(protocol=tcp)(port=1521)(host=<your_vip_node1>))" scope=both sid='INSTANCE_NAME1';
Do the same for the 2nd instance where host is set to the <vip_host_node2> and the sid is set to the 2nd instance name.
alter system set LOCAL_LISTENER="(address=(protocol=tcp)(port=1521)(host=<your_vip_node2>))" scope=both sid='INSTANCE_NAME2'; 
 
Now the correct address using the VIP hostname will be registered against the listeners in the cluster.
 
The client should be able to resolve all forms of the VIP host via their /etc/hosts file.  i.e. short name and fully qualified.
In the case that a VIP hostname cannot be resolved by the client, the following syntax can be used with LOCAL_LISTENER to tell the client to connect using an IP address instead of the VIP hostname
-----------------------------------
alter system set LOCAL_LISTENER="(address=(protocol=tcp)(port=1521)(host=1.2.3.4))"
... where "1.2.3.4" is the IP address of your VIP interface

上面从MOS上贴出来的这些内容足以说明，RAC的负载均衡机制在将连接请求重定向时，如果client端未解析vip名时，而local_listener又未直接使用具体值而是标识符时，极有可能出现ORA-12545错误，解决办法上面已经说了，当然如果条件允许还可以考虑统一使用域名服务器解析。