By Mouhamadou Diaw
Reading Franck Pachot blog about comparing source and target in a Dbvisit replication (http://dbi-services.com/blog/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
|
1
2
3
4
|
datasource:o.SPLEXDB#source tables target tables routing maptiti.article titi.article_rep [email protected] |
From the source let’s insert 2 rows to verify that replication is working fine.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
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
|
1
2
3
4
5
6
|
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
|
1
2
3
4
5
6
7
|
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 7sp_ctrl (atlas:2104)> |
And now let’s see the status of our replication
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
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' commandJob ID : 7PID : 8644Host : atlas.localdomainStarted : 06-JUL-16 11:34:48Job Type : CompareStatus : Done - 1 object completedID Tablename Status Time Total Rows %Comp Total Time------ ------------------------------------ ---------- ---------- ---------- ----- ----------1 "TITI"."ARTICLE" In Sync N/A 2000002 100 0:31sp_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)
|
1
2
3
4
5
6
7
8
9
|
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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
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 2000000003SQL> select count(*) from article; COUNT(*)---------- 2000003SQL> |
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
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> select * from article_rep where idart=2000003; IDART DESIGNATION PRIX---------- ------------------------------ ---------- 2000003 2000003_designation 2000000003SQL> select count(*) from article_rep; COUNT(*)---------- 2000002 |
Let’s run again compare command on the source and let’s see the output now
|
1
|
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)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
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' commandJob ID : 9PID : 12583Host : atlas.localdomainStarted : 06-JUL-16 13:24:48Job Type : CompareStatus : Done - 1 object completedID 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.
|
1
2
3
4
5
|
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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
[oracle@atlasrep2 log]$ less declt_10-1_SPLEXDB_192.168.1.40_p9883.logdeclt 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 : Offdeclt 2016-07-06 13:36:45.229009 9883 1352263552 Job ID : 10declt 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.localdomaindeclt 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: atlasrep2@SPLEXSTR2declt 2016-07-06 13:36:45.229140 9883 1352263552 Batch Size : 100000 rowsdeclt 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: 9883declt 2016-07-06 13:36:45.229155 9883 1352263552 Read Buffer Size : 1 mbytesdeclt 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, 3declt 2016-07-06 13:36:54.064711 9883 1352263552 Current status: got batch of rowsdeclt 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 processdeclt 2016-07-06 13:37:04.616704 9883 1352263552 Leaving de_msg_clt_preCompareMessaging1_5,L1297declt 2016-07-06 13:37:06.659513 9883 1352263552 Leaving de_msg_clt_preCompareMessaging2,L614declt 2016-07-06 13:37:06.659716 9883 1352263552 No new key column order, using default column order for TITI.ARTICLE_REPdeclt 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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
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' commandJob ID : 10PID : 13008Host : atlas.localdomainStarted : 06-JUL-16 13:36:37Job Type : RepairStatus : Done - 1 object completedID 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
|
1
2
3
4
5
|
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.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL> select count(*) from article_rep; COUNT(*)---------- 2000003SQL> 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).
|
1
|
<br> |
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/12/oracle-square.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)