Page Comparison

To force the conflict, the target table is going to be updated to Ok, now we have set up the data for our test, lets do something that you wouldn't normally do. That is, we need to force a conflict between the source and target table by deliberately updating a record in the target table to a different value than the in the source table table.

1. On the target table on the target server (dbv02), run the following SQL to update the value

sqlplus oerepoe/oe@ttorclrepoe@ttorcl_trg

The current value of the amount_received for prod_id 101 in the SALES table is NULL. Update this to 60 on the target.

UPDATE sales 
   SET amount_received = 60 
   whereWHERE SALESsales_STATUSstatus = 'OVERDUE' 
   andAND PRODprod_IDid = 101;
commitCOMMIT;

2. The AMOUNT_RECEIVED column in record PROD_ID = 101 on 101 in the target table now differs from the source.

Now lets update the same column in the same record on the source (with a different value) and see what happens when Replicate tries to apply that change on the target.

3. Update the AMOUNT_RECEIVED for PROD_ID = 101 on the source database. On source server (dbv01) run the following SQL to update the value

sqlplus oerepoe/oe@ttorclrepoe@ttorcl_src

SQL>UPDATE sales
 update SALES setSET AMOUNTamount_RECEIVEDreceived = 120 
where SALES_STATUS   WHERE sales_status = 'OVERDUE' 
and PROD_ID   AND prod_id = 101;
commitCOMMIT;

4. This will create a conflict because the update statement will be replicated to the target database. The target value was already changed and so a conflict was is created.

5. On the replication console the conflict will be shown.

...

6. The type of conflict is shown against the sales table. The conflict is "Command affected 0 rows".

OEREPOE.SALES:                      80%  Mine:5/5             Unrecov:0/0         Applied:4/4         Conflicts:1/1       RETRY:Command affected 0 row(s).

7. Type " list conflict" in the command console to view the complete SQL statement causing the conflict

dbvrep> list conflict                                                                                                                                                           
Information for conflict 724010046998 (current conflict):
Table: OEREPOE.SALES at transaction 001e.008.00000060 at SCN 3144514
SQL text (with replaced bind values): update OE"REPOE"."SALES"
set AMOUNT_RECEIVED = 120
where (1=1)
and AMOUNT_RECEIVED IS NULL 
and PROD_ID = 101
Error: Command affected 0 row(s).
Handled as: PAUSERETRY
Conflict repeated 218 times.

8. As can be seen from the SQL statement in the above list conflict, the SQL statement contains the OLD value of "AMOUNT_RECEIVED IS NULL". This is the reason for the "Command affected 0 rows". The AMOUNT_RECEIVED was updated from NULL to 60 on the target database in step 1 and so the original value of "AMOUNT_RECEIVED IS NULL" is no longer valid. In order to detect such changes in the data, the replication has added the old value of the value to be update in the where WHERE predicate. This is true for most logical replication technologies.

...