服务器IP信息如下:
[grid@rac1 bin]$ more /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
#Public
192.168.8.10 rac1.localdomain rac1
192.168.8.11 rac2.localdomain rac2
#Private
192.168.100.10 rac1-priv.localdomain rac1-priv
192.168.100.11 rac2-priv.localdomain rac2-priv
#Virtual
192.168.8.8 rac1-vip.localdomain rac1-vip
192.168.8.7 rac2-vip.localdomain rac2-vip
#Scan
192.168.8.6 rac-scan.localdomain rac-scan
安装RAC的时候用的是openfiler做共享存储,那就不多说了,这里主要介绍RAC的负载均衡配置。
一般来说我们只需要通过配置客户端$ORACLE_HOME/network/admin/tnsnames.ora文件就可以实现RAC负载均衡,这种方式是连接数上的负载均衡。
ORARAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.8)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.7)(PORT = 1521))
(LOAD_BALANCE = yes) #启用负载均衡
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac.localdomain)
(FAILOVER_MODE =
(TYPE = session) #session当一个连接好的会话的实例发生故障,系统会自动将会话切换到其他可用的实例,前台应用无须再度发起连接,但会话正在执行的SQL需要重新执行。可选参数还有select和none,对于select,用户正在执行的select语句会被转移到新的实例上,在新的节点上继续返回后续结果集,而已经返回的记录集则抛弃。none,这个是客户端默认值,表示禁止SQL接管功能
(METHOD = basic) #在感知到节点故障时才创建到其他实例的连接,有时间延迟,还有个可选参数preconnect是初始连接所有的接点,没有时间延迟,这个参数一般认为是资源浪费
(RETRIES = 180) #重试次数
(DELAY = 5) #重试间隔时间
)
)
)
经过测试发现,在客户端的tnsnames.ora文件中配置监听服务名的时候,如果写上“ADDRESS_LIST=”这句话,那么连接到的总是第一个VIP地址,不会起到负载均衡的功能,所以必须删除掉这句话。
由于连接是由客户端发起的,它并不知道RAC数据库集群中各个节点的繁忙状态和连接信息,因此负荷较大的节点仍然会增加新的连接,这样就可能导致RAC节点无法真正做到负载均衡。不过幸运的是,从Oracle 10g开始,服务器端负载均衡就可以根据RAC中各节点的负荷及连接数情况,将新的请求分配到集群中负载较低、连接数较少的节点上来,这样就从根本上实现了数据库的负载均衡,并且使客户端连接的负载均衡与服务器端的负载均衡可以配合使用,互不影响。
每个集群节点的负载情况是由PMON进程来定期更新的。PMON进程每3秒会将集群中每个节点的负载信息及连接数写入service_register中,当节点的负载发生变化时,将会立刻通知监听程序,最后由监听程序来决定将新的客户端连接分配到哪个节点上,通过这种方式,RAC数据库实现了真正的负载均衡。
服务器端listener.ora配置如下(这里只写其中一个节点,每一个节点都需要更改节点名称与IP地址等,做类似的配置):
LISTENER_RAC1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.10)(PORT = 1521)(IP = FIRST))
)
)
SID_LIST_LISTENER_RAC1 =
(SID_LIST =
(SID_DESC =
(SID_NAME = rac.localdomain)
(ORACLE_HOME = /u01/app/oracle)
(PROGRAM = extprocrac)
)
)
如果想根据节点的连接数进行分配,可以加上这句话(本人认为加上这句话是没有任何实际的用途,如果想通过连接数来分配session的话,服务器端直接删除掉listener.ora文件即可实现):
PREFER_LEAST_LOADED_NODE=OFF
服务器端tnsnames.ora配置如下(每个节点都一样的配置):
RAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.8)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.7)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac.localdomain)
(FAILOVER_MODE =
(TYPE = session)
(METHOD = basic)
(RETRIES = 180)
(DELAY = 5)
)
)
)
RAC1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.8)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac.localdomain)
(INSTANCE_NAME = rac1)
)
)
RAC2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.7)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac.localdomain)
(INSTANCE_NAME = rac2)
)
)
LISTENERS_RAC=
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.8)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.7)(PORT = 1521))
)
LISTENERS_RAC1 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.8)(PORT = 1521))
)
LISTENERS_RAC2 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.7)(PORT = 1521))
)
接下来配置参数文件remote_listener
SQL> alter system set remote_listener='LISTENERS_RAC' scope=both sid='*';
然后重启监听服务:
[oracle@rac1 ~]$ srvctl stop listener
[oracle@rac1 ~]$ srvctl start listener
正确配置参数后,通过lsnrctl status命令应该要看到监听器上有rac1和rac2两个instance。
[oracle@rac1 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 27-NOV-2012 00:38:11
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 27-NOV-2012 00:12:58
Uptime 29 days 10 hr. 25 min. 13 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.8.10)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.8.8)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "rac.localdomain" has 2 instance(s).
Instance "rac1", status READY, has 2 handler(s) for this service...
Instance "rac2", status READY, has 1 handler(s) for this service...
Service "racXDB.localdomain" has 2 instance(s).
Instance "rac1", status READY, has 1 handler(s) for this service...
Instance "rac2", status READY, has 1 handler(s) for this service...
The command completed successfully
这样,服务器端的负载均衡就配置完成了。
服务器端的负载均衡主要是通过REMOTE_LISTENER参数控制的,在连接被定位到某个接点后,系统会根据REMOTE_LISTENER考虑多个接点的负载,把连接再次分发出去,这个和客户端的load balance是可以结合在一起混合使用的,互不干扰。
转载于:https://blog.51cto.com/kazake/1101048