node1的服务名
SQL> show parameter service

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string orcl
SQL> select instance_number,instance_name,status from v$instance;

INSTANCE_NUMBER INSTANCE_NAME STATUS
--------------- ---------------- ------------
1 orcl1 OPEN

node2的服务名
SQL> select instance_number,instance_name,status from v$instance;

INSTANCE_NUMBER INSTANCE_NAME STATUS
--------------- ---------------- ------------
2 orcl2 OPEN

SQL> show parameter service

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string ORCL

使用srvctl 来配置fail_over
[oracle@uprac01 ~]$ srvctl add service -d orcl -s orcl_TAF -r orcl1,orcl2
[oracle@uprac01 ~]$ srvctl start service -d orcl -s orcl_TAF
[oracle@uprac01 ~]$ srvctl modify service -d orcl -s orcl_TAF -q TRUE -P BASIC -e SELECT

发现新多了
[grid@uprac01 ~]$ crs_stat
NAME=ora.orcl.orcl_taf.svc
TYPE=ora.service.type
TARGET=ONLINE
STATE=ONLINE on uprac01

客户端tnsnames.ora中添加如下信息，主要是利用新的服务名orcl_TAF连接数据库
TEST_TAF =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.46)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl_TAF)
)
)

node1的服务名
SQL> select instance_number,instance_name,status from v$instance;

INSTANCE_NUMBER INSTANCE_NAME STATUS
--------------- ---------------- ------------
1 orcl1 OPEN

SQL> show parameter service

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string orcl, orcl_TAF

node2的服务名
SQL> select instance_number,instance_name,status from v$instance;

INSTANCE_NUMBER INSTANCE_NAME STATUS
--------------- ---------------- ------------
2 orcl2 OPEN

SQL> show parameter service

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string orcl, orcl_TAF

故障转移验证

利用客户端连接
[oracle@hkhydb ~]$ sqlplus scott/oracle@test_TAF

SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 18 13:40:36 2014

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> set linesize 160
SQL> select instance_number,instance_name,host_name,status from v$instance;

INSTANCE_NUMBER INSTANCE_NAME HOST_NAME STATUS
--------------- ---------------- -------------------------------- ------------
2 orcl2 uprac02.uni-top.com OPEN

SQL> select sid,serial#,failover_type,failover_method,failed_over from v$session
where username='SCOTT';

SID SERIAL# FAILOVER_TYPE FAILOVER_M FAI
---------- ---------- ------------- ---------- ---
1147 39419 NONE NONE NO

--就在下面的查询正在进行时，以sys用户打开一个session，然后执行shutdown abort
SQL> show user
USER 为 "SYS"
SQL> select instance_number,instance_name,status from v$instance;

INSTANCE_NUMBER INSTANCE_NAME STATUS
--------------- ---------------- ------------
2 orcl2 OPEN

SQL> shutdown abort;
ORACLE 例程已经关闭。

SQL> select count(1) from --虽然node2已经shutdown abort但是会故障转移到node1 查询会继续进行
(
select * from dba_source
union
select * from dba_source
union
select * from dba_source
union
select * from dba_source
union
select * from dba_source
union
select * from dba_source
) 2 3 4 5 6 7 8 9 10 11 12 13 14
15 ;

COUNT(1)
----------
638685

发现已经故障转移，sid和serial#，failed_over都变了
SQL> select sid,serial#,failover_type,failover_method,failed_over from v$session
where username='SCOTT'; 2

SID SERIAL# FAILOVER_TYPE FAILOVER_M FAI
---------- ---------- ------------- ---------- ---
1711 6903 SELECT BASIC YES

连接已经有node2故障转移到node1
SQL> select instance_number,instance_name,host_name,status from v$instance;

INSTANCE_NUMBER INSTANCE_NAME HOST_NAME STATUS
--------------- ---------------- ---------------------------- -------------------
1 orcl1 uprac01.uni-top.com OPEN

下面dbca方法创建fail_over转载自：http://blog.csdn.net/leshami/article/details/6791667

  配置故障转移
   负载均衡是用于实现基于连接的负载均衡，但不能解决节点是否可用，一旦一个节点损坏，已成功连接的客户端并不能转移到其他正常服务的
   实例中。而故障转移功能则使得该功能得以实现。可以使用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中多出了以下内容，注意是各个节点都会多出以下内容

[sql] view plain copy print ?

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] view plain copy print ?

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] view plain copy print ?

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中

[sql] view plain copy print ?

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] view plain copy print ?

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服务名登陆

[sql] view plain copy print ?

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] view plain copy print ?

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原则来执行，大部分情况需要回滚事务。

ITPub博客

11g R2 RAC 故障转移_使用srvctl或dbca 来配置fail_over

最新文章