11G dbms_comparison的研究

最新推荐文章于 2024-10-14 22:42:36 发布

conghuan4367

最新推荐文章于 2024-10-14 22:42:36 发布

阅读量223

点赞数

文章标签：数据库

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
2 dba_comparison_row_dif where comparison_name='HAOCOMP1';

LOCAL_ROWID        REMOTE_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<=1000;
create table hao2.hao2 as select * from hao1.hao1;
create index hao1.hao1idx on hao1.hao1(object_id);
create index hao2.hao2idx on hao2.hao2(object_id);
delete from hao2.hao2 where object_id=6;

最终compre结果：
the tables are equivalent.

于是我一直删除hao2，终于percent 1的compare发现了differences。
SQL> delete from hao2.hao2 where object_id<10;
SQL> delete from hao2.hao2 where object_id<100;
SQL> delete from hao2.hao2 where object_id<200;
SQL> delete from hao2.hao2 where object_id<400;
SQL> delete from hao2.hao2 where object_id<600;

Bad news... there is data divergence.
Check the dba_comparison and dba_comparison_scan_summary views for locate the differences for scan_id:34

SQL> select * from DBA_COMPARISON_SCAN where PARENT_SCAN_ID=34 or SCAN_ID=34;

以上，我们可以见到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时，不检测具体每行的差异。

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;

以上我们见到了又一种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。

MAX(OBJECT_ID)
--------------
24001
delete from hao2.hao2 where object_id=24001;

第一次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;

会run两次才能发现difference，这是我事先预料到的。

但是，出乎我的预料的是第一次扫描了999行，第二次只扫描了1行。

但我的scan_percent是50%啊，这种奇怪的表现一直存在，即使我减小了percent的大小。

于是我减小percent到15，这样我预计可以进行100/15=6.67，即7次。

事实也是这样的，直到我run到第七次，才发现最后一行的difference。

SQL> select * from dba_comparison_scan;

OWNER COMPARISON    SCAN_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_VALUE MAX_VALUE MAX_VALUE-MIN_VALUE
---------- ---------- ---------- -------------------
        58 2          3601.85                3599.85
        59 3601.85    7201.7                 3599.85
        60 7201.7     10801.55               3599.85
        61 10801.55   14401.4                3599.85
        62 14401.4    18001.25               3599.85
        63 18001.25   21601.1                3599.85
        64 21601.1    24001                   2399.9
        65 24001      24001                        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
2 WHERE COMPARISON_NAME = 'HAOCOMP1' AND
3 INDEX_COLUMN = 'Y';

COLUMN_NAME COLUMN_POSITION
------------------------------ ---------------
CREATED 1

然后一定要在run compare的时候指定min_value和max_value，否则会报错。

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;

验证下是否正确：
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" <= :scan_max )OR "OBJECT_ID" IS NULL

-----------------------------------
| Id | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 | SORT AGGREGATE    |      |
|* 2 |   TABLE ACCESS FULL| HAO1 |
-----------------------------------

我在想，如果这个表特别大，如此全表扫描的SQL必然会浪费我们的时间。

而且OBJECT_ID上是有index的，所以，我的建议是我们尽量选择的index上有not null的约束，这样就会走index了。

因此，我马上给object_id这列上加上了not null的约束，再compare一把。

于是compare产生的SQL马上选择走index range scan了。

SQL> 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" <= :scan_max )OR "OBJECT_ID" IS NULL

Plan hash value: 3152400090

-------------------------------------
| Id | Operation         | Name    |
-------------------------------------
|   0 | SELECT STATEMENT |         |
|   1 | SORT AGGREGATE   |         |
|* 2 |   INDEX RANGE SCAN| HAO1IDX |
-------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID">=TO_NUMBER(:SCAN_MIN) AND
"OBJECT_ID"<=TO_NUMBER(:SCAN_MAX))

7.2尽量使用CBO，并且表有分析数据。

在compare的时候，有另一条FTS的SQL。

SELECT MIN("OBJECT_ID"), MAX("OBJECT_ID") FROM "HAO1"."HAO1";

在我的测试机器上，使用的RBO，所以走的FTS。

所以建议尽量保证被compare的表有分析数据，已经使用CBO。

8.Converge简述

Converge就相当于shareplex中的compare/repair中的repair。

declare
compare_info dbms_comparison.comparison_type;
begin
dbms_comparison.converge (comparison_name=>'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.其他常用的命令

9.1 recheck一次scan：

SET SERVEROUTPUT ON
DECLARE
consistent BOOLEAN;
BEGIN
consistent := DBMS_COMPARISON.RECHECK(
comparison_name => 'HAOCOMP1',
scan_id => 101);
IF consistent=TRUE THEN
DBMS_OUTPUT.PUT_LINE('No differences were found.');
ELSE
DBMS_OUTPUT.PUT_LINE('Differences were found.');
END IF;
END;

recheck跟重新再run一遍compare有什么区别呢？

其一，recheck可以只对某一次scan重新check。不用整个进行compare。

其二，不会在dba_comparison_scan等视图中增加更多的行，他只更改某次scan的数据。而如果我重新run compare语句，那么会新增一系列的行在这些视图中。

9.2 清除compare结果。

为了维护系统表，删除历史compare结果。

BEGIN
DBMS_COMPARISON.PURGE_COMPARISON(
comparison_name => 'HAOCOMP1',
scan_id => NULL,
purge_time => NULL);
END;
/

9.3 删除comparison

exec DBMS_COMPARISON.DROP_COMPARISON('HAOCOMP1');

9.4 查看具体哪些行有不一致

COLUMN COLUMN_NAME HEADING 'Index Column' FORMAT A15
COLUMN INDEX_VALUE HEADING 'Index Value' FORMAT A15
COLUMN LOCAL_ROWID HEADING 'Local Row Exists?' FORMAT A20
COLUMN REMOTE_ROWID HEADING 'Remote Row Exists?' FORMAT A20
SELECT c.COLUMN_NAME,
r.INDEX_VALUE,
DECODE(r.LOCAL_ROWID,
NULL, 'No',
'Yes') LOCAL_ROWID,
DECODE(r.REMOTE_ROWID,
NULL, 'No',
'Yes') REMOTE_ROWID
FROM DBA_COMPARISON_COLUMNS c,
DBA_COMPARISON_ROW_DIF r,
DBA_COMPARISON_SCAN s
WHERE c.COMPARISON_NAME = 'HAOCOMP1' AND
r.SCAN_ID = s.SCAN_ID AND
--s.PARENT_SCAN_ID = 100 AND
r.STATUS = 'DIF' AND
c.INDEX_COLUMN = 'Y' AND
c.COMPARISON_NAME = r.COMPARISON_NAME AND
c.OWNER = r.OWNER
ORDER BY r.INDEX_VALUE;

Index Column Index Value Local Row Exists? Remote Row Exists?
--------------- --------------- -------------------- --------------------
OBJECT_ID 24001 Yes No

10.结束语

dbms_comparison确实是一个令人惊喜的package，他提供了对logical replication支持的一个辅助套件。

但在同时，也期待Oracle在将来的版本中增多对其他column的支持，例如LOB字段。

（不支持long raw这种被淘汰的字段还能理解。）

也希望能看到其他朋友使用它的performance的经验之谈。

参考资料：

Oracle Streams
Replication Administrator’s Guide
11g Release 1 (11.1)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15415488/viewspace-617044/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15415488/viewspace-617044/