其实谈不上是陷阱,只是不注意的话,很容易出现配置错误。
这一篇介绍LOAD_BALANCE和FAILOVER配合可能导致的错误。
当服务名同时配置了LOAD_BALANCE和FAILOVER时:
TESTRAC =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.224)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.225)(PORT = 1521))
(LOAD_BALANCE = ON)
(FAILOVER = OFF)
)
(CONNECT_DATA =
(SERVICE_NAME = TESTRAC)
)
)
且配置了REMOTE_LISTENER参数:
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
INSTANCE_NAME
----------------
testrac1
SQL> SHOW PARAMETER REMOTE_LISTENER
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_listener string LISTENERS_TESTRAC
检查实例2:
本栏目更多精彩内容:http://www.bianceng.cn/database/Oracle/
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
INSTANCE_NAME
----------------
testrac2
SQL> SHOW PARAMETER REMOTE_LISTENER
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_listener string LISTENERS_TESTRAC
数据库服务器上两个节点的TNSNAMES中LISTENERS_TESTRAC的配置均为:
LISTENERS_TESTRAC =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = racnode1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = racnode2-vip)(PORT = 1521))
)
当实例2被关闭,并不会导致连接错误:
bash-2.03$ srvctl stop inst -d testrac -i testrac2
连接服务名:
SQL> CONN YANGTK/YANGTK@TESTRAC
已连接。
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
INSTANCE_NAME
----------------
testrac1
SQL> CONN YANGTK/YANGTK@TESTRAC
已连接。
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
INSTANCE_NAME
----------------
testrac1
SQL> CONN YANGTK/YANGTK@TESTRAC
已连接。
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
INSTANCE_NAME
----------------
testrac1
但是如果实例2没有问题,只是实例2的监听关闭,那么这个服务名的配置可能会导致错误:
bash-2.03$ srvctl start inst -d testrac -i testrac2
bash-2.03$ srvctl stop listener -n racnode2
再次测试连接:
SQL> CONN YANGTK/YANGTK@TESTRAC
已连接。
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
INSTANCE_NAME
----------------
testrac1
SQL> CONN YANGTK/YANGTK@TESTRAC
ERROR:
ORA-12541: TNS:无监听程序