从Oracle迁移到MySQL [英] Migrate from Oracle to MySQL

问题描述

我们的Oracle数据库遇到严重的性能问题，我们想尝试将其迁移到基于MySQL的数据库（直接使用MySQL，或者更优选的是，Infobright）。

We ran into serious performance problems with our Oracle database and we would like to try to migrate it to a MySQL-based database (either MySQL directly or, more preferably, Infobright).

问题是，如果新数据库的所有功能都符合我们的需要，我们需要让新系统和新系统重叠至少几周（如果不是几个月），然后我们才真正知道。

The thing is, we need to let the old and the new system overlap for at least some weeks if not months, before we actually know, if all the features of the new database match our needs.

所以，这是我们的情况：

So, here is our situation:

Oracle数据库由多个表组成，每个表有数百万行。在白天，有数千个语句，我们无法停止迁移。

The Oracle database consists of multiple tables with each millions of rows. During the day, there are literally thousands of statements, which we cannot stop for migration.

每天早上，新数据被导入Oracle数据库，取代了数千行。复制这个过程不是问题，因此我们理论上可以并行导入两个数据库。

Every morning, new data is imported into the Oracle database, replacing some thousands of rows. Copying this process is not a problem, so we could, in theory, import in both databases in parallel.

但是，这里面临的挑战是，为了实现这个目标我们需要从Oracle数据库导出一天的一致状态。 （我们不能在星期一导出一些表格，周二也不能导出其他表格等。）这意味着，至少出口应该在不到一天的时间内完成。

But, and here the challenge lies, for this to work we need to have an export from the Oracle database with a consistent state from one day. (We cannot export some tables on Monday and some others on Tuesday, etc.) This means, that at least the export should be finished in less than one day.

我们的首先想到的是转储模式，但是我无法找到将Oracle转储文件导入MySQL的工具。以CSV文件导出表格可能有效，但我担心这可能需要很长时间。

Our first thought was to dump the schema, but I wasn't able to find a tool to import an Oracle dump file into MySQL. Exporting tables in CSV files might work, but I'm afraid it could take too long.

所以现在我的问题是：

我该怎么办？有没有工具可以将Oracle转储文件导入MySQL？有没有人有这么大规模迁移的经验？

What should I do? Is there any tool to import Oracle dump files into MySQL? Does anybody have any experience with such a large-scale migration?

PS：请不要建议Oracle的性能优化技术，我们已经尝试了很多:-)

PS: Please, don't suggest performance optimization techniques for Oracle, we already tried a lot :-)

编辑：我们之前已经尝试过一些ETL工具，但却发现它们不够快：只导出一个表超过4个小时......

We already tried some ETL tools before, only to find out, that they were not fast enough: Exporting only one table already took more than 4 hours ...

第二次编辑：来吧伙计......没有人试过像以前一样快速导出整个数据库可能并转换数据，以便可以导入到另一个数据库系统？

2nd Come on folks ... did nobody ever try to export a whole database as fast as possible and convert the data so that it can be imported into another database system?

推荐答案

Oracle不提供数据盒子卸载实用程序。

Oracle does not supply an out-of-the-box unload utility.

请记住，没有关于您的环境的全面信息（oracle版本？服务器平台？有多少数据？什么数据类型？）这里的一切都是YMMV你想要给它为了性能和时间安排你的系统。

Keep in mind without comprehensive info about your environment (oracle version? server platform? how much data? what datatypes?) everything here is YMMV and you would want to give it a go on your system for performance and timing.

我的观点1-3只是通用的数据移动思路。第4点是一种将停机时间或中断减少到几分钟或几秒的方法。

My points 1-3 are just generic data movement ideas. Point 4 is a method that will reduce downtime or interruption to minutes or seconds.

1）有第三方实用程序可用。我已经使用了其中的一些，但最适合您自己查看它们的预期目的。此处列出了一些第三方产品： OraFaq 。不幸的是，很多它们在Windows上运行会降低数据卸载过程的速度，除非你的数据库服务器在Windows上，你可以直接在服务器上运行加载实用程序。

1) There are 3rd party utilities available. I have used a few of these but best for you to check them out yourself for your intended purpose. A few 3rd party products are listed here: OraFaq . Unfortunately a lot of them run on Windows which would slow down the data unload process unless your DB server was on windows and you could run the load utility directly on the server.

2）如果您没有像LOB这样的复杂数据类型，那么您可以使用SQLPLUS进行自己的数据类型。如果你一次做一个表，那么你可以轻松地并行化它。这个网站上访问的主题可能不止一次，这是一个例子： Linky

2) If you don't have any complex datatypes like LOBs then you can roll your own with SQLPLUS. If you did a table at a time then you can easily parallelize it. Topic has been visited on this site probably more than once, here is an example: Linky

3）如果你是10g +那么外部表可能是完成这项任务的高效方法。如果创建一些与当前表具有相同结构的空白外部表并将数据复制到它们，则数据将转换为外部表格式（文本文件）。 OraFAQ再次向救援。

3) If you are 10g+ then External Tables might be a performant way to accomplish this task. If you create some blank external tables with the same structure as your current tables and copy the data to them, the data will be converted to the external table format (a text file). Once again, OraFAQ to the rescue.

4）如果必须将系统并行保存数天/数周/月，则使用更改数据捕获/应用工具以实现接近零的停机时间。准备支付$$$。我使用了Golden Gate Software的工具，可以挖掘Oracle重做日志并为MySQL数据库提供插入/更新语句。您可以迁移大部分数据，而不会在上线前一周停机。然后在您的上线期间，关闭源数据库，让Golden Gate赶上最后剩余的事务，然后打开对新目标数据库的访问。我用它进行升级，追赶时间只有几分钟。我们已经获得了Golden Gate的站点许可证，因此对我们来说没有什么可以自掏腰包的。

4) If you must keep systems in parallel for days/weeks/months then use a change data capture/apply tool for near-zero downtime. Be prepared to pay $$$. I have used Golden Gate Software's tool that can mine the Oracle redo logs and supply insert/update statements to a MySQL Database. You can migrate the bulk of the data with no downtime the week before go-live. Then during your go-live period, shut down the source database, have Golden Gate catch up the last remaining transactions, then open up access to your new target database. I have used this for upgrades and the catch up period was only a few minutes. We already had a site licenses for Golden Gate so it wasn't anything out of pocket for us.

我将在这里扮演Cranky DBA的角色并说如果你不能让Oracle表现良好我很想看到MySQL如何修复你的特别的问题。如果您的应用程序无法触及SQL，那么仍​​有许多方法可以调整Oracle。 / soapbox

And I'll play the role of Cranky DBA here and say if you can't get Oracle performing well I would love to see a write up of how MySQL fixed your particular issues. If you have an application where you can't touch the SQL, there are still lots of possible ways to tune Oracle. /soapbox

这篇关于从Oracle迁移到MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！