Feb 05 2008
Zizzy: Open Source Log-based Replication for Oracle
If you’ve ever looked into purchasing third-party replication software for Oracle, you’ve likely run across log-based replication products such as Quest Software’s SharePlex or GoldenGate Software’s Transactional Data Management platform. Unlike trigger-based replication systems, log-based replication relies on translating Oracle’s REDO and UNDO data into SQL which can be applied to various target databases be they Oracle, DB2, SQL Server, or otherwise.
Until now, outside of using Change Data Capture, there were very few ways for an individual developer or advanced DBA to reliably perform their own log-based replication; something a new project, Zizzy, is trying to change.
This quick little blog entry is intended to discuss the basic differences between trigger and log-based replication, Zizzy, and how you can help.
What is the difference between trigger and log-based replication?
In a trigger-based replication scenario, triggers are placed on all tables which are to be replicated; their role is to capture the necessary data required to replicate a transaction. When these triggers fire, they record the change data in a log table on the primary database. By using the data stored in the log tables, a remote system can simply reapply the changes to itself. As triggers require additional CPU and storage on the server, they are often regarded as an older replication technology.
Contrarily, log-based replication reads everything required to replicate a transaction directly from the database’s native transaction log files; requiring almost no additional storage and significantly less CPU overhead. Given the advantages of log-based replication, it is often portrayed as a newer replication technology.
Where does Zizzy fit in?
In short, Zizzy is an open source log-based replication system written by Sean Young, Steve Sheil, and Hongliang Wang. While it’s currently in the alpha stages of development, it’s an exciting project that I’m glad someone has started.
Where can I find Zizzy?
Zizzy is hosted on Source Forge at the following URL:
Where do I go from here?
Well, if you happen to know C, have some free time, and like researching Oracle, you could join the project. If not, you could always play with it and send reports back to the developers.
Hopefully, in time, Zizzy will become a production-ready replication option for Oracle.
I spent some time in the log format research, I think I can comtribute the code I wrote (MyLOG) for this project.
http://www.dbatools.net/mytools/mylog_fasten_drop_recovery.html
The above link is a demo of what MyLOG program can do now.
Thank you for your post.
I had read on your site that you were also considering open-sourcing MyLOG. Are you still thinking about it?
Yes, I want to open source it, becase I cannot continue this kind of research alone, need more and more people to continue it.
Extracting create/drop tables stuff from the Oracle logs is on Zizzy’s ToDo list, so the MyLog could be valuable. Zizzy’s current focus on insert/update/delete is going well; the development branch now has support for LONGs and LOBs and I think row-chaining support is only a few weeks away…
Fangxin,
It would be cool to see MyLOG open-sourced. What little time I have, I’d be willing to help out on it.
-Jonah
Steve,
It’s really cool to see the progress you’re making :)
Question to log-based gurus:
I am just going to dive in log-based replication and gone through zizzy project from surface. It looks like it is written in C/C++. Obviously C/C++ choice is due to efficiency reasons.
Can we use JAVA APIs as well ?? as for portability reasons it is a good choice especially in distributed environments.
Well, there’s no reason Java wouldn’t work. The thing it really comes down to is performance.
The number of instructions required to do this in C is significantly less than Java, and it’s closer to the hardware. However, I think Java would work OK for a small-to-medium loaded system. But, because it has much more processing overhead, I don’t think it would be able to keep up on a heavily loaded system and may actually contend a bit with Oracle for CPU time.
While I don’t think it would be highly performant, it would be interesting to see.
-Jonah
Hi Affaan,
If you look Zizzy’s development branch you’ll see that all the logic is all encapsulated in a C API with a simple interface. This is so we can wrap the API up in a Java class with JNI. Actually it will be a few classes; one to read a record, one to parse it and one to paste together long/lob/chained segments. I realize is not perfect for the Java-only folk, but pure Java won’t fit the non-Java folk, and writing everything twice is not feasible.
Steve.
Thanks Jonah and Steve for response.
Of course, C is near to hardware making the replication efficient but I think at later stages when zizzy gets matured, JAVA or some OOP language may have to play its role. May be through using JNI or other emedded mechanism.
Anyways thanks for your feedback!
Affaan.
Affaan,
I’ve been writing code for 17 years now, and I’ve done a lot in Java, but I honestly can’t see any reason for writing something like this in Java; it’s just not the right language. C is very portable if it’s written properly. And, I can’t name one thing that’s written in Java that is very performant. In fact, I’ve seen more performance-related things translated from Java back to C/C++.
Java is great for web stuff or cross-platform user-oriented applications which don’t require high performance. Regardless, you seem to be a fan of Java, so go ahead and write it. Don’t say I didn’t warn ya!
-Jonah
Ok Jonah,
I give up. I am not that much experienced as you. Actually I am not quite used to of C/C++. And was just thinking to try this stuff in java. Now let me think it in C. I will need your guidance in future for it.
Thanks,
Affaan.
Affaan,
Don’t be discouraged. You should write your utility in any language you want. I’m just suggesting that, rather than first selecting a language to develop the utility in, you should look at the requirements of the utility and choose an appropriate language from there.
If you’re looking to do a proof-of-concept or small-scale version of Oracle log-based replication, Java would probably be the fastest and easiest. If you’re looking to build something high-performance, my experience leads me to believe that Java isn’t appropriate. I don’t want you to think I’m hating on Java, because I actually like it and use it for many things.
Regardless, it’s your utility, and you should write it however you want :)
-Jonah
Jonah/Steve,
I did build and running zizzy code. Uptill now what i feel is that zizzy prints the redo log file content given by the user in meaning ful form to stdout. But still I couldn’t be able to see any user table data in zizzy output.
Does current zizzy produce user table data or is it in TODO list?
I want to do some inserts in scott.emp and then look these transactions in zizzy output, Is it possible? If yes, how?
Thanks,
–Affaan
Last time I checked, it will output SQL for INSERT/UPDATE/DELETE. IIRC, you had to specify a command-line flag to do it. Steve would know more than I :)
Yes i specified command-line flag, -t for dump text format and -z for xml format. But there is no other flag for extracting user table data from redo log file read by zizzy.
Steve, can you explain me the function of ZizzyPaster, as well? Is it for sending parsed red log file contents to stdout only?
Thanks,
-Affaan
Hi Steve/Jonah,
Running make from ../row-chaining/src is not producing dump_text and dump_sql executables (As written in README file ) rather it produces oradumplog executable.
When oradumplog is executed with -r option, it produces parse error. I think two files are missing from svn dump_text.c and dump_sql.c.
Any comments???
Thanks,
Affaan.
Hi Affaan,
I think you are using the old version from Sean, and we are now moving into a new version called v2. Therefore, please execute:
svn checkout https://zizzy.svn.sourceforge.net/svnroot/zizzy/zizzy/trunk
to get the latest version.
Furthermore, I am doing research on row chaining and row migration. Hopefully I could add those features in in May.
Also to Affaan,
1, The paster thing is used to support LOB/LONG segments. We found that Oracle will split those columns into small segments, while we have to assemble them again in some objects other than reader and parser.
2, If you want to see some inserts, then simply execute
./zizzy /
Zizzy will automatically start monitoring Redo Log Files. Then on another terminal, you can do insert into any table as you like, e.g.
insert into person values (’Steve’, ‘Sheil’, 45);
commit;
and you can see the output immediately on zizzy screen.
3, If you want to mean the dictionary file which contains user table information, then please use the -D option
Hey, the blog is eating my words!
2, If you want to see some inserts, then simply execute
./zizzy username/password
Zizzy will automatically start monitoring Redo Log Files. Then on another terminal, you can do insert into any table as you like, e.g.
insert into person values (’Steve’, ‘Sheil’, 45);
commit;
Hongliang,
By default, you’re comments are moderated. As such, I just approved them. Was there any other issue about, “eating your words”… or was it because you didn’t see them post immediately?
Thanks Hongliang …… let me check out latest code.
-Affaan
Hongliang,
When i issued insert && commit command with ./zizzy username/digident. First it produced Segmentation fault, then re-issuing ./zizzy username/digident on newly opened terminal cause it to respond nothing .
Is it a bug?
Here are the steps I performed:
1) ./zizzy system/passwd
it immediately displayed:
col_begin_before_delete: non existent transaction: 0×0019.000000c1
col_value_before: non existent transaction: 0×0019.000000c1
col_value_before: non existent transaction: 0×0019.000000c1
col_value_before: non existent transaction: 0×0019.000000c1
col_value_before: non existent transaction: 0×0019.000000c1
col_value_before: non existent transaction: 0×0019.000000c1
col_value_before: non existent transaction: 0×0019.000000c1
col_value_before: non existent transaction: 0×0019.000000c1
col_value_before: non existent transaction: 0×0019.000000c1
col_end_before: non existent transaction: 0×0019.000000c1
Got a commit from non-existent transaction 0×0019.000000c1
SQL commit failed.
2) I logged in to oracle using hr/hr
3) insert into countries values(’it’,'italy’,4);
commit;
after some delay it displayed:
New Transaction: 0×0023.000000d1
insert into sys.smon_scn_time (
thread,
time_mp,
time_dp,
scn_wrp,
scn_bas,
num_mappings,
tim_scn_map,
scn
) values (
650272533,
-0.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000098769385,
DATE/TIMESTAMP has length 1,
405876,
97,
-6-9363-1652951011011011011017406-9363-17529510110110114-17106-9363-18529510110110119-1356806-9363.00529510110110110110165060063005295101101101101101620600630052951011011014700590600630052951011011016500560600630052951011011010063530600630052951011011011019350060063005295101101101101101470600630052951011011011011014406006300529510110110100004106006300529510110110110110138060063005295101101101000035060063005295101101101000032060063005295101101101000029060063005295101101101000026060063005295101101101000023060063005295101101101000020060063005295101101101000017060063005295101101101101101140600630052951011011013000110600630052951011011011011010806006300529510110110100910506006300529510110110110110102060063005295101101101101101000600630052951011011011400000600630052951011011013000000600630052951011011010000000600630052951011011010147000600630052951011011011011010006006300529510110110100000006006300529510110110100000006006300529510110110149000006006300529510110110198000006006300529510110110145000006006300529510110110110138000600630052951011011014100000600630052951011011010000000600630052951011011010000000600630052951011011011011010006006300529510110110110110100060063005295101101101101101000600630052951011011010006000600630052951011011010000000600630052951011011011011010006006300529510110110100000006006300529510110110110110100060063005295101101101008900060063005295101101101009200060063005295101101101009200060063005295101101101008900060063005295101101101941010006006300529510110110100910006006300529510110110110110100060063005295101101101101101000600630052951011011011011010006006300529510110110110110100060063005295101101101101101000600630052951011011011011010006006300529510110110110110100060063005295101101101101101000600630052951011011011011010006006300529510110110110110100060063005295101101101101101000600630052951011011010000000600630052951011011010030000600630052951011011010035000600630052951011011011011010006006300529510110110110110100060063005295101101101000000060063005295101101101000000060063005295101101101009300060063005295101101101101101000600630052951011011010039000600630052951011011010084000600630052951011011011011011010500630052951011011011011019805006300529510110110100009505006300529510110110197101920500630052951011011011011018905006300529510110110110110186050063005295101101101101101830500630052951011011011011018005006300529510110110100897705006300529510110110110110174050063005295101101101101101710500630052951011011010000680500630052951011011010000650500630052951011011011011015905006300529510110110100005605006300529510110110110110153050063005295101101101000050050063005295101101101101101470500630052951011011010039440500630052951011011011011014105006300529510110110100,
‘c3293b4d’,
0);
delete from sys.smon_scn_time where
thread = 581309788 and
time_mp = -0.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000099999844 and
time_dp = DATE/TIMESTAMP has length 1 and
scn_wrp = 243458 and
scn_bas = 100 and
num_mappings = -0.000000000000000000000000000000000000000000000000000000000000000080006798009810110110110110103800067970098101101101266200800067960098101101101308800800067950098101101101101101008000679400981011011011011010080006793009810110110100620080006792009810110110100000080006791009810110110110110100800067900098101101101101890080006789009810110110110110100800067880098101101101101101008000678700981011011010061008000678600981011011011011010080006785009810110110100610080006784009810110110100000080006783009810110110100610080006782009810110110100610080006781009810110110100000080006780009810110110100610080006779009810110110100610080006778009810110110110110100800067770098101101101006200800067760098101101101101101008000677500981011011010091008000677400981011011011011010080006773009810110110110110100800067720098101101101266200800067710098101101101006200800067700098101101101006300800067690098101101101014700800067680098101101101101101008000676700981011011010063008000676600981011011010000008000676500981011011010089008000676400981011011010089008000676300981011011010000008000676200981011011011011010080006761009810110110100000080006760009810110110110110100800067590098101101101000000800067580098101101101101101008000675700981011011011011010080006756009810110110110110100800067550098101101101009300800067540098101101101000000800067530098101101101101101008000675200981011011010061008000675100981011011011011010080006750009810110110100890080006749009810110110192690080006748009810110110155930080006747009810110110192690080006746009810110110100000080006745009810110110100001007900674400981011011010000977900674300981011011019269947900674200981011011010089917900674100981011011011011018879006740009810110110110110185790067390098101101101101101827900673800981011011010091767900673300981011011010000737900673200981011011010062707900673100981011011011011016779006730009810110110110110164790067290098101101101006361790067280098101101101008958790067270098101101101008955790067260098101101101101101497900672100981011011011011014679006720009810110110100004379006719009810110110100004079006718009810110110100933779006717009810110110110110134790067160098101101101000031790067150098101101101008428790067140098101101101101101257900671300981011011011011012279006712009810110110100001979006711009810110110197101167900671000981011011011011011379006709009810110110110110110790067080098101101101101101077900670700981011011011011010479006706009810110110100890179006705009810110110110110100790067040098101101101101101007900670300981011011010000007900670200981011011010061007900670100981011011011011010079006700009810110110100610079006700009810110110110110100790067000098101101101000000790067000098101101101101101007900670000981011011010000007900670000981011011011011010079006700009810110110100610079006700009810110110110110100790067000098101101101399300790067000098101101101101 and
tim_scn_map = ‘c319233b’ and
scn = 0
;
commit; /* SCN: 0×0000.000631DB */
if i issue ./zizzy hr/hr, following error occured:
The following command failed:
sqlplus -S -L hr/hr >zizzy_dict_oracle.tmp 2>zizzy_dict_oracle.stderr <zizzy_dict_oracle.sql
The error message was:
oracle_version 10.2.0.1.0
declare
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at line 21
Creating dictionary failed.
Sorry for long posting. But I am very curious to know whats wrong is happening. I am using Oracle XE. Do i need to do some configuration with ORA XE for the correct working of zizzy as claimed in README. As I am still not able to see SQL statements issued by me on zizzy terminal.
Regards,
-Affaan
Hey Affaan,
What you saw was the normal SMON-driven SCN time update to SMON_SCN_TIME (which you can see is used by things such as timestamp_to_scn and scn_to_timestamp). This has been a standard Oracle function for quite a long time.
As to not seeing the INSERT for countries, do you have the dictionary exported? Without that, you won’t get the SQL. IIRC, the last time I tried it, I think I used a Java-based app to export it.
Also, can you move this discussion to the Zizzy project on SourceForge? That seems like the most logical place, and the most commonly helpful location for others trying to use Zizzy.
Thanks!
-Jonah
jonah …. Thanks for reply. I was thinking to move this discussion to zizzy source forge .. ok its my last message here.
Thanks,
-Affaan
I just have a change of my work, So have no time to work on it.
Because I have AUL/MyDUL development experience, so I should know how to handle the row migration/chain case. And also for LOB/LONG columns.
MyLOG now support INSERT/UPDATE/DELETE/Bulk Insert, I cannot found any bulk update/delete case, from the archive log side, it’s just the same as normal update/delete.
What MyLOG did not do, is to track a running Oracle instance, last year, I was think to write a logtail, to extract the specified objects’ operation, like unix’s “tail -f file” features.
However the log’s internal format research is more important than attach it to an Oracle instance. I also have shareplex maintance experiece.
Thanks.
Hongliang,
When i issued insert && commit command with ./zizzy username/digident. First it produced Segmentation fault, then re-issuing ./zizzy username/digident on newly opened terminal cause it to respond nothing .
Is it a bug?
thanksss