By Franck Pachot
.
I’ve tested a little thing for a customer, and as usual I spool the result into a blog post in case it helps others. Not all replication software – at least in their current version – can manage both different target structure (which is often the main reason for logical replication) and keep up with new DDL on the source (which you can’t avoid – applications evolves). Let’s see how smart is Dbvisit replication with that.
I tested that with the #repattack configuration: replicate the Swingbench application on Oracle XE. Here is the customer table:
SQL> desc repoe.customers
Name Null Type
----------------- -------- -------------
CUSTOMER_ID NOT NULL NUMBER(12)
CUST_FIRST_NAME NOT NULL VARCHAR2(40)
CUST_LAST_NAME NOT NULL VARCHAR2(40)
NLS_LANGUAGE VARCHAR2(3)
NLS_TERRITORY VARCHAR2(30)
CREDIT_LIMIT NUMBER(9,2)
CUST_EMAIL VARCHAR2(100)
ACCOUNT_MGR_ID NUMBER(12)
CUSTOMER_SINCE DATE
CUSTOMER_CLASS VARCHAR2(40)
SUGGESTIONS VARCHAR2(40)
DOB DATE
MAILSHOT VARCHAR2(1)
PARTNER_MAILSHOT VARCHAR2(1)
PREFERRED_ADDRESS NUMBER(12)
PREFERRED_CARD NUMBER(12)
This is the structure in both source and target.
Then I add column in destination to show that I can have a different structure:
SQL> alter table repoe.customers add new_in_tgt(char(1))
And I add a column in source to show that I can replicate DDL:
SQL> alter table repoe.customers add (new_in_src varchar(30) default 'xxxxxxxxxx')
It’s on purpose that I added a column that is larger in the source because some replication tools that don’t rely on column names but only on column position fail in that case. When datatype is compatible they don’t fail but just put the value in the wrong column.
My point here is to show that Dbvisit replicate uses column names, so the replication of DML and DDL is possible even when structure differs.
Because I’ve a default clause, it takes a while to update all rows:
| Dbvisit Replicate 2.7.06.4485(MAX edition) - Evaluation License expires in 30 days
MINE IS running. Currently at plog 308 and SCN 3999184 (11/03/2015 05:58:50).
APPLY IS running. Currently at plog 308 and SCN 3999168 (11/03/2015 05:58:48).
Progress of replication dbvrep_XE:MINE->APPLY: total/this execution
--------------------------------------------------------------------------------------------------------------------------------------------
REPOE.CUSTOMERS: 99% Mine:518319/518319 Unrecov:0/0 Applied:518317/518317 Conflicts:0/0 Last:03/11/2015 05:58:46/OK
REPOE.ADDRESSES: 98% Mine:142/142 Unrecov:0/0 Applied:140/140 Conflicts:0/0 Last:03/11/2015 05:58:46/OK
REPOE.CARD_DETAILS: 98% Mine:135/135 Unrecov:0/0 Applied:133/133 Conflicts:0/0 Last:03/11/2015 05:58:46/OK
REPOE.ORDER_ITEMS: 99% Mine:727/727 Unrecov:0/0 Applied:725/725 Conflicts:0/0 Last:03/11/2015 05:58:51/OK
REPOE.ORDERS: 99% Mine:586/586 Unrecov:0/0 Applied:583/583 Conflicts:0/0 Last:03/11/2015 05:58:51/OK
REPOE.INVENTORIES: 99% Mine:707/707 Unrecov:0/0 Applied:705/705 Conflicts:0/0 Last:03/11/2015 05:58:51/OK
REPOE.LOGON: 99% Mine:789/789 Unrecov:0/0 Applied:784/784 Conflicts:0/0 Last:03/11/2015 05:58:51/OK
--------------------------------------------------------------------------------------------------------------------------------------------
7 tables listed.
And then time to query the target table: scroll right to see that I’ve the column I’ve added in the target as well as the one added in the source – with it’s value in the right column.
SQL> select * from repoe.customers where rownum<=10
CUSTOMER_ID CUST_FIRST_NAME CUST_LAST_NAME NLS_LANGUAGE NLS_TERRITORY CREDIT_LIMIT CUST_EMAIL ACCOUNT_MGR_ID CUSTOMER_SINCE CUSTOMER_CLASS SUGGESTIONS DOB MAILSHOT PARTNER_MAILSHOT PREFERRED_ADDRESS PREFERRED_CARD NEW_IN_TGT NEW_IN_SRC
----------- ---------------------------------------- ---------------------------------------- ------------ ------------------------------ ------------ ---------------------------------------------------------------------------------------------------- -------------- ------------------ ---------------------------------------- ---------------------------------------- ------------------ -------- ---------------- ----------------- -------------- ---------- ------------------------------
432 vernon russell XH Minnesota 6500 [email protected] 490 09-MAY-04 00:00:00 Occasional Sports 27-AUG-98 00:00:00 Y N 191596 607724 xxxxxxxxxx
433 steve baker ZG Washington 4000 [email protected] 364 01-AUG-07 00:00:00 Regular Health 07-JUL-60 00:00:00 Y N 284785 731933 xxxxxxxxxx
434 nicholas delgado CF Spain 5000 [email protected] 573 06-JUL-08 00:00:00 Occasional Music 08-SEP-82 00:00:00 Y N 291081 645655 xxxxxxxxxx
435 brad washington BA New Jersey 6000 [email protected] 546 21-JAN-12 00:00:00 Regular Car 03-MAY-53 00:00:00 N N 311712 628307 xxxxxxxxxx
436 greg romero TN Oklahoma 6000 [email protected] 478 27-AUG-09 00:00:00 Occasional Photography 25-JUN-72 00:00:00 N Y 537245 170957 xxxxxxxxxx
437 keith chambers EQ Arizona 6000 [email protected] 584 01-JUL-05 00:00:00 Prime Electronics 28-APR-66 00:00:00 Y N 87989 516342 xxxxxxxxxx
438 frank graham CE Texas 5500 [email protected] 419 11-SEP-11 00:00:00 Business Electronics 16-JUN-99 00:00:00 Y N 549348 586035 xxxxxxxxxx
439 norman johnson JP Mexico 5500 [email protected] 567 12-AUG-01 00:00:00 Occasional Books 18-FEB-95 00:00:00 Y Y 357156 147077 xxxxxxxxxx
440 jorge lee TD Vietnam 10000 [email protected] 457 16-MAY-08 00:00:00 Occasional Sports 18-JAN-63 00:00:00 Y N 40089 446330 xxxxxxxxxx
441 dale mason PR Pennsylvania (Eastern) 6000 [email protected] 530 01-JAN-01 00:00:00 Regular Cooking 22-JUN-88 00:00:00 Y Y 365919 746867 xxxxxxxxxx
10 rows selected
Of course, you are not that much stunned by something which seems so simple, but it depend on the way the dictionary is gathered.
For example, in current versions GoldenGate uses either a static definition from the source SOURCEDEFS file gathered with defgen – but in that case cannot propagate DDL. Or it gets definition from target – but that requires that there are no additional columns in target and even the column positions must be the same or you will get errors such as:
- OGG-01161 Bad column index (…) specified for table …, max columns = …
- OGG-01163 Bad column length (…) specified for column … in table …, maximum allowable length is …
- or worse: values going into bad column if they happen to fit there
Fortunately, as annouced at OOW15, the future GoldenGate 12.2 version should improve that, gathering the dictionary in the same way as Dbvisit replicate currently does.
Dbvisit replicate always used the safe way:
It takes a bit longer when initialization starts because it has to get the dictionary definition, but then can replicate changes (add column, drop column, etc) as well as customize the target table (additional columns for example). The dictionary definition, stored in the database, is always up-to-date with the replication SCN. It is updated when DDL is seen in the mined redo logs.
![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)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/JDU_web-min-scaled.jpg)