Since Oracle and MySQL are enhanced DBMS with wide set of features and capabilities, complete database migration from one system to anotheris not an easy task. However, some important benefits of MySQL over Oracle such as open source, flexible concept of storage engines, easy development and administration, force many companies and organizations to migrate their Oracle databases to MySQL server.
There are multiple approaches to database migration from Oracle to MySQL and this whitepaper explores the most popular of them.Let us start from manualmigration according to extract-transform-load (ETL) concept.
Although export of Oracle data into an intermediate format and next loading to MySQL is one of the cheapest and most simple forms of migration, it can involve a lot of manual efforts and is slower than automated migration process through dedicate tools. One of the most straight forwardoptions to export Oracle data is Oracle SQL Developer. The tool can export Oracle data in multiple formats such as SQL insert statements, MS Excel, XML, plain text, etc.
After the data is exported into a flat file, we can load it into pre-created MySQL database. It is supposed that MySQL database already has table with identical structure as the corresponding Oracle table to accept the data. The most straight forwardoption to import flat file into MySQL table is ‘LOAD DATA INFILE’statement:
LOAD DATA INFILE ‘data.txt’ INTO TABLE table_name FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\r\n’
IGNORE 1 LINES;
This statement will import the data from file ‘data.txt’ into table ‘table_name’ with respect to the specified separators.
As you may see, manual migration requires a lot of manual action such as creating tables, indexes and constraints and so there is high risk of human errors. Also, it does not migrate such sophisticated database objects as views, stored procedures and triggers. Those are reasons why many database migration specialists use dedicate commercial tools to migrate databases from Oracle to MySQL.
One of those tools is Oracle to MySQL developed by Intelligent Converters, a software vendor specializing in solutions for migration and synchronization for the most popular database management systems like MySQL, Oracle, PostgreSQL, MS Access, SQL Server, SQLite, FoxPro and IBM DB2. The converter provides high performance due to implemented algorithms of direct reading and writing data. It works with all modern versions of both DBMS including forks and SAAS variations. Command line support allows to script and to schedule the migration. Oracle to MySQL converter allows not only convertOracle data into new database but also merge or synchronize it with an existing MySQL database.
The program can filter data to convert as well as join multiple tables into a single one using SELECT queries. For those cases where there is no direct connection to the target MySQL server, the converter provides importing Oracle data into MySQL script file. Later that file may be transferred to MySQL server machine and imported into the database locally.