Using the DBMS

Advanced | resolution | data | Problems using Dbms_rectifier_diff To resolve data conflicts in advanced replication Author: Eygle Origin: http://blog.eygle.com Date: January 19, 2005
«oracle recovery based on Point-in-time | Blog Home | Research on Oracle's conflict resolution mechanism»

Many times there may be data conflicts and inconsistencies in advanced replication.
Oracle-supplied Dbms_rectifier_diff packages can be used to resolve the conflict.

The following examples illustrate the use of the package.

1. Create replication groups and copy objects


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 public N
HAWA test$rp PACKAGE VALID 8641 system-generated:replication 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. Create data tables that hold conflicting data

A.missing_rows table to save conflicting rows
Sql> CREATE TABLE Hawa.missing_rows_test
2 AS
3 SELECT * from Hawa.test where 1=0;

Table created

B. For saving missing row position and rowID
Sql> CREATE TABLE Hawa. Missing_location_test (
2 present VARCHAR2 (128),
3 Absent VARCHAR2 (128),
4 r_id ROWID);

Table created

3. Use Dbms_rectifier_diff. Differences 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 comparison_site => ' AUTHAA.COOLYOUNG.COM.CN ',
8 Where_clause =>null,
9 column_list =>null,
Missing_rows_sname => ' HAWA ',
One 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
16);
The end;
18/
Pl/sql procedure successfully completed





The conflict record is saved in the specified table that we created
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 for data consolidation

The first thing to note is:
The rectify process uses the data produced by differences to adjust data.
In the first table, data that does not exist in the second table is inserted into the second table.
In the second table, data that does not exist in the first table is deleted from the second table.

In addition, in this data correction process, you can use dbms_repcat.suspend_master_activity to temporarily suspend the replication group.
This makes it easier to ensure data integrity.
But this is not necessary, and if replication is active, new conflicts may occur.

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 ', missing_rows_oname1 => ' missing_rows_test ', one MISSI Ng_rows_oname2 => ' missing_location_test ', Missing_rows_site => ' AVATAR. Coolyoung.com. CN ', commit_rows =>100 14); End; 16/pl/sql procedure successfully completedsql> select COUNT (*) from Hawa.test@authaa; COUNT (*)----------548sql> Select COUNT (*) from hawa.test; COUNT (*)----------548



Data is automatically removed from the Missing_rows table after data correction is completed.

Sql> Select COUNT (*) from hawa.missing_rows_test;

COUNT (*)
----------
0

Sql>