How to Replicate Data Between Oracle and MySQL Database? (Doc ID 1605674.1)

0    307    3

Tags:

👉 本文共约1556个字,系统预计阅读时间或需6分钟。

How to Replicate Data Between Oracle and MySQL Database? (Doc ID 1605674.1)

GOAL

This note describes a set up of a GoldenGate replication between Oracle and MySQL

SOLUTION

Please make sure you have installed GoldenGate on the Oracle and the MySQL server. First let's start with the configuration of GoldenGate on the MySQL machine.

1. Pre-requisites

So let's start ggsci: ./ggsci
and in ggsci let's create the subdirectories:

2. GoldenGate Manager

The next step is to configure a basic GoldenGate manager process:

(SIDE NOTE - I have to use 7806 port here as my replicat and extract will be running on the same machine...)

We do the same steps now for the GG side that will connect to the Oracle database and use here now the port 7809.
So at the end we have now 2 manager processes running - one for the Oracle GG extract and the other one for the MySQL replicat.

3. GoldenGate demo tables

For a basic replication we create the demo tables in Oracle and MySQL using the scripts shipped with the installation.

3.1 The demo tables for the Oracle database

connect to the Oracle database and execute the demo_ora_create and demo_ora_insert script:

3.2 Creating the MySQL demo tables

Connect to the MySQL database and execute demo_mysql_create.sql script:

4. Oracle database pre-requisites

4.1 Verify Oracle DB connection

In my Oracle database I have a user mysql with password which owns the 2 tables.

Found 2 tables matching list criteria.

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!
4.2 Add the TRANDATA

to be able to capture the changes for the table we need to add trandata:

5. GoldenGate initial load (no data pump process configured to keep it simple)

The Oracle table already contains some data, so let's define an initial load process to first replicate the data to the target:

6. The SOURCEDEFS file

when replicating data between an Oracle and a foreign database we also need to create the SOURCEDEFS file which requires a definition generator parameter file. It is created in ggsci containing the username and password as well as the table we want to map.

6.1 DEFGEN parameter file

6.2 Defgen command

Now we need to exit from the ggsci command line tool and call the defgen executable:

6.3 COPY DEFGEN file

The defgen file was created in the installation directory from the extract process and we need to copy it to the target GoldenGate installation.

Copy DEFGEN File ./dirsql/GGMySQLTEST.sql to TARGET machine into ./dirsql/GGMySQLTEST.sql

7. MySQL initial load replicat process

We already created a process that extracts the source table data and directly pushes the extracted data to the target machine/direcory. So we skipped the PUMP process that is commonly getting the data from the extract and pushing it to the source and need to configure now a replicat process only to read the extracted data:

8. Start Initial Load Extract process

There's already a replicat process running and waiting for the data from the initial load extract process. So let's start it...

=> We got 2 records replicated now from the source Oracle database to the target which can be checked out using a MySQL tool as well:

The initial load was working and we can configure also a normal replication to push the changes from the Oracle database to the MySQl database. Again we here do not configure a data pump process to keep it simple.

9. GoldenGate Extract for the Oracle database

9.1 GoldenGate extract parameter file

9.2 Add GoldenGate extract

9.3 start GoldenGate extract

9.4 Status extract

Checking out the status on the extract side shows a running Manager and Extract process:

10 MySQL replicat

10.1 MySQl replicat parameter file

10.2 add and start MySQL REPLICAT process

10.3 REPLICAT status

11. Verify the replication

Extract and replicat process are running. Now let's manipulate the data on the Oracle side:

11.1 Update Oracle table content

11.2 check replicat statistics

Looks good - the replicat statistics contain at least an update... let's verify it using a MySQL tool:

So the update was replicated from the Oracle source database to the MySQL target.

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

12 + 19 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部