• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

[Best practice] From Oracle to MySQL

 
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hey there,
We are building a Struts webapp for a client that will use a MySQL database.
The webapp will only do getMethods (selects).
The main database were all the info is, is an Oracle DB and so somehow the necessary data will have to go from the Oracle to the MySQL DB.
As the budget is rather limited, I would like to ask you guys with experience what the best way is to do this?
We were thinking of the following:
- Some kind of scheduling, being done via the commandline
- Do a dump of the oracle db to csv or xml and upload it to MySQL
- Some kind of transfer application that will do this locally (as Oracle is NOT connected to the internet and MySQL is)

I hope you understand above points.
Does anyone of you have experience with this? Commercial applications will be our last thing to do, so please advise me other methods.
I was thinking something like, since Tomcat can handle multiple datasources, even the struts webapp can handle multiple datasources, would it be possible to define 2 datasources for the app or Tomcat and somehow do a transfer from time to time?
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Just curious - why use two databases?
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ritchie,
I think we need to investigate the requirements a bit more before recommending an approach.

How often do you need to transfer the data? Is it done on a schedule or on demand? How large is the data? Does it change a lot between each transfer or just a little?
 
Ritchie Warsi
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The use of 2 databases is not an issue we, as programmers of the webapp, can give advise on. It's something they want to keep like it is.
They've used the oracle database for local purposes, inserting data in it from clients and such and other company related data that is only used locally.
The MySQL DB came along with the implementation of the webapp we have to build and is only necessary to have SOME (not all) info from the Oracle DB, for access with the webapp (only selects mostly).

The data needs to be transfered every day, on schedule or demand is not certain yet. We're talking about 8 tables here, not really large data, but not small either.
The data changes every day, that's why the transfer needs to be on a daily basis.
 
Paul Sturrock
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
A scheduled export is probably the easiest route. You could create some scripts to export the data you need and import it into MySQL and run them as a cron job/scheduled task. Oracle exp and mysqlimport could do it.


However here's some (unasked for, I know) advice. If I was working on this, I'd ask the DBA to explain why you need to export a view of the database to another schema, rather than just setting up the correct user privilages. And he'd need to come up with a pretty convincing reason, since the requirement as it stands creates the need for a lot more development and ensures the schema your web app sees will almost never be uptodate.
 
Ritchie Warsi
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yeah I know it's pretty strange. They said something about the Oracle DB not being connected to the internet by any means, because of other data in it. User priviliges would indeed be the right way, but hell, I'll have to ask again what good reasons they have.

Tnx for your advice and the advice on the approach to take. We'll look into it immediately.
Other advice is always still welcome.

edit: Oracle exp makes a dump right? As far as I know MySQL can't read/import Oracle dumps...or has this changed recently?

edit2: Nevermind, Oracle's CSV export can be read by MySQL it seems.
[ January 06, 2005: Message edited by: Ritchie Warsi ]
 
Jeanne Boyarsky
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ritchie,
I agree with Paul's recommendation. I've seen some systems where the "main" database is unavailable for security reasons. Other reasons we've used two databases are for presentation and efficiency.
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Ritchie Warsi:

Other advice is always still welcome.

edit: Oracle exp makes a dump right? As far as I know MySQL can't read/import Oracle dumps...or has this changed recently?

edit2: Nevermind, Oracle's CSV export can be read by MySQL it seems.

[ January 06, 2005: Message edited by: Ritchie Warsi ]




Hi Ritchie,

I don't know how tight is your budget for this project.
You may want to look FastReader www.wisdomforce.com

FastReader extracts large Oracle tables into flat file with any delimiter (csv type)or xml. At same time FastReader generates loaders for major databases, including MySQL. You can easily set up some automatic scripts with that. I am using FastReader to unload very large Oracle tables (tens millions of rows) into MS SQL Server. Works very well for me, fast and effective, supports most Oracle data types, including binary, blobs, cblobs, etc.
reply
    Bookmark Topic Watch Topic
  • New Topic