如果使用DBCA建立RAC資料庫，預設情況下會在各個節點上配置REMOTE_LISTENER引數，因此以前測試的LOAD_BALANCE是在REMOTE_LISTENER存在的情況下進行的測試，這裡測試一下去掉REMOTE_LISTENER對LOAD_BALANCE的影響。

這一篇測試REMOTE_LISTENER存在的情況下，LOAD_BALANCE的生效情況。

REMOTE_LISTENER對LOAD_BALANCE的影響（一）：http://yangtingkun.itpub.net/post/468/508234

連線RAC資料庫中單個例項（二）：http://yangtingkun.itpub.net/post/468/508047

資料庫環境為10.2.0.4 Rac for Solaris for sparc 64：

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.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: Release 10.2.0.1.0 - Production on 星期三 11月 3 16:42:01 2010

連線到:
Oracle Database 10g Enterprise Edition Release 10.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: Release 10.2.0.1.0 - Production on 星期三 11月 3 16:42:04 2010

連線到:
Oracle Database 10g Enterprise Edition Release 10.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 Database 10g Enterprise Edition Release 10.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: Release 10.2.0.1.0 - Production on 星期三 11月 3 16:44:04 2010

連線到:
Oracle Database 10g Enterprise Edition Release 10.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: Release 10.2.0.1.0 - Production on 星期三 11月 3 16:44:06 2010

連線到:
Oracle Database 10g Enterprise Edition Release 10.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 Database 10g Enterprise Edition Release 10.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: Release 10.2.0.1.0 - Production on 星期三 11月 3 17:04:04 2010

連線到:
Oracle Database 10g Enterprise Edition Release 10.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: Release 10.2.0.1.0 - Production on 星期三 11月 3 17:04:08 2010

連線到:
Oracle Database 10g Enterprise Edition Release 10.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 Database 10g Enterprise Edition Release 10.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。

REMOTE_LISTENER對LOAD_BALANCE的影響（二）

相關文章