I’ve been hard at work setting up a GoldenGate replication system between two sites to serve as a HA solution for a customer that is … unwilling to pay the rather high licensing fees to use Oracle Enterprise Edition and Data Guard. Instead, we’ve opted to go with Standard Edition One 11g on two nodes and Oracle GoldenGate 12c as the way to synchronize data from site A to site B.
Before going into the setup side of things, there are a few things to consider when using this technology in this way. The first item to think about is the fact that this is in fact a logical, transactional replication – not a binary log transfer (like physical Data Guard). That means that the secondary on site B is open for business – both read and write. That can be a very good thing and that can be a very bad thing.
The second item to keep in mind is the list of limitations. With the new “integrated extract” that appeared in 12c the list keeps shrinking, but there are still some things that simply cannot be transferred using GoldenGate.
Third, GoldenGate is not too keen on instantiating the secondary with open transactions on the primary. Yes, you read that correctly – it might bite you when you start the replication. More about this in part 2.
Fourth, there is no concept of “primary” or “secondary”, so that has to be handled outside of Oracle. This is by design an effect of the first item and not a limitation of the software. I’m discussing this more in depth in part 2 as well.
The design is fairly simple in itself – two databases that have no idea whatsoever that they have any kind of relationship, an extract process on the primary that is responsible for, well, extracting the data that goes into the transaction log on the primary, a pump process on the primary responsible for tossing the result from the extract over to the secondary and finally a replication process on the secondary that applies the transactions as they come in. The extract, pump and replicat processes are all part of GoldenGate.
There are quite a few tutorials and such on the internet that describes how to setup a simple unidirectional GoldenGate replication pair, so I won’t go into any details regarding the basic settings. With that said, my situation was a … bit different. I’ve got well over 300 schemas to keep track of, as well as both integrated capture (extract) and replication, and about them you can find a lot less information.
The integrated approach as opposed to the classic GoldenGate processes are Oracles recommended way of doing things these days, provided that you’re running on a new enough database (11.2+, preferrably 18.104.22.168 or 12c.) The devil is in the details as the actual setup is very straight forward – basically just say
TRANLOGOPTIONS INTEGRATED in the extract process parameter file as well as specify
INTEGRATED when creating the exract process definition (add extract ext1, integrated tranlog, begin now). There is one more thing needed – registering the extract process with the database. This is done in GGSCI like this:
register extract ext1 database
There is actually some output from this: a SCN. This is the first SCN that the extract start extracting from and can be used for instantiating the secondary database, either via data pump or RMAN.
Another slightly surprising detail is the way that the 64-bit GoldenGate behaves from 11.2 and onwards – it defaults the
CACHESIZE parameter to 64GB. In short, this is very likely to result in a “cachesize too small” error, followed by abending (stopping) of the extract process. Oracle ACE Gavin Soorma has written a post of it here.
Other settings to note are:
ROLLOVER in the extract parameter file that controls when to switch trail files (lest they become ginormous),
PURGEOLDEXTRACTS in the manager parameter file to handle obsolete trail files,
LOGALLSUPPCOLS that together with the
TRANDATA settings help handle tables without keys and a nice new feature in the shape of
DBOPTIONS SUPPRESSTRIGGERS that, well, supresses triggers on the secondary.
All right, the primary generally won’t provide any surprises. Sure, the parameter files for both the extract and the pump are by now rather large as every schema that is to participate in the replication (in this case ALL of them) needs to have both DDL mappings and table definitions, but otherwise there is little to deviate from any tutorial out there.