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.
