With Oracle Goldengate take care of additional column creation on the replicated database

This week I worked on a GoldenGate 12.1.2.1.10 POC setup and was facing an issue which for me is a serious drawback of the Oracle GoldenGate product.If you want to create additional columns on the target database online in a GoldenGate configuration you have to be aware of the below situation which can happen in your setup:The below demo was created on a Oracle GoldenGate Downstream server

For the test, I have created the schema scott/tiger on both the source and target databases, thus no initial load is needed

1.Create SCOTT on source database DB1 and target database DB2 using utlsampl.sql script.

Source>@utlsampl.sql
Target>@utlsampl.sql

First we have to configure the replication for the SCOTT user

2.Configure SCOTT extract process on downstream server

GGSCI (srv01) 1> dblogin useridalias ggsource
Successfully logged into database.

GGSCI (srv01 as goldengate@DB1) 2> miningdblogin useridalias ggcap
Successfully logged into mining database.

GGSCI (srv01 as goldengate@DB1) 3> register extract scott database
Extract SCOTT successfully registered with database at SCN 277324431694.

GGSCI (srv01 as goldengate@DB1) 5> add extract scott integrated tranlog, begin now
EXTRACT added.

GGSCI (srv01 as goldengate@DB1) 6> add trandata scott.emp

GGSCI (srv01 as goldengate@DB1) 6> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     SCOTT       00:00:00      00:00:07

GGSCI (srv01 as goldengate@DB1) 7> add exttrail /u01/directories/ggtrail/POCGGP15/es, extract SCOTT
EXTTRAIL added.

GGSCI (srv01 as goldengate@DB1) 12> view params scott

EXTRACT SCOTT
USERIDALIAS ggsource
DBOPTIONS ALLOWUNUSEDCOLUMN
DDL INCLUDE ALL
TRANLOGOPTIONS MININGUSERALIAS ggcap
TRANLOGOPTIONS INTEGRATEDPARAMS (downstream_real_time_mine Y)
EXTTRAIL /u01/directories/ggtrail/POCGGP15/es
TABLE SCOTT.EMP;

GGSCI (srv01 as goldengate@DB1) 4> start extract scott

Sending START request to MANAGER ...
EXTRACT SCOTT starting

3.Configure the replicat process on downstream server

GGSCI (srv01) 1> add replicat repscott, exttrail /u01/directories/ggtrail/POCGGP15/es
REPLICAT added.

GGSCI (srv01 as goldengate@DB2) 10> view params repscott

REPLICAT REPSCOTT
useridalias ggtarget
DISCARDFILE /u01/app/goldengate/product/12.1.2.1/discard/REPSCOTT_DISCARD.txt,APPEND,megabytes 10
ASSUMETARGETDEFS
DBOPTIONS NOSUPPRESSTRIGGERS
MAP SCOTT.EMP,TARGET SCOTT.EMP;

GGSCI (srv01) 3> dblogin useridalias ggtarget
Successfully logged into database.

Extract current_scn on source database

sys@GMAS2> select current_scn from v$database;

   CURRENT_SCN
--------------
  277324550446

GGSCI (srv01 as goldengate@DB2) 5> start replicat repscott, afterscn 277324550446

Sending START request to MANAGER ...
REPLICAT REPSCOTT starting

GGSCI (srv01 as goldengate@DB2) 9> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     SCOTT           00:00:03      00:00:05
REPLICAT    RUNNING     REPSCOTT        00:00:00      00:02:08

Now we have a running GoldenGate replication for the table scott.emp including the DDL

======START DEMO =======

On the target database DB2 we create an additional column

scott@DB2> alter table emp add TARGET_COL varchar(10) default null;

Table altered.

On the source database DB1 after that, we create an additional column on the source database, which will be replicated to the target database.

scott@DB1> alter table emp add SOURCE_COL varchar(10) default null;

Table altered.

Now on target database DB2 we have the 2 additional columns, as described below:

scott@DB2> select ename,target_col,source_col from emp;

ENAME      TARGET_COL SOURCE_COL
---------- ---------- ----------
SMITH
ALLEN
WARD
...

And on the source database DB1  there is only one additional column

scott@DB1> select ename, source_col from emp;

ENAME      SOURCE_COL
---------- ----------
SMITH
ALLEN
WARD
...

Now on the source database DB1 its time to update the entry for the additional column

scott@DB1> update emp set source_col='change';

14 rows updated.

scott@DB1> commit;

Commit complete.

scott@DB1> select ename, source_col from emp;

ENAME      SOURCE_COL
---------- ----------
SMITH      change
ALLEN      change
WARD       change

Until now everything work as expected.

But now on the target database DB2 we will check the entry updated on table scott.emp

scott@DB2> select ename,target_col,source_col from emp;

ENAME      TARGET_COL SOURCE_COL
---------- ---------- ----------
SMITH      change
ALLEN      change
WARD       change

!!!!! TARGET_COL  is updated, and not the SOURCE_COL Column !!!!

GoldenGate works with the column order and not explicitly with the column names. Thus if you create additional columns on the target database and the column type is compatible with the value of the source database, GoldenGate will automatically put the insert/update in identical column number as on the source database :-(((. Without generating ANY warning or error.

With a sourcedef file you will have the same issue because the sourcedef file is not aware about the additional column created on the target database.

Conclusion: A Solution exist: You’ll have to install Oracle GoldenGate release 12.2 which is available since this week, next blog with the solution will come soon 🙂