Easier Oracle to MariaDB Migrations with sql_mode and DBeaver
If you’re considering migrating from Oracle to MariaDB, you need to know the sql_mode configuration property in MariaDB as well as DBeaver’s features for schema and data migration. These two tools will ease your migration process. In this article – based on a recent webinar titled “Oracle to MariaDB: Simplifying Migration with DBeaver” – I show you some tips to streamline your migrations to MariaDB from Oracle.
Why Migrate to MariaDB?
To set a context for this article, let me first list some important reasons to migrate to MariaDB:
- Lower costs: This is not a secret. Moving from Oracle to MariaDB reduces costs – companies can save up to 90% in total database costs in fact. In addition to this, MariaDB has simpler licensing and no hidden fees.
- Flexibility: From bare metal on powerful hardware all the way to cloud environments using VMs, Docker, or Kubernetes, MariaDB is deployable anywhere. Even on a tiny Raspberry Pi Zero. MariaDB is thread-based and offers high performance in truly demanding workloads.
- Ease of use: MariaDB has modern features without the bloat, constantly improving with customer focus.
- Multiple storage engines: MariaDB can handle different workloads within a single node thanks to the pluggable storage engine architecture. Multiple storage engines can live simultaneously in a server, and cross-engine queries are possible.
- Advanced proxy (MaxScale): MariaDB has a robust and advanced intelligent database proxy for query rewriting, security, read/write splitting, automatic failover, automatic transaction replay, NoSQL support, and more.
- Built-in vector search: Starting with MariaDB Server 11.7, you have fast and scalable vector search thanks to the industry standard and high-performant Hierarchical Navigable Small World (HNSW) algorithm.
- Truly Open Source: Developed in the open (GPL licensed and public bug tracker) you can be sure that the software will continue to evolve while remaining Open Source.
The Migration Process
A database migration process involves several steps including schema migration, data transfer, and application code migration. Here’s how tools like DBeaver and MariaDB help with the steps of this process.
Schema Migration and Data Transfer with DBeaver
Schema migration focuses on moving tables, indexes, and views from Oracle to MariaDB. This means creating a “copy” of your Oracle schema in MariaDB. Here’s a practical example on how to do this with DBeaver:
1. Connect to the databases: Create new connections to your Oracle and MariaDB databases.
2. Export Data from Oracle:
a. In the Oracle connection, navigate to the schema that you want to migrate.
b. Select and highlight all the tables you want to migrate.
c. Right-click and select Export Data.
3. Configure Data Export:
a. In the Data Transfer Wizard, select Database Tables as the export target.
b. Drill down into the MariaDB server and select the target schema.
c. Map the Oracle tables to the target tables in MariaDB.
d. Modify the data types if needed (e.g., change DATE in Oracle to DATETIME in MariaDB).
4. Data Transfer Settings:
a. Choose to pull data in a single query with one thread.
b. Set the maximum number of rows (if needed for testing purposes).
c. Use multi-row and value inserts for faster data transfer.
d. Enable transactions to maintain data consistency during transfer.
5. Execute Data Transfer:
a. Click Next to start the schema and data export process.
b. The process will create tables and migrate data from Oracle to MariaDB.
c. Monitor the progress to ensure successful migration.
DBeaver allows you to verify the data transfer with the Data Compare option available when you right-click on a table name. It also allows you to schedule migration tasks so you can run them at regular intervals (e.g., weekly on Sundays).
Application Code Migration using MariaDB’s sql_mode option
Your application’s SQL code needs to be migrated as well. It must be compatible with MariaDB SQL’s dialect which happens to be one of the best implementations of the SQL/PSM standard. Oracle uses SQL/PL instead, so if you try to run SQL code that uses Oracle-specific syntax on a MariaDB server, you’ll get an (expected) error:
Here we are using double quotes as delimiters for table and column names and using Oracle’s VARCHAR2 data type. If you activate the Oracle mode in MariaDB by running SET SQL_MODE = 'ORACLE', then MariaDB mimics Oracle’s SQL dialect (to a good extent), making the previous statement work:
This configuration reduces the effort required to migrate application code (less code needs to be manually modified). The configuration can be set for the current session (as in the previous example), globally for all sessions, or permanently in a configuration file.
There also are SQL modes in MariaDB Server for SQL Server, PostgreSQL, and old versions of MySQL.
Query Rewriting with MaxScale
The sql_mode option is useful as is, but sometimes there are corner cases not implemented (yet). For example, if an Oracle SQL statement contains the Oracle-specific BYTE keyword for VARCHAR2, MariaDB throws an error even when the SQL mode is set to Oracle:
In cases like this, you can use MariaDB MaxScale to intercept and modify SQL queries on the fly. Enable this via MaxScale’s GUI (or alternatively, using the command line or configuration files) to set up a filter with a regular expression to fix the syntax. For example:
Specify a regular expression to match (remember to escape special characters like parenthesis) and the value to use as a replacement:
- Match:
BYTE\) - Replace:
)
After this, the previously failing SQL statement successfully works:
Conclusion and Additional Resources
MariaDB is an attractive alternative to Oracle when you consider the benefits of lower costs, ease of deployment, and modern features like vector search for AI applications. Tools like DBeaver and MariaDB’s Oracle SQL mode simplify migration processes even for highly complex databases.
Here are some additional resources to learn more:
- Webinar – Oracle to MariaDB: Simplifying Migration with DBeaver
- Webinar – Untethering from Oracle: Technical Guide for Migrating Your Enterprise to MariaDB
- Whitepaper – Lower TCO Without Lowering Standards: Migrate to MariaDB Enterprise from Oracle
- Video – Tips and Tricks for Migrating from Oracle to MariaDB
- Blog post – You Know You Want To: Migrate from Oracle to MariaDB
- Documentation – Migrating to MariaDB from Oracle
- Migration practice – Database Migration Service
