01 - Create the No Rows Affected Conflict

So 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 in the source table. For our test we will choose PRODUCT_INFORMATION table under REPOE schema.

On the target table on the target server, run the following SQL to update the value

sqlplus repoe/repoe@ttorcl_trg
REPOE@TTORCL_TRG> update REPOE.PRODUCT_INFORMATION set PRODUCT_NAME='Dbvisit Standby' where product_id=100;
REPOE@TTORCL_TRG> commit;

By this command we created a difference between source and target table. Situation, when target and source tables are not in sync can lead to an conflict.

At this moment the PRODUCT_NAME column of table REPOE.PRODUCT_INFORMATION on target database defers from the source database in row with PRODUCT_ID=100.

Now lets update the same column in the same record on the source and see what happens when Replicate tries to apply that change on the target.

Update the PRODUCT_INFORMATION for PRODUCT_ID=100 on the source database. On source server run the following SQL to update the value:

sqlplus repoe/repoe@ttorcl_src
REPOE@TTORCL_SRC> update REPOE.PRODUCT_INFORMATION set PRODUCT_NAME='Dbvisit Replicate' where product_id=100;
REPOE@TTORCL_SRC> commit;

This will create a conflict because the update statement will be replicated to the target database, where the row has already different value, than the source.

On the replication console the conflict will be shown:

APPLY IS running. Currently at plog 65 and SCN 1482983 (02/15/2015 10:51:30) and 1 apply conflicts so far (last at 15/02/2015 10:51:45) and WILL RETRY SQL statement of apply conflict id 65010063922

The default behaviour of resolving conflict is RETRY. After 20 tries, the conflict will be "resolved" as PAUSE, that means waiting for human intervention to resolve the conflict:

APPLY IS running. Currently at plog 65 and SCN 1482983 (02/15/2015 10:51:30) and 1 apply conflicts so far (last at 15/02/2015 10:53:15) and WAITING on manual resolve of apply conflict id 65010063922

From the dbvrep console output you can also see, what type of conflict you are facing. The conflict is "Command affected 0 rows".

------------------------------------------------------------------------------------------------------------------
REPOE.PRODUCT_INFORMATION:     85%  Mine:1/1             Unrecov:0/0         Applied:6/0         Conflicts:0/1       Last:15/02/2015 10:51:35/RETRY:Command affected 0 row(s).
------------------------------------------------------------------------------------------------------------------

Type list conflict in the command console to view the complete SQL statement causing the conflict:

Information for conflict 65010063922 (current conflict):
Table: REPOE.PRODUCT_INFORMATION at transaction 0002.002.000006da at SCN 1482974
SQL text (with replaced bind values): update "REPOE"."PRODUCT_INFORMATION"
set "PRODUCT_NAME" = 'Dbvisit Replicate'
where (1=1)
and "PRODUCT_NAME" = '3qmaQueJ1Ou 5 aNzkUP8cmTjrO lvPj'
and "PRODUCT_ID" = 100
Error: Command affected 0 row(s).
Handled as: PAUSE
Conflict repeated 21 times.

At this moment is worth it, describe columns, that figure in our conflict update. All columns, that have enabled supplemental logging (basicaly adds old column values to the redo logs) are put into the query. By default, when the table has a primary key, then only primary key supplemental logging is enabled ("PRODUCT_ID" = 100). If you enabled supplemental logging on more columns, than all of these would be added into this query.

You can also see in the update past value of the changed column - PRODUCT_NAME. The values of the REPOE.PRODUCT_INFORMATION are randomly generated, that's why the value looks so strange. This command as you see it in list conflict output is run against the target database.

The conflict itself arises, because value of PRODUCT_NAME='3qmaQueJ1Ou 5 aNzkUP8cmTjrO lvPj' and "PRODUCT_ID" = 100 cannot be found on target. 

The method of using past values of columns is common for most logical replication technologies.

For more information about 0 rows affected see Command affected 0 row(s) conflict on our online wiki page.