How to migrate Oracle to MySQL

Intro

It may sound unbelievable, but I have found nothing when googling for some good HowTo regarding the migration from Oracle to MySQL. Fortunately based on various tips spread across numerous sites I have finally succeeded after almost 2 days thanks to the SQLyog software. Why not to share this experience then? The following are the components that were used by me in the migration process:

• CentOS 5.4 server with Oracle 11.1.0.6.0 - source
• Ubuntu 12.04.2 server with MySQL 5.5.29 - destination
• Windows Server 2008 R2 acting as an agent

Preparation

First of all as I was using the Windows server as an agent I had to configure both Oracle and MySQL DBMS that they're accessible via network. To accomplish that follow these steps:

1) Oracle:

According to some tips that I found on the Oracle endpoint the only thing you need to do is to check if the Oracle listener is running. If so, you should be able to connect remotely:

lsnrctl status

Indeed, I was able to connect. Then you can create a dedicated user that will be used in the migration process and grant him proper privileges:

SQL> create user migration identified by 'migration';

User created.

SQL> grant all privileges to migration;

Grant succeeeded.

2) MySQL:

• In /etc/mysql/my.cnf file change the value of bind-address attribute from 127.0.0.1 to IP address of the NIC
• Grant proper privileges to the user that will be used in the migration process based on the IP address of the agent. In my case I also created an empty database for that purpose:

mysql> CREATE DATABASE db;
Query OK, 1 row affected (0.06 sec)

mysql> GRANT ALL ON db.* TO migration@'1.2.3.4' IDENTIFIED BY 'migration';
Query OK, 0 rows affected (0.29 sec)

That's all we need to do on the source and destination sides. The rest of work is done by the agent. On a dedicated Windows box follow this steps then:

1) Install SQLyog MySQL GUI software. There is a 30 days trial version available to download for everyone. You don't need to register to download it. The only thing that you need to do is to provide a valid email address that a link and installation instructions are sent to you. The installation process is self explainary.

2) Install Oracle Database 11g Release 2 Client software. Be careful to select a proper platform and client version. Unfortunately, this time you need to be registered at Oracle to download the client software. The installation process is self explainary.

6) Select Any ODBC Source radio button from the Type of Data Source menu. Select System/User DSN radio button from the Select or Create an ODBC Data Source Name (DSN) menu. From the drop-down list you should be able to select the ORACLE DSN that we defined in step 4 of the Preparation section. Provide Username and Password used to connect into the Oracle database:

3) In the Oracle home directory (C:\app\Administrator in my case) you will find the product\11.2.0\client_1\network\admin directory. Go there and create tnsnames.ora file containing the information about your Oracle database instance. Most of those information you can find inside tnsnames.ora and listener.ora files inside your Oracle database home directory on the Oracle server. The most important data (IP address and the service name) have been highlighted:

ORACLE=
 (DESCRIPTION=
  (ADDRESS_LIST=
   (ADDRESS = (PROTOCOL = TCP)(HOST = 5.6.7.8)(PORT = 1521))
  )
 (CONNECT_DATA =  (SERVICE_NAME=DB)
 )
)

4) Go into Start menu on your Windows box and open Administrative Tools -> Data Sources (ODBC) tool. Select System DSN menu and press Add... button:

Select Oracle in OraClient11g_home1 driver from the list and press Finish button:

Provide a name for the connection by typing into Data Source Name field and select the connection defined in step 3 from the drop-down list called TNS Service Name 

Press OK button. Alternatively, you can test the connection by pressing Test Connection button and providing proper credentials:

Press OK button on the Data Sources (ODBC) tool window. 

Note: If in step 6 of the Migration section you get an error, then you need to create 32-bit ODBC DSN. In such a case, in step 4 of the Preparation section, instead of running the ODBC from the Start menu, run the following executable: C:\Windows\SysWOW64\odbcad32.exe

Migration

Now we're ready to perform the migration. Please, follow these steps to accomplish that:

1) Open SQLyog software and press the New... button to set up a new connection:

2) Provide a name for the new connection and press OK button:

3) Provide all necessary credentials to connect into your MySQL database and press Connect button:

4) SQLyog will open and you'll be connected into your database. You'll see it in the top left corner of the window. Right-click on the database name and select Import -> Import External Data:

5) A new window will appear. Select Start a new job and press Next > button:

6) Select Any ODBC Source radio button from the Type of Data Source menu. Select System/User DSN radio button from the Select or Create an ODBC Data Source Name (DSN) menu. From the drop-down list you should be able to select the ORACLE DSN that we defined in step 4 of the Preparation section. Provide Username and Password used to connect into the Oracle database:

7) Confirm all the settings by pressing the Next > button:

8) Select Copy table(s) from the data source radio button and press Next > button:

9) Select the required tables or simply press Select All button to select and migrate all tables from the database. Press Next > button (I'm sorry but I have unfortunately lost the screenshot from this post. Should be self-explainary anyway).

10) Specify Error handling settings according to your demands and press Next > button:

11) Specify when to run the job that you've just created, the logfile and press Next > button:

12) The migration job stars. You'll see the output and results in the main window. After successful migration press the Next > button:

13) You're done. Press the Finish button.

14) Verify that the data have been successfully migrated from Oracle to MySQL either from the SQLyog GUI or from the CLI on the MySQL server:

mysql> SHOW TABLES;
+--------------+
| Tables_in_db |
+--------------+
| APEX$_ACL    |
+--------------+
1 row in set (0.00 sec)