Oracle to MySQL Migration | Here Are 2 Effective Ways!

Migrating databases from one platform to another is a common yet complex task in the world of data management. Oracle and MySQL are two of the most widely used relational database management systems (RDBMS). Oracle to MySQL migration can bring numerous benefits.

However, migrating from Oracle to MySQL isn't as simple as exporting and importing data. This guide provides a 2 step-by-step ways to help you migrate from Oracle to MySQL efficiently and successfully. Additionally, we’ll cover essential Oracle and MySQL database backup steps to ensure a secure migration process.

Why Migrate from Oracle to MySQL?

✔ Cost Savings: Oracle is a powerful enterprise database, but its licensing fees can be expensive. MySQL, on the other hand, is open-source and free to use, making it a cost-effective alternative for businesses.

✔ Simplified Licensing: With MySQL, you no longer have to deal with complex licensing agreements and restrictions, making database management easier.

✔ Community Support: MySQL has a vast community of developers and contributors who actively support and enhance the database system, ensuring continuous improvements.

✔ Performance and Scalability: MySQL is lightweight and optimized for fast read-heavy operations, making it ideal for high-performance applications.

✔ Cross-Platform Compatibility: MySQL runs on various operating systems, including Windows, Linux, and macOS, ensuring better flexibility and integration.

Key Differences Between Oracle and MySQL

These differences highlight some of the challenges involved in Oracle to MySQL migration.

How to Migrate from Oracle to MySQL (2 Ways)

Migrating from Oracle to MySQL has its own set of challenges, especially in terms of SQL syntax, indexes, and stored procedures. However, a proper way can help overcome these challenges. Here are 2 effective ways for Oracle database to MySQL migration.

Way 1: Migrate from Oracle to MySQL Using Oracle SQL Developer

Oracle SQL Developer is a free tool that allows users to export Oracle database tables into different formats, including SQL insert statements, SQL loader format, XML, CSV, and more. Here’s how you can use it to migrate from Oracle to MySQL.

Step 1: Export Data from Oracle Database

1. Open Oracle SQL Developer and navigate to the Tools menu. Click on Database Export…

2. In the Export Wizard, select the Oracle connection you want to migrate.

3. Choose the necessary export options, such as Export DDL, Export Data, Add Force to Views, and SQL Format.

4. Under Format, select Insert and check Save as Single File. Choose a file name and click Next.

5. On the Types to Export page, select the database objects you wish to export (Tables, Views, Indexes, Constraints, etc.) and click Next.

6. If needed, apply filters for specific rows and columns. Otherwise, click Next to proceed.

7. Click Finish to generate the SQL dump file containing the exported Oracle data.

Step 2: Modify the SQL Dump for MySQL Compatibility

Before importing the exported file into MySQL, you must convert Oracle-specific data types to their MySQL-compatible equivalents.

Convert Oracle Data Types to MySQL

★ Other SQL Adjustments:

Lorem ipsum dolor sit amet, consectetur adipisicing elit. Esse expedita nesciunt quidem.

Replace double quotes ( " ) with backticks ( ` ).

Remove COMMENT ON statements (MySQL handles comments differently).

Remove Insert statements on Joined Views.

Update ALTER TABLE constraint statements to match MySQL syntax.

Example:

Original Oracle Query:

ALTER TABLE "EMPLOYEES" MODIFY ("EMP_ID" CONSTRAINT "EMP_ID_NN" NOT NULL ENABLE);

Converted MySQL Query:

ALTER TABLE `EMPLOYEES` MODIFY `EMP_ID` VARCHAR(30) NOT NULL;

Additionally, convert TO_TIMESTAMP function to STR_TO_DATE in MySQL:

TO_TIMESTAMP('6-FEB-01','DD-MON-RR HH.MI.SSXFF AM')

TO

STR_TO_DATE('6-FEB-01','%d-%b-%y')

Step 3: Import Data into MySQL

1. Create a new MySQL database:

CREATE DATABASE new_mysql_db;

2. Connect to MySQL and import the modified SQL dump file:

mysql -u root -p new_mysql_db < exported_file.sql

3. Verify the migration by checking if tables and data were imported successfully.

Way 2: Migrate Oracle to MySQL Manually Using ODBC Connector

For a more hands-on approach, you can use the Oracle ODBC Connector to directly transfer data from Oracle to MySQL.

Step 1: Configure ODBC Data Source for Oracle

1. Install and open the ODBC Data Source Administrator on your machine.

2. Click Add, select Oracle ODBC Driver, and configure the connection:

• Enter the Oracle database name.
• Provide username and password.
• Assign a connection name.
• Click Test to verify the connection.

Step 2: Configure Oracle Database for ODBC Connection

1. Locate the listener.ora file in the $ORACLE_HOME/NETWORK/ADMIN directory and add the following:

SID_LIST_LISTENER =

(SID_LIST=

(SID_DESC=

(SID_NAME=test)

(ORACLE_HOME=C:oracleproduct11.2.0dbhome_1)

(PROGRAM=dg4odbc)

)

)

2. Restart the Oracle service and TNS Listener.

Step 3: Create a Database Link to MySQL

1. Open an Oracle SQL session and run:

CREATE PUBLIC DATABASE LINK link2mysqltest

CONNECT TO "oracle" IDENTIFIED BY "oracle" USING 'TSTLINK';

2. Test the connection:

SELECT * FROM "employees"@link2mysqltest;

3. If the test is successful, you can now transfer data from Oracle to MySQL using:

INSERT INTO mysql_table SELECT * FROM "oracle_table"@link2mysqltest

How to Backup MySQL and Oracle in Advance

Migrating databases involves risks, including data loss or corruption. It’s crucial to back up your MySQL and Oracle database before proceeding. A robust MySQL backup software like AOMEI Cyber Backup ensures a smooth migration process by creating full, incremental, and differential backups of Oracle and MySQL databases.

★Key features of AOMEI Cyber Backup:

User-Friendly: The intuitive interface makes setting up backups quick and easy.

Backup Scheduling: Set automatic backup schedules to run at your preferred times.

Centralized Backup: Manage and automate SQL backups from a single, easy-to-use interface.

Wide Compatibility: Support Microsoft SQL (2005-2022), VMware ESXi (6.0+), and Hyper-V (Windows 8/8.1/10/11, Windows Server, and Microsoft Hyper-V Server 2012 R2 and newer).

AOMEI Cyber Backup also helps you backup VMware, Hyper-V, Windows computers, MS SQL database, and other critical business data. You can test it out for 30 days free!

Conclusion

Oracle to MySQL migration can bring numerous benefits, including cost savings, simplified licensing, and improved scalability.By following 2 ways, you can ensure a smooth and efficient Oracle to MySQL migrationwhile maintaining data integrity and performance.

It’s crucial to back up your database before proceeding. Using a professional backup solution like AOMEI Cyber Backup ensures data safety during migration.

FAQs on Oracle to MySQL Migration

1. Why should I migrate from Oracle to MySQL?

MySQL is an open-source, cost-effective alternative to Oracle, offering strong community support and high performance.

2. What are the main challenges in Oracle to MySQL migration?

The key challenges include:

• Compatibility issues with Oracle-specific features.
• Data type and syntax conversion.
• Performance tuning in MySQL after migration.

3. Can I migrate all Oracle features to MySQL?

Some advanced Oracle features may not have direct equivalents in MySQL, requiring custom workarounds.

4. How can I optimize MySQL after migration?

Optimize indexes, queries, and server settings, and monitor performance using MySQL Enterprise Monitor.