配置 RAC 负载均衡与故障转移

                    Oracle负载均衡主要是指新会话连接到RAC数据库时,如何判定这个新的连接要连到哪个节点进行工作?通常情况下,负载均衡分为客户端负载
均衡与服务器端负载均衡。客户端负载均衡通常是在客户端的tnsnames.ora中多添加一个链接地址以及LOAD_BALANCE与failover参数。而服务器
端的负载均衡则相对复杂,下面具体描述服务器端负载均衡。
    
一、负载均衡
    注意这里的负载均衡指的是连接的负载均衡,即客户可以随机从不同的实例中连接到数据库
     1.配置tnsnames.ora使得该文件中包含如下全部内容:       
# LISTENERS_DEVDB DEVDB是数据库名,可以使用netmgr,netca编辑或直接使用Vim创建LISTENERS_DEVDB =                                                             (ADDRESS_LIST =                                                            (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.robinson.com)(PORT = 1521))    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.robinson.com)(PORT = 1521))     )                                                                         
    2.配置参数文件remote_listener         
SQL> alter system set remote_listener='LISTENERS_DEVDB' scope=both sid='*'; 
    3.需要配置连接描述信息的两个IP地址、端口号、以及load_balance子项为yes (主要是load_balance子项)      
DEVDB =                                                                         (DESCRIPTION =                                                               (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.robinson.com)(PORT = 1521))      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.robinson.com)(PORT = 1521))      (LOAD_BALANCE = yes)                                                         (CONNECT_DATA =                                                                (SERVER = DEDICATED)                                                         (SERVICE_NAME = devdb.robinson.com)                                        )                                                                             )                                                                           
    4.查看侦听器的状态,从下面可以看到devdb.robinson.com服务中有两个实例为其提供服务     
[oracle@rac2 ~]$ lsnrctl status                                                 Service "devdb.robinson.com" has 2 instance(s).                                 Instance "devdb1", status READY, has 1 handler(s) for this service...         Instance "devdb2", status READY, has 2 handler(s) for this service...      
    5.测试负载均衡
    使用shell脚本来进行测试负载均衡     
--编辑TestLoadBalance.sh                                                                           #!/bin/bash                                                                                      #Usage: TestLoadBalance devdb 1000                                                               count=0                                                                                          while [ $count -lt $2 ]   # Set up a loop control                                                do                        # Begin the loop                                                        count='expr $count + 1' # Increment the counter                                                 sqlplus -s usr1/usr1pwd@$1 @TestLoadBalance.sql  # Connect instance and execute sql statement   sleep 1                                                                                        done                                                                                                                                                                                              --TestLoadBalance.sql 脚本                                                                         col instance_name format a30                                                                     select instance_name from v$instance;                                                                                                                                                             --实施测试                                                                                         ./TestLoadBalance.sh devdb 1000                                                                                                                                                                  --查看结果                                                                                         SQL> select inst_id,count(1) from gv$instance group by inst_d;                                                                                                                                                    INST_ID       COUNT(1)                                                                           ----------    ----------                                                                         devdb1        446                                                                                devdb2        554                                                                               
二、配置故障转移
    负载均衡是用于实现基于连接的负载均衡,但不能解决节点是否可用,一旦一个节点损坏,已成功连接的客户端并不能转移到其他正常服务的
    实例中。而故障转移功能则使得该功能得以实现。可以使用srvctl 和dbca来创建服务。下面使用dbca来创建一个新的服务,客户端连接到实
    例后,对故障实现透明切换。
    
     1.配置故障转移服务
        在节点rac1使用oracle帐户启动dbca工具,
            a.选择 Oracle Real Application Clusters database
            b.选择 Services Management
            c.集群数据库列表:单击 Next。
            d.数据库服务:单击 Add。
                添加服务:输入sales。
                选择 devdb1 作为首选实例。
                选择 devdb2 作为可用实例。
                TAF 策略:选择 Basic。
                单击 Finish。
            e.数据库配置助手:单击 No 退出。
        配置完毕后,在tnsnames.ora中多出了以下内容,注意是各个节点都会多出以下内容           
SALES =                                                                      (DESCRIPTION =                                                            (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.robinson.com)(PORT = 1521))   (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.robinson.com)(PORT = 1521))   (LOAD_BALANCE = yes)                                                      (CONNECT_DATA =                                                             (SERVER = DEDICATED)                                                      (SERVICE_NAME = sales.robinson.com)                                       (FAILOVER =      --failover_mode是实现故障转移的关键选项                 (TYPE = SELECT)                                                          (METHOD = BASIC)   --TAF 策略:此处当前为 Basic                          (RETRIES = 180)                                                          (DELAY = 5)                                                               )                                                                       )                                                                          )                                                                        
        同时参数service_names会多出一个服务名,位于在配置数据库服务时选择的首选实例中      
SQL> select instance_name from v$instance;                                                                                                                          INSTANCE_NAME                                                                     ----------------                                                                  devdb1                                                                                                                                                              SQL> show parameter service_names                                                                                                                                   NAME                                 TYPE        VALUE                            ------------------------------------ ----------- ------------------------------   service_names                        string      devdb.robinson.com, sales                                                                                          SQL> select instance_name from v$instance;                                                                                                                          INSTANCE_NAME                                                                     ----------------                                                                  devdb2                                                                                                                                                              SQL> show parameter service                                                                                                                                         NAME                                 TYPE        VALUE                            ------------------------------------ ----------- ------------------------------   service_names                        string      devdb.robinson.com               
        使用srvctl工具也可以看到该服务已经正常开始提供服务       
SQL> ho srvctl status service -d devdb -s sales                            Service sales is running on instance(s) devdb1                                                                                                        SQL> ho lsnrctl status                                                                                                                                Service "sales.robinson.com" has 1 instance(s).   --sales正常提供服务        Instance "devdb1", status READY, has 2 handler(s) for this service...    
    2.实现故障转移
        下面使用帐户usr1,服务名sales从Windows客户端来登陆,注意要配置好客户端tnsnames,可以将服务器sales项内容全部复制到客户端tnsnames.ora中     
C:\>sqlplus usr1/usr1@sales                                                                       SQL> col host_name format a20                                                                     SQL> select instance_number ins_no,instance_name,host_name,status from v$instance;                                                                                                                   INS_NO INSTANCE_NAME    HOST_NAME            STATUS                                             ---------- ---------------- -------------------- ------------                                        1 devdb1           rac1.robinson.com    OPEN                                                 SQL> select failover_type,failover_method,failed_over from v$session                                2  where username='USR1';                                                                                                                                                                         FAILOVER_TYPE FAILOVER_M FAI                                                                      ------------- ---------- ---                                                                      SELECT        BASIC      NO                                                                                                                                                                         --从其它会话使用sys帐户登陆到crm 并关闭该实例                                                    SQL> show user;                                                                                   USER is "SYS"                                                                                     SQL> select instance_name from v$instance;                                                                                                                                                          INSTANCE_NAME                                                                                     ----------------                                                                                  devdb1                                                                                                                                                                                              SQL> shutdown abort                                                                               ORACLE instance shut down.                                                                                                                                                                          --从先前登陆到sales的会话中验证会话故障切换功能                                                   SQL> select instance_number ins_no,instance_name,host_name,status from v$instance;                                                                                                                   INS_NO INSTANCE_NAME    HOST_NAME            STATUS                                             ---------- ---------------- -------------------- ------------                                           2 devdb2           rac2.robinson.com    OPEN                                                                                                                                               SQL> select failover_type,failover_method,failed_over from v$session                                2  where username='USR1'--第3列显示的为yes,也表明经过故障切换后提供的服务                                                                                                                     FAILOVER_TYPE FAILOVER_M FAI                                                                      ------------- ---------- ---                                                                      SELECT        BASIC      YES                                                                                                                                                                      --由下面的查询中可以看到服务名sales被添加到可用实例devdb2,节点rac2 的service_names参数中         SQL> select instance_name from v$instance;                                                                                                                                                          INSTANCE_NAME                                                                                     ----------------                                                                                  devdb2                                                                                                                                                                                              SQL> show parameter service                                                                                                                                                                         NAME                                 TYPE        VALUE                                            ------------------------------------ ----------- ------------------------------                   service_names                        string      devdb.robinson.com, sales                      
    3.重新定位故障转移服务到首选实例
        对于首选实例从故障中恢复后,需要手动来重新定位到首选实例       
SQL> startup  --启动devdb1                                                         SQL> show parameter service_names                                                                                                                                     NAME                                 TYPE        VALUE                             ------------------------------------ ----------- ------------------------------    service_names                        string      devdb.robinson.com                                                                                                   SQL> ho srvctl relocate service -d devdb -s sales -i devdb2 -t devdb1                                                                                                 SQL> show parameter service_names                                                                                                                                     NAME                                 TYPE        VALUE                             ------------------------------------ ----------- ------------------------------    service_names                        string      devdb.robinson.com, sales         
    4.DML故障转移(不同于DQL,因此单独列出)
        使用Windows客户端通过sales服务名登陆       
C:\>sqlplus usr1/usr1@sales                                                                                                                                               SQL> show user;                                                                      USER is "USR1"                                                                       SQL> create table tb_temp (id int,ename varchar2(20)) tablespace tbs1;                                                                                                   Table created.                                                                                                                                                            SQL> insert into tb_temp                                                               2  select 1,'Robinson' from dual                                                     3  union all                                                                         4  select 2,'Jackson'  from dual;                                                                                                                                       2 rows created.                                                                                                                                                           SQL> commit;                                                                                                                                                              Commit complete.                                                                                                                                                          SQL> select * from tb_temp;                                                                                                                                                 ID ENAME                                                                         ---------- --------------------                                                         1 Robinson                                                                         2 Jackson                                                                                                                                                            SQL> delete from tb_temp;                                                                                                                                                 2 rows deleted.                                                                                                                                                           SQL> select * from tb_temp;                                                                                                                                               no rows selected                                                                     
        从另一个会话中使用sysdba关闭devdb1(shutdown abort)
        再在刚刚执行表创建的会话中查询记录,收到提示,事务必须被回滚    
SQL> select * from tb_temp;                                                select * from tb_temp                                                      *                                                                          ERROR at line 1:                                                           ORA-25402: transaction must roll back                                                                                                                 SQL> rollback;                                                                                                                                        Rollback complete.                                                        SQL> select * from tb_temp;                                                                                                                             ID ENAME                                                               ---------- --------------------                                               1 Robinson                                                               2 Jackson                                                                                                                                        SQL> select failover_type,failover_method,failed_over from v$session         2  where username='USR1';                                                                                                                           FAILOVER_TYPE FAILOVER_M FAI                                               ------------- ---------- ---                                               SELECT        BASIC      YES                                               
        总结:对于DML 操作在实现故障转移时,将严格按照ACID原则来执行,大部分情况需要回滚事务。   


           

再分享一下我老师大神的人工智能教程吧。零基础!通俗易懂!风趣幽默!还带黄段子!希望你也加入到我们人工智能的队伍中来!https://blog.csdn.net/jiangjunshow

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值