database 如何同步两个 Oracle 数据库？

How do I synchronize two Oracle Databases?

提问by Xinus

I want to create a project in which it is required to synchronize local database with remote database. Changes made in local database must be reflected to remote database. This must be done synchronously. I have application which does CRUD operations on local database written in java/jpa. Synchronization should be triggered from java code. I thought of triggers on each table of database which will maintain changes in local database. But I doubt its secure or not. After googling I found out that oracle lite database is best for synchronizing two databases, but I need to get started quickly. Operating system I am using is Windows Xp. Database size is around 2gb.

我想创建一个需要将本地数据库与远程数据库同步的项目。在本地数据库中所做的更改必须反映到远程数据库中。这必须同步完成。我有对用 java/jpa 编写的本地数据库执行 CRUD 操作的应用程序。同步应该从 java 代码触发。我想到了每个数据库表上的触发器，它将维护本地数据库中的更改。但我怀疑它是否安全。谷歌搜索后，我发现 oracle lite 数据库最适合同步两个数据库，但我需要快速上手。我使用的操作系统是 Windows Xp。数据库大小约为 2GB。

I did created thread for achieving same thing in java but from suggetion I restarted same thread in the database context.

我确实为在 Java 中实现同样的事情创建了线程，但是根据建议，我在数据库上下文中重新启动了相同的线程。

采纳答案by Vincent Malgrat

First of all I'd suggest you reconsider your design. The simplest way to repicate your data is through views as suggested in this SO. You could create a DATABASE LINK between your two DBs and create views at the remote site that would query the local database. This would be the simplest way to have Real-Time synchronization (less code, less maintenance).

首先，我建议您重新考虑您的设计。复制数据的最简单方法是通过此 SO 中建议的视图。您可以在两个数据库之间创建一个 DATABASE LINK，并在远程站点上创建查询本地数据库的视图。这将是实现实时同步的最简单方法（更少的代码，更少的维护）。

If you really want to replicate your data synchronously, you should read the Replication Guide. You could go with materialized views. You will need to define materialized view logson your tables at your master site. At the remote site you will create ON COMMIT REFRESH materialized views.

如果你真的想同步复制你的数据，你应该阅读复制指南。你可以使用物化视图。您需要在主站点的表上定义物化视图日志。在远程站点，您将创建ON COMMIT REFRESH 物化视图。

回答by rekounas

What you are referring to is called replication. Synchronization is something different.

您所指的称为复制。同步是不同的。

If you have multiple devices with a data subset requirement, for example a region sales person only requires data that reflects the area he/she serves, then Oracle Database Lite Mobile Server is what you are looking for. Basically, you have a central Oracle database and your want some data to be pushed to the client. That client will make updates/inserts/deletes to the device and then they will sync. New information on the server will be pushed to their device while their changes will be uploaded to the server. You will require conflict resolution (server/client wins... Oracle Mobile Server also allows for custom built conflict resolution). Oracle Mobile Server is also use to provision your Application Software and Manage Devices remotely. The tool is designed so that you only require a central DBA. The remote machines do not require any DBA type of activities.

如果您有多个具有数据子集要求的设备，例如区域销售人员只需要反映他/她所服务区域的数据，那么 Oracle Database Lite Mobile Server 就是您要寻找的。基本上，您有一个中央 Oracle 数据库，并且您希望将一些数据推送到客户端。该客户端将对设备进行更新/插入/删除，然后它们将同步。服务器上的新信息将推送到他们的设备，而他们的更改将上传到服务器。您将需要解决冲突（服务器/客户端获胜... Oracle Mobile Server 还允许自定义构建的冲突解决方案）。Oracle Mobile Server 还用于远程供应您的应用软件和管理设备。该工具的设计使您只需要一个中央 DBA。

回答by mlaccetti

I've been mulling this problem for a few days as we need to do development in parallel with QA running their tests. We do have two separate Oracle instances, but keeping them in sync manually would have been a huge pain. I'm not a DBA, so just looking at the replication stuff made me run away in fear.

我已经考虑这个问题几天了，因为我们需要在 QA 运行测试的同时进行开发。我们确实有两个单独的 Oracle 实例，但是手动保持它们同步会是一个巨大的痛苦。我不是 DBA，所以只是看着复制的东西就让我害怕了。

Instead, I fired up Navicat for Oracle and (ab)used the Data Transfer feature - makes keeping two schemas in physically separate databases synchronized pretty painless. A pretty damn useful tool! (I don't work for them, just like their tool.)

相反，我为 Oracle 启动了 Navicat 并（ab）使用了数据传输功能 - 使在物理上分开的数据库中保持两个模式同步非常轻松。一个非常有用的工具！（我不为他们工作，就像他们的工具一样。）