TAGGED POSTS / redo log

OpenLogReplicator – Delete row piece (OP 11.3) and Truncate table (OP 24.1)

The OpenLogReplicator has been a new release 0.0.8 with new functionality:

  • Added rowid field to JSON (Kafka target)
  • Support for OP code 11.3 – Delete Row Piece
  • Support for OP code 24.1 – DDL: Truncate table (only Kafka target)

Here is an example of usage. Let’s create a table:

CREATE TABLE SYSTEM.ADAM3(
  A NUMERIC
);

Next add to the CFG file in the tables section:

{"table": "SYSTEM.ADAM3"}

Next run OpenLogReplicator and execute some SQL:

INSERT INTO SYSTEM.ADAM3 VALUES(100);
COMMIT;
TRUNCATE TABLE SYSTEM.ADAM3;
INSERT INTO SYSTEM.ADAM3 VALUES(200);
COMMIT;
DELETE FROM SYSTEM.ADAM3;
COMMIT;

The following JSON message is sent to Kafka:

{"scn": "8849551", dml: [{"operation":"insert", "table": "SYSTEM.ADAM3", "rowid": "AAAIDRAAFAAAACFAAA", "after": {"A": "100"}}]}
 {"scn": "8849568", dml: [{"operation":"truncate", "table": "SYSTEM.ADAM3"}]}
 {"scn": "8849573", dml: [{"operation":"insert", "table": "SYSTEM.ADAM3", "rowid": "AAAIDSAAFAAAACFAAA", "after": {"A": "200"}}]}
 {"scn": "8849580", dml: [{"operation":"delete", "table": "SYSTEM.ADAM3", "rowid": "AAAIDSAAFAAAACFAAA", "before": {"A": "200"}}]}

I have just adjusted the formatting to make the JSON more readable. This is exactly what is expected. This functionality is available with the latest build (0.0.8). Stay tuned for next OP codes to be implemented too.

PS. I know, that the JSON attribute is named dml even though truncate is actually a ddl operation in Oracle, but it’s actually a matter of naming fields. Maybe it should be changed to something more generic…

PS2. You can notice that after running truncate table newly inserted rows contain a different Data Object ID in the rowid.

OpenLogReplicator – Insert multiple rows (OP 11.11)

The OpenLogReplicator has been extended to support also OP code 11.11 – Insert Multiple Rows.

OpenLogReplicator supports OP code 11.11 - insert multiple rows

This type of redo record is used when an INSERT is used with a set of rows to be inserted. For example with INSERT … SELECT … .

Here is an example of usage. Let’s create a table:

CREATE TABLE SYSTEM.ADAM5(
A NUMERIC,
B NUMBER(10)
C NUMBER(10, 2),
D CHAR(10),
E VARCHAR2(10),
F TIMESTAMP,
G DATE
);

Next add to the CFG file in the tables section:

    {"table": "SYSTEM.ADAM5"}

Next run OpenLogReplicator and execute some SQL:

INSERT INTO SYSTEM.ADAM5 
SELECT OBJECT_ID, DATA_OBJECT_ID, 1.0, STATUS, STATUS, CREATED, CREATED FROM ALL_OBJECTS WHERE ROWNUM <= 2;
COMMIT;

The following JSON message is sent to Kafka:

{"scn": "8568574", dml: [
{"operation":"insert", "table": "SYSTEM.ADAM5", "after": {"A": "20","B": "2","C": "1","D": "VALID ","E": "VALID","F": "2018-07-29T21:16:55","G": "2018-07-29T21:16:55"}},
{"operation":"insert", "table": "SYSTEM.ADAM5", "after": {"A": "46","B": "46","C": "1","D": "VALID ","E": "VALID","F": "2018-07-29T21:16:55","G": "2018-07-29T21:16:55"}}
]

I have just adjusted the formatting to make the JSON more readable. This is exactly what is expected. This functionality is available with the latest build (0.0.5). Stay tuned for next OP codes to be implemented too.

OpenLogReplicator – CDC replication from Oracle to Redis

OpenLogReplicator – the first open-source transaction replicator (CDC) to Kafka now supports also Redis as a target.

Check the new released version 0.0.3 (compiled for x86_64). It requires additionally the hiredis library

To run:

export LD_LIBRARY_PATH=/opt/oracle/instantclient_11_2:/usr/local/lib:/opt/hiredis
Debug/OpenLogReplicator

Testing with Redis:

create table adam5(a numeric, b number(10), c number(10, 2), d char(10), e varchar2(10), f timestamp, g date);
alter table adam5 add constraint idx1 primary key(a);

insert into adam5 values(100, 999, 10.22, 'xxx', 'yyy', sysdate, null);
commit;
insert into adam5 values(101, 999, 10.22, 'xxx', 'yyy', sysdate, null);
commit;
insert into adam5 values(102, 999, 10.22, 'xxx', 'yyy', sysdate, null);
insert into adam5 values(103, 999, 10.22, 'xxx', 'yyy', sysdate, null);
commit;

Redis:

127.0.0.1:6379> KEYS *
1) "SYSTEM.ADAM5.\"100\""
2) "SYSTEM.ADAM5.\"103\""
3) "SYSTEM.ADAM5.\"101\""
4) "SYSTEM.ADAM5.\"102\""
127.0.0.1:6379> GET "SYSTEM.ADAM5.\"100\""
"\"100\",\"999\",\"10.22\",\"xxx       \",\"yyy\",\"2019-01-05T11:21:54\",NULL"

All transactions are replicated with consistency (MULTI/EXEC).

Enjoy!

OpenLogReplicator – first log-based open source Oracle to Kafka CDC replication

I have figured out that writing about somebody else’s software is boring. Why not create your own.

So here it is:

  • All code GPL v.3
  • Just C++ code – absolutely no Java/Python or any other interpreted language
  • Purely reading Oracle Redo Log from disk – zero additional load to the database instance
  • High performance architecture from the beginning – no lazy slow code
  • Minimum latency approac
  • Memory-based approach – no storing of intermediate files on disk

Currently is implemented:

  • Possible to compile for Linux x/64 only
  • Supported only Oracle 11.2.0.4
  • Replicated Redo Codes: just single row Insert (OpCode 11.2)
  • Supported full transactionality: begin, commit, rollback, savepoint
  • Supported types: numeric, char, varchar2, timestamp, date

If I have more time, more documentation will appear here.

How to run?

git clone https://github.com/bersler/OpenLogReplicator
vi Debug/makefile
make
cp OpenLogReplicator.json.example OpenLogReplicator.json
vi OpenLogReplicator.json
./OpenLogReplicator

Make sure that you set the proper paths for needed dependencies for libraries:

  • Oracle client 11.2.0.4
  • RapidJson library
  • The Apache Kafka C/C++ library

Make sure the JSON file contains proper information. Everything is very easy and logical.

For who are inpatient and don’t want to compile – here is release 0.0.3 compiled for Linux x64. To execute it run:

export LD_LIBRARY_PATH=/opt/oracle/instantclient_11_2:/usr/local/lib
Debug/OpenLogReplicator

Have fun, but please do not send me any complains about not working code. I will maybe write here some help&docs when I have time. I could of course add more functionality, but I didn’t have time. You have the code – you can do it by yourself!

Sample input is:

create table adam3(a numeric, b number(10), c number(10, 2), d char(10), e varchar2(10), f timestamp, g date);
insert into adam3 values(100, 999, 10.22, 'xxx', 'yyy', sysdate, null);
commit;

In Kafka you should have:

{"scn": "4856388", dml: [{"operation":"insert", "table": "SYSTEM.ADAM3", "after": {"A": "100", "B": "999", "C": "10.22", "D": "xxx       ", "E": "yyy", "F": "2018-12-10T21:27:42"}}]}

Please have in mind that only single row insert operations are now supported. insert .. select, insert append, etc. would not work. Just 1-row INSERT operations.

Preventing archive log deletion before being processed by Oracle GoldenGate

Oracle GoldenGate just like Oracle Streams has a mechanism of preventing archive logs from being deleted before they are processed.

This mechanism is supported both in Classic and Integrated Extract mode. Although the latter one offers more functionality and control. This article brings in dept analysis what is happening in the database. (more…)

Oracle GoldenGate: What is ADD TRANDATA really doing?

In the process of data replication using Oracle GoldenGate the first step is assuring that all the needed information has been written to the database redo log. By default the database does not write all the information that might be needed by the replication process. That’s why some additional supplemental logging is needed.

According to the documentation supplemental logging is being added by the ADD TRANDATA command. A very good research about this subject has been made by Julian Dyke in the article about adding supplemental logging. But what does the  GoldenGate command actually do and which options should be used? (more…)

loading
×