[20130522]service以及一些falover的测试.txt
以前我记得我第一次安装rac的时候,那个时候许多东西不懂,基本按照人家写的文档安装,其中的辛苦只有自己知道.
对于service名之类的东西,更加不理解.
直到看一些相关书籍,才慢慢一点点理解oracle服务以及falover的含义.
比如在rac中.
我可以定义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登录:
--如果使用40g
--说明一下,在sql提示符前面加40G表示使用falover的方式连接,不再另外说明.
40G_SQL> select * from dba_objects ;
...
--由于输出信息很多,要等待一段时间.这个时候切换到服务器重启数据库.
--从客户端看,你可以发现输出停止,等到数据库open后,可以正常使用以及输出.
--可以发现sid发生了变化.另外FAILED_OVER no=>yes.
--而使用40连接就没有这个特性.
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
--可以发现服务名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;
/
--使用40连接登录:
--同样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;
/
以前我记得我第一次安装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
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
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
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
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
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
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
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
