Replication from Oracle to MariaDB - Part 3

This is the third part of a series of blogs on how to do Change Data Capture from Oracle in an effort to be able to replicate that data to MariaDB. Part 1 dealt with some basics on the Oracle side of things regarding the Oracle redo log which we use as a source for out CDC efforts and Part 2 was about how to get this to work. We left part2 with an example of how to Replicate INSERTs from Oracle to MariaDB, but that is hardly good enough, we also have to deal with UPDATE and DELETE to make it at least somewhat complete, and this is what we will have a look at in this post.

And I know what you are asking your selves now, you spent all that time explaining the Oracle ROWID and then you never used that. That was an awful waste of time. And no, it wasn't a waste, we will put it to good use now when we look at UPDATEs.

For an INSERT, things are reasonably simple, right, we just add a row with some columns to the table. As for an UPDATE or a DELETE though, we need a means to identify the row to be updated. To reiterate one thing, the Oracle redo log is not like the MariaDB binlog here, and here I am assuming that the binlog format is STATEMENT. Let's look at an example, and for that let's insert some data into out EMP table (see the previous port for details).

SQL> INSERT INTO emp VALUES(2, 'John', 'Developer', 12000, 20);
SQL> INSERT INTO emp VALUES(3, 'Georgina', 'Design', 11000, 20);
SQL> INSERT INTO emp VALUES(4, 'Anne', 'Assistant', 8000, 30);
SQL> INSERT INTO emp VALUES(5, 'Marge', 'HR Mgr', 14000, 30);
SQL> COMMIT;

With that in place, let's now ensure that this is also replicated to MariaDB by running the script from the last post:

$ sqlplus -S / as sysdba @cdcemp.sql | mysql -u anders -panders anders

And to ensure that we got this right, let's check this out in MariaDB now:

$ mysql -u anders -panders anders
MariaDB [anders]> SELECT * FROM emp;
+-------+----------+-----------+----------+--------+
| empno | ename    | job       | sal      | deptno |
+-------+----------+-----------+----------+--------+
|     1 | Anders   | Sales     | 10000.00 |     10 |
|     2 | John     | Developer | 12000.00 |     20 |
|     3 | Georgina | Design    | 11000.00 |     20 |
|     4 | Anne     | Assistant |  8000.00 |     30 |
|     5 | Marge    | HR Mgr    | 14000.00 |     30 |
+-------+----------+-----------+----------+--------+
5 rows in set (0.00 sec)

OK, seems like we got it right so far. Then let's try an UPDATE and raise the salary for the two developers in Department 20:

$ sqlplus anders/anders
SQL> UPDATE emp SET sal = sal + 100 WHERE deptno = 20;

Now, what does this result in? If this was MariaDB with STATEMENT binlog format, the binlog would have something like this in it:

# at 9535
#160919 13:00:44 server id 112  end_log_pos 9652 CRC32 0x987f9dea       Query   thread_id=101   exec_time=0     error_code=0
SET TIMESTAMP=1474282844/*!*/;
UPDATE emp SET sal = sal + 100 WHERE deptno = 20
/*!*/;

We've seen this before, no surprises there. Now, let's try this from SQL*Plus and see what the SQL_REDO column in V$LOGMNR_CONTENTS looks like.

update "ANDERS"."EMP" set "SAL" = '12100' where "SAL" = '12000' and ROWID = 'AAAE7RAAEAAAAK9AAB';
update "ANDERS"."EMP" set "SAL" = '11100' where "SAL" = '11000' and ROWID = 'AAAE7RAAEAAAAK+AAA';

Whoa, that doesn't look like our UPDATE at all, what's going on? Well, I already told you that the Oracle redo log doesn't work like the binlog. In this case, we get an SQL statement from it, but it is not the same SQL statement that we asked Oracle to execute for us. No, this SQL is reconstructed from the redo log record, which logs updates row by row, and uses the ROWID to identify the row that was changed. We have already discussed that the ROWID is unique in a table and to an extent even acroess tables. The issue now is we don't have a ROWID in MariaDB. But the ROWID in the redo log is available in the ROW_ID column in the V$LOGMNR_CONTENTS table. And if we assume that all tables that we replicate has to have a PRIMARY KEY, then we could use the ROWID to look up that PRIMARY KEY and use that for UPDATE operations in MariaDB, right? Something like this in our script:

SELECT empno INTO v_empno FROM anders.emp WHERE rowid = v_row_id;

That works but there is one issue with it. We get out data from the redo log, which is historic, so to speak, but we look at the PRIMARY KEY using the ROW ID based on how data looks right now, which means that if you are in the habit of updating your PRIMARY KEYs (don't do this, by the way, this is a bad habit even if we even if we exclude the specific use case we look at here), then this isn't going to fly. Sorry then, I will not fix that for you, if you update a row and then update it's primary key, the first update will fail.

Then let's look at DELETEs. Can we use the same method as used for UPDATEs, it's tempting, I know, but it is not going to work at all. Think about it, we just determined that we get the ROWID for a row that is historic, but in the case of a DELETE there is no "current row" as we DELETEd it? Right? Whoa, how do we solve that? Are we stuck now, out in the desert, the hot Oracle sun is shining on our unprotected pale skin and it's getting hotter and hotter. Our old Studebaker has broken down by the side of the abandoned dirt road, and you stare into the unforgiving sun and you know this is it?

Nope. This can be fixed, luckily! The Oracle redo log contains redo data, that is what we have been using so far. The thing is that it also contains undo data! Hey! And then we think about this amazing concept a bit and consider what is necessary to undo a delete? Frankly, the value of ALL columns in the deleted row, right? Our friendly DBMS_LOGMNR.MINE_VALUE function comes to the rescue, but this time not to operate on the redo value but on the undo value!

Armed with all this, we can now revisit out cdcemp.sql script and add stuff to handle UPDATE and DELETE operations also:

SET serveroutput ON
SET linesize 4000
SET feedback off
DECLARE
   v_scn NUMBER;
   v_scnstart NUMBER;
   v_scnend NUMBER;
   v_redo_value NUMBER;
   v_undo_value NUMBER;
   v_operation VARCHAR2(32);
   v_row_id VARCHAR2(18);
   v_sqlstmt VARCHAR2(4000);
   v_firstcol BOOLEAN;
   v_empno NUMBER;
   CURSOR cur1 IS
     SELECT scn, redo_value, undo_value, operation, row_id
       FROM v$logmnr_contents
       WHERE seg_owner = 'ANDERS' AND table_name = 'EMP'
         AND operation IN('INSERT', 'UPDATE', 'DELETE');
BEGIN
-- Start LogMiner.
   SELECT NVL(MIN(l.scn) + 1, MIN(e.ora_rowscn)) INTO v_scnstart
     FROM anders.emp e, scnlog l
     WHERE l.table_name = 'EMP';
   SELECT MAX(e.ora_rowscn) INTO v_scnend
     FROM anders.emp e;
   DBMS_OUTPUT.PUT_LINE('-- SCN Range: ' || v_scnstart || ' - ' || v_scnend);
   DBMS_LOGMNR.START_LOGMNR(STARTSCN => v_scnstart,
     ENDSCN => v_scnend,
     OPTIONS => DBMS_LOGMNR.COMMITTED_DATA_ONLY + DBMS_LOGMNR.CONTINUOUS_MINE +
     DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
-- Dummy update to insert a more recent SCN to ensure that we don't have to wait
-- for the next update in the following select.
   UPDATE scnlog SET table_name = table_name WHERE table_name = 'EMP';
   COMMIT;

-- Open cursor to get data from LogMiner.
   OPEN cur1;

-- Loop for all the rows in the redo log since the last time we ran this.
   LOOP
      FETCH cur1 INTO v_scn, v_redo_value, v_undo_value, v_operation, v_row_id;
      EXIT WHEN cur1%NOTFOUND;
      IF v_operation = 'INSERT' THEN
         v_sqlstmt := 'INSERT INTO emp(EMPNO, ENAME, JOB, SAL, DEPTNO) VALUES(';
         v_sqlstmt := v_sqlstmt || DBMS_LOGMNR.MINE_VALUE(v_redo_value, 'ANDERS.EMP.EMPNO') || ', ';
         v_sqlstmt := v_sqlstmt || '''' || DBMS_LOGMNR.MINE_VALUE(v_redo_value, 'ANDERS.EMP.ENAME') || ''', ';
         v_sqlstmt := v_sqlstmt || '''' || DBMS_LOGMNR.MINE_VALUE(v_redo_value, 'ANDERS.EMP.JOB') || ''', ';
         v_sqlstmt := v_sqlstmt || DBMS_LOGMNR.MINE_VALUE(v_redo_value, 'ANDERS.EMP.SAL') || ', ';
         v_sqlstmt := v_sqlstmt || DBMS_LOGMNR.MINE_VALUE(v_redo_value, 'ANDERS.EMP.DEPTNO') || ')';
      ELSIF v_operation = 'UPDATE' THEN
         v_sqlstmt := 'UPDATE emp SET ';
         v_firstcol := TRUE;

-- Build UPDATE statement using only column in redo log record.
         IF DBMS_LOGMNR.COLUMN_PRESENT(v_redo_value, 'ANDERS.EMP.EMPNO') = 1 THEN
            v_sqlstmt := v_sqlstmt || 'EMPNO = ' || DBMS_LOGMNR.MINE_VALUE(v_redo_value, 'ANDERS.EMP.EMPNO');
            v_firstcol := FALSE;
         END IF;
         IF DBMS_LOGMNR.COLUMN_PRESENT(v_redo_value, 'ANDERS.EMP.ENAME') = 1 THEN
            v_sqlstmt := v_sqlstmt || CASE WHEN NOT v_firstcol THEN ', ' ELSE '' END;
            v_sqlstmt := v_sqlstmt || 'ENAME = ''' || DBMS_LOGMNR.MINE_VALUE(v_redo_value, 'ANDERS.EMP.ENAME')
              || '''';
            v_firstcol := FALSE;
         END IF;
         IF DBMS_LOGMNR.COLUMN_PRESENT(v_redo_value, 'ANDERS.EMP.JOB') = 1 THEN
            v_sqlstmt := v_sqlstmt || CASE WHEN NOT v_firstcol THEN ', ' ELSE '' END;
            v_sqlstmt := v_sqlstmt || 'JOB = ''' || DBMS_LOGMNR.MINE_VALUE(v_redo_value, 'ANDERS.EMP.JOB')
              || '''';
            v_firstcol := FALSE;
         END IF;
         IF DBMS_LOGMNR.COLUMN_PRESENT(v_redo_value, 'ANDERS.EMP.SAL') = 1 THEN
            v_sqlstmt := v_sqlstmt || CASE WHEN NOT v_firstcol THEN ', ' ELSE '' END;
            v_sqlstmt := v_sqlstmt || 'SAL = ' || DBMS_LOGMNR.MINE_VALUE(v_redo_value, 'ANDERS.EMP.SAL');
            v_firstcol := FALSE;
         END IF;
         IF DBMS_LOGMNR.COLUMN_PRESENT(v_redo_value, 'ANDERS.EMP.DEPTNO') = 1 THEN
            v_sqlstmt := v_sqlstmt || CASE WHEN NOT v_firstcol THEN ', ' ELSE '' END;
            v_sqlstmt := v_sqlstmt || 'DEPTNO = ' || DBMS_LOGMNR.MINE_VALUE(v_redo_value, 'ANDERS.EMP.DEPTNO');
            v_firstcol := FALSE;
         END IF;

-- Get empno from emp using rowid.
         SELECT MAX(empno) INTO v_empno FROM anders.emp WHERE rowid = v_row_id;
         IF v_empno IS NULL THEN
           CONTINUE;
         END IF;
         v_sqlstmt := v_sqlstmt || ' WHERE empno = ' || v_empno;
      ELSIF v_operation = 'DELETE' THEN
-- Get the empno from the undo record.
         v_empno := DBMS_LOGMNR.MINE_VALUE(v_undo_value, 'ANDERS.EMP.EMPNO');
         IF v_empno IS NULL THEN
           CONTINUE;
         END IF;
         v_sqlstmt := 'DELETE FROM emp WHERE empno = ' || v_empno;
      END IF;

      DBMS_OUTPUT.PUT_LINE('-- SCN = ' || v_scn);
      DBMS_OUTPUT.PUT_LINE(v_sqlstmt || ';');
   END LOOP;
   IF v_scn IS NOT NULL THEN
     UPDATE scnlog SET scn = v_scn WHERE table_name = 'EMP';
     COMMIT;
   END IF;
   CLOSE cur1;
END;
/
EXIT

Let's try this script, and see if the UPDATE to the sal column that I did at the beginning of this post are properly replicated to MariaDB:

$ sqlplus -S / as sysdba @cdcemp.sql | mysql -u anders -panders anders

And then we check what we have in MariaDB:

$ mysql -u anders -panders anders
MariaDB [anders]> SELECT * FROM emp;
+-------+----------+-----------+----------+--------+
| empno | ename    | job       | sal      | deptno |
+-------+----------+-----------+----------+--------+
|     1 | Anders   | Sales     | 10000.00 |     10 |
|     2 | John     | Developer | 12100.00 |     20 |
|     3 | Georgina | Design    | 11100.00 |     20 |
|     4 | Anne     | Assistant |  8000.00 |     30 |
|     5 | Marge    | HR Mgr    | 14000.00 |     30 |
+-------+----------+-----------+----------+--------+
5 rows in set (0.00 sec)

And then see if we can also handle a DELETE. In SQL*Plus we run this:

SQL> DELETE FROM emp WHERE ename = 'John';
SQL> COMMIT;

The we run out script:

$ sqlplus -S / as sysdba @cdcemp.sql | mysql -u anders -panders anders

And then we check what we have in MariaDB:

$ mysql -u anders -panders anders
MariaDB [anders]> SELECT * FROM emp;
+-------+----------+-----------+----------+--------+
| empno | ename    | job       | sal      | deptno |
+-------+----------+-----------+----------+--------+
|     1 | Anders   | Sales     | 10000.00 |     10 |
|     3 | Georgina | Design    | 11100.00 |     20 |
|     4 | Anne     | Assistant |  8000.00 |     30 |
|     5 | Marge    | HR Mgr    | 14000.00 |     30 |
+-------+----------+-----------+----------+--------+
4 rows in set (0.00 sec)

Bazinga, it worked! Are we done then? I'm afraid not, we still have transactions to take care of, but that has to wait a day or two.

Happy SQL'ing
/Karlsson

Replication from Oracle to MariaDB - Part 2

In part 1 on this series of blogs on how to replcate from Oracle to MariaDB, we looked at how to get data out from Oracle, and in an effort to look more modern than I really am, let's say that what we were attempting was CDC (Change Data Capture). Hey, I feel like I'm 18 again! Modern, cool! Maybe I should start to wear a baseball cap backwards and have my jeans no higher than my knees. Or again, maybe not. But CDC it is.

When I left you in the previous post we have just started to get some data. reluctantly, out of Oracle. But the SQL statement we got from the LogMiner was hardly useful. But fact is, we can fix that. Before we do that we need to look at a few other functions in LogMiner. The two functions we want to loot at are called DBMS_LOGMNR.COLUMN_PRESENT and DBMS_LOGMNR.MINE_VALUE. These two functions are simple enough, they both take two arguments, first a reference to a redo log record, which is the value of the column redo_value in the V$LOGMNR_CONTENTS table, and the other argument is the fully qualified name of the table in question. The table name of the redo log record is, surprisingly, in the table_name column. The reason we need two functions for this is that the second of these functions can return NULL, both when the column is question doesn't exist and well as when the value is NULL.

I will for the rest of this post assume that there exists an Oracle user called anders with the password anders and that the same thing exists in MariaDB, where there is also a database called anders (you already guess that, right?) where the user anders has full access.

So with the table_name of the value of the involved columns, we are ready to go. Sure. let's look at an insert.  First we need a table to test this on, and let's use the classic Oracle EMP sample table for this, but I have simplified this a bit to make my example easier to grasp without getting into too many details. So this is what it looks on the Oracle side of things, and let's call this script cretabora.sql:

CREATE TABLE anders.emp(
  empno NUMBER NOT NULL PRIMARY KEY,
  ename VARCHAR2(10) NOT NULL,
  job VARCHAR2(9) NOT NULL,
  sal NUMBER(7,2) NOT NULL,
  deptno NUMBER NOT NULL) TABLESPACE users;

What we also need is some way to keep track of processing, in this case we want to keep track of the SCN that was last used, so we know where to start when we get the next chunk of CDCs (He, I used it again. Boy am I young and hot today). And add this to the script above:

CREATE TABLE scnlog(
  table_name VARCHAR2(30) NOT NULL PRIMARY KEY,
  scn NUMBER) TABLESPACE users;
INSERT INTO anders.scnlog
  SELECT 'EMP', current_scn FROM v$database;

I also allocated space for the EMP table and set the SCN to the current database SCN. As I am using the V$DATABASE virtual table, I have to run the above as a privileged user.

$ sqlplus / as sysdba @cretabora.sql

OK, now I have some tables to work with. Before to go on let's also create the same objects in a MariaDB database. The minimum thing we need is something is, let's call this script cretabmaria.sql:

CREATE TABLE emp(
  empno INTEGER NOT NULL PRIMARY KEY,
  ename VARCHAR(10) NOT NULL,
  job VARCHAR(9) NOT NULL,
  sal DECIMAL(7,2) NOT NULL,
  deptno INTEGER NOT NULL);

I will not explain this in detail, you get it already I think:

$ mysql -u root
MariaDB [(none)]> CREATE USER 'anders'@'%' IDENTIFIED BY 'anders';
MariaDB [(none)]> CREATE DATABASE anders;
MariaDB [(none)]> GRANT ALL ON anders.* TO 'anders'@'%';
MariaDB [(none)]> exit
$ mysql -u anders -panders anders < cretabmaria.sql

The above really shows that MariaDB is easier to deal with than Oracle, right? But that wasn't what we were looking for here, and we're getting closer now, aren't you excited?

Let's insert a single row of data in the EMP table, just to get started:

$ sqlplus anders/anders
SQL> INSERT INTO emp VALUES(1, 'Anders', 'Sales', 10000, 10);
SQL> COMMIT;

Now we are ready to extract data from the redo log. For this we will run a small script that runs LogMiner, extracts rows from the redo log and converts that to valid MariaDB SQL syntax. Create a script called cdcemp.sql with this content:

SET serveroutput ON
SET linesize 4000
SET feedback off
DECLARE
   v_scn NUMBER;
   v_scnstart NUMBER;
   v_scnend NUMBER;
   v_redo_value NUMBER;
   v_sqlstmt VARCHAR2(4000);
   CURSOR cur1 IS
     SELECT scn, redo_value
       FROM v$logmnr_contents
       WHERE seg_owner = 'ANDERS' AND table_name = 'EMP'
         AND operation = 'INSERT';
BEGIN
-- Start LogMiner.
   SELECT NVL(MIN(l.scn) + 1, MIN(e.ora_rowscn)) INTO v_scnstart
     FROM anders.emp e, scnlog l
     WHERE l.table_name = 'EMP';
   SELECT MAX(e.ora_rowscn) INTO v_scnend
     FROM anders.emp e;
   DBMS_OUTPUT.PUT_LINE('-- SCN Range: ' || v_scnstart || ' - ' || v_scnend);
   DBMS_LOGMNR.START_LOGMNR(STARTSCN => v_scnstart,
     ENDSCN => v_scnend,
     OPTIONS => DBMS_LOGMNR.COMMITTED_DATA_ONLY +
       DBMS_LOGMNR.CONTINUOUS_MINE +
       DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
-- Dummy update to insert a more recent SCN to ensure
-- that we don't have to wait for the next update in the
-- following select.
   UPDATE scnlog SET table_name = table_name WHERE table_name = 'EMP';
   COMMIT;

-- Open cursor to get data from LogMiner.
   OPEN cur1;

-- Loop for all the rows in the redo log since the last time we ran this.
   LOOP
      FETCH cur1 INTO v_scn, v_redo_value;
      EXIT WHEN cur1%NOTFOUND;
      v_sqlstmt := 'INSERT INTO emp(EMPNO, ENAME, JOB, SAL, DEPTNO)'
        || 'VALUES(';
      v_sqlstmt := v_sqlstmt || DBMS_LOGMNR.MINE_VALUE(v_redo_value,
         'ANDERS.EMP.EMPNO') || ', ';
      v_sqlstmt := v_sqlstmt || '''' || DBMS_LOGMNR.MINE_VALUE(v_redo_value,
        'ANDERS.EMP.ENAME') || ''', ';
      v_sqlstmt := v_sqlstmt || '''' || DBMS_LOGMNR.MINE_VALUE(v_redo_value,
        'ANDERS.EMP.JOB') || ''', ';
      v_sqlstmt := v_sqlstmt || DBMS_LOGMNR.MINE_VALUE(v_redo_value,
        'ANDERS.EMP.SAL') || ', ';
      v_sqlstmt := v_sqlstmt || DBMS_LOGMNR.MINE_VALUE(v_redo_value,
        'ANDERS.EMP.DEPTNO') || ')';

      DBMS_OUTPUT.PUT_LINE('-- SCN = ' || v_scn);
      DBMS_OUTPUT.PUT_LINE(v_sqlstmt || ';');
   END LOOP;
   IF v_scn IS NOT NULL THEN
      UPDATE scnlog SET scn = v_scn WHERE table_name = 'EMP';
      COMMIT;
   END IF;
   CLOSE cur1;
END;
/
EXIT

The first three lines in this script are SQL*Plus specific settings. Then we declare some variables and a cursor for the V$LOGMNR_CONTENTS table. Following that we get the starting and ending SCN that we want and use that to start LogMiner and output some information on what we are doing. Then comes a wacko UPDATE. This seems to be necessary to ensure that the redo log is  progressed past the last log record so that we can get the data. Without this the SELECT from the cursor would wait until someone else generated some redo log content. This is a kludge, I know, but it seems to work. Also, Oracle isn't smart enough to skip these kinds of dummy updates.

Following this the cursor is opened and we get the rows from it and build a suitable SQL statement for MariaDB. Finally when the loop is over I update the SCN log table so I know where to start the next run. This script can be done to generate incremental updates from Oracle to be inserted into MariaDB. And with all that, let's now finally run the script, and here I will run it and extract the data to MariaDB:

$ sqlplus -S / as sysdba @cdcemp.sql | mysql -u anders -panders anders

Before we finish, let's check if this worked:

$ mysql -u anders -panders anders
MariaDB [anders]> SELECT * FROM emp;
+-------+--------+-------+----------+--------+
| empno | ename  | job   | sal      | deptno |
+-------+--------+-------+----------+--------+
|     1 | Anders | Sales | 10000.00 |     10 |
+-------+--------+-------+----------+--------+
1 row in set (0.00 sec)

That's it for this time. There are few few more things to look into though, like managing transactions on the MariaDB side for example. And UPDATEs and DELETEs, those require some special care. But we are getting there now, right? So stay tuned for the third post in this series.

Happy SQL'ing
/Karlsson

Replication from Oracle to MariaDB - Part 1

If you have read my blog before, you know that I have shown a couple of ways of getting data from an Oracle database and into something more approachable, such as a text-file or even, surprise, MariaDB (or MySQL for that matter). Not that the Oracle database really likes you doing this and some tweaking is necessary. One way I have shown before to do this is by using triggers, a UDF, UPD-communication and a simple server, this was a series of blogposts starting with this one: Replication from Oracle to MariaDB the simple way. Another means is to exports data from Oracle in some text format, something Oracle really doesn't like you do it, it really likes to keep the data to itself, but yours truly worked around this and now you can use the Oracle dump utility and this has been developed quite a bit and now works really well and for most standard Oracle datatypes.

But replicating properly, without triggers or anything, would be nice to do using only Open Source stuff, or maybe we can do without any special tools at all? One way would be to put a proxy in front of Oracle. Useful, but takes too much time to develop and requires that one figure out the Oracle communication protocol, which is not something I am prepared to do (I am lazy, I know).

So this time around I will try something different, a way to replicate straight out of Oracle. I will eventually show you a practical example of doing this, without too much code, but before we get there I'll explain how it works. The more difficult part here is getting the data replicated properly out from Oracle, so this first installment of a series of blog post will be more on Oracle than MariaDB specifically, but this is all for a good cause (i.e. get stuff done without having to pay) so let's get started.

To begin with, there is no binlog in Oracle (nor is there an update log, if you remember that puppy). What Oracle does have though is a redo log, which is also there in InnoDB / XtraDB by the way, and it serves the same purpose, to be able to recover the database should there be a crash. This log is, because of the purpose it is used for, logging on a much lower level compared to the binlog. Looking at the Oracle redo log, there are log records on any modifying event in the database, including a lot of internal Oracle processing, Oracle does do a lot of stuff in the background and manages a lot more stuff that MariaDB instead leaves to the operating system, like file space management and such things. This is not to say Oracle is better than MariaDB in this respect, nor the other way around, but Oracle was born at a time where you had to support so many different operating systems, so some layers were necessary to create some transparency. Also Oracle was born at a time when having 5Mb of disk space was a lot of disk and when real estate was something that you handled conservatively (which is a giveaway of my age. Yes, I was born before "My Boy Lollipop" was a hit with Mille Small. And contrary to popular belief, it is not Rod Stewart that plays the harmonica solo in that song. And that ends todays "useless knowledge" lesson).

Before we go into looking at the Oracle redo log in detail, there are a couple of things I want to explain first. There is another file in Oracle, a small but important one called the control file. The control file is what keeps track of all the other files and related settings in the database. As for the redo log files, these can be used in two ways. The basic way is what is called NOARCHIVELOG mode, which means that the log files are reused. This is similar to how the InnoDB / XtraDB logfiles are used, and the purpose of this is to provide a means to recover after a crash. Thinking about it, you realize that these should then also be able to be used for, say, point in time recovery, assuming that we saved them, i.e. starting with a physical backup and then applying the saved files. With MariaDB we instead perform point in time recovery using the binlog. To do a point in time recovery with Oracle, and use the redo log for this purpose, we have to save the redo log files, and this is what happens when you run in ARCHIVELOG mode, in which case a separate Oracle process will "archive", which is Oracle lingo for "saving in some other place", the redo log files when they are full and a switch is done to the next logfile. All of this, the current and archived redo log files, are recorded in the Oracle control file.

OK, A lot of Oracle stuff, and I warned you, but this is critical to understanding how the code I present later works. There is one more thing I want to discuss briefly which is the Oracle ROWID. A ROWID in oracle is a unique identifier of a row in a table. The ROWID is actually a physical adress and there are cases when the same ROWID can be used for two different tables, which is when the tables are clustered, but for a single table the ROWID uniquely identifies a row. To use the ROWID of a table, one can use the ROWID pseudocolumn, which can be used in the WHERE clause as well as in the SELECT list.

So, with all those things explained, lets put it all together. The redo log of Oracle contains all the changes to the database, so it really should be possible to use this for the same purpose that we use the binlog in MariaDB, i.e. replication. To do that we first need to be able to read the redo log, and we could either read the redo log file itself, but this is rather complex and I'm not even sure this really is a good idea, but if you insist you can read this document for example. Instead we are going to use an Oracle utility. For the DML that interests us, all we need is the INSERT, UPDATE and DELETE statements that deal with the table we want to replicate and then the COMMIT when the transaction in question ends. For these operations, some of the data we want that is in the redo log are:

• The type of operation
• An identifier of the table (not the actual table name though)
• The changed columns and values
• The ROWID of the row in question
• And the SCN (System Change Number).

And then I have to describe the SCN I guess. The SCN is simple, it's a 48-bit integer value that identified the transaction and that is incrementing for each transaction. You can get the SCN of the last change in a table, which is actually the SCN of the block where the row exists (but this you can change to track the actual ROW. Oracle has sooo many strange options to cater for all the special needs of it's users).

Before I finish this blog post, I want to show some code on how to get data from the redo log. And before you ask, no, there is no oraredolog utility or something like that, this isn't MariaDB, this is Oracle. A utility in Oracle is mostly not a utility per se, but rather a Oracle PL/SQL package, in this case this is LogMiner, or as a package DBMS_LOGMNR. For LogMiner to work we need to run in archivelog mode, so if you have a fresh Oracle installation, log in as a priviledged user:

$ sqlplus / as sysdba

And run the commands

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

Also, to get all the data we want from LogMiner we have to add "supplemental data" to the redo log. What this means exactly I haven't looked in to but trust me, you want it. So from the same SQL*Plus session, run:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

With that in place we are ready to start mining the log, this consists of two steps, first we start logminer itself, and then we select from a table that logminer populates. Let's look at how we can start logminer with an sql script, let's call this script startlog.sql:

column min_scn new_value startscn
column current_scn new_value endscn
SELECT MIN(FIRST_CHANGE#) min_scn FROM v$log;
SELECT current_scn FROM v$database;
EXECUTE DBMS_LOGMNR.START_LOGMNR(STARTSCN => &startscn, -
  ENDSCN => &endscn, -
  OPTIONS => DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
  DBMS_LOGMNR.CONTINUOUS_MINE + -
  DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
SET ARRAYSIZE 1

And then we run this script and as there is no explicit exit in it, you get the SQL*Plus prompt back after running it, which is not how the MariaDB command line works:

$ sqlplus / as sysdba @startlog.sql

And now we can get some log data, let's say we have a table called T1 that we have done some changes to, then we can get the relevant records like this:

SQL> SELECT sql_redo FROM v$logmnr_contents WHERE table_name = 'T1'  AND seg_owner = 'ANDERS' AND operation IN ('INSERT', 'UPDATE', 'DELETE');
SQL_REDO
--------------------------------------------------------------------------------
insert into "ANDERS"."T1"("C1","C2") values ('1','2');
insert into "ANDERS"."T1"("C1","C2") values ('2','2');
update "ANDERS"."T1" set "C2" = '3' where "C2" = '2' and ROWID = 'AAAE5KAAEAAAAFfAAA';
update "ANDERS"."T1" set "C2" = '3' where "C2" = '2' and ROWID = 'AAAE5KAAEAAAAFfAAB';

This hardly looks complete? But there is something in there that we possibly could make some sense from, right? Let's try that in the next blog post in this series. And this post has been all about Oracle, more or less, now in the next post we will get closer to replicating to MariaDB, which is where we want our data to reside anyway? Right?

Happy SQL'ing
/Karlsson

OraMySQL 1.0 Alpha released - Replication from Oracle to MariaDB and MySQL!!

Now it's time to release something useful! At least I hope so. I have been going through how I came up with this idea and how I came up with the implementation in a series of blog posts:

• Part 1
• Part 2
• Part 3
• Part 4 

 But now it's time for the real deal, the software itself. This is an Alpha 1.0 release but it should work OK in the more basic setups. It's available for download from sourceforge, and here you find the source package which uses GNU autotools to build. The manual is part of this download, but is also available as a separately.

You do need MariaDB libraries and includefiles to build it, but no Oracle specific libraries are needed, only Oracle itself of course. There are build instructions in the documentation.

Cheers and happy replication folks

/Karlsson

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

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

Replication from Oracle to MariaDB the simple way - Part 2

The theme for this series of posts is, and indicated in the previous post, "Try and try, again", and there will be more of this now when I start to make this work by playing with Oracle, with PL/SQL and with the restrictions of Oracle Express (which is the version I have available).

So, what we have right now is a way of "sending" SQL statements from Oracle to MariaDB, the question is when and how to send them from Oracle. The idea for this was then to use triggers on the Oracle tables to send the data to MariaDB, like this, assuming we are trying to replicate the orders table from Oracle to MariaDB:

In Oracle, and assuming that the extproc I have that created to send UDP messages is called oraudp, then I would do something like this:
CREATE OR REPLACE TRIGGER orders_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
   CALL oraudp('INSERT INTO orders VALUES '||
     :new.order_id||', '||:new.customer_id||', '||
     :new.amount||')');
END;
/

CREATE OR REPLACE TRIGGER orders_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
   CALL oraudp('UPDATE orders SET order_id = '||:new.order_id||
     ', customer_id = '||:new.customer_id||', amount = '||:new.amount||
     ' WHERE order_id = '||:old.order_id);
END;
/

CREATE OR REPLACE TRIGGER orders_delete
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
    CALL oraudp('DELETE FROM orders WHERE order_id = '||:old.order_id);
END;
/
 As I noted in my previous post though, DML TRIGGERs execute when the DML statement they are defined for executes, not when that DML is committed, which means that if I replicate to MariaDB in a trigger and then roll back, the data will still be replicated to MariaDB. So the above design wasn't such a good one after all. But the theme is, as I said, try and try again, so off to think and some googling.

Google helped me find a workaround, which is by using Oracle MATERIALIZED VIEWs. And I know what you think, you think "What has dear old Karlsson been smoking this time, I bet it's not legal", how is all this related to MATERIALIZED VIEWs? Not at all, actually, but this is a bit of a kludge, it does actually work, and this is the nature of a kludge (I've done my fair share of these over the years). There are two attributes of Oracle MATERIALIZED VIEWs that makes these work for us:

• A MATERIALIZED VIEW can be updated ON COMMIT, which is one of the two ways a MATERIALIZED VIEW is updated (the other is ON DEMAND). So when there is a COMMIT that affects the tables underlying the MATERIALIZED VIEW, we have something happening.
• And the above would be useless if you couldn't attach a TRIGGER to a MATERIALIZED VIEW, but you can!

So, to make this work, I can define a MATERIALIZED VIEW, one that I don't really use for any other purpose, on the table I want to replicate. So then the design gets something like this, again using the orders table as an example:

In practice, if we continue to use theorders table, this is what I would do in Oracle:
CREATE MATERIALIZED VIEW orders_mvREFRESH ON COMMIT
AS SELECT order_id, customer_id, amount
FROM orders;

CREATE OR REPLACE TRIGGER orders_mv_insert
AFTER INSERT ON orders_mv
FOR EACH ROW
BEGIN
   CALL oraudp('INSERT INTO orders VALUES '||
     :new.order_id||', '||:new.customer_id||', '||
     :new.amount||')');
END;
/

CREATE OR REPLACE TRIGGER orders_mv_update
AFTER UPDATE ON orders_mv
FOR EACH ROW
BEGIN
   CALL oraudp('UPDATE orders SET order_id = '||:new.order_id||
     ', customer_id = '||:new.customer_id||', amount = '||:new.amount||
     ' WHERE order_id = '||:old.order_id);
END;
/

CREATE OR REPLACE TRIGGER orders_mv_delete
AFTER DELETE ON orders_mv
FOR EACH ROW
BEGIN
    CALL oraudp('DELETE FROM orders WHERE order_id = '||:old.order_id);
END;
/ 

Now we have a solution that actually works, and it also works with Oracle transactions, nothing will be sent to MariaDB if a transaction is rolled back. There is a slight issue with this though, you might think it's no big deal, but fact is that performance sucks when we start adding data to the orders table, and the reason for this is that the default way for Oracle to refresh a materialized view is to delete all rows and then add the new ones. In short, Oracle doesn't keep track of the changes to the base table, except writing them to the base table just as usual, so when the MATERIALIZED VIEW is to be refreshed, all rows in the materialized view will first be deleted, and then all the rows in the base table inserted. So although this works, it is painfully slow.

So it's back to the drawing board, but we are getting closer, now it works at least, we just have a small performance problem to fix. I'll show you how I did that in the next post in this series, so see you all soon. And don't you dare skip a class...

/Karlsson

Replication from Oracle to MariaDB the simple way - Part 1

Yes, there is a simple way to do this. Although it might not be so simple unless you know how to do it, so let me show you how this can be done. It's actually pretty cool. But I'll do this over a number of blog posts, and this is just an introductory blog, covering some of the core concepts and components.

But getting this to work wasn't easy, I had to try several things before I got it right, and it's not really obvious how you make it work at first, so this is a story along the lines of "If at first you don't succeed mr Kidd" "Try and try again, mr Wint" from my favorite villains in the Bond movie "Diamonds are forever":

So, I had an idea of how to achieve replication from Oracle to MySQL and I had an idea on how to implement it, and it was rather simple, so why not try it.

So, part 1 then. Oracle has the ability to let you add a UDF (User Defined Procedure) just like MariaDB (and MySQL), but Oracle calls then extproc. So my first idea was an extproc that would send a message over UDP, any generic message. In this specific case though, I was to have a server that received that UDP message and then sent it to MariaDB as a query. So this server was connected to MariaDB and a message came in though UDP, and this message would of course be some DML statement. Simple and efficient, right? Of course UDP is not a reliable protocol, but for this usage it should be OK, right?

All that was needed after this was a set of triggers on the Oracle tables that would send a message to the server which would then send these to MySQL, and this message would be based on the changed data, as provided by the trigger, like a corresponding INSERT, UPDATE or DELETE statement. This should work. OK, let's get to work.

My first attempt at the server meant that a lot of UDP messages were lost. The reason for this was that there were just to many messages coming in bursts (this is how extprocs works in Oracle, nothing wrong with Oracle here, it's just the nature of the beast). The solution was to have the server be multithreaded, with one tight thread receiving messages on the UDP port, and then having a queue and another thread processing the queue and send the messages to MariaDB?

Well, this didn't work out either. The reason was that the means I did synchronization between the threads was using Mutexes, and this also slowed things down. It worked OK for a while, but some burst could easily mean that UDP messages were lost, and really not much was necessary in terms of load for this to happen.

So, what do we do now? I had two choices, either I skip UDP and go for a connected TCP solution instead. The issue with this was that partly the extra overhead this would cause on the Oracle end of things, and partly that this would increase the complexity of my extproc and server, and above all the API to all this. The other option was to go for a lock-less queue. Some googling and some testing allowed me to figure out how to do this with gcc and I could move on and test this. Fact is, it worked fine. And as UDP messages can't be routed, using UDP messages in and of themselves made things a bit more secure.

But then there was the issue with the TRIGGERs calling the extproc. A trigger is called on a DML operation, but that operation might later be rolled back, and in that case I would break the consistency of data. And for some reason, for all the enhancement to standard SQL that Oracle provides, (including DDL triggers), there are no ON COMMIT triggers. But there just had to be a way around that one, right?

So Try and try again, mr Wint. Yes, I will solve all this, but the story is to be continued in another blog post. Also, I will eventually provide with the code for all this. GPL code that you can use out of the box to replicate from Oracle to MariaDB. But not just yet.

So don't touch that dial!

/Karlsson

Automatically restarting MySQL slaves the easy way.

MySQL Replication is something that is used by many, many MySQL users, and here at Recorded Future we are no exception. In our case, the slaves are used for different purposes, and as we develop our system so much and so fast, so sometimes things happen which could have been avoided. Like something getting executed in the master that really should not end up in a slave at all, and which would cause all sorts of problems on the slave.

Also, some things that work fine on the master can sometimes cause things to break on the slave, a typical such issue is a big operation on the master that when executed on the slave would cause a lock timeout. And you could argue that the lock timeout should be increased, but the question is how much, and frankly, do you really want those locks hanging around? And in some cases, it's the replication thread that has a lock timeout.

I can easily identify three different actions to take based on the errors that the MySQL slave had, and which cased the slave to stop processing, and which I have so far processed manually:

• Errors which really is no issue at all to restart the slave on. One such issue is lock timeouts, but there are more.
• Then there are errors which you just know you can skip a record in the binlog on. In our case, error number 1317 is one such error. This is an error when a large operation has been interupted on the master, for example a large INSERT INTO .. SELECT statement. In our case, this only happens rarely when we use large temp tables, and more or less always we really should not have the temp table on the slave at all. What this means is that, in our case, these errors can be disregarded, we can stop this binlog record and go on to restart the slave. I.e. we can do a SET SQL_SLAVE_SKIP_COUNTER=1; and then a SLAVE START; just like that.
• And then there are errors which needs manual intervention always, we just should not attempt a restart.

This seems simple enough, right? So it should be possible to have a program that runs in the background, checks the status of the slave a regular intervals, and if it's an error we can restart, or possibly skip 1 and restart, on, do just that. Look no further then, I created a small shellscript for just that, and I have tried it a bit with our systems and it seems to work OK.

To be honest, this is as far from rocket science as you can get I think, but the script works well so here it is.

It's a bash shell-script and I run it on Ubuntu, but I don't use many fancy bash features, so it should be easy enough to understand. There is a --help option, and you can configure which errors not to attempt a restart on, which errors to skip one binlog record and restart on (any errors beside these, a restart is attempted on) and a sleep-time to set the time between checking the slave status (default is 10 minutes).

As i said, nuthin' fancy, but simple and workable. And before I finish this up, no i didn't check if someone has already published a script for this, I assume someone has, but here is my attempt at the same thing then.

Cheers
/Karlsson

Slavereadahead 1.3 available

Version 1.3 of Slave read-Ahead is available for download. If you don't know what this tool is about, it is a tool that runs in the background, reads the incoming replication log on a slave and converts INSERT, UPDATE and INSERT ... SELECT statements into SELECT statements and executes these before the statement in question is executed on the server, the idea being that this will "prewarm" the MySQL caches for this date, for example the rows that an UPDATE is affecting will already be in the cache when the UPDATE arrives on the slave. Because of the way replication data is read, this tool only works with MySQL5.5 and up.

Version 1.3 introduces the auto-reconnect feature. This will reconnect to the MySQL server if the connection fails. To be sure that we restart, all existing connectings are released before a reconnect attempt is made. This version also has some more log printouts, mainly operations per second.

In addition, Version 1.3 fixes a few minor bugs, and one semi-major one, which caued not all INSERT ... SELECT statements to be handled properly.

/Karlsson

Slave Readahead 1.2 available

Version 1.2 of Slave Readahead is now available for download here. If you don't know what this little project is about, it is used to pre-warm the MySQL Cache for the Replication thread on MySQL slaves.

It is built to support MySQL 5.5 and up only, as it uses some new commands in MySQL (like the SHOW RELAYLOG EVENTS admin command). For more information regarding this little project, either read this blogpost or download the documentation for the project.

/Karlsson