05 - Examples of replication

Simple DML operations:

Before we start, we should ensure, that replication is setup correctly and is running. Connect to dbvrep console using script start-console.sh on the source machine:

If everything went fine, you should see at the top of the console output that MINE and APPLY processes are running and applying changes from PLOG files:

MINE IS running. Currently at plog 32 and SCN 573985 (01/22/2015 23:06:56).
APPLY IS running. Currently at plog 32 and SCN 573979 (01/22/2015 23:06:55)

Lets insert some simple row and check in console, how did the replication work.

We are going to insert a row to the table REPOE.CARD_DETAILS. We will create a unique value of CARD_TYPE column in order to be able to check, whether the row was replicated or not.

Check on target database that the row does not exist on the target:

Check if CARD_TYPE REPOE_TEST does exist on target database

select count(*) from REPOE.CARD_DETAILS where card_type='REPOE_TEST';
  COUNT(*)
----------
         0

Now insert a row into REPOE.CARD_DETAILS table.

Open any SQL tool, connect as repoe/repoe@TTORCL_SRC to source database and run following command:

Simple insert operation

REPOE@TTORCL_SRC> insert into REPOE.CARD_DETAILS (card_id, customer_id, card_type, card_number, expiry_date, is_valid, security_code)
2 values (CARD_DETAILS_SEQ.nextval, 1287, 'REPOE_TEST', 9102450385, sysdate, 'Y', 1000 );
1 row created.
REPOE@TTORCL_SRC> commit;
Commit complete.

Now look at the dbvrep console, if the row was successfully replicated;

Output of dbvrep console

Progress of replication repoe:MINE->APPLY: total/this execution
------------------------------------------------------------------------------------------------------------------
REPOE.CARD_DETAILS:           100%  Mine:1/1             Unrecov:0/0         Applied:1/1         Conflicts:0/0       
Last:22/01/2015 23:23:54/OK
------------------------------------------------------------------------------------------------------------------

When we see in the console, that the row was sucessfully replicated to the target database, we should check, if it is correctly replicated. Connect to target database as user repoe/repoe and run following command:

Check if CARD_TYPE REPOE_TEST does exist on target database

select * from REPOE.CARD_DETAILS where card_type='REPOE_TEST';
   CARD_ID CUSTOMER_ID CARD_TYPE                      CARD_NUMBER EXPIRY_DATE        I SECURITY_CODE
---------- ----------- ------------------------------ ----------- ------------------ - -------------
    750005        1287 REPOE_TEST                      9102450385 22-JAN-15          Y          1000

Creation of duplicate rows (potential 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 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:

Insert duplicate row

REPOE@TTORCL_SRC> insert into repoe.logon select * from repoe.logon where logon_id=1000000;
1 row created.
REPOE@TTORCL_SRC> 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.

Check if duplicate row was created

REPOE@TTORCL_trg> 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.

Update causing conflicts

REPOE@TTORCL_SRC> update repoe.logon set logon_id=logon_id*100 where logon_id =1000000;
2 rows updated.
REPOE@TTORCL_SRC> commit;
Commit complete.

Check in the console, that we have really created the conflict

Conflict confirm

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

List conflict example

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.

Take a note of how the original query was transformed. You can see, that except our original "LOGON_ID" = 1000000 condition, there were automatically added "CUSTOMER_ID" = 496840 and "LOGON_DATE" = to_date('2006.03.31 04:18:40','yyyy.mm.dd hh24:mi:ss') search conditions. These were added in order to apply be able to uniquely identify the row it needs to change.

Also look at the row "

Error: Command "affected 2 row(s)." This

Create duplicate using more rows DML

How many conflicts were created (1 conflict for 1 row) = >large transactions are separated into single rows

Resolve conflict

DML operation with trigger / constraint DML - show cascade constraint problem

Prepare sample schema to replicate

With NODDL option

With DDL option