oracle中balance,Oracle技术之REMOTE_LISTENER对LOAD_BALANCE的影响二

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

这一篇测试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

SQL> ALTER SYSTEM SET REMOTE_LISTENER = 'LISTENERS_TESTRAC' SCOPE = BOTH;

系统已更改。

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME

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

testrac1

SQL> SHOW PARAMETER REMOTE_LISTENER

NAME                                 TYPE        VALUE

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

remote_listener                      string      LISTENERS_TESTRAC

检查节点2上初始化参数配置：

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME

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

testrac2

SQL> SHOW PARAMETER REMOTE_LISTENER

NAME                                 TYPE        VALUE

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

remote_listener                      string      LISTENERS_TESTRAC

节点1和节点2上LISTENERS_TESTRAC的配置均为：

LISTENERS_TESTRAC =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = racnode1-vip)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = racnode2-vip)(PORT = 1521))

)

本地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 = YES)

)

(CONNECT_DATA =

(SERVICE_NAME = TESTRAC)

)

)

连接TESTRAC服务名：

SQL> HOST SQLPLUS YANGTK/YANGTK@TESTRAC

SQL*Plus: Release10.2.0.1.0 - Production on星期三11月3 16:42:01 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 16:42:04 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断开

下面修改TESTRAC服务名，设置FAILOVER为NO：

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)

)

)

测试连接：

SQL> HOST SQLPLUS YANGTK/YANGTK@TESTRAC

SQL*Plus: Release10.2.0.1.0 - Production on星期三11月3 16:44:04 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 16:44:06 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断开

可以看到，在设置了REMOTE_LISTENER后，LOAD_BALANCE参数总是生效的，即使在TNSNAMES.ORA中禁止了LOAD_BALANCE，在连接到实例的时候也会自动连接到RAC的多个实例上。

去掉TESTRAC服务名配置的一个地址：

TESTRAC =

(DESCRIPTION =

(ADDRESS_LIST =

(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 17:04:04 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 17:04:08 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断开

和前面的结论一样，只要配置了REMOTE_LISTENER参数，LOAD_BALANCE参数就自动启用，即使在TNSNAMES.ORA中明确设置为OFF，或者在TNSNAMES.ORA中只配置一个IP地址。

当没有设置REMOTE_LISTENER参数，则TNSNAMES.ORA中配置的LOAD_BALANCE参数决定是否实现LOAD_BALANCE功能，而如果没有进行设置，则默认LOAD_BALANCE参数为ON。