Contents
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_COL1 ( ID VARCHAR2(20) NOT NULL, NAME VARCHAR2(20), PASS VARCHAR2(512), CONSTRAINT GG_TEST_COL1_PK PRIMARY KEY ( ID ) ENABLE );
Create Target Table
sqlplus gg_tgt/password -- Create target table CREATE TABLE GG_TGT.GG_TEST_COL1 ( ID VARCHAR2(20) NOT NULL, NAME VARCHAR2(20), PASSWORD VARCHAR2(512), CONSTRAINT GG_TEST_COL1_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: EDIT PARAMS gg_src
DEFSFILE dirdef/gg_src.def
USERID gg_src, PASSWORD password
TABLE gg_src.gg_test_col1;* Generate source definitions file with the defgen command:
defgen paramfile dirprm/gg_src.prmFor Source User: gg_src
Add Trandata
-- Login source schema DBLOGIN USERID GG_SRC, PASSWORD password -- Enable trandata for gg_test_col1 ADD TRANDATA gg_src.gg_test_col1
Prepare Extract Group
* Create extract group
ADD EXTRACT e_c1, TRANLOG, BEGIN now
* Create extract trail
ADD EXTTRAIL /u01/Oracle/GoldenGate/gg1121/dirdat/c1, EXTRACT e_c1, MEGABYTES 20
* Edit extract parameters: EDIT PARAMS e_c1
-- 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_c1 -- Used to login db USERID gg_src, PASSWORD password -- Specify extract trail file EXTTRAIL /u01/Oracle/GoldenGate/gg1121/dirdat/c1 -- Specify tables to extract TABLE gg_src.gg_test_col1;
* Start Extract
-- Start manager process if not already done start mgr -- Start extract start extract e_c1 -- View extract report view report e_c1
For Target User: gg_tgt
Prepare Replicat
* Create REPLICAT group
ADD REPLICAT r_c1, EXTTRAIL /u01/Oracle/GoldenGate/gg1121/dirdat/c1, NODBCHECKPOINT, BEGIN now
* Edit REPLICAT parameters: EDIT PARAMS r_c1
-- Oracle instance SETENV (ORACLE_HOME = "/usr/lib/oracle/xe/app/oracle/product/10.2.0/server") SETENV (ORACLE_SID = "XE") -- Replicat group REPLICAT r_c1 -- Source definitions file SOURCEDEFS ./dirdef/gg_src.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_col1, TARGET gg_tgt.gg_test_col1, & COLMAP (USEDEFAULTS, & PASSWORD = PASS);
*Start REPLICAT
start replicat r_c1 view report r_c1
Test
Insert Data to Source Table
INSERT INTO GG_SRC.gg_test_col1 VALUES ('1','One','Pass'); commit;
Check Data in Target Table
SELECT * FROM GG_TGT.gg_test_col1; ID NAME PASSWORD -------------------- -------------------- -------------------- 1 One Pass
3 Responses to GoldenGate COLMAP: Map Different Column Name