oracle_goldengate_generic_jdbc

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
oracle_goldengate_generic_jdbc [2020/11/12 18:06] – andonovjoracle_goldengate_generic_jdbc [2020/11/12 18:21] (current) – andonovj
Line 343: Line 343:
 mysql>  mysql> 
 </Code> </Code>
 +
 +=====Replication with Different Table Structure=====
 +What if we want to replicate between two tables which have different structure:
 +
 +
 +<Code:bash|Table Differences>
 +--Source:
 +SQL> desc product_prod
 + Name    Null?    Type
 + ----------------------------------------- -------- ----------------------------
 + PRODUCT_ID    NOT NULL NUMBER(12)
 + PRODUCT_CODE    NOT NULL VARCHAR2(20)
 + PRODUCT_NAME    NOT NULL VARCHAR2(132)
 + SUPPLIER_ID    NOT NULL NUMBER(12)
 +
 +--Destination:
 +mysql> describe product_prod;
 ++-------------+---------------+------+-----+---------+-------+
 +| Field       | Type          | Null | Key | Default | Extra |
 ++-------------+---------------+------+-----+---------+-------+
 +| product_id  | decimal(10,0) | YES  |     | NULL    |       |
 +| prod_code   | varchar(20)   | YES  |     | NULL    |       |
 +| prod_name   | varchar(132)  | YES  |     | NULL    |       |
 +| supplier_id | decimal(10,0) | YES  |     | NULL    |       |
 ++-------------+---------------+------+-----+---------+-------+
 +4 rows in set (0.00 sec)
 +</Code>
 +
 +In our case the product_id and product_code on the source are different than the prod_name and prod_code on the destination, therefore if we try to add data on the source:
 +
 +
 +<Code:bash|Try to add data on Prod>
 +SQL> insert into product_prod select * from product where product_id < 100;
 +
 +99 rows created.
 +
 +SQL> commit;
 +
 +Commit complete.
 +
 +SQL> 
 +</Code>
 +
 +The Golden Gate Replication Replicat will ABEND:
 +
 +<Code:bash|Golden Gate Replicat (Abend)>
 +GGSCI (edvmr1p0) 8> info all
 +
 +Program     Status      Group       Lag at Chkpt  Time Since Chkpt
 +
 +MANAGER     RUNNING                                           
 +REPLICAT    ABENDED     RJDBC       00:00:00      00:00:12    
 +
 +
 +GGSCI (edvmr1p0) 9> view report rjdbc
 +
 +------------------------------------------------------------------------
 +                          : [/u01/app/oggtrg/replicat(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreadin
 +g::Thread::ThreadArgs*)+0x147) [0x762e57]]
 +                          : [/u01/app/oggtrg/replicat(ggs::gglib::MultiThreading::MainThread::Run(int, char**)+0x12d) [0x7634
 +6d]]
 +                          : [/u01/app/oggtrg/replicat(main+0x3b) [0x634b2b]]
 +                          : [/lib64/libc.so.6(__libc_start_main+0xfd) [0x370421ed1d]]
 +                          : [/u01/app/oggtrg/replicat() [0x5238e9]]
 +
 +2020-11-12 18:10:16  ERROR   OGG-01296  Error mapping from OGGSRC.PRODUCT_PROD to bigdata.PRODUCT_PROD.
 +</Code>
 +
 +
 +So in order to fix that we have to do 2 things:
 +
 +  * Fix the jdbc properties file, to allow mapping of column with different names
 +  * Map these columns in the rjdbc parameter file
 +
 +So let's do it:
 +
 +====Modify JDBC Properties File====
 +Make sure to add the following parameters, to the existing configuration.
 +
 +<Code:bash|Modify JDBC Properties File>
 +gg.mdp.type=jdbc
 +gg.mdp.ConnectionUrl=jdbc:mysql://localhost:3306/bigdata
 +gg.mdp.DriverClassName=com.mysql.jdbc.Driver
 +gg.mdp.UserName=oracle
 +gg.mdp.Password=<password for oracle user>
 +</Code>
 +
 +====Modify Replicat Param File====
 +Now we can modify the RJDBC param file:
 +
 +<Code:bash|Modify Replicat param file>
 +[oracle@edvmr1p0 dirprm]$ trg
 +[oracle@edvmr1p0 oggtrg]$ ggsci
 +
 +Oracle GoldenGate Command Interpreter
 +Version 12.2.0.1.160823 OGGCORE_OGGADP.12.2.0.1.0_PLATFORMS_161019.1437
 +Linux, x64, 64bit (optimized), Generic on Oct 19 2016 16:01:40
 +Operating system character set identified as UTF-8.
 +
 +Copyright (C) 1995, 2016, Oracle and/or its affiliates. All rights reserved.
 +
 +
 +
 +GGSCI (edvmr1p0) 1> edit param rjdbc
 +
 +REPLICAT rjdbc
 +TARGETDB LIBFILE libggjava.so SET property=dirprm/jdbc_mysql.properties
 +REPORTCOUNT EVERY 1 MINUTES, RATE
 +GROUPTRANSOPS 1000
 +MAP OGGSRC.CUSTOMER_PROD, TARGET bigdata.customer_prod;
 +MAP OGGSRC.PRODUCT_PROD, TARGET bigdata.product_prod,
 +COLMAP(USEDEFAULTS, prod_code=PRODUCT_CODE,prod_name=PRODUCT_NAME);
 +
 +:wq
 +</Code>
 +
 +
 +====Verify the Replication====
 +Now, we can start the RJDBC and verify if the rows were replicated:
 +
 +<Code:bash|Verify the Replication>
 +GGSCI (edvmr1p0) 3> start rjdbc
 +
 +Sending START request to MANAGER ...
 +REPLICAT RJDBC starting
 +
 +
 +GGSCI (edvmr1p0) 4> info all
 +
 +Program     Status      Group       Lag at Chkpt  Time Since Chkpt
 +
 +MANAGER     RUNNING                                           
 +REPLICAT    STARTING    RJDBC       00:00:00      00:09:34    
 +
 +
 +GGSCI (edvmr1p0) 5> info all
 +
 +Program     Status      Group       Lag at Chkpt  Time Since Chkpt
 +
 +MANAGER     RUNNING                                           
 +REPLICAT    RUNNING     RJDBC       00:00:00      00:00:02    
 +
 +GGSCI (edvmr1p0) 7> send rjdbc,stats
 +
 +Sending STATS request to REPLICAT RJDBC ...
 +
 +Start of Statistics at 2020-11-12 18:19:49.
 +
 +Replicating from OGGSRC.PRODUCT_PROD to bigdata.product_prod:
 +
 +*** Total statistics since 2020-11-12 18:19:41 ***
 + Total inserts                              99.00
 + Total updates                               0.00
 + Total deletes                               0.00
 + Total discards                             0.00
 + Total operations                          99.00
 +</Code>
 +
 +We see that 99 inserts were processed, we can check of course also the MySQL database:
 +
 +<Code:bash|Verify MySQL Replication>
 +mysql> select count(*) from product_prod;
 ++----------+
 +| count(*) |
 ++----------+
 +|       99 |
 ++----------+
 +1 row in set (0.00 sec)
 +
 +mysql> 
 +</Code>
 +
  • oracle_goldengate_generic_jdbc.1605204404.txt.gz
  • Last modified: 2020/11/12 18:06
  • by andonovj