使用dbms_rectifier_diff解決高級復(fù)制中的數(shù)據(jù)沖突問題作者:eygle出處:http://blog.eygle.com日期:january 19, 2005
« oracle基于時間點的恢復(fù) | blog首頁 | 關(guān)于oracle的沖突解決機制的研究 »
很多時候在高級復(fù)制中可能存在數(shù)據(jù)沖突和不一致現(xiàn)象。
oracle提供的dbms_rectifier_diff包可以用于解決該沖突。
以下通過實例來說明一下該package的用法。
1.創(chuàng)建復(fù)制組及復(fù)制對象
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 min_communication replication_trigger_exists internal_package_exists group_owner nested_table
------------------------------ ------------------------------ ---------------- ---------- ----------------- ---------- -------------------------------------------------------------------------------- -----------------
hawa test table valid generated 8620 rep_tt y y y public n
hawa test$rp package valid 8641 system-generated: replication rep_tt public
hawa test$rp package body valid 8677 system-generated: replication rep_tt public
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.創(chuàng)建保存沖突數(shù)據(jù)的數(shù)據(jù)表
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
沖突記錄被保存在我們創(chuàng)建的指定表中
sql> select count(*) from hawa.missing_rows_test;
count(*)
----------
172
共有172條差異記錄
sql> select count(*) from hawa.test;
count(*)
----------
548
sql> select count(*) from [email protected];
count(*)
----------
376
sql> select count(*) from hawa.missing_location_test;
count(*)
----------
172
4.使用dbms_rectifier_diff.rectify進行數(shù)據(jù)整合
首先需要注意的是:
rectify過程使用differences產(chǎn)生的數(shù)據(jù)進行數(shù)據(jù)調(diào)整。
在第一個表中存在，在第二個表中不存在的數(shù)據(jù)將被插入第二張表。
在第二個表中存在，在第一個個表中不存在的數(shù)據(jù)將被從第二張表中刪除。
另外，在這個數(shù)據(jù)糾正過程中，你可以使用dbms_repcat.suspend_master_activity將復(fù)制組暫時掛起。
這樣便于保證數(shù)據(jù)完整性。
但這不是必須的，如果復(fù)制一直激活，可能會有新的沖突出現(xiàn)。
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 completedsql> select count(*) from [email protected]; count(*)---------- 548sql> select count(*) from hawa.test; count(*)---------- 548
數(shù)據(jù)矯正完成以后，數(shù)據(jù)會自動從missing_rows表中刪除。
sql> select count(*) from hawa.missing_rows_test;
count(*)
----------
0
sql>
網(wǎng)站運營seo文章大全提供全面的站長運營經(jīng)驗及seo技術(shù)!
