Dear Readers,
In this article we will see Mapping Columns on Oracle GoldenGate 12c.
We care for your speed. Below is an easy way to Mapping Columns on Oracle GoldenGate 12c
Oracle GoldenGate provides for column mapping at the table level and at the global level. Default column mapping is also provided in the absence of explicit column mapping rules.
Configuring Table-level Column Mapping with COLMAP
Use the COLMAP option of the MAP and TABLE parameters to :
Note :
1.Configure GG setup between Source and Target
2. Make sure Extract ,Datapump process running on Source and Replicat on Target
Node Details :
GG1 (SOURCE)
192.168.0.120 gg1.orcl.com gg1
Database Name :orcl
Instance Name : orcl
GG2(TARGET)
192.168.0.180 gg2.orcl.com gg2
Database Name :orcl
Instance Name : orcl
Let’s start the Scenario
SOURCE :
Create table on AJAY schema
|
1 2 |
AJAY >> create table test10(sno number, sname varchar2(10),mobile number) ; Table created. |
TARGET :
Create test10 table with different structure
|
1 2 |
SQL> create table test10 (sno number,sname varchar2(10),cell number); Table created. |
SOURCE :
Edit extract (EXTINT) param file and include TABLE AJAY.TEST10;
|
1 2 3 4 5 6 7 8 9 10 |
GGSCI (gg.orcl.com) 5> edit param extint EXTRACT EXTINT SETENV(ORACLE_SID='ORCL') SETENV(ORACLE_HOME='/oraeng/app/oracle/product/12.1.0') useridalias ggadmin_src TRANLOGOPTIONS INTEGRATEDPARAMS(MAX_SGA_SIZE 100) EXTTRAIL /u01/app/oracle/product/ogg_src/dirdat/lt --DDL include all --TABLE ajay.DEPT COLSEXCEPT(MOBILE,PIN); TABLE AJAY.TEST10; |
Check datapump(DPINT) param file
|
1 2 3 4 5 6 7 |
GGSCI (gg.orcl.com) 6> edit param dpint EXTRACT DPINT RMTHOST 192.168.0.180,MGRPORT 7809 RMTTRAIL /u01/app/oracle/product/ogg_trg/dirdat/rt PASSTHRU TABLE ajay.DEPT; table AJAY.TEST10; |
TARGET :
Edit Repilcat(REPINT) param file
|
1 2 3 4 5 6 7 8 9 |
GGSCI (gg2.orcl.com as ggadmin@orcl) 3> edit param repint REPLICAT REPINT SETENV(ORACLE_SID='ORCL') SETENV(ORACLE_HOME='/u01/app/oracle/product/12.1.0') useridalias ggadmin_trg DBOPTIONS INTEGRATEDPARAMS(parallelism 4) ASSUMETARGETDEFS MAP ajay.DEPT,target ajay.DEPT; MAP ajay.test10,target ajay.test10; |
SOURCE :
Insert a record into test10 table
|
1 2 3 4 5 |
AJAY> insert into test10 values(10,'AJAY',9000807027); 1 row created. SQL> commit; COMMIT COMPLETE |
TARGET :
Check replicat process (It should be abended because of column mismatch)
|
1 2 3 4 |
GGSCI (gg2.orcl.com as ggadmin@orcl) 4> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT ABENDED REPINT 00:00:00 00:03:09 |
Check report file for errors
|
1 |
2019-08-08 00:57:51 ERROR OGG-00918 Key column CELL is missing from map. |
In Source column name is MOBILE and the target column name is CELL
Replicat process not able to write the data into target database due to column mismatch.
Specifying the Columns to be Mapped in the COLMAP Clause
The COLMAP syntax is the following:
|
1 |
COLMAP ([USEDEFAULTS, ] <span class="italic"><code class="codeph hljs">target_column</code></span> = <span class="italic"><code class="codeph hljs">source_expression</code></span>) |
Edit the repint file
|
1 2 3 4 5 6 7 8 9 10 |
GGSCI (gg2.orcl.com as ggadmin@orcl) 5> edit param repint REPLICAT REPINT SETENV(ORACLE_SID='ORCL') SETENV(ORACLE_HOME='/oraeng/app/oracle/product/12.1.0') useridalias ggadmin_trg DBOPTIONS INTEGRATEDPARAMS(parallelism 4) ASSUMETARGETDEFS MAP ajay.DEPT,target ajay.DEPT; MAP ajay.test10,target ajay.test10, & COLMAP (USEDEFAULTS,CELL = MOBILE ); |
Stop and Start the Replicat process
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
GGSCI (gg2.orcl.com as ggadmin@orcl) 6> stop repint Sending STOP request to REPLICAT REPINT ... Request processed. GGSCI (gg2.orcl.com as ggadmin@orcl) 7> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT STOPPED REPINT 00:00:00 00:00:00 GGSCI (gg2.orcl.com as ggadmin@orcl) 8> start repint Sending START request to MANAGER ... REPLICAT REPINT starting GGSCI (gg2.orcl.com as ggadmin@orcl) 9> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REPINT 00:00:00 00:00:08 |
Check stats for repint
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
GGSCI (gg2.orcl.com as ggadmin@orcl) 10> stats repint Sending STATS request to REPLICAT REPINT ... Start of Statistics at 2019-08-08 00:59:52. Integrated Replicat Statistics: Total transactions 1.00 Redirected 0.00 Replicated procedures 0.00 DDL operations 0.00 Stored procedures 0.00 Datatype functionality 0.00 Operation type functionality 0.00 Event actions 0.00 Direct transactions ratio 0.00% Replicating from AJAY.TEST10 to AJAY.TEST10: *** Total statistics since 2019-08-08 00:59:47 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 1.00 *** Daily statistics since 2019-08-08 00:59:47 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 1.00 *** Hourly statistics since 2019-08-08 00:59:47 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 1.00 *** Latest statistics since 2019-08-08 00:59:47 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 1.00 End of Statistics. |
Check records in target table
|
1 2 3 4 |
AJAY> select * from test10; SNO SNAME CELL ----- ------- ---------- 10 AJAY 9000807027 |
The Replication is happening successfully !
ADD one more column On source with different structure
SOURCE :
|
1 2 3 4 5 6 7 8 9 10 |
AJAY> alter table test10 add address varchar2(10); Table altered. #############TABLE STRUCTURE ############### SQL> desc test10 Name Null? Type ----- ------------------------------------ SNO NUMBER SNAME VARCHAR2(10) MOBILE NUMBER ADDRESS VARCHAR2(10) |
TARGET :
|
1 2 3 4 5 6 7 8 9 |
AJAY> alter table test10 add addr varchar2(10); Table altered. SQL> desc test10 Name Null? Type ----------------------------------------- SNO NUMBER SNAME VARCHAR2(10) CELL NUMBER ADDR VARCHAR2(10) |
On Source column name is ADDRESS and on Target ADDR
Edit the repint and put multiple column
|
1 2 3 4 5 6 7 8 9 10 11 |
GGSCI (gg2.orcl.com as ggadmin@orcl) 11> REPLICAT REPINT SETENV(ORACLE_SID='ORCL') SETENV(ORACLE_HOME='/oraeng/app/oracle/product/12.1.0') useridalias ggadmin_trg DBOPTIONS INTEGRATEDPARAMS(parallelism 4) ASSUMETARGETDEFS MAP ajay.DEPT,target ajay.DEPT; MAP ajay.test10,target ajay.test10, & COLMAP (USEDEFAULTS,CELL = MOBILE,ADDR = ADDRESS ); |
Stop and Start the Replicat process
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
GGSCI (gg2.orcl.com as ggadmin@orcl) 12> stop repint Sending STOP request to REPLICAT REPINT ... Request processed. GGSCI (gg2.orcl.com as ggadmin@orcl) 13> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT STOPPED REPINT 00:00:00 00:00:00 GGSCI (gg2.orcl.com as ggadmin@orcl) 14> start repint Sending START request to MANAGER ... REPLICAT REPINT starting GGSCI (gg2.orcl.com as ggadmin@orcl) 15> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REPINT 00:00:00 00:00:08 |
SOURCE :
Insert records on test10 table
|
1 2 3 4 |
AJAY> insert into test10 values(20,'RAMESH',12345789,'viman ngr'); 1 row created. SQL> commit; Commit complete. |
TARGET :
Check stats for repint
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
GGSCI (gg2.orcl.com as ggadmin@orcl) 16> stats repint Sending STATS request to REPLICAT REPINT ... Start of Statistics at 2019-08-08 01:29:52. Integrated Replicat Statistics: Total transactions 1.00 Redirected 0.00 Replicated procedures 0.00 DDL operations 0.00 Stored procedures 0.00 Datatype functionality 0.00 Operation type functionality 0.00 Event actions 0.00 Direct transactions ratio 0.00% Replicating from AJAY.TEST10 to AJAY.TEST10: *** Total statistics since 2019-08-08 01:29:47 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 1.00 *** Daily statistics since 2019-08-08 01:29:47 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 1.00 *** Hourly statistics since 2019-08-08 01:29:47 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 1.00 *** Latest statistics since 2019-08-08 01:29:47 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 1.00 End of Statistics. |
Now check records on target table
|
1 2 3 4 5 6 |
SQL> select * from test10; SNO SNAME CELL ADDR ---- ---------- ---------- ---------- 10 AJAY 9000807027 20 RAMESH 12345789 viman ngr |
Replication happening successfully on two different column structure !
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Loading...
