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.