Oracle to MySQL Tutorial

[Oracle to MySQL Converter] [Introduction to Migration] [FAQ] [Command line]

The process of database migration from Oracle to MySQL, MariaDB or Percona consists of the following steps:

Select Migration Mode
Connect to Oracle
Connect to MySQL Server or MySQL script file settings
Conversion Settings
Select Tables
Select Views

Step 1 of 6. Select Migration Mode

On this step user should specify how to convert Oracle database into MySQL format. Oracle to MySQL converter provides two options of database migration:

Move to MySQL server directly - choose this option to migrate database from Oracle to MySQL server directly. It requires live instance of MySQL server and sufficient permissions to write into a database on the target server. See Connect to MySQL Server section for related information.

Store into dump file - choose this option to export the Oracle database into a local MySQL script file. The resulting file contains MySQL data definition statements to create tables and 'INSERT' statements to fill tables with the data. See Import MySQL dump files article for related information.

Step 2 of 6. Connect to Oracle

This page allows specifying the necessary settings to establish connection with Oracle server:

Server - IP address or network name of Oracle server. If Oracle to MySQL converter has been installed on the server machine, "127.0.0.1" or "localhost" can be specified in this box.

Database name - enter the same title that is used by SQLPlus or any other standard Oracle tools to point the database.

Port - port for connecting to Oracle. It must be specified only when the value differs from the default value 1521.

Username - Oracle user name

Password - Oracle user password

Oracle to MySQL Migration: Connect to Oracle Server

Oracle to MySQL migration tool provides two methods of connecting to Oracle server: direct and via TNSNAME.ORA entry. To use the first method enter information in all fields except the port if it is configured by default (1521). The second method requires leaving both "Server" and "Port" fields empty and enter Oracle service name as it appears in TNSNAME.ORA file in "Database" field.

Sometime user may get the following error: "Oracle data source does not exist. Check to verify correct name was given." despite of the fact that all Oracle connection settings have been specified properly. It means the converter failed to connect to Oracle server using TNSNAME.ORA. To bypass the error leave both "Server" and "Port" fields empty and enter in "Database" field the connection string as follows:


(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=IP address of Oracle server)(PORT=the port)))

Of course, it is required to replace "IP address of Oracle server" and "the port" by actual values.

Step 3a of 6. Connect to MySQL Server

If "Move to MySQL server directly" option has been selected on the first window, this page appears next to enter the necessary settings for connection with the server. Access to MySQL migration tool can connect to MySQL server using local or remote mode. Local mode is used to connect server launched on the same machine where the program is running. Remote mode allows to connect server launched on another computer.

By default, Oracle to MySQL converter uses local connection mode. To use remote connection, select "Remote" radio button and specify host and port values. Host can be either network name or IP address. Port value is necessary only if it differs from the default 3306. If you need to specify custom port for local connection, select "Remote" and type "localhost" or "127.0.0.1" into "Host" box.

Oracle to MySQL Migration: Connect to MySQL Server

Finally, user name and password are entered. If the target server allows anonymous connection, these fields can be empty.

Step 3b of 6. MySQL script file settings

This wizard page allows to specify how the resulting dump file should be formatted. There are following settings available:

MySQL version - determines the syntax of SQL statements. 'Old' versions put more restrictions. 'Old' syntax is accepted by 'new' versions of the server but not vice versa. Use 'old' syntax whenever you are not sure about the version of MySQL server.

Line breaks - specifies how to separate text lines in the dump file. 'MS Windows style' option means to use '\r\n', 'Unix style' means to use '\n' as end of line.

Add lock for write around 'INSERT' statements - makes import of the dump file into database much faster. To use this feature MySQL requires LOCK TABLES and SELECT privilege on the involved tables.

Insert 'CREATE DATABASE' statement

Insert 'CREATE TABLE' statements - always select this option in order to get valid dump file. The only exception is appending the data to existing tables.

Insert comments

Oracle to MySQL Migration: Script File Settings

See Import MySQL dump files article to learn how to import script file to MySQL server.

Step 4 of 6. Conversion Settings

This wizard page is designed to enter MySQL database name or path to a dump file (depending on which conversion scenario you choose). If the name of existing MySQL database or dump file is specified it will be overwritten. Also, the following settings of Oracle to MySQL converter can be specified here:

Convert table definitions only - select this checkbox to convert only table definitions of Oracle database. All the data will not be migrated.
Skip converting indexes - select this checkbox if you don't want to convert indexes
Make all strings case sensitive - select this checkbox to make CHAR and VARCHAR fields sorted and compared in case-sensitive fashion. You can also try this option to bypass "Duplicate field values being used for an unique index were found..." error.
View tables owned by the user - by default the program lists all tables found in the database (table names look like SCHEMA.TABLE). Select this checkbox to work with tables owned by the specified Oracle user only (do not append schema to table name).

The sufficient privileges to create new database on MySQL server are required. Otherwise, Oracle to MySQL converter can import the source data into an existing MySQL database. See Virtual Server User Notes for related information.

Step 5 of 6. Select Tables

On this page Oracle to MySQL converter allows user to select tables and to compose filtering queries for migration to MySQL server.

To add new Oracle table for converting into MySQL format, select it in "Available tables" list and click "Add" button (or double-click the selected item). Use "Add all" button to add all tables available in Oracle database. To remove a table from "Selected tables" list highlight it and click "Remove" button. Use "Remove all" button to remove all items from "Selected tables" list.

Use "Add Query" button for composing SELECT-query to filter, to group or to merge data that will be migrated to MySQL. See Using Queries article for the further information.

Oracle to MySQL Migration: Select Tables

To edit table or query double click the corresponding item in "Selected tables" list box. If it is a table, Oracle to MySQL converter displays the dialog box to edit table attributes and to define custom type mapping. See Edit Table article for the further information. If selected item is a query, Oracle to MySQL converter displays the dialog box to edit SQL-statement of the query.

Step 6 of 6. Select Views

This wizard page is designed to manage list of Oracle views. "Available queries" list box contains all available queries of the source database except those are selected already.

To add new Oracle view for converting into MySQL format, highlight it in "Available queries" list box and click "Add" button (or double-click the selected item). Use "Add all" button to add all available Oracle views. To remove an item from "Selected queries" list you have to highlight it and click "Remove" button. Use "Remove all" button to remove all items from "Selected queries" list box.

Sometimes it is important to convert views in the particular order to preserve dependencies. Use "up" and "down" buttons in right part of the wizard page to organize Oracle views in the appropriate order.