Manhandling GoldenGate, part 1

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.

Design

ggThe 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.

Setup

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 11.2.0.4 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.

Blog developments

Things are slowly getting back to normal after the sudden passing a few weeks ago. Sure, it was “only” a cat, but as me and my wife don’t have any kids (and have no intention of getting any either), the cats are our kids. Anyhow, I’ve updated the blog a bit to make it more useful to myself. I’m using Feedly on my iPad for RSS aggregation, and I had the bright idea the other day to do the same on the blog as I don’t always carry my iPad around. Said and done, and there is a new link at the top for the aggregation page. I’m always on the lookout for new blogs so if you have any suggestions, shoot me an e-mail or hit me up in Twitter.

I’m also in the process of revisiting and rewriting my best practice post for SQL Server 2008/2012/2014. Since this is always evolving I’ve decided to create a page specifically for it, and it will be appearing as soon as I’ve cleaned up and updated the code. Stay tuned!

A eulogy in a technical blog

I’ve been absent from the blog for a while. This has several reasons, ranging from work, to lots of things to do outside work and what I’d like to take a moment to talk about today – the death of one of my cats.

I got Salsa, as she was called, back in May of 2001 when she was about 8-9 weeks old. She and her sister Tesla (along with a couple of other kittens) were born on March 15th, 2001. Straight from the get-go it was obvious that they were very different from each other – most likely due to different fathers. Tesla is rather calm and thoughtful, and Salsa was everything you’d expect from a calico cat – all paws, more speed than control and an utter lack of consequence analysis. After less than a year me and my then-girlfriend separated and I received sole custody of two small balls of fur.
This was the way of things until I met my wife 10 years ago.

Fast forward to May 2013 where we had a very bad week. Tesla broke a tooth and required surgery, Amys, my wife’s cat of 14 years passed away, and Salsa was diagnosed with hyperthyroidism – a chronic, wide-ranging illness that would require medication morning and evening for the rest of her life. A year later Tesla was also diagnosed with this illness, something not unexpeced as they were siblings.

The medication worked out fine despite some tricky periods where we had to adjust the dosage. She had a great life, lots of love, lots of toys. And food. She loved food. So much we had a blimp for a while (this was somewhat due to the medication, but mostly due to her love for all things edible). We weathered that as well, and have had the pleasure to share our lives with a generally healthy and always happy pair of cats. Until yesterday.

I had paramedical training back in the late stone age, and looking at her beside me in the morning I realized that Salsa showed signs of somewhat labored breathing. After a short  phone discussion with the vet we drove the 10 minutes to the local clinic for an emergency chest X-ray. The news were not good. She had a rather large amount of fluid in her lungs and around her heart, pointing to congestive heart failure. This is not that uncommon in older cats and especially not in an older cat with hyperthyroidism (and the resulting hypertension, despite medication). She received some diuretics and we were told to come back a few hours later for a follow-up X-ray to see if the fluid would be cleared.
After observing her for a few hours and noting no change in her breathing or level of activity, I decieded to call my wife and ask her to meet us at the clinic. From Salsa’s clinical presentation and the knowledge that none of the reasons behind the heart failure would be in any way curable (and most likely not treatable to any larger degree) we decided then and there to put her to sleep and end her suffering.

So there we have it – one of my best friends of 15 years is no more.

I am very, very thankful for the 15 years we had together – and all the ups and downs associated with any life. The picture below was taken a few minutes before we went to the vet for the final time. She leaves a cat-shaped hole in our hearts and she will be sorely missed.

Sleep well, my beloved calico cat. Say hi to Amys for us.

salsa