oracle_goldengate_generic_jdbc [My wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
=====Configure Destination=====
In this section we will define Generic JDBC Adapter for MySQL replication, the steps are similar as the previous:


  * JDBC Drivers
  * MySQL
  * Properties File
  * Golden Gate (replicat)


So, let's get going.

====Configure JDBC Drivers====
<Code:bash|Configure JDBC Driver>
[oracle@edvmr1p0 practice08]$ cd /stage
[oracle@edvmr1p0 stage]$ mkdir mysql_jdbc
[oracle@edvmr1p0 stage]$ cp mysql-connector-java-5.1.40.tar.gz mysql_jdbc/
[oracle@edvmr1p0 stage]$ cd mysql_jdbc/
[oracle@edvmr1p0 mysql_jdbc]$ tar xzvf mysql-connector-java-5.1.40.tar.gz 
mysql-connector-java-5.1.40/
mysql-connector-java-5.1.40/docs/
mysql-connector-java-5.1.40/src/
mysql-connector-java-5.1.40/src/com/
mysql-connector-java-5.1.40/src/com/mysql/
mysql-connector-java-5.1.40/src/com/mysql/fabric/
mysql-connector-java-5.1.40/src/com/mysql/fabric/hibernate/
mysql-connector-java-5.1.40/src/com/mysql/fabric/jdbc/
mysql-connector-java-5.1.40/src/com/mysql/fabric/proto/
mysql-connector-java-5.1.40/src/com/mysql/fabric/proto/xmlrpc/
</Code>

====Configure MySQL====
<Code:bash|Configure my.cnf (e.g. /etc/my.cnf)>
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
lower_case_table_names = 1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
</Code>

We can start mysql and configure MySQL for the replication

<Code:bash|Start & Verify MySQL>
[oracle@edvmr1p0 oggtrg]$ service mysqld status
mysqld (pid  3092) is running...
[oracle@edvmr1p0 oggtrg]$ 
[oracle@edvmr1p0 practice08]$ mysql -u oracle -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
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> use bigdata
Database changed
mysql> create table if not exists customer_prod (
    -> customer_id numeric,
    -> first_name varchar(50),
    -> last_name varchar(100),
    -> email_address varchar(132),
    -> ssn varchar(20),
    -> address varchar(200),
    -> city varchar(50),
    -> zip_code varchar(10),
    -> customer_type numeric);
Query OK, 0 rows affected (0.01 sec)

mysql> create table if not exists product_prod(
    -> product_id numeric,
    -> prod_code varchar(20),
    -> prod_name varchar(132),
    -> supplier_id numeric);
Query OK, 0 rows affected (0.00 sec)
</Code>

====Configure Properties File====
<Code:bash|Configure Properties File(e.g. TRG_OGGHOME/dirprm/jdbc_mysql.properties>
gg.handlerlist=jdbc
gg.handler.jdbc.type=jdbc
gg.handler.jdbc.DriverClass=com.mysql.jdbc.Driver
gg.handler.jdbc.connectionURL=jdbc:mysql://localhost:3306/bigdata
gg.handler.jdbc.userName=oracle
gg.handler.jdbc.password=oracle
gg.error.duplicateErrorCodes=1022,1062
gg.error.notFoundErrorCodes=1329
gg.error.deadlockErrorCodes=1213,1614
gg.classpath=/stage/mysql_jdbc/mysql-connector-java-5.1.40/mysql-connector-java-5.1.40-bin.jar

goldengate.userexit.timestamp=utc
goldengate.userexit.writers=javawriter
javawriter.stats.display=TRUE
javawriter.stats.full=TRUE
gg.log=log4j
gg.log.level=INFO
gg.report.time=30sec
jvm.bootoptions=-Xmx512m -Xms32m -Djava.class.path=.:ggjava/ggjava.jar:./dirprm


</Code>

====Configure Golden Gate (replicat)====
<Code:bash|Configure Golden Gate>
[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> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           


GGSCI (edvmr1p0) 2> edit param rjdbc

REPLICAT rjdbc
TARGETDB LIBFILE libggjava.so SET property=dirprm/jdbc_mysql.properties
REPORTCOUNT EVERY 1 MINUTES, RATE
GROUPTRANSOPS 1000
MAP OGGSRC.*, TARGET bigdata.*;
:wq

GGSCI (edvmr1p0) 3> add replicat rjdbc, exttrail ./dirdat/jd
REPLICAT added.


GGSCI (edvmr1p0) 4> start rjdbc

Sending START request to MANAGER ...
REPLICAT RJDBC starting


GGSCI (edvmr1p0) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     RJDBC       00:00:00      00:00:06    


GGSCI (edvmr1p0) 6> 
</Code>


=====Verify=====
To verify the repplication configuration, we will again insert data and check if that data is replicated

<Code:bash|Insert Data into Oracle>
[oracle@edvmr1p0 oggsrc]$ sqlplus oggsrc/oracle@orcl

SQL*Plus: Release 12.1.0.2.0 Production on Thu Nov 12 17:57:25 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Thu Nov 12 2020 17:37:43 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> insert into customer_prod select * from customer where customer_id > 1000 and customer_id < 4001;

3000 rows created.

SQL> commit;

Commit complete.

SQL> 
</Code>

Now, we can verify if the Golden Gate Replication Worked


<Code:bash|Golden Gate Replication>
--Source
GGSCI (edvmr1p0) 2> send priex,stats

Sending STATS request to EXTRACT PRIEX ...

Start of Statistics at 2020-11-12 17:59:35.

Output to ./dirdat/in:

Extracting from OGGSRC.CUSTOMER_PROD to OGGSRC.CUSTOMER_PROD:

*** Total statistics since 2020-11-12 17:59:29 ***
	Total inserts                   	        3000.00
	Total updates                   	           0.00
	Total deletes                   	           0.00
	Total discards                  	           0.00
	Total operations                	        3000.00

*** Daily statistics since 2020-11-12 17:59:29 ***
	Total inserts                   	        3000.00
	Total updates                   	           0.00
	Total deletes                   	           0.00
	Total discards                  	           0.00
	Total operations                	        3000.00

*** Hourly statistics since 2020-11-12 17:59:29 ***
	Total inserts                   	        3000.00
	Total updates                   	           0.00
	Total deletes                   	           0.00
	Total discards                  	           0.00
	Total operations                	        3000.00

*** Latest statistics since 2020-11-12 17:59:29 ***
	Total inserts                   	        3000.00
	Total updates                   	           0.00
	Total deletes                   	           0.00
	Total discards                  	           0.00
	Total operations                	        3000.00

End of Statistics.


GGSCI (edvmr1p0) 3> 

--Destination
GGSCI (edvmr1p0) 6> send rjdbc, stats

Sending STATS request to REPLICAT RJDBC ...

Start of Statistics at 2020-11-12 17:59:42.

Replicating from OGGSRC.CUSTOMER_PROD to bigdata.CUSTOMER_PROD:

*** Total statistics since 2020-11-12 17:59:32 ***
	Total inserts                   	        3000.00
	Total updates                   	           0.00
	Total deletes                   	           0.00
	Total discards                  	           0.00
	Total operations                	        3000.00

*** Daily statistics since 2020-11-12 17:59:32 ***
	Total inserts                   	        3000.00
	Total updates                   	           0.00
	Total deletes                   	           0.00
	Total discards                  	           0.00
	Total operations                	        3000.00

*** Hourly statistics since 2020-11-12 17:59:32 ***
	Total inserts                   	        3000.00
	Total updates                   	           0.00
	Total deletes                   	           0.00
	Total discards                  	           0.00
	Total operations                	        3000.00

*** Latest statistics since 2020-11-12 17:59:32 ***
	Total inserts                   	        3000.00
	Total updates                   	           0.00
	Total deletes                   	           0.00
	Total discards                  	           0.00
	Total operations                	        3000.00

End of Statistics.


GGSCI (edvmr1p0) 7> 

</Code>


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               |
+--------------------+
3 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 |
+----------+
1 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                |
+-------------+------------+-----------+--------------------------+
10 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    |       |
+-------------+---------------+------+-----+---------+-------+
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>