Page Comparison

Ok, 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. 1. 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

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 
   WHERE sales_status = 'OVERDUE' 
   AND prod_id = 101;
COMMIT;

...

repoe@TARGET
REPOE@TARGET> update REPOE.PRODUCT_INFORMATION set PRODUCT_NAME='Dbvisit Standby' where product_id=100;
REPOE@TARGET> 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 (with a different value) and see what happens when Replicate tries to apply that change on the target.

3. Update the AMOUNT_RECEIVED for PRODPRODUCT_INFORMATION for PRODUCT_ID= 101 on 100 on the source database. On source server run the following SQL to update the value:

sqlplus repoe/repoe@ttorcl_src

UPDATE sales
   SET amount_received = 120 
   WHERE sales_status = 'OVERDUE' 
   AND prod_id = 101;
COMMIT;

...

repoe@SOURCE
REPOE@SOURCE> update REPOE.PRODUCT_INFORMATION set PRODUCT_NAME='Dbvisit Replicate' where product_id=100;
REPOE@SOURCE> commit;

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 is created.5. , 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 72465 and SCN 148585361482983 (02/15/2015 10:51:30) and 1 apply conflicts so far (last at 15/02/2015 10:51:45) and WAITINGWILL onRETRY manualSQL resolvestatement of apply conflict id 724010046998.65010063922

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

...

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:46/40         Conflicts:10/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 72401004699865010063922 (current conflict):
Table: OEREPOE.SALESPRODUCT_INFORMATION at transaction 001e0002.008002.00000060000006da at SCN 31445141482974
SQL text (with replaced bind values): update OE"REPOE"."SALESPRODUCT_INFORMATION"
set AMOUNT"PRODUCT_RECEIVEDNAME" = 120'Dbvisit Replicate'
where (1=1)
and AMOUNT_RECEIVED IS NULL "PRODUCT_NAME" = '3qmaQueJ1Ou 5 aNzkUP8cmTjrO lvPj'
and PROD"PRODUCT_ID" = 101100
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 systemAt 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. Remember the ID of the conflict (65010063922). We will use this number for resolving the conflict in next chapter.

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.