By Franck Pachot
.
Usually, the logical replication of changes uses the primary key. Each row updated or deleted generate a statement to be applied on the target, which affects only one row because it accesses with the primary key. If there is no primary key, we need to have something unique and at worst it is the whole row. But sometimes old applications were designed before being implemented into relational database and have no unicity. It it a problem for logical replication? We will see that Dbvisit replicate can address that.
Here is the case I encountered at a customer. The application has a master-detail table design, and the detail tables are inserted/deleted all together for the same master key. And there is no primary key, and even nothing unique. The only value that may help is a timestamp but sometimes timestamps do not have the sufficient precision to be unique. And anyway, imagine what happens if we change back the system time, or during daylight saving changes.
At dbi services we have very good contact with our partner Dbvisit and it’s the kind of question that can be addressed quickly by the support. Anyway, I was at the Oracle Open World and then was able to discuss directly with the Dbvisit replicate developers. There is a solution and it is even documented.
The basic issue is that when the delete occurs, a redo entry is generated for each row that is deleted and then Dbvisit replicate generates an update statement to do the same on the target. But when there are duplicates the first statement will affect several rows and the next statement will affect no rows.
This is the kind of replication complexity that is addressed with conflict resolution. It can be addressed manually: the replication stops when a conflict is detected and continues once we have decided what to do. But we can also set rules to address it automatically when the problem occurs again so that the replication never stops.
Here is the demo about that as I tested it before providing the solution to my customer.
Note that it concerns only deletes here but the same can be done with updates.
1. I create a table with 4 identical rows for each value of N:
create table TESTNOPK as select n,’x’ x from (select rownum n from dual connect by level
SQL> connect repoe/repoe
Connected.
SQL> create table TESTNOPK as select n,’x’ x from (select rownum n from dual connect by level
Table created.
2. Status of replication from the Dbvisit console:
| Dbvisit Replicate 2.7.06.4485(MAX edition) – Evaluation License expires in 29 days
MINE IS running. Currently at plog 35 and SCN 796568 (10/01/2014 01:08:04).
APPLY IS running. Currently at plog 35 and SCN 796566 (10/01/2014 01:08:04).
Progress of replication dbvrep_XE:MINE->APPLY: total/this execution
——————————————————————————————————————————————–
REPOE.CUSTOMERS: 100% Mine:1864/1864 Unrecov:0/0 Applied:1864/1864 Conflicts:0/0 Last:30/09/2014 02:38:30/OK
REPOE.ADDRESSES: 100% Mine:1864/1864 Unrecov:0/0 Applied:1864/1864 Conflicts:0/0 Last:30/09/2014 02:38:30/OK
REPOE.CARD_DETAILS: 100% Mine:1727/1727 Unrecov:0/0 Applied:1727/1727 Conflicts:0/0 Last:30/09/2014 02:38:30/OK
REPOE.ORDER_ITEMS: 100% Mine:12520/12520 Unrecov:0/0 Applied:12520/12520 Conflicts:0/0 Last:30/09/2014 02:38:35/OK
REPOE.ORDERS: 100% Mine:10040/10040 Unrecov:0/0 Applied:10040/10040 Conflicts:0/0 Last:30/09/2014 02:38:35/OK
REPOE.INVENTORIES: 100% Mine:12269/12269 Unrecov:0/0 Applied:12269/12269 Conflicts:0/0 Last:30/09/2014 02:38:35/OK
REPOE.LOGON: 100% Mine:12831/12831 Unrecov:0/0 Applied:12831/12831 Conflicts:0/0 Last:30/09/2014 02:38:35/OK
REPOE.TESTNOPK: 100% Mine:40/40 Unrecov:0/0 Applied:40/40 Conflicts:0/0 Last:01/10/2014 01:08:02/OK
——————————————————————————————————————————————–
8 tables listed.
3. I delete the lines with the value 10:
SQL> select * from TESTNOPK where n=10;
N X
———- –
10 x
10 x
10 x
10 x
SQL> delete from TESTNOPK where n=10;
4 rows deleted.
SQL> commit;
Commit complete.
5. apply is stop on a conflict: too many rows affected by the delete
MINE IS running. Currently at plog 35 and SCN 797519 (10/01/2014 01:10:56).
APPLY IS running. Currently at plog 35 and SCN 796928 (10/01/2014 01:09:08) and 1 apply conflicts so far (last at 01/10/2014 01:10:57) and WAITING on manual resolve of apply conflict id 35010009996.
Progress of replication dbvrep_XE:MINE->APPLY: total/this execution
——————————————————————————————————————————————–
REPOE.CUSTOMERS: 100% Mine:1864/1864 Unrecov:0/0 Applied:1864/1864 Conflicts:0/0 Last:30/09/2014 02:38:30/OK
REPOE.ADDRESSES: 100% Mine:1864/1864 Unrecov:0/0 Applied:1864/1864 Conflicts:0/0 Last:30/09/2014 02:38:30/OK
REPOE.CARD_DETAILS: 100% Mine:1727/1727 Unrecov:0/0 Applied:1727/1727 Conflicts:0/0 Last:30/09/2014 02:38:30/OK
REPOE.ORDER_ITEMS: 100% Mine:12520/12520 Unrecov:0/0 Applied:12520/12520 Conflicts:0/0 Last:30/09/2014 02:38:35/OK
REPOE.ORDERS: 100% Mine:10040/10040 Unrecov:0/0 Applied:10040/10040 Conflicts:0/0 Last:30/09/2014 02:38:35/OK
REPOE.INVENTORIES: 100% Mine:12269/12269 Unrecov:0/0 Applied:12269/12269 Conflicts:0/0 Last:30/09/2014 02:38:35/OK
REPOE.LOGON: 100% Mine:12831/12831 Unrecov:0/0 Applied:12831/12831 Conflicts:0/0 Last:30/09/2014 02:38:35/OK
REPOE.TESTNOPK: 90% Mine:44/44 Unrecov:0/0 Applied:40/40 Conflicts:1/1 Last:01/10/2014 01:09:17/RETRY:Command affected 4 row(s).
——————————————————————————————————————————————–
8 tables listed.
dbvrep> list conflict
Information for conflict 35010009996 (current conflict):
Table: REPOE.TESTNOPK at transaction 0008.003.0000022b at SCN 796930
SQL text (with replaced bind values): delete from “REPOE”.”TESTNOPK”
where (1=1)
and “N” = 10
and “X” = ‘x’
Error: Command affected 4 row(s).
Handled as: PAUSE
Conflict repeated 22 times.
6. I resolve the conflict manually, forcing the delete of all rows
dbvrep> resolve conflict 35010009996 as force
Conflict resolution set.
At that point, there is 3 following conflicts that I need to force as well because of the other deletes affecting no rows. I don’t reproduce them here.
7. Once the conflits are resolved, the replication continues:
MINE IS running. Currently at plog 35 and SCN 800189 (10/01/2014 01:19:16).
APPLY IS running. Currently at plog 35 and SCN 800172 (10/01/2014 01:19:14).
Progress of replication dbvrep_XE:MINE->APPLY: total/this execution
——————————————————————————————————————————————–
REPOE.CUSTOMERS: 100% Mine:1864/1864 Unrecov:0/0 Applied:1864/1864 Conflicts:0/0 Last:30/09/2014 02:38:30/OK
REPOE.ADDRESSES: 100% Mine:1864/1864 Unrecov:0/0 Applied:1864/1864 Conflicts:0/0 Last:30/09/2014 02:38:30/OK
REPOE.CARD_DETAILS: 100% Mine:1727/1727 Unrecov:0/0 Applied:1727/1727 Conflicts:0/0 Last:30/09/2014 02:38:30/OK
REPOE.ORDER_ITEMS: 100% Mine:12520/12520 Unrecov:0/0 Applied:12520/12520 Conflicts:0/0 Last:30/09/2014 02:38:35/OK
REPOE.ORDERS: 100% Mine:10040/10040 Unrecov:0/0 Applied:10040/10040 Conflicts:0/0 Last:30/09/2014 02:38:35/OK
REPOE.INVENTORIES: 100% Mine:12269/12269 Unrecov:0/0 Applied:12269/12269 Conflicts:0/0 Last:30/09/2014 02:38:35/OK
REPOE.LOGON: 100% Mine:12831/12831 Unrecov:0/0 Applied:12831/12831 Conflicts:0/0 Last:30/09/2014 02:38:35/OK
REPOE.TESTNOPK: 100% Mine:44/44 Unrecov:0/0 Applied:44/44 Conflicts:4/4 Last:01/10/2014 01:18:21/RETRY:Command affected 0 row(s).
——————————————————————————————————————————————–
8 tables listed.
dbvrep> list conflict
Information for conflict 0 (current conflict):
No conflict with id 0 found.
8. Now I want to set a rule that manages that situation automatically. I add a ‘too many rows’ conflict rule to touch only one line for each delete:
dbvrep> SET_CONFLICT_HANDLERS FOR TABLE REPOE.TESTNOPK FOR DELETE ON TOO_MANY TO SQL s/$/ and rownum = 1/
Connecting to running apply: [The table called REPOE.TESTNOPK on source is handled on apply (APPLY) as follows:
UPDATE (error): handler: RETRY logging: LOG
UPDATE (no_data): handler: RETRY logging: LOG
UPDATE (too_many): handler: RETRY logging: LOG
DELETE (error): handler: RETRY logging: LOG
DELETE (no_data): handler: RETRY logging: LOG
DELETE (too_many): handler: SQL logging: LOG, regular expression: s/$/ and rownum = 1/
INSERT (error): handler: RETRY logging: LOG
TRANSACTION (error): handler: RETRY logging: LOG]
9. Now testing the automatic conflict resolution:
SQL> delete from TESTNOPK where n=9;
4 rows deleted.
SQL> commit;
Commit complete.
10. the conflicts are automatically managed:
MINE IS running. Currently at plog 35 and SCN 800475 (10/01/2014 01:20:08).
APPLY IS running. Currently at plog 35 and SCN 800473 (10/01/2014 01:20:08).
Progress of replication dbvrep_XE:MINE->APPLY: total/this execution
——————————————————————————————————————————————–
REPOE.CUSTOMERS: 100% Mine:1864/1864 Unrecov:0/0 Applied:1864/1864 Conflicts:0/0 Last:30/09/2014 02:38:30/OK
REPOE.ADDRESSES: 100% Mine:1864/1864 Unrecov:0/0 Applied:1864/1864 Conflicts:0/0 Last:30/09/2014 02:38:30/OK
REPOE.CARD_DETAILS: 100% Mine:1727/1727 Unrecov:0/0 Applied:1727/1727 Conflicts:0/0 Last:30/09/2014 02:38:30/OK
REPOE.ORDER_ITEMS: 100% Mine:12520/12520 Unrecov:0/0 Applied:12520/12520 Conflicts:0/0 Last:30/09/2014 02:38:35/OK
REPOE.ORDERS: 100% Mine:10040/10040 Unrecov:0/0 Applied:10040/10040 Conflicts:0/0 Last:30/09/2014 02:38:35/OK
REPOE.INVENTORIES: 100% Mine:12269/12269 Unrecov:0/0 Applied:12269/12269 Conflicts:0/0 Last:30/09/2014 02:38:35/OK
REPOE.LOGON: 100% Mine:12831/12831 Unrecov:0/0 Applied:12831/12831 Conflicts:0/0 Last:30/09/2014 02:38:35/OK
REPOE.TESTNOPK: 100% Mine:48/48 Unrecov:0/0 Applied:48/48 Conflicts:7/7 Last:01/10/2014 01:19:57/OK
——————————————————————————————————————————————–
8 tables listed.
Now the replication is automatic and the situation is correctly managed.
 |
As I already said, Dbvisit is a simple tool but is nethertheless very powerfull. And Oracle Open World is an efficient way to learn: share knowlege during the day, and test it during the night when you are too jetlagged to sleep…
|
Post Views: 559
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/12/oracle-square.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/JDU_web-min-scaled.jpg)