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)





26 comments:

  1. Hi,

    Great information and clear instruction. BTW, does this migration also migrate data into each table or just the schema?

    Thanks.

    ReplyDelete
    Replies
    1. @Anonymous:

      It should migrate both the schema and the data. That's how it worked in my case at least ;).

      Delete
  2. Hi,

    thank you for interesting post! Please let me know if the tool converts views, triggers and stored procedures? If not, have you tried Oracle-to-MySQL by Intelligent Converters? Vendor says it supports views but in full (paid) version only. But I don't want to pay before know that is really work. I would appreciate any information you can give!

    Hans

    ReplyDelete
    Replies
    1. @Anonymous:

      I've no idea. I encourage you to contact the SQLyog support at https://www.webyog.com/ anyway. In my case it just worked and I was able to connect my Tomcat server into the MySQL server instead of Oracle.

      Delete
  3. this is good for migration oracle schema and data, on the site epworks.altervista.org I found a solution to migrate an application pro*c/c++ from Oracle to Mysql without ricompilation the application

    ReplyDelete
  4. Hi Tytus Kurek,
    This is good post.

    can it convert PL/SQL code (procedures, packages, functions and triggers) to MySQL supported syntax ?
    What will happen if Non-ANSI compatible SQL functions used in Oracle Database ?

    ReplyDelete
    Replies
    1. @Anonymous:

      I've no idea. I encourage you to contact the SQLyog support at https://www.webyog.com/ anyway.

      Delete
  5. on the page http://epworks.altervista.org/joomla/porting/docs/porting/2 you can see the limitations

    ReplyDelete
  6. It could not migrate data to MySQL DB.

    ReplyDelete
  7. The library does not need to migrate data to Mysql but to run an application Pro*C/C++ using the db Mysql

    ReplyDelete
  8. Not so far I have found new cool tool to work with mySQL - Valentina Studio. Its free edition can do things more than many commercial tools!!
    I very recommend check it. http://www.valentina-db.com/en/valentina-studio-overview

    ReplyDelete
    Replies
    1. @Anonymous:

      Thanks for sharing that.

      Delete
    2. Can this valentina studio migrate Oracle schema into mySQL?

      Delete
  9. Thanks for the nice article. Good to learn how to migrate Oracle to MySQL using free tool. I myself was comfortable in using this tool

    ReplyDelete
  10. Thanks for the article,
    but an issue: if use the trial version (i'm using version 11.33 32 bit ) when i try to migrate all my 100 tables , i see this messages: This is a TRIAL version you can select only two tables at the time !!!
    so i have execute 50 batch for migrate all tables ....

    ReplyDelete
    Replies
    1. You're right. But that's the only working solution that I found when writing this article.

      Delete
  11. Good Article. Nice to know you can do the migration using free trial edition. I am using this paid tool for migration

    ReplyDelete
  12. for migration why we have need full privileges in oracle database.

    ReplyDelete
  13. great information....gone through only this blog and I was able to move my whole schema in one day.

    Thanks Tytus Kurek for sharing this information.

    Regards
    Akshay Zunke

    ReplyDelete
  14. Great information. I am trying to use this to migrate from Oracle to MySQL. I can not create a migration user. I get a message missing or invalid password. Thank you for your help.

    ReplyDelete
  15. Thank you very much for great information. I am using SQLYOG to migrate data from Oracle to MYSQL. I did migrate most of the tables but few tables are giving me error # 1215 and 1825 Which says that failed to add foreign key constraint. Any suggestions, how to fix this problem? Thank you very much for your help.

    ReplyDelete
  16. I am using ESF Database Migration Toolkit to help me migrate Oracle to mysql, it is very easy and quickly.

    https://www.easyfrom.net/articles/oracle_to_mysql/

    ReplyDelete
  17. Hello,
    I would like to use your information to convert Oracle XE 11g to MySQL. Unfortunately, the article's images are not displayed. Could you fix it please? Thank you

    ReplyDelete
  18. Your blog has given me that thing which I never expect to get from all over the websites. Nice post guys!

    Web Design

    ReplyDelete