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).
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
No comments:
Post a Comment