Home » RDBMS Server » Server Administration » Instance duplication, Bi-directional implementation (11g DB on Windows Server 2008 R2 )
Instance duplication, Bi-directional implementation [message #671664] Wed, 12 September 2018 08:20 Go to next message
CRDBA
Messages: 5
Registered: September 2018
Junior Member
I was doing some research into this process and ran into some concerns in regards to configuring a duplicate DB for bi-directional replication.

1. The remote server to be host the duplicate instance is a considerable distance away from the master server.
2. No dev environment to test the performance once databases and connection is created.
3. What concerns regarding the network should I be aware of?

This is a first time doing this so I'm unaware of all of the factors that should be considered before starting this project. Any information would be greatly appreciated. If my post is unclear, I will be more than happy to elaborate where necessary.
Re: Instance duplication, Bi-directional implementation [message #671665 is a reply to message #671664] Wed, 12 September 2018 08:51 Go to previous messageGo to next message
BlackSwan
Messages: 26139
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read


What is OS name & version?
What is Oracle version to 4 decimal places
Re: Instance duplication, Bi-directional implementation [message #671666 is a reply to message #671665] Wed, 12 September 2018 08:59 Go to previous messageGo to next message
Roachcoach
Messages: 1568
Registered: May 2010
Location: UK
Senior Member
Do you mean Oracle Dataguard, "stretch" RAC (risky business) or one of these interesting "active/active" plans?

[Updated on: Wed, 12 September 2018 09:01]

Report message to a moderator

Re: Instance duplication, Bi-directional implementation [message #671667 is a reply to message #671664] Wed, 12 September 2018 09:07 Go to previous messageGo to next message
John Watson
Messages: 7557
Registered: January 2010
Location: Global Village
Senior Member
Your hardest decision may be what technology to use. Advanced Replication is de-supported from release 12.2, Streams is still supported but deprecated from 12.1. So within the Oracle product set, you are limited to the GoldenGate tool, which is neither cheap nor easy. You may need to look at third party products, such s dbVisit's Replicate or Quest's SharePlex.
Writing your own solution would not be a small task.
Re: Instance duplication, Bi-directional implementation [message #671668 is a reply to message #671667] Wed, 12 September 2018 09:36 Go to previous messageGo to next message
CRDBA
Messages: 5
Registered: September 2018
Junior Member
@Roachcoach
This is an active/active plan on 11g db. We don't have a dev environment to test this once configured. I will need to look into Dataguard to see if that can help.
Re: Instance duplication, Bi-directional implementation [message #671669 is a reply to message #671668] Wed, 12 September 2018 09:52 Go to previous messageGo to next message
CRDBA
Messages: 5
Registered: September 2018
Junior Member
@John Watson
Thanks, I will check out these products. Do you see any issues regarding connectivity over the long distance. I'm concerned that there will be a downgrade in performance as well. The network is already slow to begin with. Would you suggest having a separate VPN tunnel?
Re: Instance duplication, Bi-directional implementation [message #671670 is a reply to message #671668] Wed, 12 September 2018 09:58 Go to previous messageGo to next message
Roachcoach
Messages: 1568
Registered: May 2010
Location: UK
Senior Member
Dataguard won't help, you cannot write to both sides at once.

Active/active demands a few things, and not just from the DBA:

A technology as John mentioned to keep the data flowing between systems
An understanding of how the technology actually replicates information as this may affect day to day operation
Strong application discipline for collision handling and fault detection
In fact, strong application discipline in general
A plan in place for the breakdown of ACID compliance, i.e. I would anticipate dirty reads
A more complex than usual recovery strategy


Exceptions exist to these of course, but they are few. An example would be where either side only writes to her own parts so there is no possible DML/DDL collision whilst still allowing either DB to see the others data in 'near' real-time (YMMV, I often see lag between systems).

Bluntly, if you are a DBA and not the developer of the system then I would say the most important thing you can likely do is ensure your responsibilities stop and start at the database/s being up, available and backed up. Anything else is asking for an absolute world of pain you do not want. If you are the developer, you're going to have your work cut out for you, it'll be a great challenge for a while but I wager you'll soon end up cursing this approach as the requirements evolve over time.

In all honesty, I've never seen a good use case of this where transactional integrity matters anywhere. I tend to see it presented as a fait-accompli as a questionable solution to a non existent problem.

[Updated on: Wed, 12 September 2018 10:02]

Report message to a moderator

Re: Instance duplication, Bi-directional implementation [message #671671 is a reply to message #671670] Wed, 12 September 2018 10:34 Go to previous messageGo to next message
BlackSwan
Messages: 26139
Registered: January 2009
Location: SoCal
Senior Member
Roachcoach wrote on Wed, 12 September 2018 07:58
Dataguard won't help, you cannot write to both sides at once.

Active/active demands a few things, and not just from the DBA:

A technology as John mentioned to keep the data flowing between systems
An understanding of how the technology actually replicates information as this may affect day to day operation
Strong application discipline for collision handling and fault detection
In fact, strong application discipline in general
A plan in place for the breakdown of ACID compliance, i.e. I would anticipate dirty reads
A more complex than usual recovery strategy


Exceptions exist to these of course, but they are few. An example would be where either side only writes to her own parts so there is no possible DML/DDL collision whilst still allowing either DB to see the others data in 'near' real-time (YMMV, I often see lag between systems).

Bluntly, if you are a DBA and not the developer of the system then I would say the most important thing you can likely do is ensure your responsibilities stop and start at the database/s being up, available and backed up. Anything else is asking for an absolute world of pain you do not want. If you are the developer, you're going to have your work cut out for you, it'll be a great challenge for a while but I wager you'll soon end up cursing this approach as the requirements evolve over time.

In all honesty, I've never seen a good use case of this where transactional integrity matters anywhere. I tend to see it presented as a fait-accompli as a questionable solution to a non existent problem.
+99
Re: Instance duplication, Bi-directional implementation [message #671672 is a reply to message #671671] Wed, 12 September 2018 10:47 Go to previous messageGo to next message
CRDBA
Messages: 5
Registered: September 2018
Junior Member
@Blackswan

Windows 2008 R2
Oracle 11.2.0.4.0
Re: Instance duplication, Bi-directional implementation [message #671673 is a reply to message #671672] Wed, 12 September 2018 10:49 Go to previous messageGo to next message
CRDBA
Messages: 5
Registered: September 2018
Junior Member
Thanks Roachcoach, Unfortunately I am in an umbrella role. I was hired as a DBA and am receiving Developer tasks.
Re: Instance duplication, Bi-directional implementation [message #671674 is a reply to message #671673] Wed, 12 September 2018 11:40 Go to previous messageGo to next message
BlackSwan
Messages: 26139
Registered: January 2009
Location: SoCal
Senior Member
> What concerns regarding the network should I be aware of?
If the network link between the two sites breaks, what should occur with regard to DML against each of the 2 databases?
Re: Instance duplication, Bi-directional implementation [message #671676 is a reply to message #671673] Wed, 12 September 2018 13:03 Go to previous messageGo to next message
joy_division
Messages: 4887
Registered: February 2005
Location: East Coast USA
Senior Member
CRDBA wrote on Wed, 12 September 2018 11:49
Thanks Roachcoach, Unfortunately I am in an umbrella role. I was hired as a DBA and am receiving Developer tasks.
Ouch! I am not going to pile on. That is NOT a task I would want to be burdened with. Good luck. It's kind of why I left my last job. They wanted me to be a DBA, developer, DR coordinator and find any issues whenever they come up with the network, firewall and even mail server. I left.
Re: Instance duplication, Bi-directional implementation [message #671677 is a reply to message #671673] Wed, 12 September 2018 13:05 Go to previous messageGo to next message
Roachcoach
Messages: 1568
Registered: May 2010
Location: UK
Senior Member
CRDBA wrote on Wed, 12 September 2018 16:49
Thanks Roachcoach, Unfortunately I am in an umbrella role. I was hired as a DBA and am receiving Developer tasks.
Yikes. Well here are some questions for the stakeholders over and above the one posed by BlackSwan:

What happens if two sessions update the same row at the same time in different databases?
What happens if two sessions insert the same primary key value at the same time in different databases?
What happens if an update has not yet been replicated but a session in the destination database reads the row?
What happens to the replication technology if the system crashes?
What happens if a session tries to update a row which has been deleted in the other database?
What happens to the replication if a row lock is held in the destination database?
What happens to one database if the other is flashed back, partially recovered or otherwise experiences data loss?
What happens when an application tries to select a row which has not yet been replicated?
In fact, what happens when the replication lags? Not "if", when.
What happens to DB1 if DB2 is slow/unresponsive?
How are end users assured the information the application is feeding them is accurate?


The bottom line here is the oracle databases will be ACID in of themselves, but they will NOT be aware of one another and care not a jot for the applications idea of ACID.


I can't stress enough the complexity here, hopefully those questions will give the stakeholders pause. Of course I half anticipate them to respond with "we don't know, you're the techie, you tell us", in that event I would recommend politely pointing out these are business rules and that requires the business analysts/designers to sign off on and as the "techie" you are not those people. If that doesn't fly...there is always whiskey.

[Updated on: Wed, 12 September 2018 13:06]

Report message to a moderator

Re: Instance duplication, Bi-directional implementation [message #671678 is a reply to message #671677] Wed, 12 September 2018 13:15 Go to previous messageGo to next message
John Watson
Messages: 7557
Registered: January 2010
Location: Global Village
Senior Member
These are all problems that have been solved by Advanced Replication and Streams. Both products have astonishing conflict resolution techniques (@CRDBA, note the term "conflict resolution", it is a vital concept) as, I am told, do the later releases of GoldenGate. If this replication is likely to be required for only a few years, I would use Streams. It is free and still supported in release 18, which has another four years of premier support. If the need is likely to be indefinite and if release 18 is not good enough then you probably have to look at licensing GoldenGate.

btw, this is one of enjoyable parts of DBA work: you are expected to fix everything. The loo is blocked? Call the DBA...
Re: Instance duplication, Bi-directional implementation [message #671680 is a reply to message #671678] Wed, 12 September 2018 13:32 Go to previous messageGo to next message
Roachcoach
Messages: 1568
Registered: May 2010
Location: UK
Senior Member
Goldengate is our bad idea of choice too. It does all these things but it can only do it with all the planning and forethought I mentioned. Often what happens is someone sees the active/active buzzword, decrees it be so with little heed to the above. The questions posed are not meant to be unsolvable, but to provoke thought about the complexity, supportability and the future of the platform Smile
Re: Instance duplication, Bi-directional implementation [message #671687 is a reply to message #671673] Wed, 12 September 2018 18:57 Go to previous message
BlackSwan
Messages: 26139
Registered: January 2009
Location: SoCal
Senior Member
CRDBA wrote on Wed, 12 September 2018 08:49
Thanks Roachcoach, Unfortunately I am in an umbrella role. I was hired as a DBA and am receiving Developer tasks.

Remember that nothing is impossible for the person who does NOT have to actually make it work.
Previous Topic: When does an index require to be rebuilt
Next Topic: Oracle DML/Redo Data
Goto Forum:
  


Current Time: Sun Sep 23 03:11:22 CDT 2018