Home » Other » Client Tools » What is the best way to convert SQL DB to Oracle DB? (11g 11.2.0.1 , windows server 2008r2 standard)
What is the best way to convert SQL DB to Oracle DB? [message #617166] Wed, 25 June 2014 12:12 Go to next message
JP10
Messages: 29
Registered: May 2014
Location:
Junior Member
Hello,

I am trying to migrate a sql database to Oracle using SQL Developer migration tool. I got the migration project to show up and the "Captured Database Objects" tree looks fine, but when I open up the "Converted Database Objects" tree there are no tables, views or indexes listed. I look at the MIGRLOG table and I get a bunch of these messages? What am I doing wrong? Any help is much appreciated.

No derived object found for 105151[MD_STORED_PROGRAMS]
Index 'nk_allocd' on table 'alloc' has been dropped as it is a duplicate of a constraint

Open to other ideas of how to convert SQL DB to Oracle as well if there are any. Thanks.
What is the best way to convert SQL DB to Oracle DB? [message #617188 is a reply to message #617166] Wed, 25 June 2014 18:05 Go to previous messageGo to next message
JP10
Messages: 29
Registered: May 2014
Location:
Junior Member
Beside SQL Developer and SQLWay? I can not get SQL Developer migration to work and SQLWay is too expensive. Anybody use some other method? I can not pump the data to Oracle through SQL export wizard then apply schema, store procedures, triggers, functions etc. because there is no scripts saved off that I can apply it to Oracle side. I would have to manually convert it and I'm not going down that route. Please help never been in this situation before. Thanks.
Re: What is the best way to convert SQL DB to Oracle DB? [message #617190 is a reply to message #617188] Wed, 25 June 2014 18:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
OK, I give up.
Please post URL to documentation which describes the source SQL database.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/
Re: What is the best way to convert SQL DB to Oracle DB? [message #617191 is a reply to message #617166] Wed, 25 June 2014 19:02 Go to previous messageGo to next message
JP10
Messages: 29
Registered: May 2014
Location:
Junior Member
BlackSwan,

I'm not sure what your asking, but I am thinking you want the documentation of what I'm going off of? If so this is what I used online to start with.

http://docs.oracle.com/cd/E25259_01/appdev.31/e24285/migration.htm#CHDJGEHC
http://www.youtube.com/watch?v=W1QaEHpNw6Y
Re: What is the best way to convert SQL DB to Oracle DB? [message #617192 is a reply to message #617191] Wed, 25 June 2014 19:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
which vendor make the source RDBMS?
I ask because you have NOT posted this detail & it can impact any possible solution.

Do you have DDL for the source database objects?
Re: What is the best way to convert SQL DB to Oracle DB? [message #617197 is a reply to message #617192] Wed, 25 June 2014 19:34 Go to previous messageGo to next message
JP10
Messages: 29
Registered: May 2014
Location:
Junior Member
Oh sorry vendor is Microsoft SQL server 2008. No DDL definitions stored anywhere.
Re: What is the best way to convert SQL DB to Oracle DB? [message #617198 is a reply to message #617197] Wed, 25 June 2014 19:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://blog.data-miners.com/2010/02/creating-ddl-for-entire-database-in-sql.html

I hope you folks are prepared to manually re-write the application code to operate against the new Oracle DB objects.
Re: What is the best way to convert SQL DB to Oracle DB? [message #617330 is a reply to message #617188] Fri, 27 June 2014 08:10 Go to previous messageGo to next message
thatjeffsmith
Messages: 81
Registered: July 2009
Location: Raleigh, NC
Member

Why can't you get SQL Developer to work? It's our official migration platform for moving your SQL Server applications to Oracle. It's fully supported, and like you said, free. If you need assistance, you can open SR's with MOS or feel free to email me directly at jeff.d.smith@oracle.com

We do support SQL Server 2008 and R2 migrations.
Re: What is the best way to convert SQL DB to Oracle DB? [message #617338 is a reply to message #617330] Fri, 27 June 2014 09:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You do it for free? Twisted Evil

[Updated on: Fri, 27 June 2014 09:59]

Report message to a moderator

Re: What is the best way to convert SQL DB to Oracle DB? [message #617348 is a reply to message #617330] Fri, 27 June 2014 10:53 Go to previous messageGo to next message
JP10
Messages: 29
Registered: May 2014
Location:
Junior Member
Hello Jeff,

I have tried opening u SRs in past never really helped I get more positive feed back from this forum. Anyways read my first post that is the issue I am having any suggestions? Thanks.
Re: What is the best way to convert SQL DB to Oracle DB? [message #617370 is a reply to message #617348] Fri, 27 June 2014 13:10 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Man, Jeff has given you the privilege to reach him personally at his email address, of course, unless you raised a SR to Oracle. Now that you have done it as you say, then use your privilege.

Remember, nothing comes for free, but when it comes then don't just let it go.
Re: What is the best way to convert SQL DB to Oracle DB? [message #617412 is a reply to message #617338] Sun, 29 June 2014 09:53 Go to previous messageGo to next message
thatjeffsmith
Messages: 81
Registered: July 2009
Location: Raleigh, NC
Member

Mostly Smile

Saving the poor databases stuck in the land of SQL Server is something I would do for free, but thankfully Oracle pays me.
Re: What is the best way to convert SQL DB to Oracle DB? [message #617413 is a reply to message #617166] Sun, 29 June 2014 09:59 Go to previous messageGo to next message
thatjeffsmith
Messages: 81
Registered: July 2009
Location: Raleigh, NC
Member

The 2nd error isn't really a problem - your index didn't come over b/c that index was already there - probably b/c of a PK or Unique constraint. Oracle creates those indexes automatically.

Not sure what's going on with the other error message.

But you don't say where you get 'stuck' or how it didn't 'work.'

In your migration project, do you have a successful capture of the SQL Server source database? What happens when you run the convert on your target Oracle instance? Do the objects get created?

The forum dedicated for migrations is here
https://community.oracle.com/community/developer/english/migration_and_modernization/database_and_application_migrations

Re: What is the best way to convert SQL DB to Oracle DB? [message #617563 is a reply to message #617413] Tue, 01 July 2014 12:05 Go to previous messageGo to next message
JP10
Messages: 29
Registered: May 2014
Location:
Junior Member
Hello Jeff,

Yes I have a successful capture of the sql server source db. I do not see the tables or other objects under the convert section. I have uploaded an image of sql developer convert section I do not see. Let me know if you need more information. Appreciate it.
Re: What is the best way to convert SQL DB to Oracle DB? [message #617824 is a reply to message #617563] Thu, 03 July 2014 15:18 Go to previous message
JP10
Messages: 29
Registered: May 2014
Location:
Junior Member
Got it to work. Solution is to use SQL Developer 3.1. I tried using SQL Developer 2.1 and 4.0 ran into different issues with both, but SQL Developer 3.1 seems to be the one to use with SQL 2008.

Next question is how do we get it to have it's own tablespace and rename the user? Noticed after the migration it put a "DBO_" in front of my source db name. So, as an a example my source db name was fwk_unittest the migrated Oracle user name is dbo_fwk_unittest now and it's default tablespace is unders USERS. How do I rename the user name name and have it's own tablespace and data files?

Appreciate the help.
Previous Topic: sqlplus report headers and titles
Next Topic: SQLPLUS Commands
Goto Forum:
  


Current Time: Thu Mar 28 12:35:33 CDT 2024