Oracle and MySQL database management systems belong to the class of popular relational databases supplied with a lot of advanced features, frameworks for administration routines and development APIs. At the same time, Oracle has much more strict licensing policy compared to MySQL as well as high total cost of ownership (TCO). Those are the primary reasons for many users to migrate Oracle to MySQL.
These are two most common methods of straight forward manual migration between the two databases:
- exporting Oracle database into SQL script via Oracle SQL Developer tool, modifying it according to MySQL syntax and loading into the target database
- linking MySQL to Oracle via ODBC Connector
Below all of these approaches are described in details.
Oracle SQL Developer
This standard tool coming as a part of Oracle installation allows to export data from Oracle tables into different formats such as Excel, SQL INSERT-statements, HTML, XML, PDF, plain text and others. Follow the steps below to migrate Oracle to MySQL using this tool:
- Launch SQL Developer and right-click Tools to start Database Export
- Once Export Wizard appears, choose the Oracle connection on the Source/Destination page and related options. Select insert from the drop-down for Format and then ‘Save As Single File’ and select the file name.
- Select the types of objects to migrate on the next page (Tables, Views, Indexes, Constraints).
- Choose target columns and specify filtering conditions or just click ‘Next’ to migrate entire tables.
- On the summary page click ‘Finish’ to start the task. The SQL dump file will be generated after this.
Once the resulting SQL script file is generated, it is time to modify it according to MySQL syntax:
- Do types mapping to convert data types missing in the target DBMS into appropriate equivalents
- Remove ‘COMMENT ON’ in the Create Table statements, and place comment lines beginning with — characters inside of the /*, */ symbols
- Replace double quotes by back ticks all around database object names
- Convert Oracle system functions that may occurred as default values or in CREATE VIEW statements into MySQL equivalents
Finally, create MySQL database and load the modified script into that database.
ODBC Connector
To migrate Oracle to MySQL using this method, start from logging in to MySQL and launching ODBC Connector data source. Then follow the steps below:
- connect the ODBC data source to the target MySQL server
- provide required information (user and password) to connect to the Oracle database and save ODBC connection with the appropriate name. Click ‘Test’ button to verify the created connection.
- find file listener.ora in $ORACLE HOME/NETWORK/ADMIN folder of the Oracle installation directory and add the details as follows:
SID_LIST_LISTENER = (SID_LIST= (SID_DESC= (SID_NAME=test)
(ORACLE_HOME = {path to your Oracle home})
(PROGRAM=dg4odbc)))
- edit tnsnames.ora file in $ORACLE_HOME/NETWORK/ADMIN folder by adding the statements:
TSLINK = (DESCRIPTION = (ADDRESS = (PROTOCOL=TCP)
(HOST=localhost)
(PORT=))
(CONNECT_DATA = (SID=test))
(HS=OK))
- restart Oracle and TNS Listener services
- log in to the Oracle database with sufficient privileges and create the database link:
create public database link link2mysqltest connect to {oracle user} identified by {oracle password} using ‘TSTLINK’;
- Log in to MySQL server and run the command:
select * from {table name}@link2mysqltest
Oracle to MySQL Migration Tools
All the migration methods specified above work good, but Oracle and MySQL are sophisticated DBMS and often used for large corporate-scale databases with complex logic integrated. Therefore, any option to migrate Oracle to MySQL manually or semi-automated may take a lot of time and resources. It is important to remember about high risk of losing data integrity or breaking database logics during manual migration because of human factor. To avoid these risks, it is greatly recommended to use the software that is specially designed to automate Oracle to MySQL database migration.
One of tools to migrate Oracle to MySQL is offered by Intelligent Converters software company. They are one of world’s leaders in the field of database migration and synchronization since 2001. Their solution supports all recent versions of on-premises and could Oracle and MySQL including such forks as MariaDB or Percona.
Other features:
- Intuitive wizard-style interface allows to run migration without any skills in database administration
- Support for all major database objects excluding stored procedures, functions and triggers
- Storing all migration settings into profile to simplify the next run
- Command line support to script and schedule Oracle to MySQL migration
- Option to merge or synchronize existing MySQL database with Oracle data
- Complete customization of types mapping and other conversion rules
Another powerful feature of Oracle to MySQL converter is migration resulting row sets of SELECT-queries. Using this feature the person responsible for database migration can filter rows, rename columns or even merge multiple tables into single one. Here are few examples:
- Filtering records: SELECT * FROM ThisTable WHERE KeyCol > 100 and KeyCol < 1000
- Renaming particular columns: SELECT column1 AS Name, column2 as Phone FROM Customers
- Combining columns from two different tables into the single one: SELECT Orders.ID as OrderID, Customers.Name as CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.ID
Sometimes MySQL server does not allow remote connection for security reasons. For those situations, the tool offers two-steps option to migrate Oracle to MySQL. First step to export the source database into MySQL script as collection of CREATE and INSERT statements. Second step is to import that script to MySQL server manually using standard tools:
- MySQL console client: mysql –h server_IP_or_network_name -u user_name -p –vvf database_name < script_file
- phpMyAdmin web-interface:
- Click on the appropriate database name in the left pane
- Click ‘SQL’ tab on the top of the screen
- Enter full path to the script file in ‘Path of the textfile’ field or snap ‘Browse’ button to find it through the directory tree.
- Click ‘Go’ button.
Visit official page of Intelligent Converters to learn more about how to migrate Oracle to MySQL and related software.
