Infrastructure at your Service

Franck Pachot

Dbvisit replicate allows DDL replication even when source and destination have different structure

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.

replicate_logo_370x108Dbvisit 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.

Leave a Reply

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

Franck Pachot
Franck Pachot

Principal Consultant / Database Evangelist
Oracle ACE Director, Oracle Database OCM 12c
AWS Database Specialty certified, AWS Data Hero
Oak Table member

RSS for this blog: feed
Twitter: @FranckPachot
LinkedIn : www.linkedin.com/in/franckpachot
Podcast en français: DBPod