Infrastructure at your Service

Reading Franck Pachot blog about comparing source and target in a Dbvisit replication (http://blog.dbi-services.com/compare-source-and-target-in-a-dbvisit-replication/), I decide to write a small article about how we can do same thing with SharePlex. Indeed SharePlex provides built-in commands to compare and repair synchronization.
Below is our configuration file. We are just replicating table article to article_rep. Both tables contain 2000000 rows

datasource:o.SPLEXDB

#source tables target tables routing map
titi.article titi.article_rep [email protected]

From the source let’s insert 2 rows to verify that replication is working fine.

SQL> insert into article values (2000001,'2000001_designation',2000000001);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into article values (2000002,'2000002_designation',2000000002);

1 row created.

SQL> commit;

Commit complete.


SQL> select * from article where idart in (2000001,2000002);

     IDART DESIGNATION                          PRIX
---------- ------------------------------ ----------
   2000001 2000001_designation            2000000001
   2000002 2000002_designation            2000000002

From the target let’s verify that the 2 rows are replicated

SQL> select * from article_rep where idart in (2000001,2000002);

     IDART DESIGNATION                          PRIX
---------- ------------------------------ ----------
   2000001 2000001_designation            2000000001
   2000002 2000002_designation            2000000002

To compare source and target in SharePlex, the command compare is used. Let’s see a demonstration.
From the source 

sp_ctrl (atlas:2104)>  compare titi.article to titi.article_rep at [email protected] for o.SPLEXDB

  comparing 1 of 1 objects

  compare started; job id 7

sp_ctrl (atlas:2104)>

And now let’s see the status of our replication

sp_ctrl (atlas:2104)> show compare

   The syntax to get status for the compare command has changed
   Instead of 'show compare' use the 'compare status' or 'repair status' command


Job ID    : 7
PID       : 8644
Host      : atlas.localdomain
Started   : 06-JUL-16 11:34:48
Job Type  : Compare
Status    : Done - 1 object completed

ID     Tablename                            Status     Time       Total Rows %Comp Total Time
------ ------------------------------------ ---------- ---------- ---------- ----- ----------
1      "TITI"."ARTICLE"                     In Sync           N/A    2000002   100       0:31


sp_ctrl (atlas:2104)>

We see that tables are in sync. When running the compare command, SharePlex obtains a brief exclusive lock on the source table to get read consistency for its row selection.
On the target system, SharePlex obtains an exclusive lock on the target table and retains the lock for the duration of the comparison of that table.
Now let’s Sync out our replication
From the target let’s delete a row (note that we have one directional replication only from source to target)

SQL> delete from article_rep where idart=2000002;

1 row deleted.

SQL> commit;

Commit complete.

SQL>

And from the source let’s insert a row

SQL> insert into article values (2000003,'2000003_designation',2000000003);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from article where idart=2000003;

     IDART DESIGNATION                          PRIX
---------- ------------------------------ ----------
   2000003 2000003_designation            2000000003


SQL> select count(*) from article;

  COUNT(*)
----------
   2000003

SQL>

From the target we can verify that the last row with idart=2000003 is present but the number of rows is now different between the 2 tables. Indeed we deleted the row with idart=2000002 in the target and this deletion is not replicated in the source. Now replication is sync out

SQL> select * from article_rep where idart=2000003;

     IDART DESIGNATION                          PRIX
---------- ------------------------------ ----------
   2000003 2000003_designation            2000000003

SQL> select count(*) from article_rep;

  COUNT(*)
----------
   2000002

Let’s run again compare command on the source and let’s see the output now

sp_ctrl (atlas:2104)> compare titi.article to titi.article_rep at [email protected] for o.SPLEXDB

We can see that the status is Sync Out (note that comparison of 2000000 rows was very quick)

sp_ctrl (atlas:2104)> show compare

   The syntax to get status for the compare command has changed
   Instead of 'show compare' use the 'compare status' or 'repair status' command


Job ID    : 9
PID       : 12583
Host      : atlas.localdomain
Started   : 06-JUL-16 13:24:48
Job Type  : Compare
Status    : Done - 1 object completed

ID     Tablename                            Status     Time       Total Rows %Comp Total Time
------ ------------------------------------ ---------- ---------- ---------- ----- ----------
1      "TITI"."ARTICLE"                     Out Sync          N/A    2000003   100       0:25

To sync In  the replication we just have to use the repair command. This command is magic.

sp_ctrl (atlas:2104)> repair  titi.article to titi.article_rep at [email protected] for o.SPLEXDB

  repairing 1 of 1 objects

  repair started; job id 10

In the Log file on the target we can see that SharePlex is using ORA_HASH to compare

[[email protected] log]$ less declt_10-1_SPLEXDB_192.168.1.40_p9883.log
declt    2016-07-06 13:36:45.228937 9883 1352263552 NOTE: setting up HASH column info (de_table_setup_ora_hash_compare,L2915)
declt    2016-07-06 13:36:45.228971 9883 1352263552 Key Compare : Off
declt    2016-07-06 13:36:45.229009 9883 1352263552 Job ID      : 10
declt    2016-07-06 13:36:45.229017 9883 1352263552 Source Table: "TITI"."ARTICLE"
declt    2016-07-06 13:36:45.229122 9883 1352263552 Source Node : atlas.localdomain
declt    2016-07-06 13:36:45.229130 9883 1352263552 Target Table: "TITI"."ARTICLE_REP"
declt    2016-07-06 13:36:45.229135 9883 1352263552 Target Route: [email protected]
declt    2016-07-06 13:36:45.229140 9883 1352263552 Batch Size  : 100000 rows
declt    2016-07-06 13:36:45.229145 9883 1352263552 Repair      : On (2 Pass)
declt    2016-07-06 13:36:45.229150 9883 1352263552 sp_declt PID: 9883
declt    2016-07-06 13:36:45.229155 9883 1352263552 Read Buffer Size : 1 mbytes
declt    2016-07-06 13:36:45.237727 9883 1352263552 Current status: ready to fetch batch,of rows; including the order by sorting.
declt    2016-07-06 13:36:45.237782 9883 1352263552 running sql statement : (de_select_prepare_to_fetch,L1384)
declt    2016-07-06 13:36:45.237795 9883 1352263552   SELECT /*+ PARALLEL (A,2) */ ROWID,ORA_HASH("IDART"||'~'||"PRIX"), ORA_HASH("DESIGNATION") FROM "TITI"."ARTICLE_REP" A ORDER BY 2, 3
declt    2016-07-06 13:36:54.064711 9883 1352263552 Current status: got batch of rows
declt    2016-07-06 13:37:04.527252 9883 1352263552 NOTE: total number of rows fetched = 2000002 (../src/deqtr/de_select.cpp,L1630)
declt    2016-07-06 13:37:04.533329 9883 1352263552 Notice: starting the second phase in the repair process
declt    2016-07-06 13:37:04.616704 9883 1352263552 Leaving de_msg_clt_preCompareMessaging1_5,L1297
declt    2016-07-06 13:37:06.659513 9883 1352263552 Leaving de_msg_clt_preCompareMessaging2,L614
declt    2016-07-06 13:37:06.659716 9883 1352263552 No new key column order, using default column order for TITI.ARTICLE_REP
declt    2016-07-06 13:37:06.721957 9883 1352263552 Current status: ready to fetch batch,of rows; including the order by sorting.
declt    2016-07-06 13:37:06.721996 9883 1352263552 running sql statement : (de_select_prepare_to_fetch,L1384)

When issuing again the show compare command, we can see that the status is now Repaired.

sp_ctrl (atlas:2104)> show compare

   The syntax to get status for the compare command has changed
   Instead of 'show compare' use the 'compare status' or 'repair status' command


Job ID    : 10
PID       : 13008
Host      : atlas.localdomain
Started   : 06-JUL-16 13:36:37
Job Type  : Repair
Status    : Done - 1 object completed

ID     Tablename                            Status     Time       Total Rows %Comp Total Time
------ ------------------------------------ ---------- ---------- ---------- ----- ----------
1      "TITI"."ARTICLE"                     Repaired          N/A    2000003   100       0:29

From the source we can verify the number of rows

SQL> select count(*) from article;

  COUNT(*)
----------
   2000003

From the target we also can verify that we have the same number of rows and that the row deleted (idart=2000002) is now present.

SQL> select count(*) from article_rep;

  COUNT(*)
----------
   2000003


SQL> select * from article_rep  where idart=2000002;

     IDART DESIGNATION                          PRIX
---------- ------------------------------ ----------
   2000002 2000002_designation            2000000002

Conclusion

We will retain that SharePlex provides powerful commands compare/repair to fix out of synchronization. Just note that during execution of these commands tables are locked (briefly on the source and more on the target) meaning that no transaction will not be possible during this period.
Note that the compare and repair commands can also be used for initial load (not recommended because of locks).


2 Comments

  • Saurabh says:

    Hello Diaw,

    I canceled the compare job but still compare status show its in process. Can you help where i am wrong.

    Below is the log.

    compare status

    Job ID : 5
    PID : 11597
    Host :
    Started : 24-OCT-16 13:47:02
    Job Type : Compare
    Status : Processing – 0 objects completed

    ID Tablename Status Time Total Rows %Comp Total Time
    —— ———————————— ———- ———- ———- —– ———-
    1 “SIT_SRCH_MASTER”.”COLLECTION” WaitMarker 2:26:00 152 2:26:02

    Job ID : 6
    PID : 22838
    Host :
    Started : 24-OCT-16 14:01:33
    Job Type : Compare
    Status : Processing – 0 objects completed

    ID Tablename Status Time Total Rows %Comp Total Time
    —— ———————————— ———- ———- ———- —– ———-
    1 “SIT_SRCH_MASTER”.”COLLECTION” WaitMarker 2:11:29 152 2:11:30

    cancel 5

    Canceled job 5

    cancel 6

    Canceled job 6

    compare status

    Job ID : 5
    PID : 11597
    Host :
    Started : 24-OCT-16 13:47:02
    Job Type : Compare
    Status : Processing – 0 objects completed

    ID Tablename Status Time Total Rows %Comp Total Time
    —— ———————————— ———- ———- ———- —– ———-
    1 “SIT_SRCH_MASTER”.”COLLECTION” WaitMarker 2:26:19 152 2:26:21

    Job ID : 6
    PID : 22838
    Host :
    Started : 24-OCT-16 14:01:33
    Job Type : Compare
    Status : Processing – 0 objects completed

    ID Tablename Status Time Total Rows %Comp Total Time
    —— ———————————— ———- ———- ———- —– ———-
    1 “SIT_SRCH_MASTER”.”COLLECTION” WaitMarker 2:11:48 152 2:11:49

    I just removed the host name for security purpose.

    I checked the PID’s and the do not exits any more.

    Dont know why they are now getting cleared.

    Job ID Type filename/tablename Status Started Completed
    ——- ——— —————————— ————— ————— —————
    1 Compare splex.test_repl Done 13-SEP-16 11:02 13-SEP-16 11:03
    2 Compare SIT_SRCH_MASTER.COLLECTION Done – errors 24-OCT-16 11:43 24-OCT-16 12:15
    3 Repair SIT_SRCH_MASTER.COLLECTION Done – errors 24-OCT-16 11:47 24-OCT-16 12:19
    4 Compare SIT_SRCH_MASTER.COLLECTION Done – errors 24-OCT-16 11:52 24-OCT-16 12:24
    5 Compare SIT_SRCH_MASTER.COLLECTION Processing 24-OCT-16 13:47
    6 Compare SIT_SRCH_MASTER.COLLECTION Processing 24-OCT-16 14:01

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Mouhamadou Diaw
Mouhamadou Diaw

Consultant