提高oracle MVLOG的处理效率的方法分享

 个人blog上的一篇文章：http://www.dbafree.net/?p=245

 

oracle的MVIEW LOG在数据库中被大家广泛的使用，特别是用于增量数据的迁移。
如何更好的使用MVLOG呢？我分享一下最近在进行数据迁移碰到的一个问题及一些想法,或者说一些总结和理解：

先来说说处理MVIEW LOG的一种高效的处理方式:
1.先取mvlog中的最小sequence，和最大sequence，记为min_seq和max_seq。
2.如果max_seq>min_seq+200(即MVIEW LOG大于200条),则max_seq=min_seq+200。
3.然后执行:

SELECT SEQUENCE$$
  FROM $mlog_table
 WHERE SEQUENCE$$ >= min_seq
   AND SEQUENCE$$ <= 2000 + max_seq
 ORDER BY SEQUENCE$$

注意，这里为什么是max_seq+2000，因为DB上MVIEW LOG的sequence是共用的,对于一个表的sequence值可能跨越很大。
这条SQL能够取出最多2200个SEQUENCE$$。

4.然后使用一个数组mv_seqlist：赋值SEQUENCE$$列表的前200个值（如果只有100个值，则赋值100），记下标为mv_seqlist[0]-mv_seqlist[N]。这步执行完后，再赋值mv_seqlist[N+1]到mv_seqlist[N+201]：均赋为-1。赋值-1操作是为了防止数组的长度小于200个值，为了后面的绑定变量使用方便。
即:

		mv_seqlist[0]=SEQUENCE$$XX
		mv_seqlist[1]=SEQUENCE$$XX
		mv_seqlist[2]=SEQUENCE$$XX
		mv_seqlist[3]=SEQUENCE$$XX
		...
		mv_seqlist[N]=SEQUENCE$$XX
		mv_seqlist[N+1]=-1
		mv_seqlist[N+2]=-1
		...
		mv_seqlist[N+201]=-1

N<=200;

5.选择MV_SEQLIST[0]至MV_SEQLIST[200]之间的200个值，不足200的补-1。对这200个值执行以下操作：

5.1:
select distinct pk from mvlog where SEQUENCE$$ in (200个值) and status='D'。
找出PK值之后执行批量DELETE。

5.2:
执行完delete后，再执行update。
select test.* from test where PK in (
select distinct test from mvlog where SEQUENCE$$ in (200个值) and status='U')

找出结果集，然后进行全表update。

Update时唯一约束冲突时，或者发生其它任何的错误时，不断重试。

5.3:
执行完update后，再执行insert
select iw_user.* from iw_user where iw_user_id in (
select distinct iw_user_id from mvlog where SEQUENCE$$ in (200个值) and status='I')
insert时如报唯一约束冲突则忽略，其它情况继续执行。

5.4:
若以上所有执行均成功：
则删除mvlog中的sequence$$,若有任何一步失败，则退出或重试。

5.5：
在每次200个批次处理完成后，可以选择sleep一段时间，1秒或者更久。
这种方式对于同步在任何情况下的异常中止，都不会导致数据不一致或者数据丢失。并且能够有不错的性能，这种方式被广泛的应用。

一个可能会碰到的一个问题：
在同时存在PK,UK的表中,从原库同步到新库过程中,可能会在更新中报ora-00001。

举例来说，有一个表：test，表中有两个用户：A,B

ID_PK为PK, NAME_UK为UK

1.原库中test表的记录为：

ID_PK        NAME_UK
1              A
2              B

2.将原库中test表拖到新库：
当前新库中的记录为：

ID_PK        NAME_UK
1              A
2              B

3.在拖取的过程中,原库的记录需要进行修改，修改后的结果如下：

ID_PK        NAME_UK
1              B
2              A

由于NAME_UK列上有唯一约束,因此不能直接修改。即不能直接执行

  update test set NAME_UK='B' where id=1;
	(由于此时id=2的NAME_UK值为B，因此会报ora-00001，唯一约束冲突)
  update test set NAME_UK='A' where id=2;

需要使用一个中间值来达到修改的目的，如下：

  update test set NAME_UK='C' where id=1;
  update test set NAME_UK='A' where id=2;
  update test set NAME_UK='B' where id=1;

4.此时，新库开始增量同步
当前新库中的记录为：

ID_PK        NAME_UK
1              A
2              B

原库中的记录为:

ID_PK        NAME_UK
1              B
2              A

增量同步我们要执行以下两步操作：

步骤1.	 update test set NAME_UK='B' where id=1;
步骤2.	 update test set NAME_UK='A' where id=2;

很明显,第步骤1会报唯一性约束冲突。(由于此时id=2的NAME_UK值为B，因此会报ora-00001，唯一约束冲突)
因为新库丢失了NAME='UK'的这个中间值。
这种情况在MVLOG积压的情况下特别容易碰上。这时候可以手工处理,把id为2的记录更新为B->C,步骤1和步骤2又可以正常运行了：
update test set NAME_UK='C' where id=2;

一切OK！

同步后,新库的数据即为：

ID_PK          NAME_UK
----------- ----------
B              1
A              2

这种情况容易导致逻辑复制的阻塞(因为update的唯一约束冲突不能忽略)，在碰到问题时，需要及时处理，如果不能及时处理，在mvlog积压的情况下，会越积越多。

经过上面的实例，我们可以得出对于这个问题的解决办法。
1.碰到问题时，手工处理。将重复的记录更新为中间值即可。
2.事前的处理。应用上应尽可能的避免这种情况。或者延长这种情况的发生时间间隔。
如 以下3步操作在1分钟内完成，而我们的MVLOG同步在10秒内完成，即中间值NAME_UK='C'没有丢失，即不会报错。

	update test set NAME_UK='C' where id=1;
        update test set NAME_UK='A' where id=2;
	update test set NAME_UK='B' where id=1;

3.将业务上的UK暂时去除，在同步完成之后再加上UK。
4.将违反唯一约束的UK值进行捕获，并在新库上将UK值更新为一个中间值。这种方式较好，但是增加了一点复杂度。
update test set NAME_UK='B' where id=1;报错ora-01111
捕获UK值NAME_UK='B',进行更新：
update test set NAME_UK='C' where NAME_UK='B';

PS:如有必要，可以同时将这个也PK值记录到临时表中，用于迁移完成后的数据检查。
5.对于使用MVLOG建逻辑备库的情况（双向同步），考虑将业务上的UK永远去除，并且定时作唯一性检查，进行数据订正。