Migrating from Oracle Database to MariaDB – What You Should Know

Gartner predicts that by 2022, 50% of existing commercial databases will have been converted to open source databases. Even more, 70% of new in-house applications will be developed on an open source database platform (State of the Open-Source DBMS Market, 2018).

These are high numbers considering the maturity, stability, and criticality of popular, proprietary database software. The same may be observed in the top database ranking, where most of the top ten databases are open source.

What is pushing companies to do such moves?

There could be many reasons to migrate database systems. For some, the main reason will be the cost of license and ownership; but is it really only about the cost? And s open source stable enough to move critical production systems to that new open source world?

Open source databases, especially new ones brought into an organization, often come from a developer on a project team. It’s chosen because it’s free (doesn’t impact the direct project’s external spend) and meets the technical requirements of the moment.

But the free aspect doesn’t actually come simply with no cost as you have to consider many factors including the migration and the cost of manhours. The smoother the migration the less time and money spent on the project.

Database migrations can be challenging especially for heterogeneous proprietary database migrations such as Oracle to PostgreSQL, Oracle to Percona or MySQL. The complex schema structure, data types, and database code like PL/SQL can be quite different from those of the target databases,
requiring a schema and code transformation step before the data migration starts.

In the recent article by my colleague Paul Namuag, he examined how to migrate Oracle to Percona.

This time we will take a look at what you should know before migrating from Oracle to MariaDB.

MariaDB promises the enterprise features and migration features which can help to migrate Oracle databases into the open source world.

In this blog post we will cover the following:

• Why migrate?
• Storage engine differences
• Database connectivity considerations
• Installation & administration simplicity
• Security differences
• Replication & HA
• PL/SQL and database code
• Clustering and scaling
• Backup and recovery
• Cloud compatibility
• Miscellaneous considerations

Why Migrate from Oracle?

Most enterprises will run Oracle or SQL Server, or a combination of both, with small pockets of isolated open source databases operating independently. Small to medium-sized businesses would tend to deploy mainly open source databases, especially for new applications. But this is changing, and often, open source is the main choice even for big organizations.

A quick comparison of these two databases systems looks as follows:

• Only Oracle Express Edition is free of cost, but it has very limited features when compared to MariaDB. For extensive features, either Oracle Standard Edition or Oracle Enterprise Edition has to be purchased.
• On the other hand, MariaDB and MySQL community were working hard to minimize the potential features gap. Security compliance, hot backups, and many other enterprise features are now available in MariaDB.

There are things that were always more flexible in MariaDB/MySQL than in massive Oracle setups. One of them is the ease of replication and horizontal cluster scalability.

Storage Engine Differences

First, let’s start with some basics. You can still hear a lot of legends and myths regarding MySQL or MariaDB limitations, which mostly refer to the dark times when the main storage engine was MyISAM.

MyISAM was the default storage engine from MySQL 3.23 until it was replaced by InnoDB in MariaDB 5.5. It’s a light, non-transactional engine with great performance but does not offer row-level locking or the reliability of InnoDB.

With InnoDB (default storage engine), MariaDB offers the two standard row-level locks, which are shared locks(S) and exclusive locks(X). A shared lock is obtained to read a row and allows other transactions to read the locked row. Different transactions may also acquire their own shared locks.
The particular lock is obtained to write to a row and stops additional transactions from locking the same row.

InnoDB has definitely covered the biggest transactional feature gap between these two systems.

Due to the pluggable nature of MariaDB, it offers even more storage engines so you can better adjust it to a specific workload. I.e. when space matters you can go with TokuDB which offers great compression ratio, Spider optimized for partitioning and data sharding, ColumnStore for big data scaling.

Nevertheless, for those migrating from Oracle, my recommendation would be to go first with the InnoDB storage engine.

Connectivity Considerations

MariaDB shares with Oracle good support for database access including ODBC and JDBC drivers, as well as access libraries for Perl, Python, and PHP. MySQL and Oracle both support binary large objects, character, numeric, and date data types. So you should have no issues with finding the right connector for your application services.

MariaDB doesn’t have the dedicated listener process to maintain database connections nori SCAN address for the clustered database like we know from Oracle. You will also not find flexible database services. Instead, you will need to configure manually between Unix socket (a local, most secure way to connect DB – app on the same server), remote connections (by default, MariaDB does not permit remote logins) and also pipe and memory available on Windows systems only. For the cluster, the SCAN address needs to be replaced by the load balancer. MariaDB recommends using their other product MaxScale, but you can also find others like ProxySQL or HAproxy that will work with MariaDB, with some limitations. While using external load balancers for MariaDB can be difficult you may find great features which, by way of comparison, are not available in the Oracle database.

A load balancer would also be a recommendation for those who are looking for Oracle Transparent Application Failover (TAF), Oracle Firewall DB or some of the advanced security features like Oracle Connection Manager. You can find more about choosing the right load balancer in the following white paper.

While these technologies are free and can be deployed manually using script-based installations, systems such as ClusterControl automate the process with their point-and-click interface. ClusterControl also lets you deploy caching technologies.

Installation & Administration Simplicity

The latest available Oracle DB version added a long-awaited installation feature: Oracle 18c can now be installed on Oracle Linux using an RPM. Dedicated Java-based installation was always a problem for those who wanted to write automation for their cookbooks or Puppet code snippets. You could go with predefined silent installation but the file was changing from time to time and still, you had to deal with dependency-hell. RPM-based installation was definitely a good move.

So how does it work in MariaDB?

For those who are moving from the Oracle world, it’s always a nice surprise to see how fast you can deploy instances, create new databases or even set up complex replication flows. The installation and configuration process is probably the smoothest part of the migration process. Although choosing the right settings takes time and knowledge.

Oracle provides a set of binary distributions of MySQL. These include generic binary distributions in the form of compressed tar files (files with a .tar.gz extension) for a number of platforms and binaries in platform-specific packages. On the Windows platform, you can find a standard installation wizard via a GUI.

The Oracle database configuration assistant (DBCA) is basically not needed as you will be able to create a database with a single line command.

CREATE [OR REPLACE] {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

You can also have a database with different database collations and character sets under the same MariaDB instance.

Replication setup is just to enable binary logging on a master (similar to archive log in Oracle) and running the following command on the slave to attach it to the master.

CHANGE MASTER TO
MASTER_HOST = host,
MASTER_PORT = port,
MASTER_USER = replication_user,
MASTER_PASSWORD = password,
MASTER_AUTO_POSITION = 1;

Security and Compliance

Oracle provides enhanced database security.

User authentication is performed in Oracle by specifying global roles in addition to location, username, and password. In Oracle, User authentication is performed by different authentication methods including database authentication, external authentication, and proxy authentication.

For a long time roles were not available in MariaDB or MySQL. MariaDB added roles with version 10.2 after they appeared in MySQL 8.0.

Roles, an option that is heavily used in common Oracle DB setups, can be easily transformed in MariaDB, so you don’t have to waste time on single user permission adjustments.

Create, alter user, passwords: it all works similarly to Oracle DB.

To achieve enterprise security compliance standards, MariaDB offers built-in features like:

• Audit plugin in
• Encryption of data-at-rest
• Certificates, TSS connection
• PAM Plugin

Audit plugin offers a sort of fine-grained auditing (FGA) or AUDI SQL available in Oracle. It does not offer the same set of features but usually, it’s good enough to satisfy security compliance audits.

Encryption of data-at-rest Data at rest encryption can be a requirement for security regulations like HIPAA or PCI DSS. Such encryption can be implemented on multiple levels – you can encrypt the whole disk on which the files are stored. You can encrypt only the MySQL database through functionality available in the latest versions of MySQL or MariaDB. Encryption can also be implemented in the application so that it encrypts the data before storing it in the database. Every option has its pros and cons: disk encryption can help only when disks are physically stolen, but the files would not be encrypted on a running database server.

PAM Plugin extends logging functionality to tight user accounts with LDAP settings. In fact, I find it much easier to set up than LDAP Integration with Oracle Database.

Replication & HA

MariaDB is well known for its replication simplicity and flexibility. By default, you can read or even write to your standby/slave servers. Luckily, MySQL 10.X versions brought many significant enhancements to Replication, including Global Transaction IDs, event checksums, multi-threaded slaves and crash-safe slaves/masters at making replication even better. DBAs accustomed to MySQL replication reads and writes would expect a similar or even simpler solution from it’s bigger brother, Oracle. Unfortunately not by default.

The standard physical standby implementation for Oracle is closed for any read-write operations. In fact, Oracle offers logical variation but it has many limitations, and it’s not designed for HA. The solution to this problem is an additional paid feature called Active Data Guard, which you can use to read data from the standby while you apply redo logs.

Active Data Guard is a paid add-on solution to Oracle’s free Data Guard disaster recovery software available only for Oracle Database Enterprise Edition (highest cost license). It delivers read-only access, while continuously applying changes sent from the primary database. As an active standby database, it helps offload read queries, reporting and incremental backups from the primary database. The product’s architecture is designed to allow standby databases to be isolated from failures that may occur at the primary database.

An exciting feature of Oracle database 12c and something that Oracle DBA would miss is the data corruption validation. Oracle Data Guard corruption checks are performed to ensure that data is in exact alignment before data is copied to a standby database. This mechanism can also be used to restore data blocks on the primary directly from the standby database.

MariaDB offers various replication methods and replication features like:

• synchronous,
• asynchronous,
• semi-synchronous

The feature set for MariaDB replication is rich. With synchronous replication, you can set up failover with no write transaction loss. To reduce asynchronous replication delays you may wish to go with In-order parallelized replication on slaves. The events that can be compressed are the events that normally can be of significant size: Query events (for DDL and DML in statement-based replication), and row events (for DML in row-based replication). Similar to other compression options MariaDB compressed replication is transparent. As mentioned before, the whole process is very easy compared to Oracle Data Guard physical and logical replication.

PL/SQL and Database Code

Now we come to the tough part: PL/SQL.

While replication and HA with MariaDB reign supreme. Oracle is the king of PL/SQL, no doubt there.

PL/SQL is the main obstacle for migration into the open source world in many organizations . But MariaDB does not give up here.

MariaDB 10.3 (also known as MariaDB TX 3.0) has added some amazing new features including SEQUENCE constructs, Oracle-style packages, and the ROW data type – making migrations much easier.

With new parameter SQL_MODE = ORACLE, MariaDB is now able to parse, depending on the case, a bunch of the legacy Oracle PL/SQL without rewriting the code.

As we can find on their customer story page using the core Oracle PL/SQL compatibility in MariaDB TX 3.0, the Development Bank of Singapore (DBS) has been able to migrate more than half of their business-critical applications in just 12 months from Oracle Database to MariaDB.

The new compatibility mode helps with the following syntax:

• Loop Syntax
• Variable Declaration
• Non-ANSI Stored Procedure Construct
• Cursor Syntax
• Stored Procedure Parameters
• Data Type inheritance (%TYPE, %ROWTYPE)
• PL/SQL style Exceptions
• Synonyms for Basic SQL Types (VARCHAR2, NUMBER, …)

But if we take a look at the older version 10.2, some of the compatibility between Oracle and MariaDB appeared before like:

• Common table expressions
• Recursive SQL queries
• Windows Functions, NTILETE, RANK, DENESE_RANK.

Native PL/SQL parsing or is some cases direct execution of native Oracle procedures can greatly reduce the cost of development.

Another very useful feature added by SQL_MODE=Oracle is sequences. The implementation of sequences in MariaDB Server 10.3 follows the SQL:2003 standard and includes syntax compatibility with Oracle.

To create a sequence, a create statement is used:

CREATE SEQUENCE Sequence_1 
  START WITH 1  
  INCREMENT BY 1;

When created sequences can be used for example with inserts like:

INSERT INTO database (database_id, database_name) VALUES(Sequence_1.NEXTVAL, 'MariaDB');

Clustering and Scaling

MariaDB is an asynchronous, active-active, multi-master database cluster.

MariaDB Cluster differs from what is known as Oracle’s MySQL Cluster – NDB.

MariaDB cluster is based on the multi-master replication plugin provided by Codership (Galera). Since version 5.5, the Galera technology (wsrep API) is an integral part of MariaDB. The Galera plugin architecture stands on three core layers: certification, replication, and group communication framework.

The certification layer prepares the write-sets and does the certification checks on them, guaranteeing that they can be applied.

The Replication layer manages the replication protocol and provides the total ordering capability.

Group Communication Framework implements a plugin architecture which allows other systems to connect via gcomm backend schema.

The main difference from Oracle RAC is that each node has separated data. Oracle RAC is commonly mistaken as a complementary HA solution while disks are usually in the same disk array. MariaDB not only offers redundant storage but it also supports geo located clustering without the need of dedicated fiber.

Backup and Recovery

Oracle offers many backup mechanisms including hot backup, backup, import, export, and many others.

Contrary to MySQL, MariaDB offers an external tool for hot backups called mariabackup. It is a fork of Percona XtraBackup designed to work with encrypted and compressed tables and is the recommended backup method for MariaDB databases.

MariaDB Server 10.1 introduced MariaDB Compression and Data-at-Rest Encryption, but the existing backup solutions did not support full backup capability for these features. So MariaDB decided to extend XtraBackup (version 2.3.8) and named this solution Mariabackup.

Percona and Mariabackup offer similar functionalities, but if you are interested in differences, you can find them here.

What MariaDB does not offer is the recovery catalog of your database backups. Fortunately, this can be extended with third-party systems like ClusterControl.

Cloud Compatibility

Cloud infrastructures are getting increasingly popular these days. Although a cloud VM may not be as reliable as an enterprise-grade server, the main cloud providers offer a variety of tools to increase service availability. You can choose between EC2 architecture or DBaaS like Amazon RDS.

Amazon RDS supports MariaDB Server 10.3. It does not support SQL_MODE=Oracle but you still can find a set of features making it easier to migrate. Amazon cloud supports common management tasks like monitoring, backups, multi A-Z deployments, etc..

Another popular cloud provider, Google Cloud, also offers the most recent MariaDB version. You can deploy it as a container or Bintami library certified VM image.

Azure also offers its own implementation of MariaDB. It’s similar to Amazon RDS, with the backups, scaling and builds in high availability. The guaranteed SLA is 99.99% which corresponds to 4 m 23 seconds per month of downtime.

Miscellaneous Considerations

As mentioned in the very beginning of this article, Oracle to MariaDB migration is a multi-stage process. A piece of general advice will be not to try to migrate all of the databases at once. Dividing the migration into small batches is, in most scenarios, the best approach.

If you are not familiar with the technology, give it a try. You should feel confident with the platform and know it’s pros and cons. Testing will build confidence and it affects your decisions with regards to migration.

There are interesting tools which can help you with the most difficult PL/SQL migration process. So interesting ones are dbconvert, AWS Schema Conversion Tool – AWS Documentation.

Over the years MariaDB has gained Enterprise support and maturity to run critical and complex data transaction systems. With the recent version, MariaDB has added some great new features such as SQL_Mode=Oracle compatibility, making the transition process easier than ever before.

Finally, you can join me on March 12th for a webinar during which I’ll walk you through all you need to know when it comes to migrating from Oracle database to MariaDB.