Data migration from MySQL to Oracle server

March 4th, 2020

Data migration is a tiresome task requiring a great deal of manual activity. In this article, we will examine how to migrate MySQL data to the Oracle server, using Data Export feature built-in dbForge for MySQL products. In the given worked example, we will move data from the actor table and the query result from the sakila database that exist on MySQL server to the SCOTT schema on Oracle server.

Data Export Feature

Data Export feature is part of dbForge Studio for MySQL and dbForge Fusion for MySQL products. It represents a customizable tool for exporting your workloads and data to new types of data storage and processing systems quickly and easily. The tool can boast an intuitive GUI with a bunch of advanced options that help you effortlessly migrate your data.

You can also migrate data from the MySQL database to any other DBMS using a pre-installed ODBC driver. You can move both data from the whole table (view) or data collected from several tables as the result of the query execution. Data export is painless due to the convenient Data Export Wizard.

How to move data using Data Export

To move the data, you need to follow the steps, shown in the Data Migration Plan picture. Let us examine each step in detail.

Step 1. Select export format

Right-click the actor table in the Database Explorer window and select the Export Data command from the menu that appears or execute the query and after that right-click the grid in the Data window and select Export Data.

Then in the Data Export Wizard that opens, select ODBC format to export data.

Selecting export format

Step 2. Select data to be exported

When performing export tasks with the tool, you can select:

  • connection
  • database
  • table and/or view to be moved.
Select data to be exported

Step 3. Set ODBC data provider options

On the Options tab of the Data Export Wizard, you need to provide:

  • Data Source name
  • A connection string
  • Login information.

For convenience, you can test the connection specified before running an Export job.

Set driver options

Configuring ODBC driver

To migrate MySQL data to Oracle server we will use ODBC Driver for Oracle from Devart. You need to configure its connection before proceeding with data export. You can do it right from the Data Export Wizard. To start with, click the Build button next to the Use a connection string field.

Next, in the Select Data Source window that opens, go to the Machine Data Source tab and press the New button.

Machine data source

The Create New Data Source window appears.  Press the Next button to proceed with setting the driver.

Creating new data source for migration

Now you need to select a driver to connect to the server. Select Devart ODBC Driver for Oracle and press the Next button. Then press Finish.

Selecting ODBC driver for migration

The driver configuration window opens where you need to specify the data source name, provide its description (optionally), enter the server name, user ID and password. You can press the Test Connection button to test the connection to the Target server. Press OK to save your settings. 

ODBC Driver settings

You are back to the Select Data Source window where the path for data migration to Oracle has appeared. Press OK to finish creating the data source.

ODBC driver selected as source

Now you are in the Data Export wizard again and the connection string has been created.

Step 4. Choose a destination table

On the Table tab of the wizard, you select a destination table for export.

Note, that you can select from a list of existing tables as well as create a new table and provide its name.

Choose a destination table

Step 5. Select columns to be exported

On the Data formats tab, you can select columns you want to export.

Select columns to be exported

Step 6. Choose a range of rows to be exported

It is not necessary to export the whole table. You can select to export all rows, export the selected rows only, or export a range of rows. The selective export feature facilitates data transfer and significantly saves time.

Choose a range of rows to be exported

Step 7. Specify errors processing behavior

The Data Export Wizard allows customizing errors handling by selecting to:

  • prompt a user for an action
  • ignore all errors
  • abort at the first error.

You can also select to create a log file and set a path to it.

Specify errors processing behavior

Step 8. Save the template

The Data Export wizard for dbForge for MySQL products allows saving templates for recurring export scenarios. You don’t need to waste time configuring data export over and over, just use templates to apply options to any number of export jobs.

Save the template

Step 9. Enjoy the result

As a result of our migration efforts, the actor table and the result of the query have appeared on the Oracle server. 

Data appeared on Oracle

Conclusion

dbForge Studio for MySQL has advanced built-in features for moving MySQL data between source and target systems and can significantly assist teams in their MySQL data migration efforts. The MySQL Import and Export functionality provided in the Studio makes it possible to transfer MySQL data between different databases, servers, and IDEs. Simplify your database migration with dbForge Studio for MySQL.

Leave a Comment