Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite
1

We have two smallish unrelated databases on two big Oracle Enterprise servers. Instead of buying more licenses and servers for high-availability replicas, can we replicate database B to server A and database A to server B, as long as either server can handle the load for both databases if necessary?

Seems like there are several underlying questions:

  1. Which replication methods can replicate an active DB to a standby DB in both directions between two servers with good failover?
  2. How much lighter is a replicate-to-standby workload than a production workload?
  3. If people aren't doing this, are hidden gotchas stopping them or is it just not in fashion?
share|improve this question
Need more info. What objects are you wanting to replicate? Just tables? If tables, are they going to be read/write on both, or just write on the master and read only on the slave? – Phil Oct 22 '12 at 15:18
For HA fast failover I assume we'd replicate entire database, correct? – Paul Oct 22 '12 at 15:27
Oh, I read the question wrong. You can script archive log shipping and log application yourself so that you don't have to pay for Enterprise Edition - an "emulated data guard". It's relatively easy if you've got a bit of Oracle and shell experience. – Phil Oct 22 '12 at 15:39
And if we already have EE, it might make sense to use the included Data Guard. What I don't know is if it lets you set it up to work A to B AND B to A (for different databases). – Paul Oct 22 '12 at 15:49
Yes, you can. It's a good way of setting up HA as Oracle is licensed per core, not database. I'm assuming you're not running RAC? – Phil Oct 22 '12 at 15:53
show 1 more comment

2 Answers

up vote 1 down vote accepted

These days, Data Guard is included with Enterprise Edition.If you have enough spare capacity on each of the servers, I believe they can work as a physical standby for each other.

Logical and possibly bi-directional replication is more complex, and you would have to consider the types of objects and replication in the solution. Most solutions does not easily support the replication of DML etc.

Note that Data Guard configured as physical standby might still consume a considerable amount of resources and depending on the configuration, may impact on production performance on both servers.

share|improve this answer
Would you happen to know if Data Guard actually supports cross-replication? Thanks. – Paul Oct 22 '12 at 15:29
If you by "cross-replication" mean that each server can hold both a production database and a standby database for the other, then the answer is yes. Each standby database is configured independently. – Roy Oct 23 '12 at 12:25
I don't think that is what the OP meant by cross-replication. He says "B to A and A to B" and I believe this is from a database perspective not a server perspective. Using Data Guard would be more like B to C and A to D replication. – Leigh Riffel Oct 23 '12 at 12:34
True, DataGuard is based on full database replication, so from a database perspective the answer would then be no. You would have to create two new and seperate standby databases to mirror the two production databases. From a server perspective the answer is still yes, though. Database A on server 1 can be mirrored to database C on server 2. Database B on server 2 can then be mirrored to database D on server 1. – Roy Oct 23 '12 at 13:02
1  
@Roy Apparently I was wrong, good answer. – Leigh Riffel Oct 25 '12 at 12:07
show 1 more comment
up vote 1 down vote

Bi-directional replication is technically feasible, but is anything but simple, which is why it is not used unless absolutely necessary.

Streams can be setup to do bi-directional replication. See the Oracle® Streams Replication Administrator's Guide. However, Streams cannot handle automatic client fail-over and since it seems that high-availability is your goal, this is not an option.

The Oracle solution for this type of problem is RAC. It meets both the bi-directional replication requirement and the fail-over requirement. However, if you are looking for storage redundancy, it does not provide that as the database storage must be shared between the servers. If your primary concern is instance failure, then it will help you. Since you are on Enterprise it is an extra cost option, but if you can tolerate downgrading to Standard Edition, you can run RAC without any additional cost.

Another option would be to turn your two servers into Virtual Machine Hosts and virtualize your two databases. This again would not provide storage redundancy, but would allow you to transparently migrate either database to either server, running them on the same server when there is maintenance to be done or a server outage occurs.

The Golden Gate product Oracle acquired does bi-directional replication and is more flexible than streams, but at a higher cost.

There are other options at various levels of granularity and capability all the way from Materialized Views over database links to storage based snapshots. Consider carefully what you need and whether a solution meets your primary requirements.

share|improve this answer
Leigh, thanks for your thoughtful and detailed response. I edited the question to clarify that only one replica need be updateable at a time. We need Enterprise for encryption, but I didn't know Standard includes RAC. BTW, is automatic client fail-over supported with Data Guard? – Paul Oct 24 '12 at 20:34
I think it easy to confuse bidirectional replication with what I like to call an active-active standby configuration. The latter is a fairly common way of having two production servers work as passive standby for each other, and I do think this is what the OP is asking for. Thankfully it's not nearly as hard to manage as multimaster/bidirectional replication. – Roy Oct 25 '12 at 12:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.