问题背景:
客户在一套集群上配置了两个服务名,两套业务分别在两个节点进行业务处理
但每当集群发生重启问题,则会导致服务名的漂移,需要手动将业务切换回去
操作:
切换命令如下
srvctl relocate service -d DB_NAME -s SERVICE_NAME -i old_instance_name -t new_instance_name
切换命令使用srvctl工具,对目标service进行relocate
-d 数据库名
-s 需要切换的service名
-i service当前在的实例名
-t 需要切换到的目标实例名
例如:需要切换test数据库的test_service,从节点1到节点2则需要执行
srvctl relocate service -d test -s test_servicr -i test1 -t test2
切换完成后进行会话查看,会发现,会话并没有主动切换过去
查看当前该服务连接的信息
发现存在连接业务
select sid,serial# from v$session where service_name='SERVICE_NAME';
SID SERIAL#
———- ———-
1 1
2 1
3 1
4 1
5 1
6 1
7 1
8 1
9 5
63 15
64 1
SID SERIAL#
———- ———-
65 1
66 1
67 1
68 1
69 1
71 1
125 1
126 1
127 1
128 1
129 1
SID SERIAL#
———- ———-
130 1
131 1
133 3
134 4141
135 13
137 1
187 1
188 1
189 1
190 1
191 1
SID SERIAL#
———- ———-
192 1
193 1
195 5
198 3
200 2053
使用拼接sql,创建批量杀会话语句
select ' alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where service_name='SERVICE_NAME';
‘ALTERSYSTEMKILLSESSION”’||SID||’,’||SERIAL#||”’IMMEDIATE;’
——————————————————————————–
alter system kill session ‘1,1’ immediate;
alter system kill session ‘2,1’ immediate;
alter system kill session ‘3,1’ immediate;
alter system kill session ‘4,1’ immediate;
alter system kill session ‘5,1’ immediate;
alter system kill session ‘6,1’ immediate;
alter system kill session ‘7,1’ immediate;
alter system kill session ‘8,1’ immediate;
alter system kill session ‘9,5’ immediate;
alter system kill session ‘63,15’ immediate;
alter system kill session ‘64,1’ immediate;
‘ALTERSYSTEMKILLSESSION”’||SID||’,’||SERIAL#||”’IMMEDIATE;’
——————————————————————————–
alter system kill session ‘65,1’ immediate;
alter system kill session ‘66,1’ immediate;
alter system kill session ‘67,1’ immediate;
alter system kill session ‘68,1’ immediate;
alter system kill session ‘69,1’ immediate;
alter system kill session ‘71,1’ immediate;
alter system kill session ‘125,1’ immediate;
alter system kill session ‘126,1’ immediate;
alter system kill session ‘127,1’ immediate;
alter system kill session ‘128,1’ immediate;
alter system kill session ‘129,1’ immediate;
‘ALTERSYSTEMKILLSESSION”’||SID||’,’||SERIAL#||”’IMMEDIATE;’
——————————————————————————–
alter system kill session ‘130,1’ immediate;
alter system kill session ‘131,1’ immediate;
alter system kill session ‘133,3’ immediate;
alter system kill session ‘134,4141’ immediate;
alter system kill session ‘135,13’ immediate;
alter system kill session ‘137,1’ immediate;
alter system kill session ‘187,1’ immediate;
alter system kill session ‘188,1’ immediate;
alter system kill session ‘189,1’ immediate;
alter system kill session ‘190,1’ immediate;
alter system kill session ‘191,1’ immediate;
‘ALTERSYSTEMKILLSESSION”’||SID||’,’||SERIAL#||”’IMMEDIATE;’
——————————————————————————–
alter system kill session ‘192,1’ immediate;
alter system kill session ‘193,1’ immediate;
alter system kill session ‘195,5’ immediate;
alter system kill session ‘198,3’ immediate;
alter system kill session ‘200,2053’ immediate;
但是此命令存在分隔符切不易使用,可利用oracle控制命令生成sql文件
命令详解
spool 在sqlplus中用来保存或打印查询结果
spool 文件 | spool off
set heading off 让结果行的标题不显示
set feedback off 关闭返回行数反馈
pagesize 设置页大小
set verify off 设置是否显示变量后的语句,如使用sql的变量时,如:where id=&var传入1,会出现包含变量前后的sql,&var和1。如果关闭该参数则直接出现结果,不出现变量后的值
set echo off 不显示执行sql文件里的sql
设置好格式,输出到sql文件中
SQL> set heading off feedback off pagesize 0 verify off echo off
SQL> spool ch.sql
spool off
重新执行sql后,查看sql文件
SQL> select ‘ alter system kill session ”’||sid||’,’||serial#||”’ immediate;’ from v$session
2 ;
alter system kill session ‘1,1’ immediate;
alter system kill session ‘2,1’ immediate;
alter system kill session ‘3,1’ immediate;
alter system kill session ‘4,1’ immediate;
alter system kill session ‘5,1’ immediate;
alter system kill session ‘6,1’ immediate;
alter system kill session ‘7,1’ immediate;
alter system kill session ‘8,1’ immediate;
alter system kill session ‘9,5’ immediate;
alter system kill session ‘63,15’ immediate;
alter system kill session ‘64,1’ immediate;
alter system kill session ‘65,1’ immediate;
alter system kill session ‘66,1’ immediate;
alter system kill session ‘67,1’ immediate;
alter system kill session ‘68,1’ immediate;
alter system kill session ‘69,1’ immediate;
alter system kill session ‘71,1’ immediate;
alter system kill session ‘125,1’ immediate;
alter system kill session ‘126,1’ immediate;
alter system kill session ‘127,1’ immediate;
alter system kill session ‘128,1’ immediate;
alter system kill session ‘129,1’ immediate;
alter system kill session ‘130,1’ immediate;
alter system kill session ‘131,1’ immediate;
alter system kill session ‘133,3’ immediate;
alter system kill session ‘134,4141’ immediate;
alter system kill session ‘135,13’ immediate;
alter system kill session ‘137,1’ immediate;
alter system kill session ‘187,1’ immediate;
alter system kill session ‘188,1’ immediate;
alter system kill session ‘189,1’ immediate;
alter system kill session ‘190,1’ immediate;
alter system kill session ‘191,1’ immediate;
alter system kill session ‘192,1’ immediate;
alter system kill session ‘193,1’ immediate;
alter system kill session ‘195,5’ immediate;
alter system kill session ‘198,3’ immediate;
alter system kill session ‘200,2053’ immediate;
SQL> spool off
简单修改头尾后,执行,会话会自行连接到新实例。
服务器托管,北京服务器托管,服务器租用 http://www.fwqtg.net
机房租用,北京机房租用,IDC机房托管, http://www.fwqtg.net
一元 · 复始 岁序易,华章新。当钟声回响在清朗的夜空,当日历翻开新的一页,我们迎来了新的一年,7DGroup祝大家元旦快乐,技术成就梦想,日日有进步,岁岁平安。 愿大家新年快乐,身体健康;善愿皆成,福慧增长;合家美满,六时吉祥! 愿我们的祖国风…