Golden Gate Replication When Table Structure Is Different – COLMAP
Use COLMAP to handle table structure differences in replication.
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
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
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
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.
Start the Extract, Pump and Replicat process
On proddb:
==========
GGSCI> start PFOXT4E
GGSCI> start PFOXT4D
On devdb:
=========
GGSCI> start DTOMT4LR
Let us test our replication
On proddb:
==========
INSERT INTO tab4 VALUES (1,'Alpha',10,100);
INSERT INTO tab4 VALUES (2,'Beta',20,200);
INSERT INTO tab4 VALUES (3,'Gamma',30,300);
COMMIT;