MariaDB is an open-source, multi-threaded, relational database management system (RDBMS). Starting with version 10.3, MariaDB has introduced Oracle Compatibility features in MariaDB, which can be leveraged by configuring SET SQL_MODE = ‘Oracle’. This enable MariaDB to understand majority of the Oracle PL/SQL syntax, Stored Procedures, Triggers, Functions and Oracle style packages.
According to the MariaDB post, with SQL_MODE = ORACLE, MariaDB is now able to parse, depending on the case, approximately 80 percent of the legacy Oracle PL/SQL without rewriting the code.
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. In fact, vast parts of your Oracle PL/SQL code can be migrated seamlessly, including:
- Stored Procedure Parameters
- Non-ANSI Stored Procedure Construct
- Cursor Syntax
- Loop Syntax
- Variable Declaration
- Data Type inheritance (%TYPE, %ROWTYPE)
- PL/SQL style Exceptions
- Synonyms for Basic SQL Types (VARCHAR2, NUMBER, …)
Our team recently completed a successful proof-of-concept (PoC) for a bank, to migrate Oracle RAC being used by their internet banking application, to MariaDB.
It was a great learning experience with a satisfying and successful outcome, wherein MariaDB was able to meet both functional as well as technical requirement of the bank. The migration from Oracle RAC to MariaDB will result in huge cost savings for the bank and will also pave the way to migrate other applications from Oracle to MariaDB.
Our learnings can be summarised as follows:
- Assessment is the key to success– There is no straightforward backup and restore method to migrate from Oracle to MariaDB, assessment of existing database is crucial.
- Planning – Breakdown your migration activities in multiple packages, for example, create a data-type mapping and migrate the table structure first, before moving on the code, PL/SQL, stored procedure, and packages etc.
- Application changes might be required – Depending upon the complexity of your database and application, some application change might be required, as MariaDB only supports up-to 80% PL/SQL syntax, not all.
- Multiple iterations required – Based on our experience, 4-6 iterations of data and structure migration might be required, before application team is confident of MariaDB compatibility and willing to move away from Oracle.
- Some functionality may require workaround – While its possible to replace Oracle to MariaDB, some customer or application requirements may require some workaround, hence the stakeholder’s expectations must be set accordingly.
