我们的是分布是系统，在其他的三个省市，都有服务器，数据是通过shareplex
同步的，因为开发的需要，在源断需要清除大量的历史数据，当时没有考虑到shareplex的问题
再加上vpn的问题，大时候还没有处理完（也就是commit），vpn就断开了，事务回滚，在oracle日志里面表现为
delete from table_name 很多很多（没有commit）然后就是insert 前面的数据再commit，这是一个很大的失误，
回滚分了很多次，导致把所有分站的shareplex的队列文件（rim达到300多G)撑满，并且很多归档把归档目录撑满，所有分站数据库
数据无法写入，oracle数据库也只读。

处理的方法，事出必有因
1 定为问题
进入shareplex的控制台，status看下状态
sp_ctrl (hostname:2200)> status

Brief Status for CDDBSync01
Process          State                             PID     Running   Since
--------------- ------------------------------ -------- --------------------
Cop              Running                            13675 16-May-10 22:01:48
MTPost           Stopping                           15834 17-May-10 05:20:59
Import           Idle
Cmd & Ctrl       Running                            16685 17-May-10 09:22:45
sp_ctrl (CDDBSync01:2200)> stop post
sp_ctrl (CDDBSync01:2200)> status

Brief Status for CDDBSync01
Process          State                             PID     Running   Since
--------------- ------------------------------ -------- --------------------
Cop              Running                            13675 16-May-10 22:01:48
MTPost           Stopping                           15834 17-May-10 05:20:59
Import           Idle
Cmd & Ctrl       Running                            16685 17-May-10 09:22:45
sp_ctrl (CDDBSync01:2200)> abort
   A directive is required for abort command

Usage: abort config filename [on host]

   Usage: abort service import [on host]
      Or: abort service import from host [on host]
      Or: abort service import from host queue quename [on host]

   Usage: abort service export [on host]
      Or: abort service export to host [on host]
      Or: abort service export queue quename [on host]
      Or: abort service export queue quename to host [on host]

Usage: abort service post [queue quename] [for datasrc-datadst] [on host]

Usage: abort service read [on host]
Or: abort service read for datasrc [on host]

Usage: abort service capture [on host]
Or: abort service capture for datasrc [on host]

sp_ctrl (CDDBSync01:2200)> abort service post（保证不再有数据写入）

show post detail 看下在做什么，发现就是大表上的delete操作，为了保险期间，在oracle里面用logmner抓取几个log看下，
结果全是都是，问题定位。

2处理问题
回滚该大事务
到2300端口
sp_ctrl (CDDBSync01:2300)> status

Brief Status for CDDBSync01
Process          State                             PID     Running   Since
--------------- ------------------------------ -------- --------------------
Cop              Running                             4014 20-Apr-10 21:44:41
Capture          Stopped - disk is full             (磁盘空间满导致capture队列）
Export           Running                            14147 16-May-10 23:37:01
Read             Running                            16418 17-May-10 08:44:42
Cmd & Ctrl       Running                            16698 17-May-10 09:25:18
log里面的东西
info     2010-05-17 09:14:42.241242 4014 4141311680 Disk is no longer full: /quest/splex23/vardir/rim

sp_ctrl (CDDBSync01:2300)> stop capture

sp_ctrl (CDDBSync01:2300)> version full

   SharePlex Version = 6.1.0.21
[quest22@CDDBSync01 delarch]$ qview -i
qview>
Initialize first with qsetup or qinit:
qsetup                       - init command if sp_cop is not running
qinit                        - init command if sp_cop is running
qview> qinit
qview> open p r
que_INUSE: Que is already open
qview> oseek 0 1
Can't read - queue not open
qview> exit
[quest22@CDDBSync01 delarch]$ sp_ctrl
sp_ctrl (CDDBSync01:2200)> status

Brief Status for CDDBSync01
Process          State                             PID     Running   Since
--------------- ------------------------------ -------- --------------------
Cop              Running                            13675 16-May-10 22:01:48
MTPost           Stopping                           16699 17-May-10 09:26:15
Import           Idle
Cmd & Ctrl       Running                            16771 17-May-10 09:43:35
There are no active configuration files

sp_ctrl (CDDBSync01:2200)>
sp_ctrl (CDDBSync01:2200)> abort service post
sp_ctrl (CDDBSync01:2200)> status

Brief Status for CDDBSync01
Process          State                             PID     Running   Since
--------------- ------------------------------ -------- --------------------
Cop              Running                            13675 16-May-10 22:01:48
MTPost           Aborting                           16699 17-May-10 09:26:15
Import           Idle
Cmd & Ctrl       Running                            16771 17-May-10 09:43:35
There are no active configuration files

sp_ctrl (CDDBSync01:2200)> /

Brief Status for CDDBSync01
Process          State                             PID     Running   Since
--------------- ------------------------------ -------- --------------------
Cop              Running                            13675 16-May-10 22:01:48
MTPost           Stopped by user                    等该进程完全停掉（很多时候，这被停了，他还有子进程没有停完）
Import           Idle
Cmd & Ctrl       Running                            16771 17-May-10 09:43:35
There are no active configuration files

[quest22@CDDBSync01 delarch]$ qview -p（可自动回退队列中没有commit的事务）

qview -i
qinit
open c r（打开队列，open c r为打开capture ：open p r 为打开post队列的： open x r 为打开export 队列）
qview> oseek 0 0 寻找到对应的大的回滚事务
Sque 0, qseq 694600145497, 05/15/10 09:58:01, 18970/232690368, transid 2, ROLLBACK COMMIT, AAAPeWAASAAAQg8AAA , SCN 49550141515, forward = 0

qview> invalid command
qview> seekback
qview> rrls 0 694600145497
qview> quit

不停重复（简单描述，实际操作还是有点顺序和技巧性的），就能清除队列中的大事务

其中还遇到问题，log丢失等，处理方法是更新shareplex的内部表

select object_id,object_type,OWNER
from dba_objects where object_name='table-name';

select object_id,object_name,object_type,OWNER
from dba_objects where object_id=63980;

跳过丢失的log
update splex22.shareplex_actid
set SEQNO=10158, FFSET=0, LOG_START_OFFSET=0
where actid = 30 and instance_name='****';

ITPub博客

记一次shareplex特大故障处理

最新文章