January 19, 2005
关于Oracle的冲突解决机制的研究
作者:eygle
出处:http://blog.eygle.com
关于Oracle的冲突解决机制的研究---以及如何手动解决Oracle高级复制中的冲突
实际上Oracle的dbms_rectifier_diff.DIFFERENCES过程，内部操作就是执行连个minus操作 把两边的差异记录下来，作为冲突解决的数据。
这部分后台操作可以通过跟踪Oracle进程得到:
SQL> alter session set events '10046 trace name context forever,level 12'; Session altered. Elapsed: 00:00:00.02 SQL> begin dbms_rectifier_diff.DIFFERENCES( 2 SNAME1 =>'HAWA', 3 ONAME1 =>'TEST', 4 REFERENCE_SITE =>'AVATAR.COOLYOUNG.COM.CN', 4 SNAME2 =>'HAWA', 6 ONAME2 =>'TEST', 7 COMPARISON_SITE =>'AUTHAA.COOLYOUNG.COM.CN', 8 WHERE_CLAUSE =>NULL, 9 COLUMN_LIST =>NULL, 10 MISSING_ROWS_SNAME =>'HAWA', 11 MISSING_ROWS_ONAME1 =>'MISSING_ROWS_TEST', 12 MISSING_ROWS_ONAME2 =>'MISSING_LOCATION_TEST', 13 MISSING_ROWS_SITE =>'AVATAR.COOLYOUNG.COM.CN', 14 MAX_MISSING =>500, 15 COMMIT_ROWS =>100 16 ); 17 end; 18 / PL/SQL procedure successfully completed. Elapsed: 00:00:01.97 SQL> alter session set events '10046 trace name context off'; |
从跟踪文件中我们可以清晰的看到(注意你所定义的所有参数在此都会有所体现):
1.首先是一个正向Minus
DECLARE
row_count BINARY_INTEGER := 0;
missing_rows BINARY_INTEGER := 0;
arowid ROWID;
CURSOR c
IS
SELECT "DATLOGONTIME", "NUMGENDER", "NUMSTATUS", "NUMUSERID", "VC2IP",
"VC2USERNAME"
FROM "HAWA"."TEST"
MINUS
SELECT "DATLOGONTIME", "NUMGENDER", "NUMSTATUS", "NUMUSERID", "VC2IP",
"VC2USERNAME"
FROM "HAWA"."TEST"@authaa.coolyoung.com.cn;
BEGIN
FOR r IN c
LOOP
missing_rows := missing_rows + 1;
IF missing_rows > 500
THEN
COMMIT;
EXIT;
END IF;
INSERT INTO "HAWA"."MISSING_ROWS_TEST"
("DATLOGONTIME", "NUMGENDER", "NUMSTATUS",
"NUMUSERID", "VC2IP", "VC2USERNAME"
)
VALUES (r."DATLOGONTIME", r."NUMGENDER", r."NUMSTATUS",
r."NUMUSERID", r."VC2IP", r."VC2USERNAME"
);
SELECT ROWID
INTO arowid
FROM "HAWA"."MISSING_ROWS_TEST"
WHERE ( datlogontime = r."DATLOGONTIME"
OR (datlogontime IS NULL AND r."DATLOGONTIME" IS NULL)
)
AND ( numgender = r."NUMGENDER"
OR (numgender IS NULL AND r."NUMGENDER" IS NULL)
)
AND ( numstatus = r."NUMSTATUS"
OR (numstatus IS NULL AND r."NUMSTATUS" IS NULL)
)
AND (numuserid = r."NUMUSERID")
AND (vc2ip = r."VC2IP" OR (vc2ip IS NULL AND r."VC2IP" IS NULL))
AND ( vc2username = r."VC2USERNAME"
OR (vc2username IS NULL AND r."VC2USERNAME" IS NULL)
);
INSERT INTO "HAWA"."MISSING_LOCATION_TEST"
(present, absent, r_id
)
VALUES ('AVATAR.COOLYOUNG.COM.CN', 'AUTHAA.COOLYOUNG.COM.CN',
arowid
);
row_count := row_count + 1;
IF row_count >= 100
THEN
COMMIT;
row_count := 0;
END IF;
END LOOP;
COMMIT;
END;
|
2.其次是一个反向Minus
DECLARE
row_count BINARY_INTEGER := 0;
missing_rows BINARY_INTEGER := 0;
arowid ROWID;
CURSOR c
IS
SELECT "DATLOGONTIME", "NUMGENDER", "NUMSTATUS", "NUMUSERID", "VC2IP",
"VC2USERNAME"
FROM "HAWA"."TEST"@authaa.coolyoung.com.cn
MINUS
SELECT "DATLOGONTIME", "NUMGENDER", "NUMSTATUS", "NUMUSERID", "VC2IP",
"VC2USERNAME"
FROM "HAWA"."TEST";
BEGIN
FOR r IN c
LOOP
missing_rows := missing_rows + 1;
IF missing_rows > 500
THEN
COMMIT;
EXIT;
END IF;
INSERT INTO "HAWA"."MISSING_ROWS_TEST"
("DATLOGONTIME", "NUMGENDER", "NUMSTATUS",
"NUMUSERID", "VC2IP", "VC2USERNAME"
)
VALUES (r."DATLOGONTIME", r."NUMGENDER", r."NUMSTATUS",
r."NUMUSERID", r."VC2IP", r."VC2USERNAME"
);
SELECT ROWID
INTO arowid
FROM "HAWA"."MISSING_ROWS_TEST"
WHERE ( datlogontime = r."DATLOGONTIME"
OR (datlogontime IS NULL AND r."DATLOGONTIME" IS NULL)
)
AND ( numgender = r."NUMGENDER"
OR (numgender IS NULL AND r."NUMGENDER" IS NULL)
)
AND ( numstatus = r."NUMSTATUS"
OR (numstatus IS NULL AND r."NUMSTATUS" IS NULL)
)
AND (numuserid = r."NUMUSERID")
AND (vc2ip = r."VC2IP" OR (vc2ip IS NULL AND r."VC2IP" IS NULL))
AND ( vc2username = r."VC2USERNAME"
OR (vc2username IS NULL AND r."VC2USERNAME" IS NULL)
);
INSERT INTO "HAWA"."MISSING_LOCATION_TEST"
(present, absent,
r_id
)
VALUES ('AUTHAA.COOLYOUNG.COM.CN', 'AVATAR.COOLYOUNG.COM.CN',
arowid
);
row_count := row_count + 1;
IF row_count >= 100
THEN
COMMIT;
row_count := 0;
END IF;
END LOOP;
COMMIT;
END;
|
经过这两个步骤的操作，Oracle定位了冲突数据。
可是注意，如果在解决这个问题时你没有挂起复制，Oracle得到的数据可能是存在问题的。
而且，如果你不指定column list,那么两边的数据可能会因为某些特殊字段(如时间字段)的特殊处理而存在差异。
那么这时候手工介入不可避免。
我们首先先把两个重要参数的用法说明一下。
一个是WHERE_CLAUSE，另外一个是COLUMN_LIST。
WHERE_CLAUSE用于限定进行差异比较的范围，这可以极大的缩减结果集的数量，使用索引加快访问速度等。
比如我这里使用NUMGENDER=1，只比较性别为"女"这一部分用户数据。
COLUMN_LIST用于限定比较字段，如果你能通过某个字段，如主键等确定数据差异，那么你完全可以只比较单个字段。
而且显然可以轻易通过全索引扫描来完成比较，加快比较速度。
我这里使用NUMUSERID，用户ID来比较。
但是注意，这样的比较结果中将只包含NUMUSERID信息，当然我们可以轻易通过NUMUSERID和原表的比较补全MISSING_ROWS_TEST表的信息。
begin dbms_rectifier_diff.DIFFERENCES( SNAME1 =>'HAWA', ONAME1 =>'TEST', REFERENCE_SITE =>'AVATAR.COOLYOUNG.COM.CN', SNAME2 =>'HAWA', ONAME2 =>'TEST', COMPARISON_SITE =>'AUTHAA.COOLYOUNG.COM.CN', WHERE_CLAUSE =>'NUMGENDER=1', COLUMN_LIST =>'NUMUSERID', MISSING_ROWS_SNAME =>'HAWA', MISSING_ROWS_ONAME1 =>'MISSING_ROWS_TEST', MISSING_ROWS_ONAME2 =>'MISSING_LOCATION_TEST', MISSING_ROWS_SITE =>'AVATAR.COOLYOUNG.COM.CN', MAX_MISSING =>500, COMMIT_ROWS =>100 ); end; / |
这段代码供参考。
Ok，我们继续前面的讨论。
我们提到，如果存在差异，通常需要手工介入。
清楚了DIFFERENCES的原理，实际上我们完全可以手工来完成这个过程。
以下是我的手工操作步骤，目的是为了准确性及减轻数据库压力:
1.首先创建一个ID差异表
这个表不是必须的，这里是为了清晰
SQL> create table hawa.prof as select NUMUSERID from hawa.hw_user where 1=0; Table created. Elapsed: 00:00:00.16 |
2.根据主键找到差异记录
注意这里取决于你的数据库产生差异的原因，我的差异由于初始数据不同步，即A全包含B并且，A>B。
SQL> insert into hawa.prof 2 select * from 3 ( 4 select NUMUSERID from hawa.HW_USERPROFILE 5 minus 6 select NUMUSERID from hawa.HW_USERPROFILE@authaa) 7 / 263 rows created. Elapsed: 00:00:32.49 |
3.创建记录表
SQL> create table hawa.missing_rows_hw_userprofile 2 as 3 select * from hawa.hw_userprofile where 1=0; Table created. Elapsed: 00:00:00.12 |
4.创建位置(Location)表
注意这里Oracle需要记录缺失方向，和具体记录的ROWID，这个ROWID来自missing_rows_hw_userprofile。
SQL> create table hawa.MISSING_LOC_hw_userprofile ( 2 present VARCHAR2(128), 3 absent VARCHAR2(128), 4 r_id ROWID); Table created. Elapsed: 00:00:00.04 |
4.根据差异信息查询到完整信息
SQL> insert into hawa.missing_rows_hw_userprofile 2 select * from hawa.hw_userprofile where NUMUSERID in 3 (select * from hawa.prof); 263 rows created. Elapsed: 00:00:00.06 SQL> commit; Commit complete. Elapsed: 00:00:00.02 |
5.构造位置信息
注意这里的方向信息及ROWID信息。
SQL> insert into hawa.MISSING_LOC_hw_userprofile 2 select 'AVATAR.COOLYOUNG.COM.CN','AUTHAA.COOLYOUNG.COM.CN',rowid from hawa.missing_rows_hw_userprofile; 263 rows created. Elapsed: 00:00:00.00 SQL> commit; Commit complete. Elapsed: 00:00:00.06 |
6.纠正数据冲突
SQL> BEGIN DBMS_RECTIFIER_DIFF.RECTIFY( 2 SNAME1 =>'HAWA', 3 ONAME1 =>'HW_USERPROFILE', 4 REFERENCE_SITE =>'AVATAR.COOLYOUNG.COM.CN', 5 SNAME2 =>'HAWA', 6 ONAME2 =>'HW_USERPROFILE', 7 COMPARISON_SITE =>'AUTHAA.COOLYOUNG.COM.CN', 8 COLUMN_LIST =>NULL, 9 MISSING_ROWS_SNAME =>'HAWA', 10 MISSING_ROWS_ONAME1 =>'MISSING_ROWS_HW_USERPROFILE', 11 MISSING_ROWS_ONAME2 =>'MISSING_LOC_HW_USERPROFILE', 12 MISSING_ROWS_SITE =>'AVATAR.COOLYOUNG.COM.CN', 13 COMMIT_ROWS =>100 14 ); 15 END; 16 / PL/SQL procedure successfully completed. Elapsed: 00:00:03.53 |
7.验证结果
SQL> select count(*) from hawa.HW_USERPROFILE; COUNT(*) ---------- 1746300 Elapsed: 00:00:02.22 SQL> select count(*) from hawa.HW_USERPROFILE@authaa; COUNT(*) ---------- 1746300 Elapsed: 00:00:00.21 SQL> select count(*) from hawa.HW_USERPROFILE; COUNT(*) ---------- 1746300 Elapsed: 00:00:00.59 SQL>select count(*) from hawa.HW_USERPROFILE@authaa; COUNT(*) ---------- 1746300 Elapsed: 00:00:00.20 SQL> select NUMUSERID from hawa.HW_USERPROFILE 2 minus 3 select NUMUSERID from hawa.HW_USERPROFILE@authaa ; no rows selected Elapsed: 00:00:23.51 SQL> |
Posted by eygle at 8:02 PM | Comments (3)
使用dbms_rectifier_diff解决高级复制中的数据冲突问题
作者:eygle
出处:http://blog.eygle.com
很多时候在高级复制中可能存在数据冲突和不一致现象。Oracle提供的dbms_rectifier_diff包可以用于解决该冲突。
以下通过实例来说明一下该Package的用法。
1.创建复制组及复制对象
SQL> execute dbms_repcat.create_master_repgroup('rep_tt');
PL/SQL procedure successfully completed
SQL> select gname,master,status from dba_repgroup;
GNAME MASTER STATUS
------------------------------ ------ ---------
REP_TT Y QUIESCED
|
SQL> execute dbms_repcat.create_master_repobject(sname=>'hawa',
oname=>'test', type=>'table',use_existing_object=>true,
gname=>'rep_tt',copy_rows=>false);
PL/SQL procedure successfully completed
SQL>
SQL> execute dbms_repcat.generate_replication_support('hawa','test','table');
PL/SQL procedure successfully completed
SQL> select gname, master, status from dba_repgroup;
GNAME MASTER STATUS
------------------------------ ------ ---------
REP_TT Y QUIESCED
SQL> select * from dba_repobject;
SNAME ONAME TYPE STATUS GENERATION_STATUS ID OBJECT_COMMENT GNAME
------------------------------ ------------------------------ ---------------- ---------- ---------------
HAWA TEST TABLE VALID GENERATED 8620 REP_TT
HAWA TEST$RP PACKAGE VALID 8641 SYSTEM-GENERATED: REPLICATION REP_TT
HAWA TEST$RP PACKAGE BODY VALID 8677 SYSTEM-GENERATED: REPLICATION REP_TT
3 rows selected
SQL>
SQL> execute dbms_repcat.add_master_database(gname=>'rep_tt',
master=>'AUTHAA.COOLYOUNG.COM.CN',
use_existing_objects=>true, copy_rows=>false,
propagation_mode => 'synchronous');
PL/SQL procedure successfully completed
SQL> execute dbms_repcat.resume_master_activity('rep_tt',true);
PL/SQL procedure successfully completed
SQL> select * from dba_repgroup;
SNAME MASTER STATUS SCHEMA_COMMENT GNAME FNAME RPC_PROCESSING_DISABLED OWNER
-------- ------------------ ---- ------ ---- ---- -----------------------------------------------
REP_TT Y NORMAL REP_TT N PUBLIC
|
2.创建保存冲突数据的数据表
a.missing_rows表用以保存冲突行
SQL> create table hawa.missing_rows_test 2 as 3 select * from hawa.test where 1=0; Table created |
b.用于保存缺失行位置及rowid
SQL> create table hawa.MISSING_LOCATION_TEST ( 2 present VARCHAR2(128), 3 absent VARCHAR2(128), 4 r_id ROWID); Table created |
3.使用dbms_rectifier_diff.DIFFERENCES查找缺失记录
SQL> begin dbms_rectifier_diff.DIFFERENCES( 2 SNAME1 =>'HAWA', 3 ONAME1 =>'TEST', 4 REFERENCE_SITE =>'AVATAR.COOLYOUNG.COM.CN', 5 SNAME2 =>'HAWA', 6 ONAME2 =>'TEST', 7 COMPARISON_SITE =>'AUTHAA.COOLYOUNG.COM.CN', 8 WHERE_CLAUSE =>NULL, 9 COLUMN_LIST =>NULL, 10 MISSING_ROWS_SNAME =>'HAWA', 11 MISSING_ROWS_ONAME1 =>'MISSING_ROWS_TEST', 12 MISSING_ROWS_ONAME2 =>'MISSING_LOCATION_TEST', 13 MISSING_ROWS_SITE =>'AVATAR.COOLYOUNG.COM.CN', 14 MAX_MISSING =>500, 15 COMMIT_ROWS =>100 16 ); 17 end; 18 / PL/SQL procedure successfully completed |
冲突记录被保存在我们创建的指定表中
SQL> select count(*) from hawa.missing_rows_test;
COUNT(*)
----------
172
|
共有172条差异记录
SQL> select count(*) from hawa.test;
COUNT(*)
----------
548
SQL> select count(*) from hawa.test@authaa;
COUNT(*)
----------
376
SQL> select count(*) from hawa.missing_location_test;
COUNT(*)
----------
172
|
4.使用DBMS_RECTIFIER_DIFF.RECTIFY进行数据整合
首先需要注意的是:
RECTIFY过程使用DIFFERENCES产生的数据进行数据调整。
在第一个表中存在，在第二个表中不存在的数据将被插入第二张表。
在第二个表中存在，在第一个个表中不存在的数据将被从第二张表中删除。
另外，在这个数据纠正过程中，你可以使用dbms_repcat.suspend_master_activity将复制组暂时挂起。
这样便于保证数据完整性。
但这不是必须的，如果复制一直激活，可能会有新的冲突出现。
SQL> BEGIN DBMS_RECTIFIER_DIFF.RECTIFY(
2 SNAME1 =>'HAWA',
3 ONAME1 =>'TEST',
4 REFERENCE_SITE =>'AVATAR.COOLYOUNG.COM.CN',
5 SNAME2 =>'HAWA',
6 ONAME2 =>'TEST',
7 COMPARISON_SITE =>'AUTHAA.COOLYOUNG.COM.CN',
8 COLUMN_LIST =>NULL,
9 MISSING_ROWS_SNAME =>'HAWA',
10 MISSING_ROWS_ONAME1 =>'MISSING_ROWS_TEST',
11 MISSING_ROWS_ONAME2 =>'MISSING_LOCATION_TEST',
12 MISSING_ROWS_SITE =>'AVATAR.COOLYOUNG.COM.CN',
13 COMMIT_ROWS =>100
14 );
15 END;
16 /
PL/SQL procedure successfully completed
SQL> select count(*) from hawa.test@authaa;
COUNT(*)
----------
548
SQL> select count(*) from hawa.test;
COUNT(*)
----------
548
|
SQL> select count(*) from hawa.missing_rows_test;
COUNT(*)
----------
0
SQL>
|
Posted by eygle at 3:26 PM | Comments (2)