How To Load Data From Oracle to MySQL Using 2 Easy Methods

Oracle and MySQL are two of the most popular relational database management systems (RDBMS). A large number of businesses widely use both Oracle and MySQL. These databases share several key similarities as well as differences.

Many users migrate Oracle to MySQL as per their use cases. This article will help you understand two easy methods to help you set up this migration step-by-step, enabling you to leverage the advantages of both databases. You will also get a good overview of setting up an ETL Pipeline for data migration.

Introduction to Oracle

Oracle database, developed by Oracle Corporation, is a premier enterprise-level RDBMS known for its very high performance, great scalability, and rich functionality. It supports many data types, complex queries, and large-volume transactional processing. Oracle database is applied in many organizations worldwide for mission-critical applications, large-scale data analysis, and data integrity and security.

Introduction to MySQL

MySQL is an open-source, very popular relational database management system that is highly reliable and simple. Moreover, it also offers quite impressive performance. Developed by Oracle Corporation, MySQL is highly utilized in web applications and can provide great foreign key support for high-speed data access, scalability, and flexibility. It is greatly favored because of its simplicity and tremendous community support, thus being a go-to choice for any developer or business, whether small or large.

Differences between Oracle and MySQL

Oracle	MySQL
1. Oracle requires costly licenses	1. MySQL is free and open-source
2. Oracle offers advanced enterprise features	2. MySQL is lightweight but still powerful.

Advantages of Migrating Oracle Database to MySQL

Before performing the Oracle Database Migration, you might be interested in knowing what advantages MySQL has over Oracle. I have mentioned the benefits below for your reference.

• You can reduce the licensing and maintenance costs associated with Oracle by switching to MySQL, which is open-source and more efficient.
• You can leverage MySQL’s scalability features to handle growing data.
• MySQL has more straightforward configurations than Oracle’s feature-rich but complex environment.
• MySQL offers a variety of storage engines, while Oracle’s storage mechanisms are more rigid and integrated.

Methods To Set up Oracle to MySQL Migration

Method 1: Using Hevo Data

Step 1: Configure Oracle as your Source

• Connect Hevo Data with Oracle by providing a unique name for your pipeline and information about your Oracle database, such as its name, IP Address, Port Number, Username, Password, etc.

Step 2: Configure MySQL as your Destination

• Provide your MySQL database credentials, such as your authorized username and password, along with information about your Host IP Address and Port Number value.

Method 2: Manual Oracle to MySQL Migration 

Step 1: Set Up the MySQL ODBC Data Source

• Login to MySQL with your credentials.
• Launch the option for the ODBC Connector data source.
• Set up the Oracle ODBC data source to your MySQL database.

• Fill in the required details to connect the Oracle Server name, user, password, and database. Provide a suitable ODBC connection name. 
• Click on the Test button to test the connection. 

Step 2: Configure listener.ora in Oracle

• Configure listener.ora file from Oracle. Navigate to the Oracle installation directory, and you’ll find the listener.ora file under $ORACLE_HOME/NETWORK/ADMIN directory and add the following to it:

SID_LIST_LISTENER =
<Space>(SID_LIST=
<Space>(SID_DESC=
<Space>(SID_NAME=test)
<Space>(ORACLE_HOME=C:oracleproduct11.2.0dbhome_1)
<Space>(PROGRAM=dg4odbc)
)
)

• Update the location of ORACLE_HOME if the path mentioned is not your installation path.

Step 3: Verify dg4odbc Program

• The above configuration tells Oracle to use the dg4odbc program.
• If you face any issue, type dg4odbc in the command line to check whether that program exists. If you get an error as “Program not recognized,” add $ORACLE_HOME/bin to your path variable and relaunch the command line to check. 

Step 4: Configure inittest.ora. 

• The file will be created when you create a new SID in the listener.ora file. The file’s naming convention is init + SID name. Configure data source / SID for dg4odbc. Add the following to your inittest.ora file:

HS_FDS_CONNECT_INFO = test

• The above line specifies which ODBC connection to use. In this case, in step 1, you have created an ODBC connection to form the connection. 

Step 5: Configure tnsnames.ora file. 

• The file location is $ORACLE_HOME/NETWORK/ADMIN/TNSNAMES.ora. Add the following lines to the file:

TSLINK = 
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=localhost)
(PORT=1521)
)
(CONNECT_DATA=
(SID=test)
)
(HS=OK)
)

• In the above configuration, the HS=OK entry is very important, and you must ensure that you have used the same SID created on the listener.ora file.

Step 6: Restart Oracle Services

• Restart the Oracle Service and the TNS Listener Service.

lsnrctl reload

Step 7: Create a Database Link in Oracle

• Log in to the Oracle database with DBA rights (e.g., as SYS) and create the database link using the following syntax:

Create public database link link2mysqltest connect to "oracle" identified by "oracle" using 'TSTLINK';

Step 8: Test the Database Link

• Test the database link and click on Finish.
• Log in to MySQL and run the query below using the test link in the above steps.

select * from "test_table"@link2mysqltest

Limitations of Manual Oracle to MySQL Migration

• Even though the user only has to make a few configuration changes to set up migration, getting this migration right without deep Oracle administration knowledge is difficult. This might lead to Oracle throwing errors because multiple configuration parameters vary across different versions.
• Even after getting the migration right, the connection, in reality, has many constraints and rough edges concerning the SQL queries that can be executed. You will encounter problems with functions and data types that are unsupported at source and target databases.

You have seen the Oracle to MySQL migration challenges. Let’s get into the next one.

Why Migrate from Oracle to a MySQL Database?

• MySQL is more cost-effective compared to Oracle. It is an open-source database, and it does not require associated licensing costs.
• MySQL is simple and flexible due to its straightforward setup process and user-friendly interface. The syntax and commands in it are easy to learn and understand. Contrary to this, Oracle’s feature-rich PL/SQL language is complex.
• MySQL possesses high-performance capabilities and scalability. It can handle large amounts of data efficiently and maintain optimal database performance simultaneously.
• MySQL is an open-source community that provides a lot of troubleshooting and development support, resources, and documentation. It ensures MySQL remains reliable and up-to-date as it often releases updates and improvements to the MySQL database.
• MySQL easily integrates with various programming languages, frameworks, and tools and is compatible with multiple platforms and operating systems.

Challenges of the Migration Process

There are some issues that you might face while migrating your Oracle Database to MySQL.

Challenges	Solution
Since Oracle and MySQL have different data types and formats, compatibility issues can arise during migration.	You can use transformation tools and scripts to convert the data according to MySQL’s compatible data types.
There might be some advanced Oracle features and complex SQL queries that are not directly translatable to MySQL.	You can simplify or refactor complex queries and replace Oracle-specific features with MySQL-compatible alternatives.
Migrating large volumes of data can result in significant downtime and impact system availability.	You can plan for incremental data migration and synchronization to minimize downtime.

Conclusion

This article provided a step-by-step guide on manually setting up Oracle to MySQL migration or using Hevo. Along with the information on migration step by step, it also gives a comprehensive brief overview of each of these software.

However, there are certain limitations associated with the manual method. Suppose those limitations are not a concern to your operations. In that case, using it is an option, but if it is, then you should consider using the automated data pipeline platform, Hevo.

The transparent pricing and 24×7 support make Hevo the highest-rated data pipeline software on many review sites.

Take our 14-day free trial to experience a better way to manage data pipelines. What are your thoughts on moving data from Oracle to MySQL? Let us know in the comments.

Frequently Asked Questions