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

連線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

檢查節點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: Release 10.2.0.1.0 - Production on 星期三 11月 3 11:34:39 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 11:34:42 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 11:34:45 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> 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 斷開

由於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: Release 10.2.0.1.0 - Production on 星期三 11月 3 11:36:29 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> HOST SQLPLUS YANGTK/YANGTK@TESTRAC

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 11月 3 11:36:31 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> HOST SQLPLUS YANGTK/YANGTK@TESTRAC

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 11月 3 11:36:33 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 斷開

將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: Release 10.2.0.1.0 - Production on 星期三 11月 3 11:41:49 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> HOST SQLPLUS YANGTK/YANGTK@TESTRAC

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 11月 3 11:41:52 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> 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，但是如果配置了兩個地址，那麼設定LOAD_BALANCE是可以正常工作的。

REMOTE_LISTENER對LOAD_BALANCE的影響（一）

相關文章