bj-jn

8 积分	1 好友	20 主题

发消息

[Exadata常规管理] 10g rac测试TAF时出现sql挂起现象！！

1^#

发表于 2013-1-29 17:36:23 | 查看: 3974| 回复: 2

本帖最后由 bj-jn 于 2013-1-30 10:55 编辑

环境描述：版本/10.2.0.4.0  客户端IP:10.3.29.10  rac的vip:10.1.91.12/22
节点1：(racdb1)
NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
local_listener                      string    (ADDRESS=(PROTOCOL=TCP)(HOST=10.1.91.12)(PORT=1521))
remote_listener                   string LISTENERS_RACDB

节点2:(racdb2)
NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
local_listener                      string    (ADDRESS=(PROTOCOL=TCP)(HOST=10.1.91.22)(PORT=1521))
remote_listener                   string LISTENERS_RACDB

LISTENERS_RACDB =
  (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.91.12)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.91.22)(PORT = 1521))
  )

测试步骤：
1.配置客户端的tnsnames.ora
racdb_taf =
  (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.91.12)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.91.22)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = racdb)
(FAILOVER_MODE =
      (TYPE = session)
      (METHOD = basic)
   (RETRIES = 180)
   (DELAY = 1)
   )
)
  )

2.使用racdb_ctf连接串连接rac实例
SQL>select inst_id,sid from gv$session where sid=userenv('sid');
INST_ID       SID
----------- ---------
      1          126

3.登陆rac节点1，讲server process给kill掉
SQL> select spid from v$process a,v$session b
  2  where a.addr=b.paddr and sid=126;

SPID
------------
7772

SQL> host kill -9 7772

4.返回客户端的sqlplus中执行如下sql这时被hang住
SQL>  select instance_name from v$instance
  /

5.通过监控2个节点上的监听日志发现客户端正不断的尝试连接两个节点
29-JAN-2013 17:09:05 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=session)(METHOD=basic)(RETRIES=180)(DELAY=1))(CID=(PROGRAM=E:\oracle\product\10.2.0\db_1\bin\sqlplus.exe)(HOST=LENOVO-BC500838)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.3.29.10)(PORT=1128)) * establish * racdb * 0
29-JAN-2013 17:09:06 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=session)(METHOD=basic)(RETRIES=180)(DELAY=1))(CID=(PROGRAM=E:\oracle\product\10.2.0\db_1\bin\sqlplus.exe)(HOST=LENOVO-BC500838)(USER=Administrator))(INSTANCE_NAME=racdb1)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.3.29.10)(PORT=1131)) * establish * racdb * 0
29-JAN-2013 17:09:07 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=session)(METHOD=basic)(RETRIES=180)(DELAY=1))(CID=(PROGRAM=E:\oracle\product\10.2.0\db_1\bin\sqlplus.exe)(HOST=LENOVO-BC500838)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.3.29.10)(PORT=1132)) * establis

结论：经过观察发现此现象是由于tnsnames.ora中(RETRIES=180)(DELAY=1)这两个参数导致客户端不断的去连接节点1 or节点2，将这两个参数去掉之后解决。
疑问:随后我将客户端中tnsnames.ora的rac节点ip换到另外一套rac环境，sql hang住现象消失，为什么在两套rac环境中这两个参数表现的结果却完全不一样了，求解释！
我的理解是retries应该是重新连接失败后的次数上限，既然连接再次建立成功为什么还要一直不断的发起新的retry 连接?

分享0