Migration of table from MySQL to Oracle using GoldenGate

Question

I have a table of size 120 GB in MySQL 5.7 which I need to migrate to Oracle 12c. I am using Oracle GoldenGate for initial load of the table.

I tried with initial load of full table but it consumed complete RAM and I had to cancel it.

Even trying to run with primary key range initial load for 1/20^th of table at a time, but it abends after certain time with:

ERROR OGG-01233 Send TCP params error: TCP/IP error 110 (Connection timed out)

Is there any better approach to get this completed?

Answer 1

From my experience with GoldenGate, it's better to do initial loads with database native tools. In this case, you would use:

SELECT * FROM TABLENAME INTO OUTFILE 'filename';

https://dev.mysql.com/doc/refman/5.7/en/select-into.html

Then load into Oracle with SQL*Loader.

Example control file for SQL Loader:

$ cat example1.ctl
load data
 infile '/home/ramesh/employee.txt'
 into table employee
 fields terminated by ","
 ( id, name, dept, salary )

Example taken from: https://www.thegeekstuff.com/2012/06/oracle-sqlldr/

If you cannot stop data changes to perform the load, you need to:

• Start your extract
• Export the data
• Load the data
• start your replicat with HANDLECOLLISIONS enabled

After your replicat has caught up, remove HANDLECOLLISIONS.

For some good Tutorials, I've used https://gavinsoorma.com/category/goldengate/

Also the Oracle TechNet forums were responsive for GoldenGate questions - even for some really obscure scenarios.