Wednesday, October 1, 2014

Replication from Oracle to MariaDB the simple way - Part 4

Now it's time to get serious about replicating to MariaDB from Oracle, and we are real close now, right? What I needed was a means of keeping track of what happens in a transaction, such as a LOG table of some kind, and then an idea of applying this log to MariaDB when there is a COMMIT in Oracle. And thing is, these two don't have to be related. So I can have a table which I write to and also have a Materialized View that is refreshed on COMMIT on, and I need a log table or something. And when the Materialized View is refreshed, as there is a COMMIT, then the log can be applied. From a schematic point-of-view, it looks something like this:
This looks more complex than it is, actually, all that is needed is some smart PL/SQL and this will work. I have not done much of any kind of testing, except checking that the basics work, but the PL/SQL needed I have done for you, and the order table triggers and what have you not is also created for you by a shell script that can do this for any table.

As for the DUMMY table that I have to use to get a trigger on COMMIT, this doesn't have to have that many rows, I actually just INSERT into it once per transaction, and then I INSERT the transaction id, which I get from Oracle. This table will have some junk in it after a while, all the transactions that were started and COMMITted will have an entry here. But in my code for this, I have included a simple job that purges this table from inactive transactions.

Best of all is that this works even with Oracle Express, so no need to pay for "Advanced Replication", not that I consider it really advanced or anything. I'd really like to know what you think about these ideas? Would it work? I know it's not perfect, far from it, for for the intent of having a MariaDB table reasonable well syncronized with an Oracle, this should work. Or? The solution is on one hand simple and lightweight, but I have given up on the number of features and possibly also the design affects performance a bit.But it should be good enough for many uses I think?

Let me hear what you think, I'm just about to release this puppy!

/Karlsson

7 comments:

Jobin said...

Hi Karlsson,
I was following your posts from 1 to 3. the ideas were well explained. But on this 4th one, I see very little explanation.

It will be nice if you can explain the overall setup with exact commands as you done for chapter 3.

background removal service said...

background removal service is a basic function as the starting line and the other end of the line and the second attachment point is located at any other suitable place Clipping path

Weight loss for men said...

best doctors in las vegas Do it! as saying. If your work is done, use your legs and walk with a trip to the house. Or in the morning and let rise for 5-10 minutes, and a short walk or use your bike weight loss for men I'm sure you will come back even better ideas than that.

Unknown said...

best doctor las vegas have employees and help you prepare for a complete and accurate list of the questions you want to talk to the best doctor green valley

Paulo said...

They can even help with different perspectives to consider while picking which credit is the top alternative for you in selecting your remortgage. One amazingly easy route for discovering some awesome data and determine the status of what your bank is letting you know is by looking at these home loans through a site. cash advance san-diego

Paulo said...

Depend on Yourself is brimming with tributes of individuals who have used the framework. What I will profile are a portion of the abnormal state, long haul qualities of this framework. auto title loans

Chiropractor Arlington Virginia said...

Arlington is one of the most famous places in Canada to find some of the best chiropractors in Clarendon in the country.chiropractor Arlington Virginia