Replication from Oracle to MySQL Using GoldenGate

posted May 11, 2017, 12:58 PM by

Andy Kerber (@dbakerber), Senior Consultant

This is the second part of a four part series on Oracle GoldenGate. In part one, we set up Oracle bi-directional (multi-master) replication with basic conflict resolution. In this article, part two, we will configure the replication from one of the Oracle databases to the MySQL database.

The MySQL database name is andy, and uses the innodb engine.

The MySQL server name is mysql1.

Once again, be sure and note the particular server on which each step is done – the sections have been have been color coded accordingly. There is much moving back and forth in this configuration process, so verify your environment in each step.

Starting on the MySQL database server, mysql1, install the GoldenGate software.

The installation file for GoldenGate for MySQL is tar of a zip file. Copy the file to your intended GoldenGate home directory, untar and unzip it. In this case, I am running from /u01/home/oracle/ggcore. Then make sure that the GoldenGate Home ($GGH) is in your LD_LIBRARY_PATH:

export LD_LIBRARY_PATH=/home/oracle/ggcore;$LD_LIBRARY_PATH
export GGH=/home/oracle/ggcore

 

Connect to the GoldenGate command interface, ggsci and create the subdirectories. GGSCI must be run from the GoldenGate home directory, ($GGH).

GGSCI@mysql1> create subdirs

 

Next, assign the port for the GoldenGate software. Normally this is port 7809. Then start the GoldenGate manager process.

GGSCI@mysql1> edit params mgr
port 7809

GGSCI@mysql1> start mgr

GGSCI@mysql1> exit

 

The next step is executed in the MySQL database itself. Connect to the MySQL database and create the database and tables in MySQL. We are using database andy and username andy. In order to make the replication a little easier (this will let us use wildcards), we are using lower case for the database name, and upper case for the table name.

mysql –u root –p
password

mysql> create user 'andy'@'mysql1' identified by 'ieqhdaoiu@'

mysql1>grant all privileges on *.* to ‘andy’@’mysql1’ with grant option;

mysql> create database andy

mysql> use andy

 

CREATE TABLE `andy`.`BONUS`
 (    `ENAME` VARCHAR(10),
 `JOB` VARCHAR(9),
 `SAL` DOUBLE,
 `COMM` DOUBLE,
 `LAST_UPDT_DT_TM` TIMESTAMP  DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
 )
 ;
 CREATE TABLE `andy`.`DEPT`
 (    `DEPTNO` TINYINT,
 `DNAME` VARCHAR(14),
 `LOC` VARCHAR(13),
 `LAST_UPDT_DT_TM` TIMESTAMP DEFAULT current_timestamp
 )
 ;
 CREATE TABLE `andy`.`DEPTTEST`
 (    `DEPTNO` TINYINT,
 `DNAME` VARCHAR(14),
 `LOC` VARCHAR(13),
 `LAST_UPDT_DT_TM` TIMESTAMP DEFAULT current_timestamp
 )
 ;
 CREATE TABLE `andy`.`EMP`
 (    `EMPNO` SMALLINT,
 `ENAME` VARCHAR(10),
 `JOB` VARCHAR(9),
 `MGR` SMALLINT,
 `HIREDATE` DATETIME,
 `SAL` DECIMAL(7,2),
 `COMM` DECIMAL(7,2),
 `DEPTNO` TINYINT,
 `LAST_UPDT_DT_TM` TIMESTAMP DEFAULT current_timestamp
 )
 ;
 CREATE TABLE `andy`.`MYTEST`
 (    `EMPNO` SMALLINT,
 `ENAME` VARCHAR(10),
 `JOB` VARCHAR(9),
 `MGR` SMALLINT,
 `HIREDATE` DATETIME,
 `SAL` DECIMAL(7,2),
 `COMM` DECIMAL(7,2),
 `DEPTNO` TINYINT,
 `LAST_UPDT_DT_TM` TIMESTAMP DEFAULT current_timestamp
 )
 ;
 CREATE TABLE `andy`.`SALGRADE`
 (    `GRADE` DOUBLE,
 `LOSAL` DOUBLE,
 `HISAL` DOUBLE,
 `LAST_UPDT_DT_TM` TIMESTAMP DEFAULT current_timestamp
 )
 ;
 CREATE TABLE `andy`.`SCOTTWORK`
 (    `EMPNO` SMALLINT,
 `ENAME` VARCHAR(10),
 `JOB` VARCHAR(9),
 `MGR` SMALLINT,
 `HIREDATE` DATETIME,
 `SAL` DECIMAL(7,2),
 `COMM` DECIMAL(7,2),
 `DEPTNO` TINYINT,
 `LAST_UPDT_DT_TM` TIMESTAMP DEFAULT current_timestamp
 )
 ;
 CREATE TABLE `andy`.`SCOTTWORK3`
 (    `EMPNO` SMALLINT,
 `ENAME` VARCHAR(10),
 `JOB` VARCHAR(9),
 `MGR` SMALLINT,
 `HIREDATE` DATETIME,
 `SAL` DECIMAL(7,2),
 `COMM` DECIMAL(7,2),
 `DEPTNO` TINYINT,
 `LAST_UPDT_DT_TM` TIMESTAMP DEFAULT current_timestamp
 )
 ;
 CREATE TABLE `andy`.`TCUSTMER`
 (    `CUST_CODE` VARCHAR(4),
 `NAME` VARCHAR(30),
 `CITY` VARCHAR(20),
 `STATE` CHAR(2),
 `LAST_UPDT_DT_TM` TIMESTAMP DEFAULT current_timestamp
 )
 ;
 CREATE TABLE `andy`.`TCUSTORD`
 (    `CUST_CODE` VARCHAR(4),
 `ORDER_DATE` DATETIME,
 `PRODUCT_CODE` VARCHAR(8),
 `ORDER_ID` DOUBLE,
 `PRODUCT_PRICE` DECIMAL(8,2),
 `PRODUCT_AMOUNT` INT,
 `TRANSACTION_ID` DOUBLE,
 `LAST_UPDT_DT_TM` TIMESTAMP DEFAULT current_timestamp
 )
 ;
 CREATE TABLE `andy`.`TESTAB`
 (    `EMPNO` SMALLINT,
 `ENAME` VARCHAR(10),
 `JOB` VARCHAR(9),
 `MGR` SMALLINT,
 `HIREDATE` DATETIME,
 `SAL` DECIMAL(7,2),
 `COMM` DECIMAL(7,2),
 `DEPTNO` TINYINT,
 `LAST_UPDT_DT_TM` TIMESTAMP DEFAULT current_timestamp
 )
 ;

 CREATE UNIQUE INDEX `PK_DEPT` ON `andy`.`DEPT` (`DEPTNO`)
 ;
 CREATE UNIQUE INDEX `PK_EMP` ON `andy`.`EMP` (`EMPNO`)
 ;
 CREATE UNIQUE INDEX `SYS_C0011589` ON `andy`.`TCUSTMER` (`CUST_CODE`)
 ;
 CREATE UNIQUE INDEX `SYS_C0011590` ON `andy`.`TCUSTORD` (`CUST_CODE`, `ORDER_DATE`, `PRODUCT_CODE`, `ORDER_ID`)
 ;

ALTER TABLE `andy`.`DEPT` ADD CONSTRAINT `PK_DEPT` PRIMARY KEY (`DEPTNO`);
 ALTER TABLE `andy`.`EMP` ADD CONSTRAINT `PK_EMP` PRIMARY KEY (`EMPNO`);
 ALTER TABLE `andy`.`TCUSTMER` ADD PRIMARY KEY (`CUST_CODE`);
 ALTER TABLE `andy`.`TCUSTORD` ADD PRIMARY KEY (`CUST_CODE`, `ORDER_DATE`, `PRODUCT_CODE`, `ORDER_ID`);
 ALTER TABLE `andy`.`EMP` ADD CONSTRAINT `FK_DEPTNO` FOREIGN KEY (`DEPTNO`);

mysql> exit;

 

Connect to GGSCI on the Oracle server and create the initial load extract. Since these are small tables, we will use GoldenGate to do the initial load. If these were large tables, we would need to use a different, faster loading method such as load data infile. Also, note that the parameters point to the target (MySQL) database.

GGSCI@pgorcl> add extract andy_ora, sourceistable

GGSCI@pgorcl> edit params andy_ora

 

extract andy_ora
userid ggadmin, password ggadmin
rmthost mysql1, mgrport 7809 ← parameters for the mysql server
rmttask replicat, group initandy ← This group will be the same name as the replicat used to load the data in Golden Gate on the MySQL instance.
table andy.*;

Next, create the definition file for the tables for GoldenGate. This allows GoldenGate to translate the Oracle definition to the MySQL definition. This same definition will be used for the PostgreSQL connection.

GGSCI@pgorcl>edit params defgen

 

defsfile ./dirdef/andy.def, purge
userid ggadmin, password ggadmin
table andy.*;

From the OS level, generate the defgen file.

$defgen paramfile ./dirprm/defgen.prm

 

The command will write the defgen file to the file specified, in this case $GGH/dirdef/andy.def

The file will have a section like this at the start:

Database type: ORACLE
Character set ID: UTF-8
National character set ID: UTF-16
Locale: neutral
Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14
TimeZone: GMT
*

 

And sections like this for each table:

Definition for table ANDY.EMP
Record length: 144
Syskey: 0
Columns: 9
EMPNO             134     8       0 0 0 1 0      8     8     8 0 0 0 0 1   0 1   3   2       -1     0 0 0
ENAME             64      10      12 0 0 1 0     10    10    0 0 0 0 0 1   0 0   0   1       -1     0 0 0
JOB               64      9       28 0 0 1 0     9     9     0 0 0 0 0 1   0 0   0   1       -1     0 0 0
MGR               134     8       42 0 0 1 0     8     8     8 0 0 0 0 1   0 0   3   2       -1     0 0 0
HIREDATE          192     19      54 0 0 1 0     19    19    19 0 5 0 0 1  0 0   0   12      -1     0 0 0
SAL               134     9       76 2 0 1 0     8     8     8 0 0 0 0 1   0 0   3   2       -1     0 0 0
COMM              134     9       88 2 0 1 0     8     8     8 0 0 0 0 1   0 0   3   2       -1     0 0 0
DEPTNO            134     8      100 0 0 1 0     8     8     8 0 0 0 0 1   0 0   3   2       -1     0 0 0
LAST_UPDT_DT_TM   192     29     112 0 0 1 0     29    29    29 0 6 0 0 1  0 0   0   187     -1     0 0 0
End of definition

 

The file is used to translate the Oracle data to MySQL data. After generating the file, copy it to the corresponding directory on the MySQL server.

eg scp andy.def mysql1:/home/oracle/ggcore/dirdef

 

Go back to the target (MySQL) machine and start ggsci and configure the replicat for the initial data load.

Once again, we can use wild cards. Make sure that the userid you are using for MySQL is configured to log in to the physical server, and confirm that it has appropriate privileges, as shown above when creating the user, database, and schema.

Note that in this case, the name of the replicat is the same name as specified in the extract earlier.

GGSCI@mysql1> add replicat initandy, specialrun ← Special run because it will be used just once to do the initial load.

GGSCI@mysql1> edit params initandy ← Name specified in extract definition above.

replicat initandy
targetdb andy@mysql1, userid andy, password ieqhdaoiu@
sourcedefs ./dirdef/andy.def
discardfile ./dirrpt/andysql.dsc, purge
map ANDY.*, target andy.*;

Connect to the Oracle database server and start the initial load process. The replicat does not need to be started because the source database server initiates it when the extract process is started.

GGSCI@pgorcl> start extract andy_ora

 

Log into the MySQL database and verify that the data is there.

[oracle@mysql1 dirprm]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 62
Server version: 5.7.18-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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 andy
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(1) from EMP;
  +----------+
  | count(1) |
  +----------+
  |       13 |
  +----------+
  1 row in set (0.03 sec)

mysql>

 

At this point, we have established that the initial load worked. Now we need to create the actual change data capture process, starting on the source (Oracle) database.

GGSCI@pgorcl> add extract mysqlora, tranlog, begin now

GGSCI@pgorcl> edit params mysqlora

 

extract mysqlora
userid ggadmin, password ggadmin
rmthost mysql1, mgrport 7809
rmttrail ./dirdat/lt
table andy.*;

GGSCI@pgorcl> add rmtttrail ./dirdat/lt, extract mysqlora

GGSCI@pgorcl> start extract mysqlora

 

Connect to the target (MySQL) server and start ggsci.

In GGSCI, define the checkpoint table that will be used.

GGSCI@mysql1> edit params ./GLOBALS

CHECKPOINTTABLE andy.CHECKPOINTTABLE

 

Note: the GLOBALS file must reside in $GGH in order for it to be used. After making the change, verify that the file GLOBALS exists in the GoldenGate home directory, and contains the line above.

After changing the GLOBALS file, you must exit and restart ggsci for the change to take effect.

GGSCI@mysql1> exit
./ggsci

 

Log into the database from within GGSCI and create the checkpointtable.

GGSCI@mysql1> dblogin soourcedb andy@mysql1, password ieqhdaoiu@

GGSCI@mysql1> add checkpointtable

 

Verify that no error has occurred. If there is an error about no checkpoint table being defined, check the line in the GLOBALS fine.

Next, still on the MySQL server, configure the replicat.

GGSCI@mysql1> edit params oramysql

 

replicat oramysql
targetdb andy@mysql1 userid akerber, password ieqhdaoiu@
HANDLECOLLISIONS
sourcedefs ./dirdef/andy.def
discardfile ./dirrpt/rep_mysql.dsc PURGE
map ANDY.*, target andy.*;

GGSCI@mysql1> start replicat oramysql

 

Make changes and verify that they are being replicated, use the same method we used for Oracle.

Debugging notes: The file ggserr.log is in the GoldenGate home directory. It can be helpful to tail the file during the entire process on both the Oracle and MySQL server to identify any errors.

At this point, we have configured bi-directional replication between Oracle databases, and one way replication to a MySQL database. In part 3, we will configure one way replication from Oracle to PostgreSQL.

Please note: this blog contains code examples provided for your reference. All sample code is provided for illustrative purposes only. Use of information appearing in this blog is solely at your own risk. Please read our full disclaimer for details.

Share with your networkTweet about this on TwitterShare on LinkedInShare on FacebookDigg thisEmail this to someone

Leave a Reply

Your email address will not be published. Required fields are marked *

WANT TO LEARN MORE?

Share with your networkTweet about this on TwitterShare on LinkedInShare on FacebookDigg thisEmail this to someone