How to Migrate from Oracle to MySQL

What is Oracle?

Oracle is a popular & widely used RDBMS (Relational Database Management System) created by Oracle Corporation. Oracle’s Relational Database Management System (RDBMS) is a Multi-Model System that was built primarily for Data Warehousing and Enterprise Grid Computing. It is also cross-platform i.e, it can run on a variety of operating systems. It was the first RDBMS system designed specifically for companies to manipulate records in a database.

Because of its cost-effective Data Management Solution, Oracle has become the database of choice for almost all Enterprises. Oracle has Structured Query Language (SQL) functionality, allowing users to easily interact with the database. In comparison to other Databases, Oracle is extremely scalable, portable, and readily configurable. Anyone with a basic understanding of SQL can readily execute queries. It also allows for the simple transfer of data between databases.

What is MySQL?

MySQL is a popular Open-Source Relational Database Management System based on SQL – Structured Query Language. Oracle Incorporation is responsible for its distribution, maintenance, and development. The application is often leveraged for numerous activities such as including E-commerce, Warehousing, and Logging Applications. It houses support for a variety of Database-based objects. This includes triggers, cursors, indexes, views, tables & even procedures.

MySQL utilises a basic Client-Server Model to aid users in handling their Databases storing data across tables in the form of rows & columns. It employs the well-known query language, Structured Query Language (SQL), which enables users to perform operations such as the CRUD (Create, Read, Update, and Delete) operations. Despite the fact that MySQL is Open-Source and free for everyone, it has some premium features that Oracle exclusively offers to customers who are ready to pay for its usage.

Setting up Oracle to MySQL Migration

Two methods to set up Oracle to MySQL Migration explained in this article are as follows:

• Manual Method
• Using Specific Tools

1)  Manual Method

The steps followed for setting up the migration are as follows:

• Step 1: Log in to MySQL using the appropriate credentials.
• Step 2: Launch the ODBC Connector Data Source option.
• Step 3: Configure the Oracle ODBC data source to connect to your MySQL database.
• Step 4: Now add the necessary information, such as the Oracle Server name, user, password, and database to which you want to connect. Give your ODBC connection a suitable name.
• Step 5: Click on the “Test” button to test the connection.
• Step 6: Configure the Oracle listener.ora file.
• Step 7: Go to the Oracle installation directory. The listener.ora file can be found under the $ORACLE HOME/NETWORK/ADMIN You can add it as follows:

SID_LIST_LISTENER =

<Space>(SID_LIST=

<Space>(SID_DESC=

<Space>(SID_NAME=test)

<Space>(ORACLE_HOME=C:oracleproduct11.2.0dbhome_1)

<Space>(PROGRAM=dg4odbc)

• Step 8: If the path specified is not your installation path, you can change the location of ORACLE_HOME.
• Step 9: The configuration above instructs Oracle to use the dg4odbc
• Step 10: If you come up with any problems, you need to type dg4odbc in the Command Line to check if the program exists or not.
• Step 11: If you receive an error stating “Program not recognised,” add $ORACLE HOME/bin to your path variable and restart the command line to check.
• Step 12: Configure ora. When you add a new SID to the listener.ora file, the file is created automatically. The file’s naming convention is init + SID name.
• Step 13: Set up the data source / SID for dg4odbc.
• Step 14: To your ora file, add the following:

HS_FDS_CONNECT_INFO = test

• Step 15: The previous line defines the ODBC connection to be used. In this case, you have created an ODBC connection in step 1 to form the connection.
• Step 16: Configure the ora file. The file is located in the directory $ORACLE HOME/NETWORK/ADMIN/TNSNAMES.ora.
• Step 17: You can add the following lines to the file:

TSLINK =

(DESCRIPTION=

(ADDRESS=

(PROTOCOL=TCP)

(HOST=localhost)

(PORT=1521)

(CONNECT_DATA=

(SID=test)

(HS=OK)

• Step 18: The HS=OK entry in the preceding configuration is essential. Further, you must keep a check that you have leveraged the same SID as defined in the listener.ora file.
• Step 19: Restart the Oracle and the TNS Listener Service.
• Step 20: Once Oracle is up and running, you can create a database link in Oracle. Log in to the Oracle database with DBA privileges (e.g., as SYS) and build the database link using the following syntax:

 create public database link link2mysqlt connect to “oracle” identified by “oracle” using ‘TSTLINK’;

•  Step 21: Test the database link and then click on the “Finish” button.
• Step 22: Log in to MySQL and perform the query given below, which uses the test link created in the preceding stages.

select * from “test_table”@link2mysqlt

2)  Using Specific Tools

The steps followed for setting up Oracle to MySQL Migration using specific tools are as follows:

A)  Exporting Data from Oracle

Exporting existing Oracle data is often one of the Cheapest Modes of Migration, although it requires more manual input and is slower than utilizing a migration tool. Oracle SQL Developer is a popular tool for exporting data from Oracle. The Oracle SQL Developer tool can export data from Oracle tables in a variety of formats, including Excel, SQL insert commands, SQL loader format, HTML, XML, PDF, TEXT, Fixed text, and many more.

SQL*Plus can also be used to select, format, and spool data to a file.

The following example spools out a CSV (comma separated values) file that may be imported using LOAD DATA INFILE or created as a CSV table for immediate access.

set echo off space 0 pagesize 0 newpage 0 feed off head off trimspool on

spool oracle_data.txt

select coln1 || ‘,’ || coln2 || ‘,’ || coln3

  from tab1

  where coln2 = ‘ABC’;

spool off

“set colsep” command can also be used if commas are not being used in the code. For example:

set colsep ‘,’

set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on

spool oracle_data.txt

select coln1, coln2, coln3

  from tab1 where coln2 = ‘ABC’;

spool off

B)  Importing Data into MySQL

After exporting the data from Oracle into a Flat File, then it may be imported into MySQL. This step assumes that an identical schema structure was created in the MySQL database to accept the data.

The LOAD DATA INFILE command is the most commonly used method for reading a flat file into MySQL. This is of the following form:

LOAD DATA INFILE ‘info.txt’ INTO TABLE table_name FIELDS TERMINATED BY ‘,’  ENCLOSED BY ‘”‘

   LINES TERMINATED BY ‘rn’

   IGNORE 1 LINES;

The above piece of code will read the exported data from the ‘info.txt’ file into the table ‘table_name’ using the specified separators.

A quicker method for importing data into MySQL is to use the CSV Storage Engine Capabilities. This necessitates re-creating the table with the appropriate schema and the storage engine specified as CSV. For example:

CREATE TABLE my_test_table (

Name (Full) varchar(50)  NOT NULL,

location varchar(100) NOT NULL,

zip-code CHAR(5) NOT NULL

ENGINE=CSV;

• Step 1: Once the table is created, a .csv file appears in the Data Directory corresponding to the table ready to store data.
• Step 2: To update the table information, you can copy the exported CSV File over the existing ‘csv‘ data file and execute FLUSH TABLES.
• Step 3: You now have access to the data. You can also use an INSERT INTO new table… SELECT to quickly read data from the CSV table into a more suitable table for your production system.

Conclusion

This blog introduces the process to set up Oracle to MySQL Migration in detail. It also gives a quick overview of Oracle and MySQL.