migrate oracle to mysql

The main purpose of this blog is to share my input into an Open Source development and my experience and knowledge gained

during almost 5 years of professional work in ITC industry.

How to migrate Oracle to MySQL

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

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.

Intro

Preparation

Class VI to X CBSE Notes

thedigilibrary.com/CBSE-NOTES

Select Language

Powered by Translate

Translate this page

Tytus Kurek

Kraków, Poland

5 years of professional

experience in ITC industry.

My experience covers

numerous professions and varies from

regular helpdesk support, system and

network administration to professional IT

projects, resources and services

management, and even more ... . My

interests include leading ICT trends like

Cloud Computing, Big Data, Mobile

Solutions and more.

View my complete profile

About Me

Master of Telecommunications

ITIL - Foundation

LPIC 3 - Senior

VCA - Cloud

VCA - Data Center Virtualization

CCNP - Security

CCNA - Routing & Switching

MCTS - MS Exchange 2010

Education and Certification

Subscribe to this page

Email address... Submit

Search

Search ...

HowTo (9) CaseStudy (8)Ubuntu (5) Bug (4) Server Windows

Tags

1 More Next Blog» Create Blog Sign In

of 12How to migrate Oracle to MySQL |My Technical Blog

8/8/2014http://tkurek.blogspot.in/2013/04/migrate-oracle-to-mysql.html

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

2012 (3) databases (3)

Ask Tytus Kureknow

Tytus Kurek

Partners



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

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:

Migration



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)

+1 Recommend this on Google

19 comments:

Anonymous August 5, 2013 at 7:27 AM

Hi,



Replies

Reply

Replies

Reply

Replies

Reply

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

the schema?

Thanks.

Reply

Tytus Kurek October 26, 2013 at 5:15 AM

@Anonymous:

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

Anonymous August 20, 2013 at 6:32 AM

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

Reply


@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.

Anonymous September 10, 2013 at 12:29 PM

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

Reply

Anonymous September 11, 2013 at 12:09 AM

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 ?

Reply


@Anonymous:

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

anyway.


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

Reply



Replies

Reply

Replies

Reply

Replies

Reply


@Anonymous:

Thanks for sharing that.


It could not migrate data to MySQL DB.

Reply

Anonymous September 14, 2013 at 12:58 PM

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

Mysql

Reply

Anonymous October 1, 2013 at 3:09 AM

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

Reply


@Anonymous:

Thanks for sharing that.

Anonymous November 14, 2013 at 3:32 AM

Can this valentina studio migrate Oracle schema into mySQL?

Ashwini Ahire December 13, 2013 at 11:10 PM

Thanks for this document :-)

Reply

Anonymous January 3, 2014 at 8:45 AM

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

Reply

Anonymous March 4, 2014 at 4:03 AM

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 ....

Reply

Tytus Kurek March 4, 2014 at 4:49 AM

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



Newer Post Older PostHome

Subscribe to: Post Comments (Atom)

Enter your comment...

Comment as: Select profile...

Publish Preview

Create a Link

Anonymous July 15, 2014 at 5:53 AM

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

migration

Reply

Links to this post

Template images by enot-poloskun. Powered by Blogger.



migrate oracle to mysql

Documents