文章参考：http://www.cnblogs.com/millen/archive/2009/05/06/1450844.html
ORACLE负载均衡主要是指新会话连接到RAC数据库时，如何判定这个新的连接要连到哪个节点进行工作。通常情况下，负载均衡分为客户端的负载均衡和服务器端的负载均衡。客户端负载均衡通常是在tnsnames.ora中多添加一个链接地址以及load_balance和failover参数。而服务器端的负载均衡相对复杂。
一.客户端的负载均衡(Client-Side LB)
工作原理：当客户端发起连接时，会从地址列表中随机选取一个，再使用随机算法把连接请求分散到各个实例。
1)分配连接时没有考虑每个节点的真实负载，最后分配不过不一定是平衡
2)随机算法需要长时间片，如果在短时间内同时发起多个连接，这些连接有可能被分配到一个节点上
3)有些情况下，连接可能被分配到故障节点上
配置方法：在tns中添加LOAD_BALANCE = YES条目
1.查询当前服务器中数据库的版本

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

2.在客户端的tnsnames.ora的配置中，只要连接的是整个数据库的服务名，不是实例名。
在服务器端查看RAC数据库的service_names：

SQL> show parameter service_names;

NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
service_names string orcl

3.在客户端配置TNS：
客户端的负载均衡配置相对简单，只需要在tnsnames.ora中添加 LOAD_BALANCE = yes这么一个选项即可。

配置TNS中的HOST值是服务器端RAC配置中的虚拟IP即VIP，如下：

[oracle@rac1 ~]$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
#127.0.0.1 rac1 localhost.localdomain localhost
#::1 localhost6.localdomain6 localhost6
127.0.0.1 localhost
#public ip
192.168.1.200 rac1
192.168.1.201 rac2
#private ip
10.0.0.1 rac1-priv
10.0.0.2 rac2-priv
#virtual ip
192.168.1.210 rac1-vip
192.168.1.211 rac2-vip

4.在客户端测试

总结：这样当客户端连接RAC数据库时，会随机在TNS里面挑个监听地址进行连接。在Oracle 10g以前，假如有节点宕机或者类似事故时，客户端可能还是选择连接到这个节点，这样会发生较长时间的TCP等待超时。而在10g以后，由于VIP和FAN的引入，这样的情况可以得到很大程度的改善。客户端的负载均衡在通常情况下能够较好地工作，但是由于连接是在客户端随机发起的，这样客户端并不知道RAC各节点的负荷及连接数情况，有可能负荷大的节点还会源源不断地增加新的连接，导致RAC节点无法均衡工作。
二.服务器端的负载均衡(Server-Side LB)
工作原理：
1)该均衡实现是依赖于Listener收集的负载信息。在数据库运行过程中，PMON后台进程会每3秒会将各自节点的负荷（包括LOAD、最大LOAD、CPU使用率）及连接数更新到service_register里面，然后假如节点的负荷有发生变化，将会通知到监听程序，由监听程序再决定新的客户端连接分配至哪个节点。假如RAC中一个节点的监听失败了，PMON每一分钟会去检查一次是否已经恢复正常。
2)PMON进程不仅会向本地的Listener注册，也会想其他节点上的Listener注册，但到底向何处注册，是由Remote_Listeners和Local_Listener这两个参数决定。Local_Listener不用设置，而Remote_Listeners需要设置，参数值有一个tnsnames项。
3)当收到客户端连接请求时，就会把连接转给负载最小的节点，这个节点可能是自己，也可能是其他节点，也就是Listener会转发客户端的连接请求。
配置方法：
服务器端的监听配置是在各节点的tnsnames.ora里面添加一个连接到各个节点监听的条目，然后再在初始化参数里面设置remote_listeners这个参数。
开始配置：
1.测试客户端的TNS是否能够正常连接数据库
首先查看客户端tnsnames.ora的配置，如下：

测试连接：

2.配置服务器端TNS
服务器端的监听配置是在各节点的tnsnames.ora里面添加一个连接到各个节点监听的条目，在服务器端每个节点的tnsnames.ora里面的内容如下：

[oracle@rac1 ~]$ cd /u01/app/oracle/db_1/network/admin/
[oracle@rac1 admin]$ ls
listener.ora samples shrept.lst sqlnet.log tnsnames.ora
[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora.rac1 Network Configuration File: /u01/app/oracle/db_1/network/admin/tnsnames.ora.rac1
# Generated by Oracle configuration tools.

LISTENERS_ORCL =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
)

ORCL2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
(INSTANCE_NAME = orcl2)
)
)

ORCL1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
(INSTANCE_NAME = orcl1)
)
)

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

3.在初始化参数中设置参数remote_listeners

[oracle@rac1 admin]$ export ORACLE_SID=orcl1
[oracle@rac1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Aug 22 16:23:48 2014

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> show parameter remote_listener

NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
remote_listener string LISTENERS_ORCL

正确配置参数后，通过lsnrctl status命令看到在监听启动以后，可以看到监听器上有2个instance。

[oracle@rac1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-AUG-2014 16:26:09

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER_RAC1
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 22-AUG-2014 13:22:34
Uptime 0 days 3 hr. 3 min. 35 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/db_1/network/log/listener_rac1.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.210)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.200)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 2 instance(s).
Instance "orcl1", status READY, has 2 handler(s) for this service...
Instance "orcl2", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 2 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Instance "orcl2", status READY, has 1 handler(s) for this service...
Service "orcl_XPT" has 2 instance(s).
Instance "orcl1", status READY, has 2 handler(s) for this service...
Instance "orcl2", status READY, has 1 handler(s) for this service...
The command completed successfully

4.通过客户端测试负载均衡

经过多次的客户端连接数据库操作，当实例orcl1的负载较大时，会将客户端的请求转发给实例orcl2

补充：
在使用客户端sqlplus连接服务器数据库的时候出现ORA-12545错误

解决办法：
方法1：配置客户端的Hosts文件

方法2：配置参数local_listener
首先注释掉客户端配置的hosts文件：

1)登录数据库查看local_listener的设置状况

SQL> show parameter listener;

NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
local_listener string
remote_listener string LISTENERS_ORCL

在服务器端rac1,rac2上分别编辑tnsnames.ora和listener.ora文件,将两个文件中的host主机名字均修改为具体的VIP地址，如将下面部分：
tnsnames.ora文件中：

LISTENERS_ORCL =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
)

修改为：

LISTENERS_ORCL =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.210)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.211)(PORT = 1521))
)

listener.ora文件中：

LISTENER_RAC1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.200)(PORT = 1521)(IP = FIRST))
)
)

修改为：

LISTENER_RAC1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.210)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.200)(PORT = 1521)(IP = FIRST))
)
)

2)在rac1节点的tnsnames.ora增加如下内容

LOCAL_LISTENER_RAC =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.210)(PORT = 1521))
)

3)在rac2节点的tnsnames.ora增加如下内容

LOCAL_LISTENER_RAC =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.211)(PORT = 1521))
)

4)登陆数据库修改local_listener参数

SQL> alter system set local_listener='LOCAL_LISTENER_RAC' scope=both;
System altered.
SQL> col name for a10;
SQL> col type for a10;
SQL> col value for a10;
SQL> show parameter listener;

NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
local_listener string LOCAL_LISTENER_RAC
remote_listener string LISTENERS_ORCL

ITPub博客

[WK-T]ORACLE 10G 配置负载均衡(LoadBalance)

最新文章