原文见blog：
http://space.itpub.net/15415488/viewspace-617044
11G新引进的dbms_comparison包是在Stream Replication Admin Guide里介绍的。我为什么要来研究下这个包，是因为在我们的系统中使用了不少基于逻辑复制的备份和容灾机制，例如logical standby，shareplex.那么，如果发生灾难的时候需要failover DR的时候，或者在常规的standby cutover的时候，我们能否100%拍着胸脯保证primary和standby是拥有一模一样的逻辑结构以及数据的？至少在使用shareplex的时候，遇到了一些Bug，让我们失去了逻辑完整性。于是，我们开始需要研究一种能够定期巡检我们primary和DR的逻辑完整性的办法。对于Shareplex，有自带的compare repair命令，但是对于庞大的表基本上跑不出来，更何况我们大都是大表。于是，放眼11G，dbms_comparison让我们眼前一亮。
dbms_comparison包的作用：能够比较两台数据库的objects内容的差异，然后帮助你按其中一个对象同步。这里能够比较的对象是有局限性的：
1.Tables
2.Single-table views
3.Materialized views
4.Synonyms for tables, single-table views, and materialized views
但是，不同类型的objects是可以被compare的。
当compare进行中，Oracle是分成不同的“Scan”来进行，不是一次搞定所有行。每一次Scan，都有一个Scan id唯一标示。而且，Oracle还将所有需要比较的行拆分开来，每一系列行叫做一个bucket。bucket的数量总是小于CREATE_COMPARISON这个procedure的max_num_buckets参数（default是1000）。当一个bucket里没有不一样的数据后，compare进行到下一个bucket里。当一个bucket里有不一致的情况，那么Oracle会拆分当前这个bucket为更小的bucket，直到bucket里的行数达到一个最小值，这个最小值就是CREATE_COMPARISON的min_rows_in_bucket参数（default是10000）。到了最小的bucket之后，这时候Compare这个function的perform_row_dif参数起作用，如果perform_row_dif=true，那么他会具体比较每行的差异，反之，他直接就报一个有差异而已。（后面会用例子来说明）
这个包其中使用了ORA_HASH这个function为每一个bucket计算一个HASH值，如果两个bucket匹配，那么Oracle就认为他们俩相等。这是一个非常高效的compre算法，但是从逻辑上来看并不是100%严密的，就像两个不一样的SQL也有可能拥有相同的HASH value一样。
dbms_comparison的使用有无数的限制，这也值得Oracle在将来增加更多的支持。首先，他不支持如下column类型：long，long raw，rowid，urowid，clob，blob，nclob，bfile，User-defined types，Oracle-supplied types。但是你可以在建立comparison时exclude不支持的column，这不失为一种workaround。其次，当你希望使用所有的scan mode时，他要求在被比较的表上有如下两种index中的至少一种：
1.A single-column index on a number, timestamp, interval, or DATE datatype column
2.A composite index that only includes number, timestamp, interval, or DATE datatype columns. Each column in the composite index must either have a NOT NULL constraint or be part of the primary key.
说了这么多理论，现在让我们来跟据实验来验证和研究以上理论。
我先在本机上创建两个用户：hao1和hao2。
1.验证是否可以compare不同的object类型。
SQL> show user
USER is "HAO1"
SQL> create table hao1(id number primary key);
Table created.
SQL> insert into hao1 values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> show user;
USER is "HAO2"
SQL> create table hao2 (id number primary key);
Table created.
SQL> create view hao2view as select * from hao2;
View created.
SQL> insert into hao2 values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> begin
dbms_comparison.create_comparison(comparison_name=>'haocomp1',
schema_name=>'HAO1',
object_name=>'HAO1',
dblink_name=>NULL,
remote_schema_name=>'HAO2',
remote_object_name=>'HAO2VIEW');
end;
/
PL/SQL procedure successfully completed.
set serveroutput on
declare
compare_info dbms_comparison.comparison_type;
compare_return boolean;
begin
compare_return := dbms_comparison.compare (comparison_name=>'HAOCOMP1',
scan_info=>compare_info,
perform_row_dif=>TRUE);
if compare_return=TRUE
then
dbms_output.put_line('the tables are equivalent.');
else
dbms_output.put_line('Bad news... there is data divergence.');
dbms_output.put_line('Check the dba_comparison and dba_comparison_scan_summary views for locate the differences for scan_id:'||compare_info.scan_id);
end if;
end;
/
Bad news... there is data divergence.
Check the dba_comparison and dba_comparison_scan_summary views for locate the differences for scan_id:4
PL/SQL procedure successfully completed.
select a.owner, a.comparison_name, a.schema_name, a.object_name,z.current_dif_count difference
from dba_comparison a, dba_comparison_scan_summary z
where a.comparison_name=z.comparison_name
and a.owner=z.owner and z.scan_id=4;
OWNER COMPARISON SCHEMA_NAM OBJECT_NAM DIFFERENCE
----- ---------- ---------- ---------- ----------
SYS HAOCOMP1 HAO1 HAO1
2
SQL> select local_rowid,remote_rowid,status from
2dba_comparison_row_dif where comparison_name='HAOCOMP1';
LOCAL_ROWIDREMOTE_ROWID STA
------------------ ------------------ ---
AAAF4rAABAAAOlKAAA
DIF
AAAF4tAABAAAOlOAAA DIF
declare
compare_info dbms_comparison.comparison_type;
begin
dbms_comparison.converge (comparison_name=>'HAOCOMP1',
scan_id=>4,
scan_info=>compare_info,
converge_options=>dbms_comparison.cmp_converge_local_wins);
dbms_output.put_line('--- Results ---');
dbms_output.put_line('Local rows Merged by process: '||compare_info.loc_rows_merged);
dbms_output.put_line('Remote rows Merged by process: '||compare_info.rmt_rows_merged);
dbms_output.put_line('Local rows Deleted by process: '||compare_info.loc_rows_deleted);
dbms_output.put_line('Remote rows Deleted by process: '||compare_info.rmt_rows_deleted);
end;
/
--- Results ---
Local rows Merged by process: 0
Remote rows Merged by process: 1
Local rows Deleted by process: 0
Remote rows Deleted by process: 1
PL/SQL procedure successfully completed.
以上是compare table and single table view的全过程。
以后为节约篇幅，会省略部分重复过程。
2.实验省略某些column来进行比较。
create table hao1.hao1(id number primary key,name clob);
insert into hao1.hao1 values(1,'123');
create table hao2.hao2(id number primary key,name clob);
insert into hao2.hao2 values(1,'456');
begin
dbms_comparison.create_comparison(comparison_name=>'HAOCOMP1',
schema_name=>'HAO1',
object_name=>'HAO1',
dblink_name=>NULL,
remote_schema_name=>'HAO2',
remote_object_name=>'HAO2',
COLUMN_LIST=>'ID'
);
end;
/
最终compare结果：
the tables are equivalent.
以上两个表其实有不同的行，但是我们指定了只比较ID这列，于是compare结果显示他们相等。
3.Random Scan Mode会引发compare结果的不定性。
create table hao1.hao1 as select * from dba_objects where rownum'HAOCOMP1',
schema_name=>'HAO1',
object_name=>'HAO1',
dblink_name=>NULL,
remote_schema_name=>'HAO2',
remote_object_name=>'HAO2',
SCAN_MODE=>DBMS_COMPARISON.CMP_SCAN_MODE_RANDOM,
SCAN_PERCENT=>1
);
end;
/
最终compre结果：
the tables are equivalent.
于是我一直删除hao2，终于percent 1的compare发现了differences。
SQL> delete from hao2.hao2 where object_id delete from hao2.hao2 where object_id delete from hao2.hao2 where object_id delete from hao2.hao2 where object_id delete from hao2.hao2 where object_id select * from DBA_COMPARISON_SCAN where PARENT_SCAN_ID=34 or SCAN_ID=34;
OWNER COMPARISONSCAN_ID PARENT_SCAN_ID STATUS COUNT_ROWS S LAST_UPDATE_TIME
----- ---------- ---------- -------------- ---------------- ---------- - ---------------------------------------------------------------------------
SYS HAOCOMP1 34
BUCKET DIF
14 N 20-OCT-09 08.26.55.672189 PM
SYS HAOCOMP1 35
34 ROW DIF
8 N 20-OCT-09 08.26.55.674517 PM
SYS HAOCOMP1 36
34 ROW DIF
6 N 20-OCT-09 08.26.56.152917 PM
以上，我们可以见到SCAN的status的不同意思，BUCKET DIF代表这次SCAN他不是LEAF SCAN，他可能是ROOT/BRACH SCAN。
如果status是ROW DIF，那么他就是最底层的SCAN。
由于是random scan，所以结果也会random，当再次compare，结果又不一样：
the tables are equivalent.
4.当perform_row_dif=false时，不检测具体每行的差异。
begin
dbms_comparison.create_comparison(
comparison_name=>'HAOCOMP1',
schema_name=>'HAO1',
object_name=>'HAO1',
dblink_name=>NULL,
remote_schema_name=>'HAO2',
remote_object_name=>'HAO2',
SCAN_MODE=>DBMS_COMPARISON.CMP_SCAN_MODE_FULL
);
end;
/
set serveroutput on
declare
compare_info dbms_comparison.comparison_type;
compare_return boolean;
begin
compare_return := dbms_comparison.compare (comparison_name=>'HAOCOMP1',
scan_info=>compare_info,
perform_row_dif=>FALSE);
if compare_return=TRUE
then
dbms_output.put_line('the tables are equivalent.');
else
dbms_output.put_line('Bad news... there is data divergence.');
dbms_output.put_line('Check the dba_comparison and dba_comparison_scan_summary views for locate the differences for scan_id:'||compare_info.scan_id);
end if;
end;
/
Bad news... there is data divergence.
Check the dba_comparison and dba_comparison_scan_summary views for locate the differences for scan_id:41
SQL> select * from dba_comparison_scan;
OWNER COMPARISONSCAN_ID PARENT_SCAN_ID STATUS COUNT_ROWS S LAST_UPDATE_TIME
----- ---------- ---------- -------------- ---------------- ---------- - ---------------------------------------------------------------------------
SYS HAOCOMP1 41
BUCKET DIF
1000 Y 20-OCT-09 08.56.09.074846 PM
SYS HAOCOMP1 42
41 FINAL BUCKET DIF999 N 20-OCT-09 08.56.09.078049 PM
以上我们见到了又一种Scan的status：FINAL BUCKET DIF。
这其实就表明了这次SCAN是perform_row_dif=false的。
5.Cyclic Scan Mode的特别发现
Cyclic Scan是指一次compare只Scan部分的行，他也必须制定Scan_percent。
如下我删除了hao2的最后一条记录，这样如果是Cyclic Scan，并且percent是50%，那么只有第二次才会显示有difference。
create table hao1.hao1 as select * from dba_objects where rownum select max(object_id) from hao1;
MAX(OBJECT_ID)
--------------
24001
delete from hao2.hao2 where object_id=24001;
begin
dbms_comparison.create_comparison(
comparison_name=>'HAOCOMP1',
schema_name=>'HAO1',
object_name=>'HAO1',
dblink_name=>NULL,
remote_schema_name=>'HAO2',
remote_object_name=>'HAO2',
SCAN_MODE=>DBMS_COMPARISON.CMP_SCAN_MODE_CYCLIC,
SCAN_PERCENT=>50
);
end;
/
第一次run compare结果：
the tables are equivalent.
第二次run compare结果：
Bad news... there is data divergence.
Check the dba_comparison and dba_comparison_scan_summary views for locate the differences for scan_id:44
SQL> select * from dba_comparison_scan;
OWNER COMPARISONSCAN_ID PARENT_SCAN_ID STATUS COUNT_ROWS S LAST_UPDATE_TIME
----- ---------- ---------- -------------- ---------------- ---------- - ---------------------------------------------------------------------------
SYS HAOCOMP1 43
SUC
999 Y 20-OCT-09 09.16.05.391084 PM
SYS HAOCOMP1 44
BUCKET DIF
1 N 20-OCT-09 09.16.11.799654 PM
SYS HAOCOMP1 45
44 ROW DIF
1 N 20-OCT-09 09.16.11.801492 PM
会run两次才能发现difference，这是我事先预料到的。
但是，出乎我的预料的是第一次扫描了999行，第二次只扫描了1行。
但我的scan_percent是50%啊，这种奇怪的表现一直存在，即使我减小了percent的大小。
于是我减小percent到15，这样我预计可以进行100/15=6.67，即7次。
事实也是这样的，直到我run到第七次，才发现最后一行的difference。
SQL>select * from dba_comparison_scan;
OWNER COMPARISONSCAN_ID PARENT_SCAN_ID STATUS
COUNT_ROWS S LAST_UPDATE_TIME
----- ---------- ---------- -------------- ---------------- ---------- - ---------------------------------------------------------------------------
SYS HAOCOMP1 58
SUC
242 Y 20-OCT-09 10.16.50.377451 PM
SYS HAOCOMP1 59
SUC
367 N 20-OCT-09 10.17.07.973618 PM
SYS HAOCOMP1 60
SUC
262 N 20-OCT-09 10.17.17.514272 PM
SYS HAOCOMP1 61
SUC
128 N 20-OCT-09 10.17.26.085557 PM
SYS HAOCOMP1 62
SUC
0 N 20-OCT-09 10.17.34.020262 PM
SYS HAOCOMP1 63
SUC
0 N 20-OCT-09 10.17.41.976292 PM
SYS HAOCOMP1 64
BUCKET DIF
1 N 20-OCT-09 10.17.49.906719 PM
SYS HAOCOMP1 65
64 ROW DIF
1 N 20-OCT-09 10.17.49.908470 PM
以下我的黄色部分论述有误：
“
所以，我总结出来Cyclic Scan Mode并不是完全按照scan_percent来决定每次scan的行数。当scan_percent越小的时候，即compare的次数越多时，每次scan的行数精确度才最高。
这里还有个特点，比如Oracle会根据scan_percent决定未来compare的次数，这个次数Oracle是把他钉死了的。如果还没有compare到最后这次就快没有行数拿来compare了，Oracle会拼死留下一行给预先估算好的最后一次compare。
于是就会出现未到最后一次compare时，有可能有scan 0行的compare出现。例如以上这个例子，第5，6次compare都没有compare任何行。
基于Cyclic的这个特性，我建议大家以后在使用Cyclic Scan时，scan_percent设得小一点。
”
--更改如下：
在查询完DBA_COMPARISON_SCAN_VALUES这个视图之后，我突然发现我前面想错了一点。
即scan_percent原来不是指这个表行数的一个percent，而是指compare根据的index的column的值来进行划分的percent。
SQL> select SCAN_ID,MIN_VALUE,MAX_VALUE,MAX_VALUE-MIN_VALUE from DBA_COMPARISON_SCAN_VALUES;
SCAN_ID MIN_VALUEMAX_VALUEMAX_VALUE-MIN_VALUE
---------- ---------- ---------- -------------------
58 2
3601.85
3599.85
59 3601.857201.7
3599.85
60 7201.7 10801.55
3599.85
61 10801.55 14401.4
3599.85
62 14401.418001.25
3599.85
63 18001.25 21601.1
3599.85
64 21601.124001
2399.9
65 2400124001
0
如上，于是我终于明白了为什么会有一次compare 0行的情况了。
Oracle根据index上最大值和最小值来计算每次compare的percent。而恰好在第5，6次compare的时候，落在这个区间的index column没有值。
于是结论就变成了：请在使用Cyclic Scan时，注意compare使用的index上的column是否有特别倾斜的值。如果是，那么其中某次compare可能时间就比其他compare久很多。
6.Custom Scan的注意事项
有时我们会有这样的困惑，在生产数据库中的这个表上有无数的索引，到底Compare的时候会选择哪个呢？这时，我们可以自定义地选择其中一条最优的索引，例如PK，或者其他。
create index hao1.hao1idx3 on hao1.hao1(CREATED);
create index hao2.hao2idx3 on hao2.hao2(CREATED);
首先是建立Compare的时候，一定要加上指定的index：
begin
dbms_comparison.create_comparison(
comparison_name=>'HAOCOMP1',
schema_name=>'HAO1',
object_name=>'HAO1',
dblink_name=>NULL,
remote_schema_name=>'HAO2',
remote_object_name=>'HAO2',
index_schema_name => 'HAO1',
index_name => 'HAO1IDX3',
SCAN_MODE=>DBMS_COMPARISON.CMP_SCAN_MODE_CUSTOM
);
end;
/
其次需要你查看一下是否是你希望的index，因为如果你指定的index不满足我们前面讲的约束的话，那么你指定无效。
SQL> SELECT COLUMN_NAME, COLUMN_POSITION FROM DBA_COMPARISON_COLUMNS
2WHERE COMPARISON_NAME = 'HAOCOMP1' AND
3INDEX_COLUMN = 'Y';
COLUMN_NAME
COLUMN_POSITION
------------------------------ ---------------
CREATED
1
然后一定要在run compare的时候指定min_value和max_value，否则会报错。
set serveroutput on
declare
compare_info dbms_comparison.comparison_type;
compare_return boolean;
begin
compare_return := dbms_comparison.compare (comparison_name=>'HAOCOMP1',
scan_info=>compare_info,
min_value=>'20081012',
max_value=>'20091012',
perform_row_dif=>TRUE);
if compare_return=TRUE
then
dbms_output.put_line('the tables are equivalent.');
else
dbms_output.put_line('Bad news... there is data divergence.');
dbms_output.put_line('Check the dba_comparison and dba_comparison_scan_summary views for locate the differences for scan_id:'||compare_info.scan_id);
end if;
end;
/
Bad news... there is data divergence.
Check the dba_comparison and dba_comparison_scan_summary views for locate the differences for scan_id:67
检查下发现只有一行被compare：
SQL> select * from DBA_COMPARISON_SCAN;
OWNER COMPARISONSCAN_ID PARENT_SCAN_ID STATUS COUNT_ROWS S LAST_UPDATE_TIME
----- ---------- ---------- -------------- ---------------- ---------- - ---------------------------------------------------------------------------
SYS HAOCOMP1 67
BUCKET DIF
1 N 20-OCT-09 11.42.07.813379 PM
SYS HAOCOMP1 68
67 ROW DIF
1 N 20-OCT-09 11.42.07.814707 PM
验证下是否正确：
SQL> select count(*) from hao1 where created between '20081012' and '20091012';
COUNT(*)
------------------------------
1
7.关于compare的几点performance建议
7.1尽量选择所有列not null的index
在compare的时候我找出来一条全表扫描的SQL：
SELECT COUNT(1) FROM "HAO1"."HAO1" s WHERE ("OBJECT_ID" >= :scan_min
AND "OBJECT_ID"alter table hao1 modify object_id not null;
Table altered.
SELECT COUNT(1) FROM "HAO1"."HAO1" s WHERE ("OBJECT_ID" >= :scan_min
AND "OBJECT_ID" =TO_NUMBER(:SCAN_MIN) AND
"OBJECT_ID"'HAOCOMP1',
scan_id=>101,
scan_info=>compare_info,
converge_options=>dbms_comparison.cmp_converge_local_wins);
dbms_output.put_line('--- Results ---');
dbms_output.put_line('Local rows Merged by process: '||compare_info.loc_rows_merged);
dbms_output.put_line('Remote rows Merged by process: '||compare_info.rmt_rows_merged);
dbms_output.put_line('Local rows Deleted by process: '||compare_info.loc_rows_deleted);
dbms_output.put_line('Remote rows Deleted by process: '||compare_info.rmt_rows_deleted);
end;
/
其中最重要的就是确定到底是按照哪个表同步，所以有这两个不同的option：
dbms_comparison.cmp_converge_local_wins
dbms_comparison.cmp_converge_remote_wins
其实归根到底，Converge其实就是一个简单的merge语句：
MERGE INTO "HAO2"."HAO2" t USING ( SELECT /*+ USE_NL(d s) */s.
"OBJECT_ID" "OBJECT_ID",s."OWNER" "OWNER",s."OBJECT_NAME" "O
BJECT_NAME",s."SUBOBJECT_NAME" "SUBOBJECT_NAME",s."DATA_OBJE
CT_ID" "DATA_OBJECT_ID",s."OBJECT_TYPE" "OBJECT_TYPE",s."CRE
ATED" "CREATED",s."LAST_DDL_TIME" "LAST_DDL_TIME",s."TIMESTA
MP" "TIMESTAMP",s."STATUS" "STATUS",s."TEMPORARY" "TEMPORARY
",s."GENERATED" "GENERATED",s."SECONDARY" "SECONDARY",s."N
AMESPACE" "NAMESPACE",s."EDITION_NAME" "EDITION_NAME",d.rmt_
rowid d_tar_rowid FROM "HAO1"."HAO1" s, "_USER_COMPARISON_ROW_
DIF" d WHERE d.comparison_id = :cmp_id AND d.scan_id =
:scan_id AND d.status= 2 AND d.loc_rowid = s.ROWID AND
d.idx_val = s."OBJECT_ID" ) ss ON (t.ROWID = ss.d_tar_rowid AN
D t."OBJECT_ID" = ss."OBJECT_ID" )WHEN MATCHED THEN UPDATE SET
t."OWNER"=ss."OWNER" , t."OBJECT_NAME"=ss."OBJECT_NAME" , t."SUB
OBJECT_NAME"=ss."SUBOBJECT_NAME" , t."DATA_OBJECT_ID"=ss."DATA_O
BJECT_ID" , t."OBJECT_TYPE"=ss."OBJECT_TYPE" , t."CREATED"=ss."C
REATED" , t."LAST_DDL_TIME"=ss."LAST_DDL_TIME" , t."TIMESTAMP"=s
s."TIMESTAMP" , t."STATUS"=ss."STATUS" , t."TEMPORARY"=ss."TEMPO
RARY" , t."GENERATED"=ss."GENERATED" , t."SECONDARY"=ss."SECONDA
RY" , t."NAMESPACE"=ss."NAMESPACE" , t."EDITION_NAME"=ss."EDITIO
N_NAME" WHEN NOT MATCHED THEN INSERT ("OBJECT_ID" , "OWNER" , "O
BJECT_NAME" , "SUBOBJECT_NAME" , "DATA_OBJECT_ID" , "OBJECT_TYPE
" , "CREATED" , "LAST_DDL_TIME" , "TIMESTAMP" , "STATUS" , "TEMP
ORARY" , "GENERATED" , "SECONDARY" , "NAMESPACE" , "EDITION_NAME
" ) VALUES (ss."OBJECT_ID" , ss."OWNER" , ss."OBJECT_NAME" , s
s."SUBOBJECT_NAME" , ss."DATA_OBJECT_ID" , ss."OBJECT_TYPE" , ss
."CREATED" , ss."LAST_DDL_TIME" , ss."TIMESTAMP" , ss."STATUS" ,
ss."TEMPORARY" , ss."GENERATED" , ss."SECONDARY" , ss."NAMESPAC
E" , ss."EDITION_NAME" )
所以当Converge进行过程中，我们可以看这个SQL的rows_processed来决定当前Converge的进度如何。
9.其他常用的命令
（。。超过帖子3000字数限制，剩余部分见blog原文。。）
|
