Jafni Hipni wrote:
> about 3000 transactions per second during peak
> hour...
>
3000 transactions a second should not be a problem getting this data into MySQL especially if its on similar hardware that your Oracle server is running on, however the real issue is the process to do this.
> did we have to use software agent for transfering
> data in synchronize time between Oracle9i and
If you want a product out of the box that handles this, you might look at GoldenGate as they deal in heterogenous replicated environments.
If GoldenGate or other tool vendors do not meet your requirements, there are a couple methods that I would consider, but they are home grown approaches which is probably what you need for a higher volume environment like this with real time replication requirements. First, you could consider writting triggers that would build a replication queue for each of your tables, maybe inserting events into one central replication queue table, and have a process pick these records up from the queue and batch them into MySQL in near real time.
If you really need real time, then you could consider having your application layer connect to MySQL and replicate the transactions itself, for example in the Perl world, DBD::Multiplex can be used to run SQL against multiple database connections at once (
http://search.cpan.org/~tkishel/DBD-Multiplex-1.95/Multiplex.pm ) ... in the Java world, you might consider the C-JDBC (
http://c-jdbc.objectweb.org/ ) software which publishes a white paper actually showing MySQL/Oracle (
http://c-jdbc.objectweb.org/current/doc/C-JDBC_Flexible_Database_Clustering_Middleware.pdf ) used as parallel database backends. The C-JDBC project seems to enable using various database backends to create a RAID-1 or RAID-N type cluster, but RAID in this case would be "Redundant Array of Independent Databases" :) I have not used any of these software packages, so YMMV.
If you don't really need realtime, and your data set is small enough, then you might consider doing full or partial dumps of the Oracle database, and rebuild the MySQL database entirely, or select tables via this snapshot process. This latter should be easy enough and there are plenty of tools out there that can help with this latter process.
Regards,
Josh
Josh Chamas
Director, Professional Services
MySQL Inc., www.mysql.com
Get More with MySQL!
http://www.mysql.com/consulting