Remote Listener Server side Connect-Time Load Balancing

这里不鏖述remote_listener参数的作用和服务器端连接时负载均衡的技术,只说明以下本次演示,想说明的是客户端连接原始LISTENER时只发生短暂的通信即断开,继而连接由初始连接节点分配的目标节点的LISTENER,且保持对监听端口之间的通信connection,这里在Unix/Linux上只用到监听端口,原理如下图:

 

 

我们通过client sql net trace来连接一些详细信息:

SQL> select instance_name,instance_number from gv$instance;

INSTANCE_NAME    INSTANCE_NUMBER
---------------- ---------------
VPROD2                         2
VPROD1                         1

SQL> show parameter remote_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_listener                      string      RAC_LISTENER

[grid@vrh1 admin]$ tnsping RAC_LISTENER

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 06-SEP-2012 08:15:42

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:
/g01/11.2.0/grid/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = vrh1-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = vrh2-vip)(PORT = 1521)))
OK (10 msec)

从远程节点使用TNSNAMES连接1节点实例,
由于remote_listener 的server-side connect load balancing ,会分发到2节点上
通过设置SQLNET.ORA中的参数做client sql net trace

[oracle@nas sqlnet]$ tnsping SERVER

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 06-SEP-2012 13:16:48

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:
/s01/orabase/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = vrh1-vip)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = VPROD)))
OK (10 msec)

[oracle@nas sqlnet]$ sqlplus  system/oracle@SERVER

SQL*Plus: Release 11.2.0.3.0 Production on Thu Sep 6 13:17:25 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

INSTANCE_NAME
----------------
VPROD2

 

 

以上利用remote_listener服务器端负载均衡成功由1及节点的listener路由到2节点上的LISTENER,并创建server process,我们来观察

SQL NET TRACE

 

 

(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 00 00 28 44 45 53 43 52  |..(DESCR|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 49 50 54 49 4F 4E 3D 28  |IPTION=(|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 43 4F 4E 4E 45 43 54 5F  |CONNECT_|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 44 41 54 41 3D 28 53 45  |DATA=(SE|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 52 56 49 43 45 5F 4E 41  |RVICE_NA|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 4D 45 3D 56 50 52 4F 44  |ME=VPROD|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 29 28 43 49 44 3D 28 50  |)(CID=(P|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 52 4F 47 52 41 4D 3D 73  |ROGRAM=s|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 71 6C 70 6C 75 73 29 28  |qlplus)(|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 48 4F 53 54 3D 6E 61 73  |HOST=nas|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 2E 6F 72 61 63 6C 65 2E  |.oracle.|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 63 6F 6D 29 28 55 53 45  |com)(USE|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 52 3D 6F 72 61 63 6C 65  |R=oracle|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 29 29 28 53 45 52 56 45  |))(SERVE|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 52 3D 64 65 64 69 63 61  |R=dedica|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 74 65 64 29 28 49 4E 53  |ted)(INS|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 54 41 4E 43 45 5F 4E 41  |TANCE_NA|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 4D 45 3D 56 50 52 4F 44  |ME=VPROD|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 32 29 29 28 41 44 44 52  |2))(ADDR|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 45 53 53 3D 28 50 52 4F  |ESS=(PRO|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 54 4F 43 4F 4C 3D 54 43  |TOCOL=TC|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 50 29 28 48 4F 53 54 3D  |P)(HOST=|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 31 39 32 2E 31 36 38 2E  |192.168.|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 31 2E 31 36 32 29 28 50  |1.162)(P|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 4F 52 54 3D 31 35 32 31  |ORT=1521|
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 29 29 29                 |)))     |
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: 259 bytes to transport
(890354416) [000001 06-SEP-2012 13:29:11:567] nspsend: normal exit
(890354416) [000001 06-SEP-2012 13:29:11:567] nscon: exit (0)
(890354416) [000001 06-SEP-2012 13:29:11:567] snsbitts_ts: entry
(890354416) [000001 06-SEP-2012 13:29:11:567] snsbitts_ts: acquired the bit
(890354416) [000001 06-SEP-2012 13:29:11:567] snsbitts_ts: normal exit

................

(890354416) [000001 06-SEP-2012 13:29:11:560] snsbitcl_ts: normal exit
(890354416) [000001 06-SEP-2012 13:29:11:560] nsmfr: entry
(890354416) [000001 06-SEP-2012 13:29:11:560] nsmfr: 2760 bytes at 0x10a57910
(890354416) [000001 06-SEP-2012 13:29:11:560] nsmfr: normal exit
(890354416) [000001 06-SEP-2012 13:29:11:561] snsbitcl_ts: entry
(890354416) [000001 06-SEP-2012 13:29:11:561] snsbitcl_ts: normal exit
(890354416) [000001 06-SEP-2012 13:29:11:561] nsmfr: entry
(890354416) [000001 06-SEP-2012 13:29:11:561] nsmfr: 1528 bytes at 0x10a57310
(890354416) [000001 06-SEP-2012 13:29:11:561] nsmfr: normal exit
(890354416) [000001 06-SEP-2012 13:29:11:561] nsclose: normal exit
(890354416) [000001 06-SEP-2012 13:29:11:561] nscall: connecting...
(890354416) [000001 06-SEP-2012 13:29:11:561] nsc2addr: entry
(890354416) [000001 06-SEP-2012 13:29:11:561] nsc2addr: (ADDRESS=(PROTOCOL=TCP)(HOST=vrh2.oracle.com)(PORT=1521))
(890354416) [000001 06-SEP-2012 13:29:11:561] nttbnd2addr: entry
(890354416) [000001 06-SEP-2012 13:29:11:561] snlinGetAddrInfo: entry
(890354416) [000001 06-SEP-2012 13:29:11:561] snlinGetAddrInfo: getaddrinfo() failed with error -2
(890354416) [000001 06-SEP-2012 13:29:11:561] snlinGetAddrInfo: exit
(890354416) [000001 06-SEP-2012 13:29:11:561] nttbnd2addr: looking up IP addr for host: vrh2.oracle.com
(890354416) [000001 06-SEP-2012 13:29:11:561] snlinGetAddrInfo: entry
(890354416) [000001 06-SEP-2012 13:29:11:561] snlinGetAddrInfo: exit
(890354416) [000001 06-SEP-2012 13:29:11:561] snlinFreeAddrInfo: entry
(890354416) [000001 06-SEP-2012 13:29:11:561] snlinFreeAddrInfo: exit
(890354416) [000001 06-SEP-2012 13:29:11:561] nttbnd2addr: exit

 

 

通过netstat 可以查看当前client远程连接的端口,可以看到已经与1节点断开了connection:

 

 

[root@nas ~]# netstat -anp
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name   
tcp        0      0 127.0.0.1:2208              0.0.0.0:*                   LISTEN      2959/hpiod          
tcp        0      0 127.0.0.1:199               0.0.0.0:*                   LISTEN      5424/snmpd          
tcp        0      0 0.0.0.0:111                 0.0.0.0:*                   LISTEN      2526/portmap        
tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN      5396/sshd           
tcp        0      0 127.0.0.1:631               0.0.0.0:*                   LISTEN      3000/cupsd          
tcp        0      0 0.0.0.0:632                 0.0.0.0:*                   LISTEN      2570/rpc.statd      
tcp        0      0 127.0.0.1:25                0.0.0.0:*                   LISTEN      3046/sendmail: acce 
tcp        0      0 127.0.0.1:2207              0.0.0.0:*                   LISTEN      2964/python         
tcp        0    888 192.168.1.178:22            192.168.1.6:65179           ESTABLISHED 21508/0             
tcp        0      0 192.168.1.178:22            192.168.1.6:64535           ESTABLISHED 21187/1             
tcp        0      0 192.168.1.178:44496         192.168.1.163:1521          ESTABLISHED 21482/sqlplus

关注dbDao.com的新浪微博

扫码关注dbDao.com 微信公众号:

Speak Your Mind

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569