1

Given a pre-production oracle database and a production oracle database and if around 300K records need to be transferred from the former to the latter, would using a messaging system such as an ESB/JMS/TIBCO be a good option?

procrastinationmonkey
  • 331
  • 1
  • 4
  • 13

3 Answers3

1

I don't know Oracle, but if I was trying to asynchronously replicate data with SQL Server, I would use their own internal tools to accomplish it. I would imagine Oracle has similar tools to run jobs to copy between two Oracle databases.

However, I do have quite a bit of experience using an ESB (Mule) with ActiveMQ to replicate data across database technologies. Specifically I've done SQL Server->Mongo and MySQL->Mongo with Mule and ActiveMQ.

So far I've found Mule to be a wonderful solution - especially coupled with ActiveMQ. I've been able to replicate about 400k Wordpress blog posts (from MySQL) to Mongo in about 20 minutes. To transfer 100k articles from a CMS system we were able to get it done in about 30 minutes.

I figured I'd weigh in because you mentioned and ESB and messaging. I would go that route if the integration points are heterogenous. If you do go down that route, Mule is awesome.

ryan1234
  • 7,237
  • 6
  • 25
  • 36
  • We are trying to explore this approach for replicating data from Oracle to another Oracle instance. I am curious on what role would Mule play when we also use ActiveMQ. Is there a blog post that I can look for a detailed explanation? – Andy Dufresne May 29 '19 at 13:55
0

If you are trying to move data from an old database to a new one instead of doing it asynchronously, possibly a simpler method would be sql injection. Assuming your old database allows you to "export" your database, when you export it you will download a sql file. Then you can just open that sql file in a program like notepad and copy-paste that code in the sql executor at your new database and it will re-create all your tables and populate them with the old data.

Devon Bernard
  • 2,250
  • 5
  • 19
  • 32
0

Actually using the database tools will be the recommended method for replicating data between databases.

When using messaging, one does not get the guarantee that the data will be transferred in the same sequence as it was sent and honor relationships between tables, potentially resulting in replication errors, unless one builds up some mechanism on the JMS receiver side to maintain the sequence. But that looks rather like an overhead.

Jack G.
  • 3,681
  • 4
  • 20
  • 24