Choosing the Right Configuration for an Oracle to MariaDB Migration
Author: Kathryn Sizemore | | May 18, 2021
Your database’s configuration makes or breaks an Oracle to MariaDB migration project. You may end up with data loss, data corruption, bad performance, and poor query optimization without the proper settings. Here are the lessons Datavail has learned from thousands of database migrations.
MariaDB Built-in Features That Are Add-ons in Oracle
MariaDB has many features built into its Enterprise software that are only offered as add-ons in Oracle. As part of your Oracle migration project, it’s helpful to assess the features or settings that can best benefit your application.
For one of our customers, the flexibility and granularity of MariaDB’s permissions and user roles met their security needs far better than the Oracle add-on security packages they had purchased. The flexibility of using multiple table engines on a table-by-table basis is a unique feature in MariaDB and MySQL. Most implementations use the standard transactional InnoDB Engine; but reviewing your workload and table footprint helps determine if another engine may be better for some tables. Different engines can provide two to three times the compression, 10 times the IO write or store petabytes of data with powerful analytics speeds.
Evaluating Your Default Transaction Isolation Level
Consider the need to change from the default isolation level to better match the level on which your application was built. Oracle’s default Transaction Isolation Level is Read Committed. MariaDB’s default is Repeatable Read, which is fully ACID compliant and performs more locking, as all SELECTs within a transaction return the same result. If you use a level that the application isn’t written for, gap locks appear, and performance deteriorates.
Keeping Time Zones and Dates Consistent
Any migration should assess time zone settings at an application, database, and system level. Many legacy solutions are built in a specific time zone and face challenges with staying consistent, adding new time zones, and adjusting to daylight savings time changes.
During an Oracle to MariaDB migration, it’s imperative to assess the time zone impact because MariaDB doesn’t support the datatype “datetime with time zone” from Oracle. If this process isn’t handled correctly, time zones get stripped from the dataset. This data would be essentially useless with only a datetime stamp.
You can use Oracle Golden Gate to convert to the UTC time zone during the migration process to eliminate many of these issues. An alternative is adding a column to track the time zone, but this requires code changes.
Reviewing Your Indexes
Good indexing is critical to database performance, but DBMS systems approach query execution plans in different ways. Many legacy systems accumulate indexes over time that may no longer be relevant or helpful. Even if those indexes were created for active queries, MariaDB might choose a different index to execute that same query. Foreign Key Cascade on Delete behavior and Circular Foreign Keys are two other components that may cause performance issues if they’re not optimized for your current needs.
You also need to confirm that MariaDB supports your Oracle indexes. For example, it does not support Function-Based Indexes and Index Compression.
Review these indexes to determine if they’re beneficial in the new system or are just added overhead. When you’re reviewing slow queries on the migrated system, you can identify the need for any new indexes or indexes modified for MariaDB’s query optimizer.
During the actual migration process, performing the initial load without indexes on very large tables may improve the overall migration time. Importing bulk data will slow down when you have large tables with many indexes that need to be written to disk concurrently. Sometimes, using the ADD INDEX command after the initial load is beneficial.
Pay Attention to Sequences, Triggers, Stored Procs, and Jobs
Give special consideration and preparation to Objects that may behave differently across the databases or require syntax changes to migrate. MariaDB’s Oracle Mode helps with Non-ANSI Stored Procedure Construct and Stored Procedure Parameters; you should review and test all Stored Procs and triggers after converting the necessary syntax to confirm they still work and return the same data as Oracle. Some elements may not have equivalent objects or functions in MariaDB. For example, triggers in Oracle can fire on DML, DDL, or Database changes, but MariaDB triggers are only for DML changes. You can convert all Oracle scheduler jobs to run as MariaDB events.
Sequence Options are slightly different between Oracle and MariaDB. It’s vital to keep sequences disabled in MariaDB until after it becomes the master. The following Oracle Sequence Options don’t exist in MariaDB: NOORDER, NOKEEP, NOSCALE, and GLOBAL. You can use a basic sequence create statement on MariaDB to match the Sequence numbers on Oracle when you cutover. For example:
CREATE SEQUENCE DATABASE1.TABLEA_PK_SEQ START WITH 13541225;
Check for Compatible CharSet and Collation Selections
If your migration tool or target database has a character set that doesn’t include all the characters used in Oracle, the migration ends up with corrupted or lost data. Similarly, choosing different collation settings in MariaDB than in Oracle could completely change query results against the MariaDB database, as the sorting and case sensitivity changes.
One of our customers didn’t review their character sets between their Oracle and MariaDB environments before their first migration attempt. They quickly found that the mismatched character sets caused errors in their Oracle GoldenGate migration, and certain characters in the data couldn’t migrate. Their source database had a universal charset utf8mb4, while their target was using Latin1. After changing the character set on the target database and its tables, the migration was completed successfully with no data loss.
Read This Next
Want to learn more about the Oracle to MariaDB migration process? Download our white paper for more details. This paper will provide an overview of MariaDB, including key features and benefits, to help chart your course when making the migration to MariaDB.
The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.
This blog reviews how you can generate scripts for SQL server logins, role assignments, and server permissions for a smooth migration.