[20130522]service以及一些falover的测试.txt

以前我记得我第一次安装rac的时候,那个时候许多东西不懂,基本按照人家写的文档安装,其中的辛苦只有自己知道.
对于service名之类的东西,更加不理解.

直到看一些相关书籍,才慢慢一点点理解oracle服务以及falover的含义.

比如在rac中.

$ ./dba_crs.perl

HA Resource                                  TYPE        Target      State

---------------------------------------------------------------------------------------

ora.rac1.LISTENER_rac1.lsnr            application       ONLINE      ONLINE on rac1

ora.rac1.gsd                           application       ONLINE      ONLINE on rac1

ora.rac1.ons                           application       ONLINE      ONLINE on rac1

ora.rac1.vip                           application       ONLINE      ONLINE on rac1

ora.rac2.LISTENER_rac2.lsnr            application       ONLINE      ONLINE on rac2

ora.rac2.gsd                           application       ONLINE      ONLINE on rac2

ora.rac2.ons                           application       ONLINE      ONLINE on rac2

ora.rac2.vip                           application       ONLINE      ONLINE on rac2

ora.orcl.db                            application       ONLINE      ONLINE on rac1

ora.orcl.orcl1.inst                    application       ONLINE      ONLINE on rac1

ora.orcl.orcl2.inst                    application       ONLINE      ONLINE on rac2

ora.orcl.orcl_12.cs                    application       ONLINE      ONLINE on rac1

ora.orcl.orcl_12.orcl1.srv             application       ONLINE      ONLINE on rac1

ora.orcl.orcl_21.cs                    application       ONLINE      ONLINE on rac1

ora.orcl.orcl_21.orcl2.srv             application       ONLINE      ONLINE on rac2

ora.orcl.orcl_taf.cs                   application       ONLINE      ONLINE on rac1

ora.orcl.orcl_taf.orcl1.srv            application       ONLINE      ONLINE on rac1

ora.orcl.orcl_taf.orcl2.srv            application       ONLINE      ONLINE on rac2

我可以定义orcl_12服务在rac1上运行,而orcl_21服务在rac2上运行.有问题的时候可以自动切换到对方的机器.
但是10g上有点毛病,机器恢复正常后自己不会切换回来.需要手工切换.

--实际上这些东西也可以运用到单实例中.我自己做一些测试:
--配置客户端连接如下:
40 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.40)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test.com)
    )
  )
 
40G =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.40)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test.com)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

 

1.从client登录:

SQL> @ver

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

--如果使用40

SQL> column service_name format a30

SQL> select SERVICE_NAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER from v$session where sid  in ( select sid from v$mystat where rownum<=1);

SERVICE_NAME                   FAILOVER_TYPE FAILOVER_M FAI

------------------------------ ------------- ---------- ---

test.com                       NONE          NONE       NO

--如果使用40g

SQL> column service_name format a30

SQL> select SERVICE_NAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER from v$session where sid  in ( select sid from v$mystat where rownum<=1);

SERVICE_NAME                   FAILOVER_TYPE FAILOVER_M FAI

------------------------------ ------------- ---------- ---

test.com                       SELECT        BASIC      YES

--说明一下,在sql提示符前面加40G表示使用falover的方式连接,不再另外说明.

40G_SQL> select * from dba_objects ;
...
--由于输出信息很多,要等待一段时间.这个时候切换到服务器重启数据库.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size                  2228784 bytes

Variable Size            1006636496 bytes

Database Buffers          587202560 bytes

Redo Buffers                7344128 bytes

Database mounted.

Database opened.

--从客户端看,你可以发现输出停止,等到数据库open后,可以正常使用以及输出.

40G_SQL> select sid,SERVICE_NAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER from v$session where sid  in ( select sid from v$mystat where rownum<=1);

       SID SERVICE_NAME  FAILOVER_TYPE FAILOVER_M FAI

---------- ------------- ------------- ---------- ---

         6 test.com      SELECT        BASIC      NO 

40G_SQL> select * from dba_objects ;

....

40G_SQL> select sid,SERVICE_NAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER from v$session where sid  in ( select sid from v$mystat where rownum<=1);

       SID SERVICE_NAME  FAILOVER_TYPE FAILOVER_M FAI

---------- ------------- ------------- ---------- ---

         7 test.com      SELECT        BASIC      YES

--可以发现sid发生了变化.另外FAILED_OVER no=>yes.


--而使用40连接就没有这个特性.

40_SQL> select sid,SERVICE_NAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER from v$session where sid  in ( select sid from v$mystat where rownum<=1);

       SID SERVICE_NAME  FAILOVER_TYPE FAILOVER_M FAI

---------- ------------- ------------- ---------- ---

       133 test.com      NONE          NONE       NO

ERROR:
ORA-03113: end-of-file on communication channel
Process ID: 21766
Session ID: 133 Serial number: 5
400 rows selected.

2.以上我是在客户端设置,很明显要大量修改客户端的tnsnames.ora配置是不可行的,这个时候我们可以配置服务端的failover设置.

SQL> column FAILOVER_METHOD format a20
SQL> column FAILOVER_TYPE format a20
SQL> column FAILOVER_RETRIES format 999999
SQL> column FAILOVER_delay format 999999

SQL> select name,failover_method,failover_type,failover_retries,failover_delay from dba_services where name='test.com';

NAME                 FAILOVER_METHOD      FAILOVER_TYPE        FAILOVER_RETRIES FAILOVER_DELAY

-------------------- -------------------- -------------------- ---------------- --------------

test.com

--可以发现服务名test.com没有配置failover.
BEGIN
   DBMS_SERVICE.modify_service (service_name          => 'test.com',
                                failover_method       => DBMS_SERVICE.failover_method_basic,
                                failover_type         => DBMS_SERVICE.failover_type_select,
                                failover_retries      => 180,
                                failover_delay        => 5
                               );
END;
/

SQL> select name,failover_method,failover_type,failover_retries,failover_delay from dba_services where name='test.com';

NAME                 FAILOVER_METHOD      FAILOVER_TYPE        FAILOVER_RETRIES FAILOVER_DELAY

-------------------- -------------------- -------------------- ---------------- --------------

test.com             BASIC                SELECT                            180              5

--使用40连接登录:

40_SQL> select sid,SERVICE_NAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER from v$session where sid  in ( select sid from v$mystat where rownum<=1);

       SID SERVICE_NAME  FAILOVER_TYPE FAILOVER_M FAI

---------- ------------- ------------- ---------- ---

        69 test.com      SELECT        BASIC      NO

40_SQL> select * from dba_objects ;

..

--再停止数据库启动数据库,可以发现连接hang再那里,启动后输出正常.

40_SQL> select sid,SERVICE_NAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER from v$session where sid  in ( select sid from v$mystat where rownum<=1);

       SID SERVICE_NAME  FAILOVER_TYPE FAILOVER_M FAI

---------- ------------- ------------- ---------- ---

        71 test.com      SELECT        BASIC      YES

--同样sid也发生了变化.另外FAILED_OVER no=>yes.

3.如果client有事务没有完成,只能退出回话.大家有兴趣自己可以测试.

收尾工作:

BEGIN
   DBMS_SERVICE.modify_service (service_name          => 'test.com',
                                failover_method       => DBMS_SERVICE.failover_method_none,
                                failover_type         => DBMS_SERVICE.failover_type_none,
                                failover_retries      => 180,
                                failover_delay        => 5
                               );
END;
/

SQL> select name,failover_method,failover_type,failover_retries,failover_delay from dba_services where name='test.com';

NAME                 FAILOVER_METHOD      FAILOVER_TYPE        FAILOVER_RETRIES FAILOVER_DELAY

-------------------- -------------------- -------------------- ---------------- --------------

test.com             NONE                 NONE