...
- no rows affected
- more than one row affected
- other type of error
In this previous chapter we will introduce first 2 types of conflictintroduced the first type of the conflict, now we will introduce 2nd type of conflict - more rows affected.
Creation of duplicate rows (potential more than one row affected conflict):
In order to simulate conflict in replication, we have to create at least one row, that is precise clone of another row. This cannot be created done on table, that has primary key defined. In RepAttack prepared testing environment, there are just two tables, that do not have primary keys defined:
REPOE.LOGON and REPOE.ORDERENTRY_METADATA. We will use the REPOE.LOGON table for duplicate creation. Run following insert on source database to create duplicity:
| No Format | ||
|---|---|---|
| Code Block | ||
| ||
REPOE@TTORCL_SRC>REPOE@SOURCE> insert into repoe.logon select * from repoe.logon where logon_id=1000000; 1 row created. REPOE@TTORCL_SRC>REPOE@SOURCE> commit; Commit complete. |
Wait until the row gets replicated and confirm it also with dbvrep console.
Check, that the duplicated row also exists in the target database.
| Code Blocknoformat | ||
|---|---|---|
| ||
REPOE@TTORCL_trg>REPOE@TARGET> select * from repoe.logon where logon_id=1000000 order by customer_id; LOGON_ID CUSTOMER_ID LOGON_DATE ---------- ----------- ------------------ 1000000 496840 31-MAR-06 1000000 496840 31-MAR-06 |
Create conflict
Using the duplicate row we prepared in previous chapter, we can now simulate conflict in replication.
On source database run following update command.
| Code Blocknoformat | ||
|---|---|---|
| ||
REPOE@TTORCL_SRC>REPOE@SOURCE> update repoe.logon set logon_id=logon_id*100 where logon_id=1000000; 2 rows updated. REPOE@TTORCL_SRC>REPOE@SOURCE> commit; Commit complete. |
Check in the console, that we have really created the conflict
| Code Blocknoformat |
|---|
REPOE.LOGON: 33% Mine:3/3 Unrecov:0/0 Applied:1/1 Conflicts:1/1 Last:23/01/2015 04:05:29/RETRY:Command affected 2 row(s). |
At this moment the apply process stopped and is waiting for manual resolve of the conflict.
Listing and Resolving Conflicts
As we have created a conflict, we have to see what exactly happened.
Connect to dbvrep console and run command list conflict
| Code Blocknoformat |
|---|
dbvrep> list conflict
Information for conflict 32010159940 (current conflict):
Table: REPOE.LOGON at transaction 000a.01f.000001df at SCN 653532
SQL text (with replaced bind values): update "REPOE"."LOGON"
set "LOGON_ID" = 100000000
where (1=1)
and "LOGON_ID" = 1000000
and "CUSTOMER_ID" = 496840
and "LOGON_DATE" = to_date('2006.03.31 04:18:40','yyyy.mm.dd hh24:mi:ss')
Error: Command affected 2 row(s).
Handled as: PAUSE
Conflict repeated 21 times. |
...
Now we have to resolve the conflict in order to let apply process to continue. Lets choose ignore resolution type:
| Code Blocknoformat |
|---|
dbvrep> resolve conflict 32010159940 as ignore Conflict resolution set. |
Ignore means, that the row will be discarded. Now look at the top of the console if this resolution unblocked apply process:
| Code Blocknoformat |
|---|
APPLY IS running. Currently at plog 32 and SCN 653526 (01/23/2015 04:05:20) and 2 apply conflicts so far (last at 24/01/2015 00:14:17) and WAITING on manual resolve of apply conflict id 32010159942 Progress of replication repoe:MINE->APPLY: total/this execution ------------------------------------------------------------------------------------------------------------------ REPOE.CARD_DETAILS: 100% Mine:2/2 Unrecov:0/0 Applied:2/2 Conflicts:0/0 Last:22/01/2015 23:29:39/OK REPOE.LOGON: 66% Mine:3/3 Unrecov:0/0 Applied:2/2 Conflicts:2/2 Last:24/01/2015 00:12:37/RETRY:Command affected 2 row(s). ------------------------------------------------------------------------------------------------------------------ |
What happened? The apply process is still stuck. The count of conflicts raised by 1 and apply is again waiting for manual resolution. Lets check the conflicts again:
| Code Blocknoformat |
|---|
dbvrep> list conflict
Information for conflict 32010159942 (current conflict):
Table: REPOE.LOGON at transaction 000a.01f.000001df at SCN 653533
SQL text (with replaced bind values): update "REPOE"."LOGON"
set "LOGON_ID" = 100000000
where (1=1)
and "LOGON_ID" = 1000000
and "CUSTOMER_ID" = 496840
and "LOGON_DATE" = to_date('2006.03.31 04:18:40','yyyy.mm.dd hh24:mi:ss')
Error: Command affected 2 row(s).
Handled as: PAUSE
Conflict repeated 21 times. |
...
Lets resolve the second conflict as force. The force option means, that the command will be run against the target database and conflict will be ignored:
| Code Blocknoformat |
|---|
dbvrep> resolve conflict 32010159942 as force Conflict resolution set. |
Now we can see, that the apply process finally got unstucked.
| Code Blocknoformat |
|---|
APPLY IS running. Currently at plog 32 and SCN 653550 (01/23/2015 04:05:25) |
Now we have to check whether our two rows got replicated:
| Code Blocknoformat |
|---|
REPOE@TTORCL_trg>REPOE@TARGET> select count(*) from repoe.logon where logon_id=1000000; COUNT(*) ---------- 0 REPOE@TTORCL_trg>REPOE@TARGET> select count(*) from repoe.logon where logon_id=100000000; COUNT(*) ---------- 2 |
...