Home » Other » General » Migration
Migration [message #237557] Mon, 14 May 2007 21:08 Go to next message
MSteynberg
Messages: 4
Registered: May 2007
Junior Member
Hi Everyone,

I am brand new to Oracle. My company has decided to migrate from SQL Server 2005 to Oracle.

I'm thrown in the deep end, so I could really use some good advice.

Our database is about 120 Gigs. What would be the best way for me to move the data to Oracle?

We will be having downtime during migration, but obviously would like to keep it to a minimum.

I will really appreciate any help anyone can give me.

Thank you
Re: Migration [message #237580 is a reply to message #237557] Tue, 15 May 2007 00:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Oracle has a migration workbench to migrate from SQLServer to Oracle.
A good starting point for anything Oracle-related would be http://technet.oracle.com
There you can download all the software and, more importantly, all the documentation. Another great source of documentation is http://tahiti.oracle.com
And of course this site..
Re: Migration [message #237601 is a reply to message #237580] Tue, 15 May 2007 01:55 Go to previous messageGo to next message
MSteynberg
Messages: 4
Registered: May 2007
Junior Member

Thank you for that. On the Oracle website it talks about SQL Developer, when I look for the workbench.

What is the difference between SQL Developer and the workbench?

Re: Migration [message #237604 is a reply to message #237601] Tue, 15 May 2007 02:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
SQL Developer is a developer's tool, in which you can execute SQL-queries and code stored program units etc.
SQL Workbench is (was?) something alike.
What you are looking for is the migration workbench

[Edit: Following the URL I provided, I see what you mean. "Oracle SQL Developer Migration Workbench is a new, redeveloped tool that greatly extends the functionality and usability offered by the original Oracle Migration Workbench to migrate Microsoft Access, Microsoft SQL Server and MySQL databases to Oracle."]

[Updated on: Tue, 15 May 2007 02:36]

Report message to a moderator

Re: Migration [message #237807 is a reply to message #237604] Tue, 15 May 2007 14:14 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
when migrating your data across, be careful to make sure that you understand the database characterset and NLS settings for the Oracle client. non-7 bit ascii characters could get corrupted if wrong.
Re: Migration [message #237830 is a reply to message #237557] Tue, 15 May 2007 21:59 Go to previous messageGo to next message
MSteynberg
Messages: 4
Registered: May 2007
Junior Member
Hi Guys,

Thank you for all your help so far. I have downloaded and installed the Migration workbench. However there doesnt seem to be a plugin for SQL 2005.

I found this discussion:
http://forums.oracle.com/forums/thread.jspa?threadID=365550&tstart=45

It appears that you are forced to use SQL Developer. I ran a small test using SQL Developer and although the data got transfered it took over 3 hours for 4 million rows.

Since I need to transfer over 100 million rows, that is not acceptable performace.

So where do I go from here?


Re: Migration [message #237832 is a reply to message #237557] Tue, 15 May 2007 22:14 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
>So where do I go from here?
You are free to proceed any direction you deem appropriate.
With any engineering solution, it can be good, fast or cheap. Pick any two.
Re: Migration [message #237889 is a reply to message #237832] Wed, 16 May 2007 01:36 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Very useful answer Ana, to an OP that has shown good behavior, confirmed to posting guidelines etc..

MSteynberg, can you pinpoint the bottleneck of the performance problem? I presume the Oracle db and your SQLServer db are on different machines. Does your network connections allow for faster transfer? (eg can you connect from the Oracle db server to your SQLServer database and get a good throughput?)

I never did any migration like that, so I don't know what speeds you can expect.
Re: Migration [message #237947 is a reply to message #237889] Wed, 16 May 2007 03:31 Go to previous messageGo to next message
MSteynberg
Messages: 4
Registered: May 2007
Junior Member
Hi Frank,

Thank you for all your help so far. Smile

Well the when I ran the tests the two servers where actually on the same machine. Granted its not as powerfull as the production servers, so in production it might be somewhat faster but I doubt a lot.

I am running some other tests aswell, dumping to flat files and then loading with SQL*Loader, which I read about somewhere else. Still testing so Im not sure yet how that will go.

Do you maybe know of any good third party tools which I should maybe have a look at?

Thanks,

M
Re: Migration [message #238087 is a reply to message #237947] Wed, 16 May 2007 11:47 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Another option you can look at is Oracle "hetrogeneous services" that allow you to select data directly into Oracle using an ODBC source. I don't have any reason to think it'll be quicker than the migration workbench (which may use it anyway) - you'll need to benchmark.

http://technology.amis.nl/blog/?p=1303
http://oraclebi.blogspot.com/2006/02/using-excel-as-data-source-for.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4406709207206#18830681837358


The flat file approach is attractive because it's low-tech. Watch out for characterset issues though...
Previous Topic: Sending E-mail
Next Topic: Trading Partner
Goto Forum:
  


Current Time: Mon Dec 05 14:55:16 CST 2016

Total time taken to generate the page: 0.08196 seconds