Monday, September 29, 2014

Replication from Oracle to MariaDB the simple way - Part 3

In this third installment in this series, I'll explain why the smart solution I described in the previous post actually wasn't that good, and then I go on to explain how to fix it, and why that fix wasn't such a smart thing after all. So, this was the design we ended with last time:
We have Oracle replicating to a Materialized View, this to ensure that we can run triggers when the is a commit, and then triggers on this Materialized View updates MariaDB by sending a UDP message to a server that in turn is connected to MariaDB.

The issue with the above thingy was that a Materialized View by default is refreshed in it's entirety when there is a refresh, so if the table has 10.000 rows and 1 is inserted, then there will be 20.001 messages sent to MariaDB (10.000 rows deleted, 10.001 inserted). Not fun. And it seems that Materialized Views in Oracle aren't so smart, but I was sure they were this dumbed down, if they were, noone would be using them. So I rush for the Oracle documentation, yiihaa!

The default way of updating a Materialized View is not that fast, but there is a supposedly fast, alternative, method, appropriately named FAST (that the default method isn't called something like AWFULLY CRAZY SLOW is beyond me). So the materialized view using FAST REFRESH for the orders table should really be created like this:
CREATE MATERIALIZED VIEW orders_mv
  REFRESH FAST ON COMMIT
  AS SELECT *
  FROM orders;

But this gives an error from Oracle:
ORA-23413: table "SYSTEM"."ORDERS" does not have a materialized view log
Well the, let's create a MATERIALIZED VIEW LOG for table ORDERS then, that's no big deal:
CREATE MATERIALIZED VIEW LOG ON t1_mv;
But again I get an error, and this time indicating that old Larry has run out of gas in his MIG-21 and need my money to fill it up again, so he can fly off to his yacht:
ORA-00439: feature not enabled: Advanced replication

Grrnn (this is a sound I make when I get a bit upset)! Yes, if you want Materialized Views to work properly, they way the were designed, you need to part with some $$$, and as the cheap person I am, I run Oracle Express instead of SE or EE editions, as I rather spend my hard earned money on expensive beer than on Larrys stupid MIG-21. So, as they say, "Close, but no cigar".

But I'm not one to give up easily, as you probably know. And fact is, I don't need the whole Materialized View thing, all I want is a TRIGGER to execute on COMMIT. Hmm this requires a huge box of prescription drugs to fix, and I am already on the case. Sorry Larry, but you'll have to park your MIG-21 and have someone else buy you some gas.

More details on how I tricked Larry in the next part of this series on replication from Oracle to MariaDB.

/Karlsson

4 comments:

Paulo said...

One of the more significant segments of what a decent business advance dealer does, is acquaint the borrower with moneylenders they could never,have the capacity to discover all alone. www.aaa1autotitleloans.com/chicago

Paulo said...

Despite the fact that borrowers still have three fundamental hotspots for financing, including routine bank credits, CMBS moneylenders and SBA programs, borrowers are urge to investigate the SBA programs first because of their unwavering quality of shutting and solid advantages. www.usacheckcashingstore.com/chula-vista

Paulo said...

Advance applications that accompany some kind of security are probably going to be endorsed, and of the different types of security, the most significant is home value. Truth be told, the length of the value on the property is sufficiently huge, the advance total secured can be adequately anything and a $10,000 advance with awful acknowledge is as liable to be endorsed as a $100,000 advance. www.usacheckcashingstore.com/san-diego

Paulo said...

When you go online for your advances, your portfolio is naturally looked after on the web. In this way, the bother of following your advance reimbursement is significantly diminished, sparing quite a bit of your important time. www.usacheckcashingstore.com/corona