Sql server 2016 to oracle 18c database migration

Question

0.00/5 (No votes)

See more:

SQL-Server-2016

I'm trying to migrate a database created in SQL Server 2016 to Oracle 18c.

Both database instances are installed in VMs.

However the migration fails providing a message which I could not understand as I'm not that deep into Oracle RDBMS.

Now, both VMs have 4GB of RAM and each one is used to host the specific RDBMS (SQL Server 2016 & Oracle 18c respectively)

Both RDBMSs were installed with default options. The origin database (SQL Server 2016) is a very simple one, no triggers or stored procedures or anything like that. It only has PK-FK constraints for table relations.

The target database is an empty DB (or User as Oracle calls it) with all privileges provided in the options.

The process is followed with the default options in the migration wizard, with the only option selected it to truncate the target DB (if there are any tables....in my case I don't really care since I want a clean migration from scratch, both structure and data). The error shown states the following : "Cannot retrieve object if key is not set". I couldn't find anything so far regarding the issue.

SQL Server 2016 OS is Windows Server 2016, Oracle 18c OS is 2008R2 Datacenter edition.

Please let me know If you need any further details that can help.

What I have tried:

Tried migrating from SQL Server 2008 R2 just in case there is an incompatibility. Granted all privileges to the DB. Tried to migrate only the DB constraints (the DB does not have any indices, triggers or stored procedures).

Posted 8-Oct-18 21:29pm

kyrsoronas

Updated 9-Oct-18 8:07am

Add a Solution

2 solutions

Solution 1

Since you're stuck, I would:

1) In SQL Server, right-click db and "Tasks | Generate Scripts..."

2) "script entire database and all database objects'

3) "Save to file" ... "Advanced" ... "Types of data to script" ... "schema and data".

4) Hack the resultant sql and data "text file" until Oracle accepts it.

Warning: Big database = Big exported text file.

Posted 9-Oct-18 8:07am

Gerry Schmitz

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

kyrsoronas · Accepted Answer · 2018-10-16T23:02:00

Apparently, I've misunderstood how the migration procedure works in Oracle.
I've noticed, after some time, that you need to create a migration user which will be used as a middleman to migrate the schema and data from SQL server to Oracle.
After the procedure is complete, you need to run the generated script and the migration works like a charm.

The above was used in par with some of the tutorials and documents found on OTN (Oracle Technology Network) and YouTube.