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
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.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.