Differences

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

--- oracle_goldengate_generic_jdbc [2020/11/12 18:05] – andonovj
+++ oracle_goldengate_generic_jdbc [2020/11/12 18:21] (current) – andonovj
@@ Line 284: / Line 284: @@
+Finally, we can check if the data is replicated on the MySQL
+<Code:bash|Verify MySQL>
+[oracle@edvmr1p0 oggtrg]$ mysql -uoracle -p
+Enter password:
+Welcome to the MySQL monitor.  Commands end with ; or \g.
+Your MySQL connection id is 5
+Server version: 5.1.73 Source distribution
+Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
+Oracle is a registered trademark of Oracle Corporation and/or its
+affiliates. Other names may be trademarks of their respective
+owners.
+Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
+mysql> show databases;
++--------------------+
+| Database           |
++--------------------+
+| information_schema |
+| bigdata            |
+| test               |
++--------------------+
+rows in set (0.00 sec)
+mysql> use bigdata
+Reading table information for completion of table and column names
+You can turn off this feature to get a quicker startup with -A
+Database changed
+mysql> select count(*) from customer_prod;
++----------+
+| count(*) |
++----------+
+|     3000 |
++----------+
+row in set (0.00 sec)
+mysql> select customer_id, first_name, last_name, city from customer_prod where customer_id < 1011;
++-------------+------------+-----------+--------------------------+
+| customer_id | first_name | last_name | city                     |
++-------------+------------+-----------+--------------------------+
+|        1001 | Alisa      | Vega      | Calle Blancos            |
+|        1002 | Inga       | Griffin   | San Diego                |
+|        1003 | Sopoline   | Cooke     | Ip��s                    |
+|        1004 | Maile      | Beasley   | San Pedro                |
+|        1005 | Asher      | Glenn     | Curridabat               |
+|        1006 | Rosalyn    | Mcleod    | San Isidro de El General |
+|        1007 | September  | Lloyd     | Puntarenas               |
+|        1008 | Magee      | Higgins   | San Felipe               |
+|        1009 | Pascale    | Kramer    | Ulloa (Barrial)          |
+|        1010 | Melodie    | Fox       | San Pablo                |
++-------------+------------+-----------+--------------------------+
+rows in set (0.01 sec)
+mysql>
+</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    |       |
++-------------+---------------+------+-----+---------+-------+
+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;
+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
+d]]
+                          : [/u01/app/oggtrg/replicat(main+0x3b) [0x634b2b]]
+                          : [/lib64/libc.so.6(__libc_start_main+0xfd) [0x370421ed1d]]
+                          : [/u01/app/oggtrg/replicat() [0x5238e9]]
+-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 |
++----------+
+row in set (0.00 sec)
+mysql>
+</Code>