How to Load Data From Oracle to MariaDB in 2 Easy Steps?

Oracle is a brand of commercial software created by Oracle Corporation. Oracle commonly uses RDBMS and enables speedy and secure data storage and retrieval. It is used to power Online Transaction Processing and Data Warehousing applications. Oracle is compatible with all major operating systems, including macOS, Unix, Windows, and Linux.

MariaDB is an open-source Relational Database Management System (RDBMS) that may be used in place of the popular MySQL Database technology. MariaDB has a substantially higher number of new features than MySQL, making it superior in performance and usability.

This article will walk you through the two methods to connect Oracle to MariaDB smoothly. Moreover, using a converter can help you reduce the cost and complexity of maintaining an Oracle database and leverage MariaDB’s open-source and innovative features. Read along to understand the step-by-step process.

What is Oracle?

Oracle database is a powerful and widely used relational database management system (RDBMS) designed for enterprise-level applications. Developed by Oracle Corporation, it is known for its advanced features, scalability, and robust security, making it ideal for managing large volumes of data across various industries.

Key Features of Oracle Database:

• Multi-Model Database: Supports multiple data models, including relational, document, graph, and key-value.
• High Performance: Optimized for high-speed processing and efficient query handling.
• Scalability: Supports horizontal and vertical scaling for large datasets and heavy workloads.
• Advanced Security: Offers strong encryption, access controls, and auditing for secure data management.

To move data seamlessly from your oracle databases, get to know more about Oracle ETL tools from our blog.

What is MariaDB?

MariaDB is an open-source relational database management system (RDBMS) that originated as a fork of MySQL. It is designed to offer improved performance, scalability, and security, while maintaining compatibility with MySQL. MariaDB is widely used for various applications, from small websites to large-scale enterprise systems.

Key Features of MariaDB:

• Compatibility: Fully compatible with MySQL, ensuring easy migration.
• High Performance: Optimized for faster queries and better throughput.
• Scalability: Supports horizontal scaling through clustering and replication.
• Security: Provides robust encryption, user authentication, and data protection.

How to Migrate Data from Oracle to MariaDB?

Method 1: Using MariaDB MaxScale and MariaDB Enterprise Server

Step 1: Analyze the Current SQL Mode in MariaDB

• It’s critical to double-check that SQL MODE is enabled since variables fluctuate and can be altered by third-party applications.
• From a Linux terminal, check the current SQL mode:

sudo mariadb -e "show global variables like 'sql_mode';"

• You can try to create a table with Oracle syntax using the default SQL_MODE:

MariaDB [oracle_migration]> CREATE TABLE "customers" (
"CUST_ID" NUMBER(8,0),
"FIRST_NAME" VARCHAR2(50 BYTE),
"LAST_NAME" VARCHAR2(50 BYTE),
"ADDRESS1" VARCHAR2(128 BYTE)
);

• This will produce an error because the MariaDB client does not recognize this syntax by default.

Step 2: Configure SQLMode to Oracle Compatibility Mode

• Change the SQL MODE of the session to Oracle compatibility mode:

SET SESSION sql_mode='ORACLE';

• Try creating the same table once again:

MariaDB [oracle_migration]> CREATE TABLE "customers" (
"CUST_ID" NUMBER(8,0), 
"FIRST_NAME" VARCHAR2(50 BYTE), 
"LAST_NAME" VARCHAR2(50 BYTE), 
"ADDRESS1" VARCHAR2(128 BYTE)
);

• This will end in displaying a BYTE error.

Step 3: Launch the MariaDB MaxScale GUI

You may set a rule on MaxScale to modify the live query before sending it to a MariaDB Server. This feature is handy when specific active queries need to be changed without affecting the application.

These features may also be used to correct mistakes in the application without requiring any deployment or impacting production.

• Once configured, the MaxScale GUI is accessible by default on port 8989. You may go to this interface by going to https://127.0.0.1:8989.
• Once connected, navigate to the MaxScale GUI monitoring page and choose the desired service.

• The MaxScale setup consists of multiple components. All of these modules are connected together in “Services.” The previous image shows that the service connects numerous servers, a monitoring system, a listening port, a routing rule, and maybe several filters.
• In this scenario, only one service – the “Read-Write Service” – is defined. You can access the definition of this service by clicking on its name:

Step 4: Create a New Regex Rule for Migration

You may use a Regex filter on MariaDB MaxScale to rewrite live queries.

• First, return to the MaxScale overview panel and click + Create New to configure a new filter:

• Select the option to create a new filter.
• Choose “regexfilter” and provide a name corresponding to the filter aim.
• In the parameter, enter the value to be replaced in the match parameter: BYTE (Remember to add escape special characters like parentheses with a backslash, as illustrated.)
• Fill in the replace box with the appropriate value. It is parenthesis without escaping the special character in this situation.
• Once finished, validate this new rule.

Step 5: Link your Migration Regex with MariaDB Service

• Return to the service definition page and click the + Add Filters button in the Filters section after the new filter has been created.

• Enable the newly created filter in the Add filters box to correlate it with the current service and listener.
• After that, you can test your CREATE TABLE command again.

Step 6: Test the Created MariaDB Rule in MaxScale

• Access the MaxScale service using the MariaDB command line.

$ mariadb -hmaxscale1.example.com -P4006 -utest -p

• We can test the newly established rewrite rule after properly linking via the MaxScale interface.
• Check the previously created database:

MariaDB [(none)]> use oracle_migration

• Enable on the considered session the Oracle mode:

MariaDB [(none)]> SET SESSION sql_mode='ORACLE';

• Create the table again with the same syntax, and you will observe that table was created without any errors since the application wasn’t modified.

MariaDB [oracle_migration]> CREATE TABLE "customers" (
"CUST_ID" NUMBER(8,0), 
"FIRST_NAME" VARCHAR2(50 BYTE), 
"LAST_NAME" VARCHAR2(50 BYTE), 
"ADDRESS1" VARCHAR2(128 BYTE)
);

Drawbacks of Using MaxScale and Enterprise Server:  

1. Storage Engine Differences:  MariaDB and Oracle use different storage engines, which can lead to compatibility issues. Oracle uses its proprietary engine, while MariaDB uses a variety of storage engines, with InnoDB being the default. This difference can affect the performance and behavior of the database after migration.

2. Database Connectivity Considerations: The connectivity protocols and methods may differ between Oracle and MariaDB, which could necessitate changes in the application layer to ensure proper communication with the database after migration.

3. PL/SQL and Database Code: PL/SQL is Oracle’s procedural language extension for SQL, and it is heavily used in Oracle databases. While MariaDB has introduced compatibility features for PL/SQL, not all PL/SQL features are supported, and some stored procedures and functions may need to be rewritten or adjusted.

4. Replication & High Availability (HA): MariaDB’s replication and HA solutions differ from Oracle’s Data Guard and RAC. This means that the replication setup and failover mechanisms need to be reconfigured and tested thoroughly during the migration process.

5. Backup and Recovery: Backup and recovery procedures in MariaDB may differ from Oracle’s RMAN tool, requiring new strategies and tools for ensuring data durability and recoverability in MariaDB.

When migrating from Oracle to MariaDB, careful planning is required to address compatibility issues, rewrite database code, reconfigure replication and HA, and adapt backup and recovery procedures. Additionally, licensing considerations for MariaDB MaxScale and the limitations of the free version should be taken into account to ensure a successful migration.

Method 2: Using ESF Database Toolkit

You can use ESF Database Migration Toolkit to perform a fast and easy MariaDB to Oracle migration through a wizard interface. Here are the steps.

Step 1: Select Oracle as a Source in Choose a Data Source Dialog Box

• Enter the server name (localhost by default) and port (default.: 1521).
• Enter the username (the default is “system”) and password.
• Choose your login method (SID, Service Name, or TNS).
• In the “Database” field, provide the source Oracle SID or Service_Name, or leave it blank if using the TNS technique.
• Enter the schema name or leave it blank (the default is the same as the username, you can press the “Refresh” button to list all schemas).

Step 2: Select MariaDB as a Destination in Choose a Destination Dialog Box

• Enter the server name (localhost), port (3306), username, and password.
• MariaDB supports a variety of Storage Engines; for more information on the MariaDB Storage Engine, please visit here.
• Select a character set, such as UTF-8.
• To list all MariaDB databases programmatically, click the “Refresh” button. You may select an existing Database or input a new Database name; this toolkit will construct the new Database during the migration process.

Step 3: Select Source Table (s) & View (s) will pop up

• Choose the tables/views for Oracle to MariaDB migration.

• To change the table parameters or remap the table structure, click “…”.

• Before transferring data, you may specify the data transfer technique (Overwrite Table/Empty Data/Append Data/Skip Table) and filter the data.
• If you select “Field Mapping,” you may redefine the fields in the destination table, such as field name, data type, default value, remark, etc.

Step 4: Execute Dialog Box will pop up

• To begin the migration, click “Submit.” This toolkit will assist you in swiftly migrating data from Oracle to MariaDB without intervention.

• To view the complete migration log, click “Browse Log.”
• Click “Save as job” to save the migration settings to a job file, which you can then “Load Task” to quickly reload the migration job or execute via command-prompt in the future. To receive the complete command parameters, use “esf-cmd —help” in the command prompt.

Step 5: Migration Executed

• The program will finish the data migration from Oracle to MariaDB as soon as feasible and provide you with a migration report.

Why Should You Migrate Data from Oracle to MariaDB?

• High Availability: To guarantee high availability in the case of a database, server, or data center failure, MariaDB provides replication with automated failover and multi-master clustering within a single data center or across many data centers. Disaster recovery is also provided by delayed replication, point-in-time rollback, and backup/restore.
• Better Performance: To increase query efficiency, MariaDB provides both local and distributed partitions (i.e., sharding); table, row, or column compression to minimize disc I/O; and query result caching to reduce database load. Furthermore, MariaDB offers a storage engine designed for space and writing to increase write speed and minimize disc I/O in write-intensive applications.
• Advanced Security: MariaDB offers data-in-motion encryption (e.g., connections and replication) as well as data-at-rest encryption, data protection via roles, dynamic data masking (full or partial), and database security against denial-of-service attacks. It also has a database firewall that may block queries based on syntax, role, frequency, and time.
• High-Performance Analytics: MariaDB may duplicate data via a Change Data Capture (CDC) stream to facilitate scalable, high-performance analytics on near real-time data. MariaDB Enterprise Server supports ad hoc, on-demand analytics on billions of rows without the requirement for indexes by utilizing distributed, columnar storage and Massively Parallel Processing.

What are the Best Practices for Migrating Data from Oracle to MariaDB?

• Data Backup: Before proceeding, make a backup of the data. You cannot afford to lose data if something goes wrong during the installation. Before proceeding, ensure that backup resources are available and have been tested.
• Determining Data Migration Schedule: Once you’ve decided on the rules and regions of responsibility, create a plan with start and finish dates. Include dates for progress checks or milestones if required.
• Execute Multiple Tests: Test the data migration during the planning and design phases and during implementation and maintenance to ensure that you will finally get the intended result.
• Keep Data Secure: It is easy to neglect security settings or permissions while moving data. Your data is subject to misuse or corruption if security is neglected.

Conclusion

This article reflects the two methods that can be used to migrate data from Oracle to MariaDB. Both methods offer specific advantages and disadvantages to their users, but Method 2 has a certain edge over the other.

Find out how to transfer data from SQLite to MariaDB for optimized database operations and enhanced functionality.

ESF Database Migration Toolkit is robust and delivers high performance. Moving your data using ESF Database Migration Toolkit allows you to quickly transfer Oracle data to MariaDB, saving you time and effort.

Hevo can effortlessly automate data integration and allows you to focus on other aspects of your business like Analytics, Customer Management, etc.

Sign up for a free 14-day trial to streamline your data integration process. You may examine Hevo’s pricing plans and decide on the best plan for your business needs.

FAQ