Oracle GoldenGate – Mappings
This post discusses the mapping of schema, table and column names in Oracle GoldenGate.
The post is based on Oracle GoldenGate version 11.2.1.0.1. For a basic configuration I used two Linux VMs (OEL5U6) running single instance Oracle 11.2.0.3 databases. I created both databases using DBCA.
This post assumes that the source and target databases have already been configured as described in a previous post: .
This configuration uses following hosts and databases.
| Source | Target | |
|---|---|---|
| Hostname | vm4 | vm5 |
| Database Name | NORTH | SOUTH |
The configuration includes the following on both nodes:
- Creation of a GoldenGate schema owner called GG01.
- Specification of GGSCHEMA as GG01 in GoldenGate parameters
- Creation of GOLDENGATE tablespace which is default tablespace for GG01
The GoldenGate process names are:
| Source | Target | |
|---|---|---|
| Extract | ex1 | - |
| Data Pump | dp1 | - |
| Replicat | - | rep1 |
Mapping Schema Names
GoldenGate can be configured to map all tables from a schema in the source database to a different schema in the target database. The mapping is performed by the replicat process on the target database; the trails are unaffected.
In the following example we will map all tables in the US03 schema in the source database to the US01 schema in the target database.
Shut down the EX1 extract process on the source server
Shut down the DP1 extract process on the source server
Shut down the REP1 replicat process on the target server
In the US03 schema in the source database create table T51 as follows:
CREATE TABLE t51 ( c1 NUMBER PRIMARY KEY, c2 VARCHAR2(30), c3 NUMBER, c4 NUMBER );
In the US01 schema in the target database create the same table:
CREATE TABLE t51 ( c1 NUMBER PRIMARY KEY, c2 VARCHAR2(30), c3 NUMBER, c4 NUMBER );
In GGSCI on the source server add supplemental logging for the T51 table in the US03 schema:
[oracle@vm4]$ cd /home/oracle/goldengate [oracle@vm4]$ ggsci GGSCI (vm4) 1> DBLOGIN USERID us03 PASSWORD us03 GGSCI (vm4) 2> ADD TRANDATA t51 Logging of supplemental redo data enabled for table US03.T51.
Modify the parameter files for each process as follows:
The extract process has the following parameters:
EXTRACT ex1 USERID gg01, PASSWORD gg01 EXTTRAIL /home/oracle/goldengate/dirdat/ex TABLE US03.*;
The data pump process has the following parameters:
EXTRACT dp1 USERID gg01, PASSWORD gg01 RMTHOST vm5, MGRPORT 7809 RMTTRAIL /home/oracle/goldengate/dirdat/rt TABLE US03.*;
The replicat process has the following parameters:
REPLICAT rep1 USERID gg01, PASSWORD gg01 ASSUMETARGETDEFS DISCARDFILE /home/oracle/goldengate/discards, PURGE MAP US03.* TARGET US01.*;
Start the EX1 extract process on the source server
Start the DP1 data pump process on the source server
Start the REP1 replicat process on the target server
In the US03 schema in the source database add some rows to the T51 table.
INSERT INTO t51 VALUES (1,'Alpha',10,100); INSERT INTO t51 VALUES (2,'Beta',20,200); INSERT INTO t51 VALUES (3,'Gamma',30,300); COMMIT;
In the US01 schema in the target database check that the new rows have been replicated:
[oracle@vm5]$ sqlplus us01/us01
SQL> SELECT * FROM t51;
C1 C2 C3 C4
---------- ------------------------------ ---------- ----------
1 Alpha 10 100
2 Beta 20 200
3 Gamma 30 300
On the target server, the report file for the replicat process includes details of the mapping. For example:
Wildcard MAP resolved (entry US03.*): MAP "US03"."T51" TARGET US02."T51"; Using following columns in default map by name: C1, C2, C3, C4 Using the following key columns for target table US02.T51: C1.
Mapping Table Names
GoldenGate can be configured to map all table names between the source database and the target database. The table names can be in the same or in different schemas. The mapping is performed by the replicat process on the target database; the trails are unaffected.
In the following example we will map table US03.T52 in the source database to table US01.T53 in the target database. The tables have identical columns.
Shut down the EX1 extract process on the source server
Shut down the DP1 extract process on the source server
Shut down the REP1 replicat process on the target server
In the US03 schema in the source database create table T52 as follows:
CREATE TABLE t52 ( c1 NUMBER PRIMARY KEY, c2 VARCHAR2(30), c3 NUMBER, c4 NUMBER );
In the US01 schema in the target database create table T53 as follows:
CREATE TABLE t53 ( c1 NUMBER PRIMARY KEY, c2 VARCHAR2(30), c3 NUMBER, c4 NUMBER );
In GGSCI on the source server add supplemental logging for the table US03.T52:
[oracle@vm4]$ cd /home/oracle/goldengate [oracle@vm4]$ ggsci GGSCI (vm4) 1> DBLOGIN USERID us03 PASSWORD us03 GGSCI (vm4) 2> ADD TRANDATA t52 Logging of supplemental redo data enabled for table US03.T52.
Modify the parameter files for each process as follows:
The extract process has the following parameters:
EXTRACT ex1 USERID gg01, PASSWORD gg01 EXTTRAIL /home/oracle/goldengate/dirdat/ex TABLE US03.*;
The data pump process has the following parameters:
EXTRACT dp1 USERID gg01, PASSWORD gg01 RMTHOST vm5, MGRPORT 7809 RMTTRAIL /home/oracle/goldengate/dirdat/rt TABLE US03.*;
The replicat process has the following parameters:
REPLICAT rep1 USERID gg01, PASSWORD gg01 ASSUMETARGETDEFS DISCARDFILE /home/oracle/goldengate/discards, PURGE MAP US03.T52 TARGET US01.T53;
Start the EX1 extract process on the source server
Start the DP1 data pump process on the source server
Start the REP1 replicat process on the target server
In the US03 schema in the source database add some rows to the T52 table.
INSERT INTO t52 VALUES (1,'Alpha',10,100); INSERT INTO t52 VALUES (2,'Beta',20,200); INSERT INTO t52 VALUES (3,'Gamma',30,300); COMMIT;
In the US01 schema in the target database check that the new rows have been replicated to table T53:
[oracle@vm5]$ sqlplus us01/us01
SQL> SELECT * FROM t53;
C1 C2 C3 C4
---------- ------------------------------ ---------- ----------
1 Alpha 10 100
2 Beta 20 200
3 Gamma 30 300
On the target server, the report file for the replicat process includes details of the mapping. For example:
MAP resolved (entry US03.T52): MAP "US03"."T52" TARGET US01.T53; Using following columns in default map by name: C1, C2, C3, C4 Using the following key columns for target table US01.T53: C1.
Mapping Column Names
GoldenGate can be configured to map column names between tables in the source database and the target database. The tables can be in the same or in different schemas. The mapping is performed by the replicat process on the target database; the trails are unaffected.
Column mapping is more complicated than schema name or table name mapping. It requires a definition file to be generated for the source table using the defgen utility. This definition file must be copied to the target server for use as a reference by the replicat process.
In the following example we will map table US03.T54 in the source database to table US01.T55 in the target database. The column mappings are as follows:
| T54 | T55 |
|---|---|
| c1 | col1 |
| c2 | col3 |
| c3 | col4 |
| c4 | col2 |
Shut down the EX1 extract process on the source server
Shut down the DP1 extract process on the source server
Shut down the REP1 replicat process on the target server
In the US03 schema in the source database create table T54 as follows:
CREATE TABLE t54 ( c1 NUMBER PRIMARY KEY, c2 VARCHAR2(30), c3 NUMBER, c4 NUMBER );
In the US01 schema in the target database create table T55 as follows:
CREATE TABLE t55 ( col1 NUMBER PRIMARY KEY, col2 NUMBER, col3 VARCHAR2(30), col4 NUMBER );
Note that column names and order differ between tables T54 and T55.
In GGSCI on the source server add supplemental logging for the table US03.T54:
[oracle@vm4]$ cd /home/oracle/goldengate [oracle@vm4]$ ggsci GGSCI (vm4) 1> DBLOGIN USERID us03 PASSWORD us03 GGSCI (vm4) 2> ADD TRANDATA t54 Logging of supplemental redo data enabled for table US03.T54.
On the source database create a parameter file for the defgen utility. This can be any text file. Alternatively it can be created using ggsci so that it is stored with the other GoldenGate parameter files:
[oracle@vm4]$ cd /home/oracle/goldengate [oracle@vm4]$ ggsci GGSCI (vm4) 1> EDIT PARAMS defgen1
Add the following entries to the parameter file:
DEFSFILE ./dirdef/defgen1.def USERID us03 PASSWORD us03 TABLE us03.t54;
The DEFSFILE parameter specifies the location of the definition file that will be generated by defgen. In this case we are only generating a definition for table US03.T54. However, the definition file can contain definitions for multiple files.
Generate the definition file using the defgen utility:
[oracle@vm4]$ cd /home/oracle/goldengate [oracle@vm4]$ ./defgen paramfile /home/oracle/goldengate/dirprm/defgen1.prm
The above command generated the following output:
***********************************************************************
Oracle GoldenGate Table Definition Generator for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 05:08:19
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
Starting at 2013-04-15 06:06:57
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Tue Jan 18 20:11:49 EST 2011, Release 2.6.32-100.26.2.el5
Node: vm4.juliandyke.com
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 15048
***********************************************************************
** Running with the following parameters **
***********************************************************************
DEFSFILE ./dirdef/defgen1.def
USERID us03 PASSWORD ****
TABLE us03.t54;
Retrieving definition for US03.T54
Definitions generated for 1 table in ./dirdef/defgen1.def
In this case the defgen1.def file contains the following definitions:
*+- Defgen version 2.0, Encoding UTF-8 * * Definitions created/modified 2013-04-15 06:06 * * Field descriptions for each column entry: * * 1 Name * 2 Data Type * 3 External Length * 4 Fetch Offset * 5 Scale * 6 Level * 7 Null * 8 Bump if Odd * 9 Internal Length * 10 Binary Length * 11 Table Length * 12 Most Significant DT * 13 Least Significant DT * 14 High Precision * 15 Low Precision * 16 Elementary Item * 17 Occurs * 18 Key Column * 19 Sub Data Type * Database type: ORACLE Character set ID: windows-1252 National character set ID: UTF-16 Locale: neutral Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14 * Definition for table US03.T54 Record length: 204 Syskey: 0 Columns: 4 C1 64 50 0 0 0 1 0 50 50 50 0 0 0 0 1 0 1 2 C2 64 30 56 0 0 1 0 30 30 0 0 0 0 0 1 0 0 0 C3 64 50 92 0 0 1 0 50 50 50 0 0 0 0 1 0 0 2 C4 64 50 148 0 0 1 0 50 50 50 0 0 0 0 1 0 0 2 End of definition
The definitions appear to be a little strange for an Oracle database. However, remember that GoldenGate supports replication between databases from different vendors.
Copy the definition file to the target server. For example:
[oracle@vm4]$ cd /home/oracle/goldengate/dirdef [oracle@vm4]$ scp defgen1.def vm5:`pwd`
Modify the parameter files for each process as follows:
The extract process has the following parameters:
EXTRACT ex1 USERID gg01, PASSWORD gg01 EXTTRAIL /home/oracle/goldengate/dirdat/ex TABLE US03.*;
The data pump process has the following parameters:
EXTRACT dp1 USERID gg01, PASSWORD gg01 RMTHOST vm5, MGRPORT 7809 RMTTRAIL /home/oracle/goldengate/dirdat/rt TABLE US03.*;
The replicat process has the following parameters:
REPLICAT rep1 USERID gg01, PASSWORD gg01 SOURCEDEFS ./dirdef/defgen1.def DISCARDFILE /home/oracle/goldengate/discards, PURGE MAP US03.T54 TARGET US01.T55, & COLMAP (col1=c1, col3=c2, col4=c3, col2=c4);
In the replicat parameter file the SOURCEDEFS parameter is used to specify the location of the definition file. The COLMAP clause of the MAP parameter is used to specify non-default mappings between columns. Note that it is possible for the target table to have fewer columns than the source table.
The syntax for each mapping in the COLMAP clause is:
The replicat process will abend if this clause is incorrect.
Start the EX1 extract process on the source server
Start the DP1 data pump process on the source server
Start the REP1 replicat process on the target server
In the US03 schema in the source database add some rows to the T54 table.
INSERT INTO t54 VALUES (1,'Alpha',10,100); INSERT INTO t54 VALUES (2,'Beta',20,200); INSERT INTO t54 VALUES (3,'Gamma',30,300); COMMIT;
In the US01 schema in the target database check that the new rows have been replicated to table T55:
[oracle@vm5]$ sqlplus us01/us01
SQL> SELECT * FROM t55;
COL1 COL2 COL3 COL4
---------- ---------- ------------------------------ ----------
1 100 Alpha 10
2 200 Beta 20
3 300 Gamma 30
On the target server, the report file for the replicat process includes details of the mapping. For example:
MAP resolved (entry US03.T54): MAP "US03"."T54" TARGET US01.T55, COLMAP (col1=c1, col3=c2, col4=c3, col2=c4); Using the following key columns for target table US01.T55: COL1.
Let us consider another example. The source table is unchanged. However, we now want to replicate this table to US01.T56 in the target database. Table US01.T56 has the following definition:
CREATE TABLE t56 ( c1 NUMBER PRIMARY KEY, c2 VARCHAR2(30), c3 NUMBER );
The column mappings are as follows:
| T54 | T56 |
|---|---|
| c1 | c1 |
| c2 | c2 |
| c4 | c3 |
Columns C1 and C2 in the source table, map to columns with the same names in the target table. Column C4 in the source table maps to column C3 in the target table. Column C3 is not mapped
As the source table has not been altered, the definition file on the target server is unchanged
The replicat parameter file contains the following:
REPLICAT rep1 USERID gg01, PASSWORD gg01 SOURCEDEFS ./dirdef/defgen1.def DISCARDFILE /home/oracle/goldengate/discards, PURGE MAP US03.T54 TARGET US01.T56, & COLMAP (USEDEFAULTS, c3=c4);
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 column C4 in the source table and column C3 in the target table.
In the US03 schema in the source database add some rows to the T54 table.
INSERT INTO t54 VALUES (1,'Alpha',10,100); INSERT INTO t54 VALUES (2,'Beta',20,200); INSERT INTO t54 VALUES (3,'Gamma',30,300); COMMIT;
In the US01 schema in the target database check that the new rows have been replicated to table T56:
[oracle@vm5]$ sqlplus us01/us01
SQL> SELECT * FROM t56;
C1 C2 C3
---------- ------------------------------ ----------
1 Alpha 100
2 Beta 200
3 Gamma 300
On the target server, the report file for the replicat process includes details of the mapping:
MAP resolved (entry US03.T54): MAP "US03"."T54" TARGET US01.T56, COLMAP (USEDEFAULTS, c3=c4); Using the following default columns with matching names: C1=C1, C2=C2, C3=C3 Using the following key columns for target table US01.T56: C1.
End of post