My colleague Hervé last week posted a blog concerning a bug in GoldenGate 12.1. You can find the blog here.

In fact the problem is that GoldenGate works with the column position and not with the column name. To follow up this bug I tried to reproduce that with GoldenGate 12.2 that was released last week.

As Hervé did I used the schema scott/tiger. The goal is not to test an initial load.

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

Just a small precision, contrary to my colleague, I don’t use a downstream server but just two virtual machines.

1. Configure SCOTT extract process on the source machine

GGSCI (goldengate122) 1> dblogin useridalias ggadmin
Successfully logged into database.

GGSCI (goldengate122 as ggadmin@DB1) 2> register extract scott database

2015-12-04 09:40:36  INFO    OGG-02003  Extract SCOTT successfully registered with database at SCN 558330.

GGSCI (goldengate122 as ggadmin@DB1) 3> add extract scott integrated tranlog, begin now
EXTRACT (Integrated) added.


GGSCI (goldengate122 as ggadmin@DB1) 4> add trandata scott.emp

Logging of supplemental redo data enabled for table SCOTT.EMP.
TRANDATA for scheduling columns has been added on table 'SCOTT.EMP'.
TRANDATA for instantiation CSN has been added on table 'SCOTT.EMP'.
GGSCI (goldengate122 as ggadmin@DB1) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     SCOTT       00:00:00      00:00:21


GGSCI (goldengate122 as ggadmin@DB1) 6> add exttrail /u04/app/goldengate/trail/DB1/sc, extract SCOTT
EXTTRAIL added.

GGSCI (goldengate122 as ggadmin@DB1) 7> edit params scott



GGSCI (goldengate122 as ggadmin@DB1) 8> view params scott

Extract scott
useridalias ggadmin
DDL INCLUDE MAPPED
TranlogOptions IntegratedParams (max_sga_size 256)
Exttrail /u04/app/goldengate/trail/DB1/sc
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
Table SCOTT.emp;


GGSCI (goldengate122 as ggadmin@DB1) 9> start extract scott

Sending START request to MANAGER ...
EXTRACT SCOTT starting


GGSCI (goldengate122 as ggadmin@DB1) 10>

2. Configure Data Pump extract process to transfer trail files

GGSCI (goldengate122) 1> dblogin useridalias ggadmin
Successfully logged into database.

GGSCI (goldengate122 as ggadmin@DB1) 2> add extract DPSCOTT, EXTTRAILSOURCE /u04/app/goldengate/trail/DB1/sc
EXTRACT added.


GGSCI (goldengate122 as ggadmin@DB1) 3> add rmttrail /u05/ggtrail/DB2/tc, extract DPSCOTT
RMTTRAIL added.

GGSCI (goldengate122 as ggadmin@DB1) 4> edit params DPSCOTT



GGSCI (goldengate122 as ggadmin@DB1) 5> view params DPSCOTT

extract dpscott
useridalias ggadmin
DBOPTIONS ALLOWUNUSEDCOLUMN
rmthost 192.168.56.109, MGRPORT 7809
RMTTRAIL /u05/ggtrail/DB2/tc
TABLE scott.emp;


GGSCI (goldengate122 as ggadmin@DB1) 6> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     DPSCOTT     00:00:00      00:01:25
EXTRACT     RUNNING     SCOTT       00:00:10      00:00:03


GGSCI (goldengate122 as ggadmin@DB1) 7> start extract DPSCOTT

Sending START request to MANAGER ...
EXTRACT DPSCOTT starting


GGSCI (goldengate122 as ggadmin@DB1) 8>

3. Configure SCOTT replicat process on the target machine

GGSCI (goldengate1222) 1> dblogin useridalias ggadmin
Successfully logged into database.

GGSCI (goldengate1222 as ggadmin@DB2) 2> add replicat repscott, exttrail /u05/ggtrail/DB2/tc
REPLICAT added.


GGSCI (goldengate1222 as ggadmin@DB2) 3> edit params REPSCOTT



GGSCI (goldengate1222 as ggadmin@DB2) 4> view params REPSCOTT

REPLICAT REPSCOTT
ASSUMETARGETDEFS
USERIDALIAS ggadmin
DISCARDFILE /u04/app/goldengate/product/12.2.0.0/DB2/discard/REPSCOTT_discard.txt, append, megabytes 10
MAP SCOTT.emp, TARGET SCOTT.emp;


GGSCI (goldengate1222 as ggadmin@DB2) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    STOPPED     REPSCOTT    00:00:00      00:00:20


GGSCI (goldengate1222 as ggadmin@DB2) 6> start replicat repscott

Sending START request to MANAGER ...
REPLICAT REPSCOTT starting


GGSCI (goldengate1222 as ggadmin@DB2) 7>

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

SQL> connect scott/tiger
Connected.
SQL> 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.

SQL> connect scott/tiger
Connected.
SQL> 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:

SQL> 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

SQL> 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

SQL> update emp set source_col='change';

14 rows updated.

SQL> commit;

Commit complete.

SQL> select ename, source_col from emp;

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

Until now everything work as my collegue.

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

SQL> select ename,target_col,source_col from emp;

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

And contrary at the version 12.1, it is the right column that was updated.

Conclusion : Now in the version 12.2, GoldenGate works with the column names and not anymore with the column positions. It is not a revolution, other products like dbvisit replicate can do this since years. But at least it works now.