Contents
In this note, GoldenGate is used to replicate data from a source table named GG_TEST_TBL1 to a target table named GG_TEST_TABLE1. A column in the source table named PASS is also being replicated to a column in the target table named PASSWORD.
Prepare Environment
* See GoldenGate Tutorial: Simplest to setup environment.
Prepare Database
Create Source Table
sqlplus gg_src/password -- Create src table CREATE TABLE GG_SRC.GG_TEST_TBL1 ( ID VARCHAR2(20) NOT NULL, NAME VARCHAR2(20), PASS VARCHAR2(512), CONSTRAINT GG_TEST_TBL1_PK PRIMARY KEY ( ID ) ENABLE );
Create Target Table
sqlplus gg_tgt/password -- Create target table CREATE TABLE GG_TGT.GG_TEST_TABLE1 ( ID VARCHAR2(20) NOT NULL, NAME VARCHAR2(20), PASSWORD VARCHAR2(512), CONSTRAINT GG_TEST_TBL1_PK PRIMARY KEY ( ID ) ENABLE );
Generate Source Definitions File
* Source definitions file is needed since source and target table definitions are different
* Create defgen parameter file from GGSCI: EDIT PARAMS gg_src_tbl1
DEFSFILE dirdef/gg_src_tbl1.def
USERID gg_src, PASSWORD password
TABLE gg_src.GG_TEST_TBL1;* Generate source definitions file with the defgen command:
– From GG_HOME directory:
defgen paramfile dirprm/gg_src_tbl1.prm
For Source User: gg_src
Add Trandata
-- Login source schema DBLOGIN USERID GG_SRC, PASSWORD password -- Enable trandata for GG_TEST_TBL1 ADD TRANDATA gg_src.GG_TEST_TBL1
Prepare Extract Group
* Create extract group
ADD EXTRACT e_t1, TRANLOG, BEGIN now
* Create extract trail
ADD EXTTRAIL /u01/Oracle/GoldenGate/gg1121/dirdat/t1, EXTRACT e_t1, MEGABYTES 20
* Edit extract parameters: EDIT PARAMS e_t1
-- Oracle instance SETENV (ORACLE_HOME = "/usr/lib/oracle/xe/app/oracle/product/10.2.0/server") SETENV (ORACLE_SID = "XE") -- Specify extract group EXTRACT e_t1 -- Used to login db USERID gg_src, PASSWORD password -- Specify extract trail file EXTTRAIL /u01/Oracle/GoldenGate/gg1121/dirdat/t1 -- Specify tables to extract TABLE gg_src.GG_TEST_TBL1;
* Start Extract
-- Start manager process if not already done start mgr -- Start extract start extract e_t1 -- View extract report view report e_t1
For Target User: gg_tgt
Prepare Replicat
* Create REPLICAT group
ADD REPLICAT r_t1, EXTTRAIL /u01/Oracle/GoldenGate/gg1121/dirdat/t1, NODBCHECKPOINT, BEGIN now
* Edit REPLICAT parameters: EDIT PARAMS r_t1
-- Oracle instance SETENV (ORACLE_HOME = "/usr/lib/oracle/xe/app/oracle/product/10.2.0/server") SETENV (ORACLE_SID = "XE") -- Replicat group REPLICAT r_t1 -- Source definitions file SOURCEDEFS ./dirdef/gg_src_tbl1.def -- Login DB USERID gg_tgt, PASSWORD password -- Map tables AND columns -- USEDEFAULTS: exact same names -- PASSWORD = PASS: target column name PASSWORD should be mapped to source column named PASS MAP gg_src.GG_TEST_TBL1, TARGET gg_tgt.GG_TEST_TABLE1, & COLMAP (USEDEFAULTS, & PASSWORD = PASS);
*Start REPLICAT
start replicat r_t1 view report r_t1
Test
Insert Data to Source Table
INSERT INTO GG_SRC.GG_TEST_TBL1 VALUES ('1','One','Pass'); commit;
Check Data in Target Table
SELECT * FROM GG_TGT.GG_TEST_TABLE1; ID NAME PASSWORD -------------------- -------------------- -------------------- 1 One Pass
2 Responses to GoldenGate COLMAP: Map to Different Table Name and Column Name