jdbc:oracle:thin:load balancer的功能,Oracle技术之REMOTE_LISTENER对LOAD_BALANCE影响一

如果使用DBCA建立RAC数据库，默认情况下会在各个节点上配置REMOTE_LISTENER参数，因此以前测试的LOAD_BALANCE是在REMOTE_LISTENER存在的情况下进行的测试，这里测试一下去掉REMOTE_LISTENER对LOAD_BALANCE的影响。

数据库环境为10.2.0.4 Rac for Solaris for sparc 64：

SQL> SELECT * FROM V$VERSION;

BANNER

----------------------------------------------------------------

Oracle Database10gEnterpriseEdition Release10.2.0.4.0 - 64bi

PL/SQL Release 10.2.0.4.0 - Production

CORE    10.2.0.4.0      Production

TNS for Solaris: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production

SQL> SELECT NAME FROM V$DATABASE;

NAME

---------

TESTRAC

SQL> SELECT INSTANCE_NUMBER, INSTANCE_NAME FROM GV$INSTANCE;

INSTANCE_NUMBER INSTANCE_NAME

--------------- ----------------

1 testrac1

2 testrac2

检查节点1上LISTENER相关配置：

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME

----------------

testrac1

SQL> SHOW PARAMETER LISTENER

NAME             TYPE        VALUE

---------------- ----------- ------------------------------

local_listener   string      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.224)(PORT = 1521))

remote_listener  string

节点2上的参数配置：

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME

----------------

testrac2

SQL> SHOW PARAMETER LISTENER

NAME             TYPE        VALUE

---------------- ----------- ------------------------------

local_listener   string      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.225)(PORT = 1521))

remote_listener  string

将本地TNSNAMES.ORA中服务名配置为：

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 = NO)

)

(CONNECT_DATA =

(SERVICE_NAME = TESTRAC)

)

)

根据文章开头给出的连接文章可以确定，当REMOTE_LISTENER设置为空时，服务名指定一个地址，连接这个服务名是不会连接到两个实例上的，这里测试一下当存在两个地址的情况，先将LOAD_BALANCE设置为NO：

SQL> HOST SQLPLUS YANGTK/YANGTK@TESTRAC

SQL*Plus: Release10.2.0.1.0 - Production on星期三11月3 11:34:39 2010

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

连接到:

Oracle Database10gEnterprise Edition Release10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME

----------------

testrac1

SQL> HOST SQLPLUS YANGTK/YANGTK@TESTRAC

SQL*Plus: Release10.2.0.1.0 - Production on星期三11月3 11:34:42 2010

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

连接到:

Oracle Database10gEnterprise Edition Release10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME

----------------

testrac1

SQL> HOST SQLPLUS YANGTK/YANGTK@TESTRAC

SQL*Plus: Release10.2.0.1.0 - Production on星期三11月3 11:34:45 2010

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

连接到:

Oracle Database10gEnterprise Edition Release10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME

----------------

testrac1

SQL> EXIT

从Oracle Database10gEnterprise Edition Release10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options断开

SQL> EXIT

从Oracle Database10gEnterprise Edition Release10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options断开

SQL> EXIT

从Oracle Database10gEnterprise Edition Release10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options断开

由于LOAD_BALANCE设置为NO，Oracle找到第一个可用的连接即可，因此每次都连接到实例1上，下面修改TESTRAC服务名，将两个地址调换位置：

TESTRAC =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.225)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.224)(PORT = 1521))

(LOAD_BALANCE = NO)

)

(CONNECT_DATA =

(SERVICE_NAME = TESTRAC)

)

)

测试TESTRAC的连接：

SQL> HOST SQLPLUS YANGTK/YANGTK@TESTRAC

SQL*Plus: Release10.2.0.1.0 - Production on星期三11月3 11:36:29 2010

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

连接到:

Oracle Database10gEnterprise Edition Release10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME

----------------

testrac2

SQL> HOST SQLPLUS YANGTK/YANGTK@TESTRAC

SQL*Plus: Release10.2.0.1.0 - Production on星期三11月3 11:36:31 2010

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

连接到:

Oracle Database10gEnterprise Edition Release10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME

----------------

testrac2

SQL> HOST SQLPLUS YANGTK/YANGTK@TESTRAC

SQL*Plus: Release10.2.0.1.0 - Production on星期三11月3 11:36:33 2010

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

连接到:

Oracle Database10gEnterprise Edition Release10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME

----------------

testrac2

SQL> EXIT

从Oracle Database10gEnterprise Edition Release10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options断开

SQL> EXIT

从Oracle Database10gEnterprise Edition Release10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options断开

SQL> EXIT

从Oracle Database10gEnterprise Edition Release10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options断开

将LOAD_BALANCE设置为YES(LOAD_BALANCE设置YES、ON、TRUE是等价的，相反的设置包括NO、OFF、FALSE)：

TESTRAC =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.225)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.224)(PORT = 1521))

(LOAD_BALANCE = YES)

)

(CONNECT_DATA =

(SERVICE_NAME = TESTRAC)

)

)

检查此时的连接设置：

SQL> HOST SQLPLUS YANGTK/YANGTK@TESTRAC

SQL*Plus: Release10.2.0.1.0 - Production on星期三11月3 11:41:49 2010

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

连接到:

Oracle Database10gEnterprise Edition Release10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME

----------------

testrac2

SQL> HOST SQLPLUS YANGTK/YANGTK@TESTRAC

SQL*Plus: Release10.2.0.1.0 - Production on星期三11月3 11:41:52 2010

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

连接到:

Oracle Database10gEnterprise Edition Release10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME

----------------

testrac1

SQL> EXIT

从Oracle Database10gEnterprise Edition Release10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options断开

SQL> EXIT

从Oracle Database10gEnterprise Edition Release10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options断开

当参数对于REMOTE_LISTENER不存在时，单主机地址的用户无法利用LOAD_BALANCE，但是如果配置了两个地址，那么设置LOAD_BALANCE是可以正常工作的。