Golden Gate Replication When Table Structure Is Different – COLMAP

In this article, we will be using Golden Gate COLMAP parameter for mapping table columns where the tables structure is different. Get ready to set up Golden Gate replication between tables with different structures using defgen and COLMAP!

When Table Structure is Different
Golden Gate COLMAP Replication Scenarios
Create Definitions File
When Table Structure is Semi-Different
Create Source Definitions File

When Table Structure is Different

Golden Gate COLMAP Replication Scenarios

We are going to see two things in this activity

Different column names mapping
Different column order mapping

Below is the mapping order for our tables

golden gate replication when table structure is different colmap - replication goal

Create TAB3 table on proddb

On Proddb:
==========
Conn fox/fox
CREATE TABLE tab3
(
one NUMBER PRIMARY KEY,
two VARCHAR2(30),
three NUMBER,
four NUMBER
);

On target, create TAB3_DIFFCOL table with different column names completely

On Devdb:
=========
Conn tom/tom
CREATE TABLE tab3_diffcol
(
Col_1 NUMBER PRIMARY KEY,
Col_2 NUMBER,
Col_3 VARCHAR2(30),
Col_4 NUMBER
);

Connect to database via Golden Gate and add table level supplemental logging

On proddb:
==========
cd $GG_HOME
./ggsci
GGSCI> dblogin userid ogg, password ogg
Successfully logged into database.
GGSCI> add trandata FOX.TAB3
Logging of supplemental redo data enabled for table FOX.TAB3.
TRANDATA for scheduling columns has been added on table 'FOX.TAB3'.

Create Definitions File

As our source and target tables have different structure, we need to create a source table definitions file and copy it on the target server

We use a DEFGEN utility to create definitions file. This utility comes with GG binaries. First, we need to create a parameter file for DEFGEN utility. You can create it via GG prompt or manually via vi editor

On proddb:
==========
GGSCI> edit params defgen1
DEFSFILE /u01/app/oracle/product/gg/dirdef/FoxTab3Def.def
USERID ogg PASSWORD ogg
TABLE FOX.TAB3;

Exit the GG prompt and initiate defgen utility to generate definitions file

On proddb:
==========
cd $GG_HOME
./defgen paramfile /u01/app/oracle/product/gg/dirprm/defgen1.prm

Copy the definitions file on target server under $GG_HOME/dirdef location

On proddb:
==========
cd $GG_HOME/dirdef
scp FoxTab3Def.def oracle@ggdev:$GG_HOME/dirdef/

Create extract on source

On proddb:
==========
GGSCI> add extract PFOXT3E, integrated tranlog, begin now
GGSCI> register extract PFOXT3E database
GGSCI> add exttrail /u01/app/oracle/product/gg/dirdat/t3, extract PFOXT3E
GGSCI (ggprod) 3> edit param PFOXT3E
EXTRACT PFOXT3E
USERID ogg, PASSWORD ogg
EXTTRAIL /u01/app/oracle/product/gg/dirdat/t3
TABLE FOX.TAB3;

Extract naming convention used: P(prod)FOX(schema)T3(table first & last letter)E(extract)

Create data pump process

GGSCI> Add extract PFOXT3D, EXTTRAILSOURCE /u01/app/oracle/product/gg/dirdat/t3
GGSCI> Add rmttrail /u01/app/oracle/product/gg/dirdat/r3, extract PFOXT3D
GGSCI> edit param PFOXT3D
EXTRACT PFOXT3D
USERID ogg, PASSWORD ogg
RMTHOST ggdev, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/r3
TABLE fox.tab3;

Create GG Replicate on target with mapping details of our columns

On Devdb:
==========
GGSCI> dblogin userid ogg, password ogg
GGSCI> add replicat DTOMTLR, integrated exttrail /u01/app/oracle/product/gg/dirdat/r3
GGSCI> edit param DTOMTLR
REPLICAT DTOMTLR
USERID ogg, PASSWORD ogg
SOURCEDEFS /u01/app/oracle/product/gg/dirdef/FoxTab3Def.def
MAP fox.tab3 TARGET tom.tab3_diffcol, COLMAP(col_1=one, col_3=two, col_2=three, col_4=four);

SOURCEDEFS = specifies the source definitions file location on target server

COLMAP = specifies the non-default mapping of columns from source to target

Start the Extract, Pump and Replicat process

On proddb:
==========
GGSCI> start PFOXT3E
GGSCI> start PFOXT3D

On devdb:
=========
GGSCI> start DTOMTLR

Let us test our replication

On proddb:
==========
INSERT INTO tab3 VALUES (1,'Alpha',10,100);
INSERT INTO tab3 VALUES (2,'Beta',20,200);
INSERT INTO tab3 VALUES (3,'Gamma',30,300);
COMMIT;

When Table Structure is Semi-Different

Semi-different means few columns on target are same as source

Create TAB4 table on proddb

On Proddb:
==========
Conn fox/fox
CREATE TABLE tab4
(
 one NUMBER PRIMARY KEY,
 two VARCHAR2(30),
 three NUMBER,
 four NUMBER
);

On target, create TAB4_DIFFCOL table with semi-different column names

On Devdb:
=========
Conn tom/tom
CREATE TABLE tab4_diffcol
(
 One NUMBER PRIMARY KEY,
 two VARCHAR2(30),
 Col_3 NUMBER,
 Col_4 NUMBER
);

Below is the mapping order for our tables

golden gate replication when table structure is different colmap - mapping order table

Connect to database via Golden Gate and add table level supplemental logging

On proddb:
==========
cd $GG_HOME
./ggsci
GGSCI> dblogin userid ogg, password ogg
Successfully logged into database.
GGSCI> add trandata FOX.TAB4
Logging of supplemental redo data enabled for table FOX.TAB4.
TRANDATA for scheduling columns has been added on table 'FOX.TAB4'.

Create Source Definitions File

As our source and target tables have different structure, we need to create a source table definitions file and copy it on the target server

We use a DEFGEN utility to create definitions file. This utility comes with GG binaries. First, we need to create a parameter file for DEFGEN utility. You can create it via GG prompt or manually via vi editor

On proddb:
==========
GGSCI> edit params defgen2
DEFSFILE /u01/app/oracle/product/gg/dirdef/FoxTab4.def
USERID ogg PASSWORD ogg
TABLE FOX.TAB4;

Exit the GG prompt and initiate defgen utility to generate definitions file

On proddb:
==========
cd $GG_HOME
./defgen paramfile /u01/app/oracle/product/gg/dirprm/defgen2.prm

Copy the definitions file on target server under $GG_HOME/dirdef location

On proddb:
==========
cd $GG_HOME/dirdef
scp FoxTab4.def oracle@ggdev:$GG_HOME/dirdef/

Create extract on source

On proddb:
==========
GGSCI> add extract PFOXT4E, integrated tranlog, begin now
GGSCI> register extract PFOXT4E database
GGSCI> add exttrail /u01/app/oracle/product/gg/dirdat/t4, extract PFOXT4E
GGSCI (ggprod) 3> edit param PFOXT4E
EXTRACT PFOXT4E
USERID ogg, PASSWORD ogg
EXTTRAIL /u01/app/oracle/product/gg/dirdat/t4
TABLE FOX.TAB4;

Extract naming convention used: P(prod)FOX(schema)T4(table first & last letter)E(extract)

Create data pump process

GGSCI> Add extract PFOXT4D, EXTTRAILSOURCE /u01/app/oracle/product/gg/dirdat/t4
GGSCI> Add rmttrail /u01/app/oracle/product/gg/dirdat/r4, extract PFOXT4D
GGSCI> edit param PFOXT4D
EXTRACT PFOXT4D
USERID ogg, PASSWORD ogg
RMTHOST ggdev, MGRPORT 7809
RMTTRAIL /u01/ap

Create GG Replicate on target with mapping details of our columns. If you notice, we have a situation where we would like to map tables that have few columns on target which are the same as source and few different column names.

In this case, we will use a new parameter USEDEFAULTS in the replicat file

On Devdb:
==========
GGSCI> dblogin userid ogg, password ogg

GGSCI> add replicat DTOMT4LR, integrated exttrail /u01/app/oracle/product/gg/dirdat/r4

GGSCI> edit param DTOMT4LR
REPLICAT DTOMT4LR
USERID ogg, PASSWORD ogg
SOURCEDEFS /u01/app/oracle/product/gg/dirdef/FoxTab4.def
MAP fox.tab4 TARGET tom.tab4_diffcol, COLMAP (USEDEFAULTS, col_3=three, col_4=four);

The USEDEFAULTS keyword specifies that column names are identical between the two tables except where a column mapping has been explicitly defined.

In this case, therefore we only need to specify the mapping between COL_3=three and Col_4=four. In the FOX schema in the source database add some rows to the Tab4 table.