Solve data conflicts in advanced replication using DBMS_Rectifier_Diff
Author: eygle
Source: http://blog.eygle.com
Date: January 19, 2005
«Oracle based on time point recovery | Blog Home | About Oracle Conflict Solution Mechanism Research»
Many times there may be data conflicts and inconsistent phenomena in advanced replication. The DBMS_Rectifier_DIFF package provided by Oracle can be used to resolve this conflict.
The use of this package is explained below.
1. Create a replication group and copy object
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_TTY 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 yyy public nhawa test $ rp package valid 8641 system-generated: replication rep_tt public hawa test $ rp Package Body Valid 8677 System-Generated: Replication Rep_TT PUBLIC3 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. Create a data sheet for saving conflict data
A.Missing_Rows Table To save conflict line SQL> Create Table hawa.missing_rows_test2 as3 select * from hawa.test where 1 = 0;
Table created
b. Used to save missing lines and rowidsql> Create Table hawa.missing_location_test (2 present varcha2 (128), 3 absent varchar2 (128), 4 r_id rowid);
Table created
3. Use dBMS_Rectifier_Diff.differences to find missing records
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 commit_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
Conflict records are saved in the specified table we created in the specified table SQL> Select Count (*) from hawa.missing_rows_test;
COUNT (*) ---------- 172 A total of 172 difference records
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. Use dbms_rectifier_diff.rectify to perform data integration
The first thing to note is that the Rectify process uses DIFFERENCES to perform data adjustment. In the first table, the data that does not exist in the second table will be inserted into the second sheet. In the second table, the data that does not exist in the first one table will be deleted from the second table.
In addition, during this data correction process, you can use dBMS_REPCAT.SUSPEND_MASTER_ACTIVITY to temporarily hang the replication group. This makes it easy to ensure data integrity. But this is not necessary. If the copy is activated, there may be a new conflict.
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 commit_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
After the data is corrected, the data will automatically delete from the missing_rows table.
SQL> SELECT Count (*) from hawa.missing_rows_test;
Count (*) ---------- 0
SQL>
