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 the ODBC format to export data.
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.
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.
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 Build 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 click New.
The Create New Data Source window appears. Click Next to proceed with setting the driver.
Now you need to select a driver to connect to the server. Select Devart ODBC Driver for Oracle and click Next. Then, click Finish.
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 click Test Connection to test the connection to the Target server. Click OK to save your settings.
You are back to the Select Data Source window where the path for data migration to Oracle has appeared. Click OK to finish creating the data 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.
Step 5. Select columns to be exported
On the Data formats tab, you can select columns you want to export.
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.
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.
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.
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.
Conclusion
dbForge Studio for MySQL has advanced built-in MySQL Import and Export functionality that handles the data transfers between diverse databases, servers, and IDEs. However, if your work duties include resolving challenges on various database management systems, dbForge products will be helpful again.
The latest product presented by the Devart team is dbForge Edge which covers all database-related tasks – not just data migration – on MySQL, SQL Server, Oracle, and PostgreSQL. You can try Edge for free and test all its capacities in your workflow.
