Oracle GoldenGate Active-Active Part 1
- by Nick_W
My name is Nick Wagner, and I'm a recent addition to the Oracle Maximum Availability Architecture (MAA) product management team. I've spent the last 15+ years working on database replication products, and I've spent the last 10 years working on the Oracle GoldenGate product. So most of my posting will probably be focused on OGG.
One question that comes up all the time is around active-active replication with Oracle GoldenGate. How do I know if my application is a good fit for active-active replication with GoldenGate? To answer that, it really comes down to how you plan on handling conflict resolution.
I will delve into topology and deployment in a later blog, but here is a simple architecture:
The two most common resolution routines are host based resolution and timestamp based resolution.
Host based resolution is used less often, but works with the fewest application changes. Think of it like this: any transactions from SystemA always take precedence over any transactions from SystemB. If there is a conflict on SystemB, then the record from SystemA will overwrite it. If there is a conflict on SystemA, then it will be ignored. It is quite a bit less restrictive, and in most cases, as long as all the tables have primary keys, host based resolution will work just fine.
Timestamp based resolution, on the other hand, is a little trickier. In this case, you can decide which record is overwritten based on timestamps. For example, does the older record get overwritten with the newer record? Or vice-versa? This method not only requires primary keys on every table, but it also requires every table to have a timestamp/date column that is updated each time a record is inserted or updated on the table. Most homegrown applications can always be customized to include these requirements, but it's a little more difficult with 3rd party applications, and might even be impossible for large ERP type applications.
If your database has these features - whether it’s primary keys for host based resolution, or primary keys and timestamp columns for timestamp based resolution - then your application could be a great candidate for active-active replication. But table structure is not the only requirement. The other consideration applies when there is a conflict; i.e., do I need to perform any notification or track down the user that had their data overwritten? In most cases, I don't think it's necessary, but if it is required, OGG can always create an exceptions table that contains all of the overwritten transactions so that people can be notified. It's a bit of extra work to implement this type of option, but if the business requires it, then it can be done. Unless someone is constantly monitoring this exception table or has an automated process in dealing with exceptions, there will be a delay in getting a response back to the end user.
Ideally, when setting up active-active resolution we can include some simple procedural steps or configuration options that can reduce, or in some cases eliminate the potential for conflicts. This makes the whole implementation that much easier and foolproof. And I'll cover these in my next blog.
