To force the conflict, the target table is going to be updated to a different value to the source table.
1. On the target table on the target server (dbv02), run the following SQL to update the value
sqlplus oe/oe SQL> update SALES set AMOUNT_RECEIVED = 60 where SALES_STATUS = 'OVERDUE' and PROD_ID = 101; commit;
2. The AMOUNT_RECEIVED column is now different on the target as what it is on the source for record PROD_ID = 101. On the target database it is 60 and the source database it is NULL
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 oe/oe SQL> update SALES set AMOUNT_RECEIVED = 120 where SALES_STATUS = 'OVERDUE' and PROD_ID = 101;
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 created
5. On the replication console the conflict will be shown
APPLY IS running. Currently at plog 724 and SCN 14858536 (12/29/2013 13:59:45) and 1 apply conflicts so far (last at 29/12/2013 14:01:18) and WAITING on manual resolve of apply conflict id 724010046998.
6. The type of conflict is shown against the sales table. The conflict is "Command affected 0 rows"
OE.SALES: 80% Mine:5/5 Unrecov:0/0 Applied:4/4 Conflicts:1/1 Last:29/12/2013 13:59:38/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: OE.SALES at transaction 001e.008.00000060 at SCN 14858537 SQL text (with replaced bind values): update OE."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: PAUSE Conflict repeated 21 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 predicate. This is true for most logical replication technologies.
9. Decide which value should be kept on the target database. Do you want to keep the value of AMOUNT_RECEIVED = 60 or AMOUNT_RECEIVED = 120? In this case we are going to keep the value of AMOUNT_RECEIVED = 120 as this is the value on the source system.
0 Comments