OpenLogReplicator has full DDL support?

Technically speaking support of DDL is trivial. All schema is written in SYS.xxx$ tables. Just what you need to do is to track the changes, and once they appear, rebuild internal dictionaries. Someone adds column to table? Well, you would see an INSERT to SYS.COL$ and maybe some other tables.

… A few months later

OpenLogReplicator version 0.8.8 has experimental support for DDL. Which DDL are supported? Well… I don’t know, haven’t tested all of them. This would be a good exercise to test. Add column, drop column, move, truncate, create new table, drop table, make column visible, invisible, add, drop supplemental log groups, add primary key, change it, etc. Partitioned tables, yes, should be no problem with those.

How to test it? Download the latest code from github, compile, and set for experimental ddl support:

"flags":1073741824,

And run it. Every time when a DDL event appears which would affect replicated tables you would see a message in the log like:

2021-05-20 00:07:29 [INFO] updated schema: USR1.ADAM1 (dataobj: 32483, obj: 32483)

This means that some part of the schema has been changed. It could have been just a segment allocation, so not necessary a real DDL.

Example:

create table usr1.adam1(a numeric, b number(10), c number(10, 2), d char(10), e varchar2(10), f timestamp, g date);
ALTER TABLE USR1.ADAM1 ADD SUPPLEMENTAL LOG GROUP adam1grp(a) ALWAYS;

Next you run OpenLogReplicator and execute in SQL*Plus:

insert into USR1.ADAM1 values(100, 999, 10.22, 'xxx', 'yyy', to_date('2019-08-01 12:34:56', 'YYYY-MM-DD HH24:MI:SS'), null);
commit;
alter table usr1.adam1 add(h date);
insert into USR1.ADAM1 values(100, 999, 10.22, 'xxx', 'yyy', to_date('2019-08-01 12:34:56', 'YYYY-MM-DD HH24:MI:SS'), null, to_date('2019-08-01 12:34:56', 'YYYY-MM-DD HH24:MI:SS'));
commit;

The JSON output would be:

{"scns":"0x0000000000d173aa","tm":1621466058000,"xid":"8.30.10076","payload":[{"op":"begin"}]}
{"scns":"0x0000000000d173aa","tm":1621466058000,"xid":"8.30.10076","payload":[{"op":"c","schema":{"owner":"USR1","table":"ADAM1","obj":32486},"rid":"AAAH7mAAEAAAADNAAA","after":{"A":100,"B":999,"C":10.22,"D":"xxx       ","E":"yyy","F":1564662896000}}]}
{"scns":"0x0000000000d173aa","tm":1621466058000,"xid":"8.30.10076","payload":[{"op":"commit"}]}
{"scns":"0x0000000000d173bc","tm":1621466058000,"xid":"4.14.10098","payload":[{"op":"begin"}]}
{"scns":"0x0000000000d173bc","tm":1621466058000,"xid":"4.14.10098","payload":[{"op":"c","schema":{"owner":"USR1","table":"ADAM1","obj":32486},"rid":"AAAH7mAAEAAAADNAAB","after":{"A":100,"B":999,"C":10.22,"D":"xxx       ","E":"yyy","F":1564662896000,"H":1564662896000}}]}
{"scns":"0x0000000000d173bc","tm":1621466058000,"xid":"4.14.10098","payload":[{"op":"commit"}]}

As you can see – the new In the error log you would also see:

2021-05-20 00:14:18 [INFO] updated schema: USR1.ADAM1 (dataobj: 32486, obj: 32486)

Well, this is experimental option, by default it is off. I need to finish some pieces, especially related to saving current schema on shutdown so that it would be used for the next run. So this is just a teaser what will come soon.

If you think this might be useful and you would like to use in your environment? Need some help? Fell free to contact me.