Moving Data to Oracle with Migration Workbench

I’ve been referring customers to SQL Developer’s Migration Workbench for a few months now. I thought it might be a good idea to give it a try myself before I send any more children out to play with the wolves.

Kudos to Oracle for using Foreign Keys!

This is NOT a formalized review. This is a quick glance at a cool tool from a cool vendor. I did what all geeks do – I ran it without reading ANY of the product documentation. And I got it to work in about 15 minutes. Not bad. Not bad at all. I also played around with Microsoft’s counterpart, the SQL Server Migration Assistant (SSMA) a few months ago. I was also successful there, but it did take a few trips to the help to figure it out and to get data successfully moved. More on that in a follow-up post.

For the test I was using SQL Developer 3.0.04.34 (64 bit) on my Windows 7 laptop. I migrated from MySQL 5.0 to Oracle 11gR2.

Wizards to the Rescue!

Step One: Create A Repository
One repository will work for all of your Oracle instances, assuming you are OK with the project mingling together. Give your projects different names, and you should be good to go. Pick the database you want to build it in, then use the Wizard to build the repository.

Whatever user you use to login to the instance in the wizard is apparently where the repository gets built. Don’t supply a login for any schema you do not want these 40+ tables to be created in. You can find the complete data model here if you are morbidly curious.

Repository Creation Wizard

Step Two: Migrate
For this example I chose to migrate a MySQL database. What happens is SQL Developer will take the ENTIRE MySQL database and move it to a new SCHEMA of the same name in the target Oracle database. It would be nice if the tool allowed you to choose the tables you wanted to migrate and whether you wanted to append to an existing SCHEMA, but I can live with this. If you can’t, then I recommend trying the ‘Copy to Oracle’ mode.

Once the Migration Repository is available, you can start the 7-step ‘Migrate’ wizard. I’m not going to post screen shots of every step, just enough to give you an idea of what’s happening.

Migration Workbench 'Migrate' Wizard

Make sure your repository database is available, or you’re not going to make it past Step 2!

In Step 3 we are going to create a new ‘project.’ We’ll need a project name, description, and work space (output) directory.

In Step 4, we need to tell Oracle where the data is coming from.

Where Are Getting the Data From?

Ensure the JDBC drivers for connectivity are installed

Select or Define your connection

If you don’t see the source you are looking for, try the ‘Help – Check for Updates’ engine. Be sure to install the ‘jtds driver.’ This will give you access to MySQL, SQL Server & Sybase. If you are migrating from SQL Server, be aware that the tool only supports migrations from SQL Server 2005 and older. Wouldn’t be surprised if this changes once Denali is formally released and ‘older’ 2008 installs are ripe for migrations. As it stands now, 2008 is pretty bleeding edge in a lot of SQL shops and I don’t see big demand for migrations there.

Now we get to select our database. You will notice that SQL Developer ignores the default MySQL data dictionary databases, kudos!

After we select the data to be migrated, we need to let the engine know how to treat the data type conversions that will happen. For example, will we

The Most Important Step!

If you need to make any changes, you can edit the default rules to create your own ‘user’ rules

Fun with data types!

If you are tempted to jump through this wizard as quickly as possible, I strongly urge you to stop here and think a bit. It doesn’t matter how easy the tool makes it to move your data if the date is truncated or converted in such a state as to be useless. It might make sense to test your migrations on smaller datasets before attempting a very large one to save you a lot of frustration later.

The last page is basically the ‘go’ page where you can double-check everything you have set and confirm the target database.

Where Are We Reading From, Where Are We Writing Too?

Alright, just one more click and we are good to go! Let’s confirm what we’ve asked Oracle to move for us.

Just one more click!

Move Tables Manually

If you want to control the tables migrated and where they are sent, you can simply connect to the source database, find your table, right-click, and say ‘Copy to Oracle.’

Copy Just One Table To Oracle

You’ll select your Oracle database to copy the table to. You do this by choosing a SQL Developer connection. Whichever schema the connection is tied to will determine where the table is copied. This ran fairly quickly for me, say 40,000 records in about a minute.

It’s not clear if this requires the repository to be setup, but I’m going to go with ‘No.’

Managing Migration Projects

You can open the ‘Migration Projects’ panel in SQL Developer under the ‘View’ menu. It will populate assuming you are connected to the database that houses the respository. You can view your migrations here, check out logs, space information, and even check the quality of the data involved? I tried this last piece and didn’t get very far, but I think that’s due to the migration I was working on not being 100% successful.

Migration Projects Panel

Parting Words

This worked very well the first time I ran through the wizard. I attempted to migrate the data a second time to a separate database and ran into a problem. The capture activity seems to get ‘stuck’. I recommend if you run into a problem and need to cancel the capture, to completely exit out of SQL Developer before trying anything else. I did not do this two times, and both times SQL Developer hung when going to Step 5 in the wizard.

Even with these problems, I would have little reservation recommending the tool. It’s free. It’s pretty intuitive. I’m going to assume the problems I ran into were my fault as I skipped all of the user docs. If I find out what the issue is, I’ll update the review accordingly.

If you’re on Twitter and have questions, feel free to contact me. @krisrice and @bamcgill from Oracle are also around and are very responsive.

I have done everything. It converted mySQL data to Oracle data owned by the repository user. How can I convert the meta data into Oracle physical data owned by a new user which is same as the ‘MYSQL’ user schema name? Thanks

That does not work. I created repsoitory (meta data owner called ‘MIG_USER’) and another user account called ‘NA_USER’ (to hold the converted physical data objects). After capturing the data, then I try hit convert using the right click. Then the wizrad shows up, then I point move the data to the final destination. It says it is converted, but the schema is not loaded at all. I thought the respository owner may have the converted objects, that is not true even. I am not what exactly is going on.

Migrate the Data to Oracle
The last step in the Migration Wizard is to migrate the data to the new database. Migrating the data is a process that copies the data from the third-party database to the new tables in the Oracle database. The Migration Wizard uses the same Oracle database connection required to run the scripts as it does to move the data.

To re-enter the Migration Wizard and move the data:

1.Navigate to the Converted Database Objects node
2.Select and right click on the node
3.Select Move Data…
This once again invokes the Migration Wizard and

1.The field labeled ” Source Connection” is for the connection in the third-party database
2.The field labeled ” Target” is the privileged users in oracle required to move the data to the newly created user.
3.Click OK.
Once complete, your data should now be in your newly created database schema.

It is working now. It was an access issue. The other DBA did not grant certain rights for the oracle user. When I handed over the dba priv for the new user, the migration started working. Neat product. Thanks much.

Hi,
I am in the process of migrating from ms sql server 2005 to oracle 11g. However, I am little confused here. I am new to this area of work. Migration was successful, however I am unable to verify it. Could you please help me with this?

I have a question regarding the ‘Copy to Oracle’ functionality in Oracle SQL Developer. We have a MySQL database which contains our Customer Services data. For reporting purposes every week I have to copy some MySQL tables to our reporting-schema in our Oracle database. For this I use the ‘Copy to Oracle’ functionality.

This works well, however I would very much like to automate this manual procedure I have to perform each week. However, I cannot find the PL/SQL procedure underlying the ‘Copy to Oracle’ function.

My question is: Where can I find the PL/SQL procedure underlying the ‘Copy to Oracle’ function? Or, if this is not accessible, how can I automate the ‘Copy to Oracle’ function, so that I do not have to manually copy the data from MySQL to Oracle each week?

Please sent me the full details screenshot for migrate the database from sql server to oracle.
while migrate can we create the new oracle database or sql developer will be create any script to create the new oracle database.

Anyways, you don’t need to have matching names for database schemas between SQL Server and Oracle. For the offline approach you can just update data move scripts in order to disable the constraints on the proper database schema.

Good day Jeff,
I’ve undergone a SQL Server to Oracle Migration via SQL Developer, it says the migration was successful however the actual tables don’t exist in the Oracle DB. I’ve emailed snips of the conversion issues noticed as well as the conversion database tables listed. Any assistance would be greatly appreciated.

I mostly write about working with Oracle databases, mostly. I currently work for Oracle, but I'm not an official spokesman, nor am I authorized to speak on behalf of Oracle Corp. In other words, what you read here are my words and ramblings.

If you've detected a bit of snark, that means you've been paying attention. +1 bonus credits for you!