Migrate Oracle to MySQL

For years Oracle has been very popular and powerful DBMS, however it has strict licensing policy and high cost of maintenance. This is the key reason why many companies migrate from Oracle to MySQL. Besides, MySQL offers multiple benefits:

  • It is easy to learn and to use (administration, development, etc)
  • It has low total cost of ownership – both license and support expenses
  • It provides good integration with Web especially via PHP language
  • It has large community of professionals and a lot of support forums

At the same time, Oracle DBMS ideally fits for corporate scale data warehouses due to wide range of advanced features:

  • External authentication options like LDAP, Active Directory
  • Sophisticated capabilities of full-text search
  • A lot of tools, frameworks and APIs for administration and development purposes
  • Integration with Oracle family of business intelligence applications

So, in case of any plans to upscale the database into complicated warehouse system, it is not reasonable to migrate Oracle to MySQL.

How to Migrate

The easiest to understand (but not to implement) way to migrate Oracle to MySQL is extract-transfer-load (ETL) method that includes the following steps:

  • definitions of tables, indexes and constraints are extracted as DDL-statements
  • those statements are translated into MySQL format with respect to type mapping and naming rules and then are loaded to the target database
  • the data is exported into temporary storage (CSV or plain text files)
  • those files are transformed according to MySQL format and loaded into target database
  • triggers, views, stored procedures and functions are exported into SQL-statements and the source code
  • those statements and source code are translated according to the destination dialect of SQL and loaded it to MySQL database

These steps prove that database migration is a tedious and complicated procedure, especially for large databases having triggers, stored procedures and functions. Migrating database manually may cause data integrity loss or some other errors to the human factor. Fortunately, there are special tools automating the database migration process.

Oracle to MySQL Converters

One of the tools to migrate Oracle to MySQL with optimal price-quality ratio is offered by Intelligent Converters. This software vendor specializes on database migration and synchronization since 2001. The converter handles basic database objects with necessary properties and attributes:

  • Schemas
  • Data
  • Indexes and constraints
  • Views

The product supports all versions of Oracle and MySQL including such forks as MariaDB or Percona and popular SAAS variations: Amazon RDS, Google Cloud, Azure for MySQL. Command line support allows to automate Oracle to MySQL database migration. Besides transferring the data into new database, the tool can merge or synchronize existing MySQL database with Oracle data. When MySQL server does not allow direct connection, the program can export Oracle database to MySQL script that can be imported to MySQL using standard tools.

  1. MySQL console client: mysql –h server_IP_or_network_name -u user_name -p –vvf database_name < script_file
  2. phpMyAdmin web-interface:
  • Select the database in the left pane
  • Navigate to ‘SQL’ tab at the top of the window
  • Specify the script file in the corresponding field or snap ‘Browse’ button to find it through the directory tree.
  • Click ‘Go’ button.

It is important to remember there are some tasks that cannot be thoroughly automated when migrate Oracle to MySQL:

  • Although syntax of CREATE VIEW statement is similar for Oracle and MySQL, the first DBMS provides some advanced capabilities that can hardly be emulated in MySQL (PIVOT tables, limit rows in subqueries, etc).
  • Stored procedures, functions and triggers. Oracle PL/SQL is much more complicated than MySQL procedure language. It allows using comprehensive collection of built-in packages, sophisticated user-defined types and many other features missing in MySQL. All such Oracle stored procedures, functions and triggers must be manually refactored to comply with MySQL procedure language yet preserve the original capabilities.

Visit the homepage of Intelligent Converters to get more information about how to migrate Oracle to MySQL.