I have a series of Oracle databases that need to access each other's data. The most efficient way to do this is to use database links - setting up a few database links I can get data from A to B with the minimum of fuss. The problem for me is that you end up with a tightly-coupled design and if one database goes down it can bring the coupled databases with it (or perhaps part of an application on those databases).
我有一系列需要访问彼此数据的Oracle数据库。最有效的方法是使用数据库链接 - 设置一些数据库链接我可以从A到B获取数据,而且操作起来很少。对我来说问题是你最终得到一个紧密耦合的设计,如果一个数据库发生故障,它可以将耦合数据库带到它(或者可能是这些数据库上应用程序的一部分)。
What alternative approaches have you tried for sharing data between Oracle databases?
您尝试了哪些替代方法在Oracle数据库之间共享数据?
Update after a couple of responses...
几个回复后更新......
I wasn't thinking so much a replication, more on accessing "master data". For example, if I have a central database with currency conversion rates and I want to pull a rate into a separate database (application). For such a small dataset igor-db's suggestion of materialized views over DB links would work beautifully. However, when you are dynamically sampling from a very large dataset then the option of locally caching starts to become trickier. What options would you go for in these circumstances. I wondered about an XML service but tuinstoel (in a comment to le dorfier's reply) rightly questioned the overhead involved.
我没有想太多的复制,更多的是访问“主数据”。例如,如果我有一个具有货币转换率的*数据库,并且我想将费率提取到单独的数据库(应用程序)中。对于这样一个小型数据集,igor-db建议使用DB链接的物化视图可以很好地工作。但是,当您从非常大的数据集中动态采样时,本地缓存选项开始变得更加棘手。在这种情况下你会选择什么选择。我想知道一个XML服务,但是tuinstoel(在对le dorfier的回复的评论中)正确地质疑了所涉及的开销。
Summary of responses...
回复摘要......
On the whole I think igor-db is closest, which is why I've accepted that answer, but I thought I'd add a little to bring out some of the other answers.
总的来说,我认为igor-db是最接近的,这就是为什么我接受了这个答案,但我想我会添加一点来提出其他一些答案。
For my purposes, where I'm looking at data replication only, it looks like Oracle BASIC replication (as opposed to ADVANCED) replication is the one for me. Using materialized view logs on the master site and materialized views on the snapshot site looks like an excellent way forward.
就我的目的而言,我只关注数据复制,看起来Oracle BASIC复制(而不是ADVANCED)复制对我而言。使用主站点上的物化视图日志和快照站点上的物化视图看起来是一种很好的前进方式。
Where this isn't an option, perhaps where the data volumes make full table replication an issue, then a messaging solution seems the most appropriate Oracle solution. Oracle Advanced Queueing seems the quickest and easiest way to set up a messaging solution.
如果这不是一个选项,也许数据卷使全表复制成为问题,那么消息传递解决方案似乎是最合适的Oracle解决方案。 Oracle Advanced Queuing似乎是设置消息传递解决方案的最快捷,最简单的方法。
The least preferable approach seems to be roll-your-own XML web services but only where the relative ease of Advanced Queueing isn't an option.
最不可取的方法似乎是自己动手的XML Web服务,但只有在高级排队的相对容易性不可选的情况下。
6 个解决方案
#1
5
Streams is the Oracle replication technology. You can use MVs over database links (so database 'A' has a materialized view of the data from database 'B'. If 'B' goes down, the MV can't be refreshed but the data is still in 'A').
Streams是Oracle复制技术。您可以在数据库链接上使用MV(因此数据库'A'具有来自数据库'B'的数据的物化视图。如果'B'关闭,则MV无法刷新但数据仍然在'A'中) 。
Mileage may depend on DB volumes, change volumes...
里程可能取决于数据库卷,更改量...
#2
1
It looks to me like it's by definition tightly coupled if you need simultaneous synchronous access to multiple databases.
根据定义,如果您需要同时访问多个数据库,我认为它是紧密耦合的。
If this is about transferring data, for instance, and it can be asynchronous, you can install a message queue between the two and have two processes, with one reading from the source and the other writing to the sink.
例如,如果这是关于传输数据并且它可以是异步的,则可以在两者之间安装消息队列并具有两个进程,其中一个从源读取而另一个写入接收器。
#3
1
The OP has provided more information. He states that the dataset is very large. Well how large is large? And how often are the master tables changed?
OP提供了更多信息。他说数据集非常大。那么大有多大?主表的更改次数是多少?
With the use of materialized view logs Oracle will only propagate the changes made in the master table. A complete refresh of the data isn't necessary. Oracle streams also only communicate the modifications to the other side.
通过使用物化视图日志,Oracle将仅传播在主表中所做的更改。无需完全刷新数据。 Oracle流也只将修改传达给另一方。
Buying storage is cheap, so why not local caching? Much cheaper than programming your own solutions.
购买存储很便宜,为什么不进行本地缓存?比编写自己的解决方案便宜得多。
An XML service doesn't help you when its database is not available so I don't understand why it would help? Oracle has many options for replication, explore them.
当XML数据不可用时,XML服务无法帮助您,因此我不明白为什么它会有所帮助? Oracle有许多复制选项,可以探索它们。
edit
I've build xml services. They provide interoperability between different systems with a clear interface (contract). You can build a xml service in C# and consume the service with Java. However xml services are not fast.
我已经构建了xml服务。它们通过清晰的接口(合同)提供不同系统之间的互操作性。您可以在C#中构建xml服务并使用Java来使用该服务。但是xml服务并不快。
#4
1
Why not use Advanced Queuing? Why roll your own XML service to move messages (DML) between Oracle instances - It's already there. You can have propagation move messages from one instance to another when they are both up. You can process them as needed in the destination servers. AQ is really rather simple to set up and use.
为什么不使用高级队列?为什么要将自己的XML服务转移到Oracle实例之间移动消息(DML) - 它已经存在。当它们都启动时,您可以将传播从一个实例移动到另一个实例。您可以根据需要在目标服务器中处理它们。 AQ的设置和使用非常简单。
#5
1
Why do they need to be separate databases?
为什么他们需要成为独立的数据库?
Having a single database/instance with multiple schemas might be easier.
拥有多个模式的单个数据库/实例可能更容易。
Keeping one database up (with appropriate standby databases etc) will be easier than keeping N up.
保持一个数据库(使用适当的备用数据库等)将比保持N更容易。
#6
0
What kind of immediacy do you need and how much bi-directionality? If the data can be a little older and can be pulled from one "master source", create a series of simple ETL scripts run on a schedule to pull the data from the "source" database into the others.
你需要什么样的直接性和双向性?如果数据可能稍微长一些并且可以从一个“主源”中提取,则创建一系列简单的ETL脚本按计划运行,以将数据从“源”数据库提取到其他数据库中。
You can then tailor the structure of the data to feed the needs of the client database(s) more precisely and you can change the structure of the source data until you're blue in the face.
然后,您可以定制数据结构以更精确地满足客户端数据库的需求,并且可以更改源数据的结构,直到您看到蓝色为止。
#1
5
Streams is the Oracle replication technology. You can use MVs over database links (so database 'A' has a materialized view of the data from database 'B'. If 'B' goes down, the MV can't be refreshed but the data is still in 'A').
Streams是Oracle复制技术。您可以在数据库链接上使用MV(因此数据库'A'具有来自数据库'B'的数据的物化视图。如果'B'关闭,则MV无法刷新但数据仍然在'A'中) 。
Mileage may depend on DB volumes, change volumes...
里程可能取决于数据库卷,更改量...
#2
1
It looks to me like it's by definition tightly coupled if you need simultaneous synchronous access to multiple databases.
根据定义,如果您需要同时访问多个数据库,我认为它是紧密耦合的。
If this is about transferring data, for instance, and it can be asynchronous, you can install a message queue between the two and have two processes, with one reading from the source and the other writing to the sink.
例如,如果这是关于传输数据并且它可以是异步的,则可以在两者之间安装消息队列并具有两个进程,其中一个从源读取而另一个写入接收器。
#3
1
The OP has provided more information. He states that the dataset is very large. Well how large is large? And how often are the master tables changed?
OP提供了更多信息。他说数据集非常大。那么大有多大?主表的更改次数是多少?
With the use of materialized view logs Oracle will only propagate the changes made in the master table. A complete refresh of the data isn't necessary. Oracle streams also only communicate the modifications to the other side.
通过使用物化视图日志,Oracle将仅传播在主表中所做的更改。无需完全刷新数据。 Oracle流也只将修改传达给另一方。
Buying storage is cheap, so why not local caching? Much cheaper than programming your own solutions.
购买存储很便宜,为什么不进行本地缓存?比编写自己的解决方案便宜得多。
An XML service doesn't help you when its database is not available so I don't understand why it would help? Oracle has many options for replication, explore them.
当XML数据不可用时,XML服务无法帮助您,因此我不明白为什么它会有所帮助? Oracle有许多复制选项,可以探索它们。
edit
I've build xml services. They provide interoperability between different systems with a clear interface (contract). You can build a xml service in C# and consume the service with Java. However xml services are not fast.
我已经构建了xml服务。它们通过清晰的接口(合同)提供不同系统之间的互操作性。您可以在C#中构建xml服务并使用Java来使用该服务。但是xml服务并不快。
#4
1
Why not use Advanced Queuing? Why roll your own XML service to move messages (DML) between Oracle instances - It's already there. You can have propagation move messages from one instance to another when they are both up. You can process them as needed in the destination servers. AQ is really rather simple to set up and use.
为什么不使用高级队列?为什么要将自己的XML服务转移到Oracle实例之间移动消息(DML) - 它已经存在。当它们都启动时,您可以将传播从一个实例移动到另一个实例。您可以根据需要在目标服务器中处理它们。 AQ的设置和使用非常简单。
#5
1
Why do they need to be separate databases?
为什么他们需要成为独立的数据库?
Having a single database/instance with multiple schemas might be easier.
拥有多个模式的单个数据库/实例可能更容易。
Keeping one database up (with appropriate standby databases etc) will be easier than keeping N up.
保持一个数据库(使用适当的备用数据库等)将比保持N更容易。
#6
0
What kind of immediacy do you need and how much bi-directionality? If the data can be a little older and can be pulled from one "master source", create a series of simple ETL scripts run on a schedule to pull the data from the "source" database into the others.
你需要什么样的直接性和双向性?如果数据可能稍微长一些并且可以从一个“主源”中提取,则创建一系列简单的ETL脚本按计划运行,以将数据从“源”数据库提取到其他数据库中。
You can then tailor the structure of the data to feed the needs of the client database(s) more precisely and you can change the structure of the source data until you're blue in the face.
然后,您可以定制数据结构以更精确地满足客户端数据库的需求,并且可以更改源数据的结构,直到您看到蓝色为止。