How to Migrate MySQL to Oracle Database with Oracle SQL Developer?

MySQL and Oracle database are both relational database management systems owned by Oracle Corporation, but they are very different when used. The one dramatic difference between the two lies in the license. Oracle database is made commercial while MySQL is an open-source database.

Why Migrate MySQL to Oracle Database?

MySQL is free of charge for businesses of all sizes, which has great operating system support and can be modified as desired for individual environments. It is a great candidate when your database doesn't need to be scaled up very much, and you are on a tight budget. MySQL is also a better option when the concurrency level is low or when just simple queries are needed.

However, MySQL is not a competitive option for a large environment since the database doesn’t integrate with other client applications and imposes a high load on the server from its features. Meanwhile, MySQL is slower than the Oracle database.

While Oracle database wins when it comes to large-scale IT infrastructure and supports large quantities of data. Oracle database enables data partitioning, flexibility in transaction control, integration with email services, better identity management, and user control. It also has a larger storage space, concurrency pool, and fewer operation costs than MySQL.

Thus, MySQL database users may think about migrating to the Oracle database for deployment scale (see how to convert Oracle to MySQL). In this article, there are two database migration methods for you to try.

Migrate MySQL to Oracle Using Oracle SQL Developer

Prerequisites:

Install MySQLConnector/J release on the system with SQL Developer installed.

Download and unzip SQL Developer from the Oracle website.

Notice:

Convert from the existing MySQL to the Oracle database: create a user named MySQL database in Oracle, select the default table space, and the MySQL data will be imported to the user.

Import the SQL file exported from MySQL to the Oracle database: you need to restore the file to a MySQL database first, and then convert the restored MySQL database to an oracle database. Create an Oracle user with the same name as the MySQL database to prevent the user table space from being full and affecting data import.

1.     Launch MySQL server and Oracle server, backup the source and target databases for precaution.

Note: backup MySQL and Oracle together and manage them through a single console of Vinchin Backup & Recovery.

2.     Start the Oracle SQL Developer, navigate to Tools> Preferences> Data Modeler Third Party JDBC Drivers.

3.     Specify the MySQL JAR file location's whole path name, then restart the SQL Developer.

4.     Connect to the MySQL database.

a.     Go to Connections> right-click the root node> New Connection.

b.   Click the MySQL tab and enter all the connection details, then Test it.

c.     Click Save.

5.     Connect to the new Oracle.

a.     Go to Connections> right-click the root node> New Connection.

b.     Click the Oracle tab and enter all the connection details, then Test it.

c.     Click Connect. See the connection under the Connections tree view. Remove all of the existing tables and indexes to make sure that all of the tables are empty.

6.     In the SQL Developer, go to Tools> Migration> Migrate to start the wizard.

7.     On the Repository page, connect to the MySQL server.

a.     Click+.

b.     Enter the connection details of MySQL like superuser and SID. Test the connection.

c.     Click Save> Next.

8.     On the Project page, enter the access details and click Next.

9.     On the Source Database page, choose the Mode as Online, and the MySQL connection created before as the source connection. Click Next.

10.  On the Capture page, choose the MySQL database and click Next.

11.  On the Convert page, accept the default configurations and click Next.

12.  On the Target Database page, choose the Mode as Online, and the Oracle connection created for the target database connection.

13.  Deselect the drop target objects option, and click Next.

14.  On the Move Data page, choose the source and target connection and click Next.

15.  On the Summary page, view all the details and click Finish.

16.  Change the indexes’ names in the database as needed.

a.     Navigate to Connections> Oracle connection> open SQL worksheet.

b.     Rename the indexes with truncated names and define the default value for each column in each table optionally.

17.  Confirm the result.

select username,default_tablespace from dba_users ;

Backup MySQL and Oracle Beforehand

Database migration without proper backup puts the data in danger. This is because, during the migration to the target database, some of the data may be lost or corrupted. Problems arise, for instance, the semantics error migrates the data source into a different column in the target database, or the migration order is not performed in order.

Some niggling errors may lead to the failure of database migration. Hence the importance of backing up MySQL and Oracle in advance.

Vinchin Backup & Recovery provides database backup automation and the simplification of recovery procedures for users of Oracle Database, MySQL, SQL Server, PostgreSQL, Postgres Pro, MariaDB, and 10+ virtualizations like VMware, XenServer, Hyper-V, oVirt, Sangfor, OpenStack, Oracle OLVM, and more. 

With the backup solution, you can:

Automate backup: minimize database backup workload under preset schedules and job email alerts.

Select from various types: choose from the full, incremental, differential, and log backup that changes with databases. Enable hot database backup for MySQL and Oracle without affecting the smooth running of the database.

Reduce 50% of data: deduplicate and compress at least 50% of the database size to boost backup efficiency and storage availability.

Simplified multi-database management: centrally manage all kinds of backups and tasks in one web console to relieve administration stress.

Anti-ransomware data protection: safeguard all backups in the Vinchin server and itself from malware attacks via real-time monitoring IO that denies any unauthorized access from the server layer. And optionally encrypt the transmission path for safe data transit during the process.

Cost-effective price: a flexible license includes the perpetual license and subscription mode at an extremely cost-effective price for you.

60-day free trial: a full-featured free trial of the Enterprise version is available below to give it a try.

Summary

MySQL and Oracle are both RDBMS from Oracle Corporation. The former is a free open-source database platform suitable for users who have simple queries and small-scale infrastructure, while the latter is built as the paid version for large IT environments that process data in bulk.

For those who want to convert data from MySQL to Oracle, this article details the migration procedures using Oracle SQL Developer.

Except test your database before the migration to check for any possible error, it is best to back up the source and target databases in case of data loss.