11GR1 RAC关于service/TAF的实验们
来源:互联网 发布:原油ela数据 编辑:程序博客网 时间:2024/04/29 00:43
11GR1 RAC关于service/TAF的实验们
上一篇 /下一篇 2010-09-08 22:16:00 / 个人分类:RAC
查看( 359 ) / 评论( 3 )
这几天在培训11GR1的RAC,基本跟10G差不多。
做了一些关于service和TAF的实验,特此记录一下。
实验环境为一个两节点的RAC,11.1.0.6.0 @ linux @ x86。
数据库名为RDBZ,两个节点SID分别为RDBZ1和RDBZ2。
Service就是一组做同样事情的sessions。
这里我们用到的srvctl命令去添加,启动,更改service,其实说白了,就是让CRS去帮我们修改service_names这个系统参数罢了,不过可以帮我们添加到CRS资源里。
如下实验中有些有趣的地方。
1.默认的service_names会消失
当RAC刚建好,有个默认的service_names为db name。如:
on RDBZ1:
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string RDBZ
添加service "hao",preferred节点为RDBZ1,available节点为RDBZ2:
srvctl add service -d RDBZ -s hao -r RDBZ1 -a RDBZ2
添加service "zhu",preferred节点为RDBZ2,available节点为RDBZ1:
srvctl add service -d RDBZ -s zhu -r RDBZ2 -a RDBZ1
不过当我马上在节点RDBZ1上启动一个新的service "hao","RDBZ"这个service就不在了。
srvctl start service -d RDBZ -s hao
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string hao
在节点RDBZ2上同理:
on RDBZ2:
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string RDBZ
srvctl start service -d RDBZ -s zhu
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string zhu
2.即使设置了TAF,但是如果srvctl relocate service -f 仍然会让session接受到报错,因为这时正常的断掉sessions。
首先设置tnsnames分别关联service hao和zhu,并设置了TAF。
hao =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = eg4835-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = eg4890-vip)(PORT = 1521))
(LOAD_BALANCE = on)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hao)
(FAILOVER_MODE=
(TYPE=SESSION)
(METHOD=BASIC)
(RETRIES=180)
(DELAY=5)
)
)
)
zhu =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = eg4835-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = eg4890-vip)(PORT = 1521))
(LOAD_BALANCE = on)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = zhu)
(FAILOVER_MODE=
(TYPE=SESSION)
(METHOD=BASIC)
(RETRIES=180)
(DELAY=5)
)
)
)
如果关掉节点RDBZ1上的instance,service "hao"会自动漂移到RDBZ2上。
并且有趣的事情是,第一点消失的service "RDBZ" 此时回来了!
srvctl stop instance -d RDBZ -i RDBZ1
on RDBZ2:
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string zhu,RDBZ, hao
如果重启节点RDBZ2的instance,service "hao"此时不会飘回到RDBZ1上。
此时如果有需要,可以手工relocate service回来。
有趣的事是,此时在节点RDBZ1上,消失的service "RDBZ"也回来了。
on RDBZ2:
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string zhu, RDBZ, hao
on RDBZ1:
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string RDBZ
打开session A:
[oracle@eg4835 ~]$ sqlplus[email=hao/hao@hao]hao/hao@hao[/email]
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
RDBZ2
此时如果手工relocate service 到节点RDBZ1并且使用-f,那么会强制断开在RDBZ2上属于此service的session。
此时虽然我们对这个tnsname "hao"设了TAF的,但是当接收到这样的命令是不管用的,照样会接收到错误信息,然后再次执行就回到了节点RDBZ1。
on RDBZ1:
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string hao
on RDBZ2:
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string zhu, RDBZ
Session A:
SQL> select instance_name from v$instance;
select instance_name from v$instance
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 3686
Session ID: 111 Serial number: 179
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
RDBZ1
3.即使是select level的TAF,遇到relocate service -f 也会报错。如果不加-f,即不会影响当前session,只会影响之后进来的session。
hao =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = eg4835-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = eg4890-vip)(PORT = 1521))
(LOAD_BALANCE = on)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hao)
(FAILOVER_MODE=
(TYPE=SELECT)
(METHOD=BASIC)
(RETRIES=180)
(DELAY=5)
)
)
)
sqlplus[email=hao/hao@hao]hao/hao@hao[/email]
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
RDBZ1
将service "hao" 从RDBZ1迁移到RDBZ2,加了-f也会影响select level的TAF:
srvctl relocate service -d RDBZ -s hao -i RDBZ1 -t RDBZ2 -f
SQL> select instance_name from v$instance;
select instance_name from v$instance
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 29980
Session ID: 125 Serial number: 5172
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
RDBZ2
再将service "hao"迁回到RDBZ1,但是不加-f:
srvctl relocate service -d RDBZ -s hao -i RDBZ2 -t RDBZ1
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
RDBZ2
4.srvctl modify service即时生效,不需要重启service。
Session A:
[oracle@eg4835 ~]$ sqlplus[email=hao/hao@hao]hao/hao@hao[/email]
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
RDBZ1
将service "hao" 修改为只有一个preferred node=RDBZ2,没有available node:
[oracle@eg4835 ~]$ srvctl modify service -d RDBZ -s hao -n -i RDBZ2
确认"hao"跑在RDBZ2上了:
[oracle@eg4835 ~]$ srvctl status service -d RDBZ -s hao -v
Service hao is running on instance(s) RDBZ2
新进session就来到了RDBZ2上了:
[oracle@eg4835 ~]$ sqlplus[email=hao/hao@hao]hao/hao@hao[/email]
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
RDBZ2
此时修改"hao"到RDBZ1上:
srvctl modify service -d RDBZ -s hao -n -i RDBZ1
[oracle@eg4835 ~]$ srvctl status service -d RDBZ -s hao -v
Service hao is running on instance(s) RDBZ1
Session A此时再执行语句会连续遇到三种不同错误,必须要重新连接了:
SQL> /
select instance_name from v$instance
*
ERROR at line 1:
ORA-24794: no active DTP service found
SQL> /
select instance_name from v$instance
*
ERROR at line 1:
ORA-00028: your session has been killed
SQL> /
select instance_name from v$instance
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 27954
Session ID: 120 Serial number: 3078
5.srvctl modify service会影响之前连接的session。
先让"hao"只有preferred=RDBZ1,不能跑在RDBZ2:
-bash-3.00$ srvctl modify service -d RDBZ -s hao -n -i RDBZ1
-bash-3.00$ srvctl config service -d RDBZ -s hao
hao PREF: RDBZ1 AVAIL:
然后此时连接session A:
-bash-3.00$ sqlplus[email=hao/hao@hao]hao/hao@hao[/email]
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
RDBZ1
然后将"hao"增加available=RDBZ2:
-bash-3.00$ srvctl modify service -d RDBZ -s hao -n -i RDBZ1 -a RDBZ2
-bash-3.00$ srvctl config service -d RDBZ -s hao
hao PREF: RDBZ1 AVAIL: RDBZ2
这时我们看看在之前连进来的session A是否会自动failover到RDBZ2上:
srvctl stop instance -d RDBZ -i RDBZ1
SQL> /
INSTANCE_NAME
----------------
RDBZ2
成功了,modify service其实可以影响之前连接的session,证明了一点,service的信息时记录在server端的。
6.关于TAF的preconnect设置的更多实验
6.1 一种错误的preconnect写法:
preconnect设置的TAF,可以让一个连接,再额外地生成一个备用连接,已能够更快的failover。
于是先展示一种错误的preconnect的tnsnames设置:
这里我没有设置backup选项,只简单的加上METHOD=PRECONNECT,从高可用上来看是不合适的,但是从语法上是不报错的:
hao =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = eg4835-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = eg4890-vip)(PORT = 1521))
(LOAD_BALANCE = on)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hao)
(FAILOVER_MODE=
(TYPE=SELECT)
(METHOD=PRECONNECT)
(RETRIES=180)
(DELAY=5)
)
)
)
此时"hao"preferred=RDBZ1,available=RDBZ2,并且跑在RDBZ2上:
[oracle@eg4835 ~]$ srvctl status service -d RDBZ -s hao
Service hao is running on instance(s) RDBZ2
[oracle@eg4835 ~]$ srvctl config service -d RDBZ -s hao
hao PREF: RDBZ1 AVAIL: RDBZ2
sqlplus[email=hao/hao@hao]hao/hao@hao[/email]
on RDBZ2:
SQL> select service_name,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER from v$session
2 where service_name='hao';
SERVICE_NAME FAILOVER_TYPE FAILOVER_M FAI
------------------------------ ------------- ---------- ---
hao SELECT PRECONNECT NO
hao NONE NONE NO
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
RDBZ2
on RDBZ1:
SQL> select service_name,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER from v$session
2 where service_name='hao';
no rows selected
如上,如果不设置backup的tnsname,并且设置了preconnect,那么会在当前的节点RDBZ2上一个sessions导致了两个sessions。
(其中一个显示"NONE"的session就是备用session。)
这样会造成当前节点对PGA的需求乘以2。所以一定要避免此种错误写法。
但是,由于service "hao"的available node=RDBZ1,所以即使这样错误的TAF preconnect写法,但也能提供一定的高可用。
当我们把节点RDBZ2关闭,session也跑到了RDBZ1上了。
-bash-3.00$ srvctl stop instance -d RDBZ -i RDBZ2
SQL> /
SERVICE_NAME FAILOVER_TYPE FAILOVER_M FAI
------------------------------ ------------- ---------- ---
hao SELECT PRECONNECTYES
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
RDBZ1
-bash-3.00$ srvctl config service -d RDBZ -s hao
hao PREF: RDBZ1 AVAIL: RDBZ2
-bash-3.00$ srvctl status service -d RDBZ -s hao
Service hao is running on instance(s) RDBZ1
如上,当session被failover到RDBZ1后,只有一个FAILED_OVER=YES的session了。
6.2 正确的preconnect写法也有一个有趣的缺陷
hao =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = eg4835-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = eg4890-vip)(PORT = 1521))
(LOAD_BALANCE = on)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hao)
(FAILOVER_MODE=
(TYPE=SELECT)
(BACKUP=hao_PRECONNECT)
(METHOD=PRECONNECT)
(RETRIES=180)
(DELAY=5)
)
)
)
hao_PRECONNECT =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = eg4835-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = eg4890-vip)(PORT = 1521))
(LOAD_BALANCE = on)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hao_PRECONNECT)
)
)
如上是正确的preconnect TAF的写法,需要有一个backup的tnsname -- hao_PRECONNECT。
当我们连接时,假设连上的是RDBZ1:
[oracle@eg4835 ~]$ sqlplus[email=hao/hao@hao]hao/hao@hao[/email]
SQL> select service_name,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER,USERNAME from v$session
2 where USERNAME='HAO';
SERVICE_NAME FAILOVER_TYPE FAILOVER_M FAI USERNAME
------------------------------ ------------- ---------- --- ------------------------------
hao SELECT PRECONNECT NO HAO
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
RDBZ1
此时在RDBZ2上我看到了一个backup session:
on RDBZ2:
sqlplus / as sysdba
SQL> select service_name,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER,USERNAME from v$session
2 where USERNAME='HAO';
SERVICE_NAME FAILOVER_TYPE FAILOVER_M FAI USERNAME
------------------------------ ------------- ---------- --- ------------------------------
hao_PRECONNECT NONE NONE NO HAO
此时这个backup session使用的backup service是没有记录在我们的资源里的:
[oracle@eg4835 ~]$ srvctl status service -d RDBZ -s hao_PRECONNECT
PRKO-2017 : Service hao_PRECONNECT does not exist for database RDBZ.
on RDBZ2:
SQL> select NAME,ENABLED from dba_services where NAME='hao_PRECONNECT';
NAME ENA
---------------------------------------------------------------- ---
hao_PRECONNECT NO
此时关闭RDBZ1:
srvctl stop instance -d RDBZ -i RDBZ1
Session当然自动failover到RDBZ2了:
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
RDBZ2
SQL> select service_name,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER,USERNAME from v$session
2 where USERNAME='HAO';
SERVICE_NAME FAILOVER_TYPE FAILOVER_M FAI USERNAME
------------------------------ ------------- ---------- --- ------------------------------
hao SELECT PRECONNECT YES HAO
[oracle@eg4835 ~]$ srvctl status service -d RDBZ -s hao
Service hao is running on instance(s) RDBZ2
但是如果我又开启RDBZ1,关闭RDBZ2:
srvctl stop instance -d RDBZ -i RDBZ2
[oracle@eg4835 ~]$ srvctl config service -d RDBZ -s hao
hao PREF: RDBZ1 AVAIL: RDBZ2
[oracle@eg4835 ~]$ srvctl status service -d RDBZ -s hao
Service hao is running on instance(s) RDBZ1
SQL> select service_name,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER,USERNAME from v$session
2 where USERNAME='HAO';
(hung here)
此时前面的那个session hung住了,并没有如我所期望的那样会又failover回RDBZ1。
只有我再次重启RDBZ2,才有error信息出现,然后表明该session还停留在RDBZ2上的。
srvctl start instance -d RDBZ -i RDBZ2
SQL> select service_name,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER,USERNAME from v$session
2 where USERNAME='HAO';
select service_name,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER,USERNAME from v$session
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 19225
Session ID: 121 Serial number: 34
SQL> SQL> select service_name,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER,USERNAME from v$session
2 where USERNAME='HAO';
SERVICE_NAME FAILOVER_TYPE FAILOVER_M FAI USERNAME
------------------------------ ------------- ---------- --- ------------------------------
hao SELECT PRECONNECT YES HAO
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
RDBZ2
为什么呢?
此时我去查看show parameter service_names,发现其实RDBZ1上没有hao_PRECONNECT这个service,而RDBZ2上有。
所以为了解决两边都能不断的failover,我手工地在两个节点上都加上了这个backup services。
SQL> ALTER SYSTEM SET service_names='xxx','yyy','hao_PRECONNECT';
System altered.
通过实验,此时已经可以不断地failover了。
其实通过alert.log,我们通过srvctl操作service其实就是alter system set service_names的动作。
service failover也是如此。
但是由于preconnect的如上缺陷,我们可以考虑在每个节点上手工加上backup service来解决。
做了一些关于service和TAF的实验,特此记录一下。
实验环境为一个两节点的RAC,11.1.0.6.0 @ linux @ x86。
数据库名为RDBZ,两个节点SID分别为RDBZ1和RDBZ2。
Service就是一组做同样事情的sessions。
这里我们用到的srvctl命令去添加,启动,更改service,其实说白了,就是让CRS去帮我们修改service_names这个系统参数罢了,不过可以帮我们添加到CRS资源里。
如下实验中有些有趣的地方。
1.默认的service_names会消失
当RAC刚建好,有个默认的service_names为db name。如:
on RDBZ1:
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string RDBZ
添加service "hao",preferred节点为RDBZ1,available节点为RDBZ2:
srvctl add service -d RDBZ -s hao -r RDBZ1 -a RDBZ2
添加service "zhu",preferred节点为RDBZ2,available节点为RDBZ1:
srvctl add service -d RDBZ -s zhu -r RDBZ2 -a RDBZ1
不过当我马上在节点RDBZ1上启动一个新的service "hao","RDBZ"这个service就不在了。
srvctl start service -d RDBZ -s hao
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string hao
在节点RDBZ2上同理:
on RDBZ2:
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string RDBZ
srvctl start service -d RDBZ -s zhu
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string zhu
2.即使设置了TAF,但是如果srvctl relocate service -f 仍然会让session接受到报错,因为这时正常的断掉sessions。
首先设置tnsnames分别关联service hao和zhu,并设置了TAF。
hao =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = eg4835-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = eg4890-vip)(PORT = 1521))
(LOAD_BALANCE = on)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hao)
(FAILOVER_MODE=
(TYPE=SESSION)
(METHOD=BASIC)
(RETRIES=180)
(DELAY=5)
)
)
)
zhu =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = eg4835-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = eg4890-vip)(PORT = 1521))
(LOAD_BALANCE = on)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = zhu)
(FAILOVER_MODE=
(TYPE=SESSION)
(METHOD=BASIC)
(RETRIES=180)
(DELAY=5)
)
)
)
如果关掉节点RDBZ1上的instance,service "hao"会自动漂移到RDBZ2上。
并且有趣的事情是,第一点消失的service "RDBZ" 此时回来了!
srvctl stop instance -d RDBZ -i RDBZ1
on RDBZ2:
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string zhu,RDBZ, hao
如果重启节点RDBZ2的instance,service "hao"此时不会飘回到RDBZ1上。
此时如果有需要,可以手工relocate service回来。
有趣的事是,此时在节点RDBZ1上,消失的service "RDBZ"也回来了。
on RDBZ2:
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string zhu, RDBZ, hao
on RDBZ1:
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string RDBZ
打开session A:
[oracle@eg4835 ~]$ sqlplus[email=hao/hao@hao]hao/hao@hao[/email]
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
RDBZ2
此时如果手工relocate service 到节点RDBZ1并且使用-f,那么会强制断开在RDBZ2上属于此service的session。
此时虽然我们对这个tnsname "hao"设了TAF的,但是当接收到这样的命令是不管用的,照样会接收到错误信息,然后再次执行就回到了节点RDBZ1。
on RDBZ1:
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string hao
on RDBZ2:
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string zhu, RDBZ
Session A:
SQL> select instance_name from v$instance;
select instance_name from v$instance
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 3686
Session ID: 111 Serial number: 179
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
RDBZ1
3.即使是select level的TAF,遇到relocate service -f 也会报错。如果不加-f,即不会影响当前session,只会影响之后进来的session。
hao =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = eg4835-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = eg4890-vip)(PORT = 1521))
(LOAD_BALANCE = on)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hao)
(FAILOVER_MODE=
(TYPE=SELECT)
(METHOD=BASIC)
(RETRIES=180)
(DELAY=5)
)
)
)
sqlplus[email=hao/hao@hao]hao/hao@hao[/email]
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
RDBZ1
将service "hao" 从RDBZ1迁移到RDBZ2,加了-f也会影响select level的TAF:
srvctl relocate service -d RDBZ -s hao -i RDBZ1 -t RDBZ2 -f
SQL> select instance_name from v$instance;
select instance_name from v$instance
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 29980
Session ID: 125 Serial number: 5172
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
RDBZ2
再将service "hao"迁回到RDBZ1,但是不加-f:
srvctl relocate service -d RDBZ -s hao -i RDBZ2 -t RDBZ1
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
RDBZ2
4.srvctl modify service即时生效,不需要重启service。
Session A:
[oracle@eg4835 ~]$ sqlplus[email=hao/hao@hao]hao/hao@hao[/email]
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
RDBZ1
将service "hao" 修改为只有一个preferred node=RDBZ2,没有available node:
[oracle@eg4835 ~]$ srvctl modify service -d RDBZ -s hao -n -i RDBZ2
确认"hao"跑在RDBZ2上了:
[oracle@eg4835 ~]$ srvctl status service -d RDBZ -s hao -v
Service hao is running on instance(s) RDBZ2
新进session就来到了RDBZ2上了:
[oracle@eg4835 ~]$ sqlplus[email=hao/hao@hao]hao/hao@hao[/email]
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
RDBZ2
此时修改"hao"到RDBZ1上:
srvctl modify service -d RDBZ -s hao -n -i RDBZ1
[oracle@eg4835 ~]$ srvctl status service -d RDBZ -s hao -v
Service hao is running on instance(s) RDBZ1
Session A此时再执行语句会连续遇到三种不同错误,必须要重新连接了:
SQL> /
select instance_name from v$instance
*
ERROR at line 1:
ORA-24794: no active DTP service found
SQL> /
select instance_name from v$instance
*
ERROR at line 1:
ORA-00028: your session has been killed
SQL> /
select instance_name from v$instance
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 27954
Session ID: 120 Serial number: 3078
5.srvctl modify service会影响之前连接的session。
先让"hao"只有preferred=RDBZ1,不能跑在RDBZ2:
-bash-3.00$ srvctl modify service -d RDBZ -s hao -n -i RDBZ1
-bash-3.00$ srvctl config service -d RDBZ -s hao
hao PREF: RDBZ1 AVAIL:
然后此时连接session A:
-bash-3.00$ sqlplus[email=hao/hao@hao]hao/hao@hao[/email]
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
RDBZ1
然后将"hao"增加available=RDBZ2:
-bash-3.00$ srvctl modify service -d RDBZ -s hao -n -i RDBZ1 -a RDBZ2
-bash-3.00$ srvctl config service -d RDBZ -s hao
hao PREF: RDBZ1 AVAIL: RDBZ2
这时我们看看在之前连进来的session A是否会自动failover到RDBZ2上:
srvctl stop instance -d RDBZ -i RDBZ1
SQL> /
INSTANCE_NAME
----------------
RDBZ2
成功了,modify service其实可以影响之前连接的session,证明了一点,service的信息时记录在server端的。
6.关于TAF的preconnect设置的更多实验
6.1 一种错误的preconnect写法:
preconnect设置的TAF,可以让一个连接,再额外地生成一个备用连接,已能够更快的failover。
于是先展示一种错误的preconnect的tnsnames设置:
这里我没有设置backup选项,只简单的加上METHOD=PRECONNECT,从高可用上来看是不合适的,但是从语法上是不报错的:
hao =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = eg4835-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = eg4890-vip)(PORT = 1521))
(LOAD_BALANCE = on)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hao)
(FAILOVER_MODE=
(TYPE=SELECT)
(METHOD=PRECONNECT)
(RETRIES=180)
(DELAY=5)
)
)
)
此时"hao"preferred=RDBZ1,available=RDBZ2,并且跑在RDBZ2上:
[oracle@eg4835 ~]$ srvctl status service -d RDBZ -s hao
Service hao is running on instance(s) RDBZ2
[oracle@eg4835 ~]$ srvctl config service -d RDBZ -s hao
hao PREF: RDBZ1 AVAIL: RDBZ2
sqlplus[email=hao/hao@hao]hao/hao@hao[/email]
on RDBZ2:
SQL> select service_name,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER from v$session
2 where service_name='hao';
SERVICE_NAME FAILOVER_TYPE FAILOVER_M FAI
------------------------------ ------------- ---------- ---
hao SELECT PRECONNECT NO
hao NONE NONE NO
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
RDBZ2
on RDBZ1:
SQL> select service_name,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER from v$session
2 where service_name='hao';
no rows selected
如上,如果不设置backup的tnsname,并且设置了preconnect,那么会在当前的节点RDBZ2上一个sessions导致了两个sessions。
(其中一个显示"NONE"的session就是备用session。)
这样会造成当前节点对PGA的需求乘以2。所以一定要避免此种错误写法。
但是,由于service "hao"的available node=RDBZ1,所以即使这样错误的TAF preconnect写法,但也能提供一定的高可用。
当我们把节点RDBZ2关闭,session也跑到了RDBZ1上了。
-bash-3.00$ srvctl stop instance -d RDBZ -i RDBZ2
SQL> /
SERVICE_NAME FAILOVER_TYPE FAILOVER_M FAI
------------------------------ ------------- ---------- ---
hao SELECT PRECONNECTYES
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
RDBZ1
-bash-3.00$ srvctl config service -d RDBZ -s hao
hao PREF: RDBZ1 AVAIL: RDBZ2
-bash-3.00$ srvctl status service -d RDBZ -s hao
Service hao is running on instance(s) RDBZ1
如上,当session被failover到RDBZ1后,只有一个FAILED_OVER=YES的session了。
6.2 正确的preconnect写法也有一个有趣的缺陷
hao =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = eg4835-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = eg4890-vip)(PORT = 1521))
(LOAD_BALANCE = on)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hao)
(FAILOVER_MODE=
(TYPE=SELECT)
(BACKUP=hao_PRECONNECT)
(METHOD=PRECONNECT)
(RETRIES=180)
(DELAY=5)
)
)
)
hao_PRECONNECT =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = eg4835-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = eg4890-vip)(PORT = 1521))
(LOAD_BALANCE = on)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hao_PRECONNECT)
)
)
如上是正确的preconnect TAF的写法,需要有一个backup的tnsname -- hao_PRECONNECT。
当我们连接时,假设连上的是RDBZ1:
[oracle@eg4835 ~]$ sqlplus[email=hao/hao@hao]hao/hao@hao[/email]
SQL> select service_name,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER,USERNAME from v$session
2 where USERNAME='HAO';
SERVICE_NAME FAILOVER_TYPE FAILOVER_M FAI USERNAME
------------------------------ ------------- ---------- --- ------------------------------
hao SELECT PRECONNECT NO HAO
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
RDBZ1
此时在RDBZ2上我看到了一个backup session:
on RDBZ2:
sqlplus / as sysdba
SQL> select service_name,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER,USERNAME from v$session
2 where USERNAME='HAO';
SERVICE_NAME FAILOVER_TYPE FAILOVER_M FAI USERNAME
------------------------------ ------------- ---------- --- ------------------------------
hao_PRECONNECT NONE NONE NO HAO
此时这个backup session使用的backup service是没有记录在我们的资源里的:
[oracle@eg4835 ~]$ srvctl status service -d RDBZ -s hao_PRECONNECT
PRKO-2017 : Service hao_PRECONNECT does not exist for database RDBZ.
on RDBZ2:
SQL> select NAME,ENABLED from dba_services where NAME='hao_PRECONNECT';
NAME ENA
---------------------------------------------------------------- ---
hao_PRECONNECT NO
此时关闭RDBZ1:
srvctl stop instance -d RDBZ -i RDBZ1
Session当然自动failover到RDBZ2了:
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
RDBZ2
SQL> select service_name,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER,USERNAME from v$session
2 where USERNAME='HAO';
SERVICE_NAME FAILOVER_TYPE FAILOVER_M FAI USERNAME
------------------------------ ------------- ---------- --- ------------------------------
hao SELECT PRECONNECT YES HAO
[oracle@eg4835 ~]$ srvctl status service -d RDBZ -s hao
Service hao is running on instance(s) RDBZ2
但是如果我又开启RDBZ1,关闭RDBZ2:
srvctl stop instance -d RDBZ -i RDBZ2
[oracle@eg4835 ~]$ srvctl config service -d RDBZ -s hao
hao PREF: RDBZ1 AVAIL: RDBZ2
[oracle@eg4835 ~]$ srvctl status service -d RDBZ -s hao
Service hao is running on instance(s) RDBZ1
SQL> select service_name,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER,USERNAME from v$session
2 where USERNAME='HAO';
(hung here)
此时前面的那个session hung住了,并没有如我所期望的那样会又failover回RDBZ1。
只有我再次重启RDBZ2,才有error信息出现,然后表明该session还停留在RDBZ2上的。
srvctl start instance -d RDBZ -i RDBZ2
SQL> select service_name,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER,USERNAME from v$session
2 where USERNAME='HAO';
select service_name,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER,USERNAME from v$session
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 19225
Session ID: 121 Serial number: 34
SQL> SQL> select service_name,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER,USERNAME from v$session
2 where USERNAME='HAO';
SERVICE_NAME FAILOVER_TYPE FAILOVER_M FAI USERNAME
------------------------------ ------------- ---------- --- ------------------------------
hao SELECT PRECONNECT YES HAO
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
RDBZ2
为什么呢?
此时我去查看show parameter service_names,发现其实RDBZ1上没有hao_PRECONNECT这个service,而RDBZ2上有。
所以为了解决两边都能不断的failover,我手工地在两个节点上都加上了这个backup services。
SQL> ALTER SYSTEM SET service_names='xxx','yyy','hao_PRECONNECT';
System altered.
通过实验,此时已经可以不断地failover了。
其实通过alert.log,我们通过srvctl操作service其实就是alter system set service_names的动作。
service failover也是如此。
但是由于preconnect的如上缺陷,我们可以考虑在每个节点上手工加上backup service来解决。
相关阅读:
- 11GR1 RAC关于service/TAF的实验们
- 11GR1 RAC关于service/TAF的实验
- oracle-11gR2 RAC service Side TAF的配置
- Oracle 11gR2 RAC Service-Side TAF 配置示例
- Oracle 11gR2 RAC Service-Side TAF 配置示例
- Oracle 11gR2 RAC Service-Side TAF 配置示例
- 10g RAC 使用service实现taf
- RAC的TAF简单测试
- RAC的TAF简单测试
- Oracle 11gR2版本的Service创建和TAF方法
- Oracle 11gR2 RAC LoadBalancing and TAF
- Oracle 11gR2 RAC LoadBalancing and TAF
- Oracle 11G RAC环境的load balance + TAF配置 -- Alibaba DBA Team
- rac TAF理解
- rac taf配置
- ArcGIS地理数据库是否支持Oracle的RAC和TAF
- ORACLE11G RAC 应用分开到不同的实例上.TAF
- ArcGIS地理数据库是否支持Oracle的RAC和TAF 支持
- java编程心得(十三)——哈希表根据value值排序
- Oracle RAC 负载均衡和透明应用失败切换的配置和测试过程
- 如何在cmd命令行中查看、修改、删除与添加环境变量
- Restoration of the Permutation
- 什么是.9图片
- 11GR1 RAC关于service/TAF的实验们
- linux_shell教程_细说shell编程
- C# 的Attribute特性 与C#的宏定义
- 安装sysbench过程报错,解决办法
- 调试器工作原理
- 1G、2G、3G、4G是什么
- lucene series 1 document 文档 索引创建
- Code First 数据迁移常用命令
- java替换中英文