How to Migrate from Oracle to MySQL / Percona Server
Migrating from Oracle to MySQL/Percona Server is not a trivial task. Although it is getting easier, especially with the arrival of MySQL 8.0 and Percona announced Percona Server for MySQL 8.0 GA. Aside from planning for your migration from Oracle to Percona Server, you must ensure that you understand the purpose and functionality for why it has to be Percona Server.
This blog will focus on Migrating from Oracle to Percona Server as its specific target database of choice. There’s a page in the Oracle website about SQL Developer Supplementary Information for MySQL Migrations which can be used as a reference for the planned migration. This blog will not cover the overall process of migration, as it is a long process. But it will hopefully provide enough background information to serve as a guide for your migration process.
Since Percona Server is a fork of MySQL, almost all features that come along in MySQL are present in Percona Server. So any reference of MySQL here is applicable as well to Percona Server. We previously blogged about migrating Oracle Database to PostgreSQL. I’ll reiterate again the reasons why one would consider migrating from Oracle to an open-source RDBMS such as PostgreSQL or Percona Server/MySQL/MariaDB.
- Cost: As you may know Oracle licence cost is very expensive and there is additional cost for some features like partitioning and high availability. So overall it’s very expensive.
- Flexible open source licensing and easy availability from public cloud providers like AWS.
- Benefit from open source add-ons to improve performance.
Planning and Development Strategy
Migration from Oracle going to Percona Server 8.0 can be a pain since there’s a lot of key factors that needs to be considered and addressed. For example, Oracle can run on a Windows Server machine but Percona Server does not support Windows. Although you can compile it for Windows, Percona itself does not offer any support for Windows. You must also identify your database architecture requirements, since Percona Server is not designed for OLAP (Online Analytical Processing) or data-warehousing applications. Percona Server/MySQL RDBMS are perfect fit for OLTP (Online Transaction Processing).
Identifying the key aspect of your database architecture, for example if your current Oracle architecture implements MAA (Maximum Available Architecture) with Data Guard ++ Oracle RAC (Real Application Cluster), you should determine its equivalence in Percona Server. There’s no straight answer for this within MySQL/Percona Server. However, you can choose from a synchronous replication, an asynchronous replication (Percona XtraDB Cluster is still currently on version 5.7.x), or with Group Replication. Then, there’s multiple alternatives that you can implement for your own high-availability solution. For example, (to name a few) using Corosync/Pacemaker/DRBD/Linux stack, or using MHA (MySQL High Availability), or using Keepalived/HaProxy/ProxySQL stack, or plainly rely on ClusterControl which supports Keepalived, HaProxy, ProxySQL, Garbd, and Maxscale for your high-availability solutions.
On the other side, the question you have also to consider as part of the plan is “How will Percona will provide support and who will help us when Percona Server itself encounters a bug or how high is the urgency when we need help?”. One thing to consider as well is budget, if the purpose of migration from enterprise database to an open-source RDBMS is because of cost-cutting.
There are different options from migration planning to the things you need to do as part of your development strategy. Such options include engaging with experts in the MySQL/Percona Server field and that includes us here at Severalnines. There are lots of MySQL consulting firms that can help you through this since migration from Oracle to MySQL requires a lot of expertise and know-how in the MySQL Server area. This should not be limited to the database but it should cover expertise in scalability, redundancy, backups, high-availability, security, monitoring/observability, recovery and engaging on mission critical systems. Overall, it should have an understanding of your architectural insight without exposing confidentiality of your data.
Assessment or Preliminary Check
Backing up your data including configurations or setup files, kernel tunings, automation scripts shall not be left into oblivion. It’s an obvious task, but before you migrate, always secure everything first , especially when moving to a different platform.
You must assess as well that your applications are following the up-to-date software engineering conventions and ensure that they are platform agnostic. These practices can be to your benefit especially when moving to a different database platform, such as Percona Server for MySQL.
Take note that the operating system that Percona Server requires can be a show-stopper if your application and database run on a Windows Server and the application is Windows dependent; then this could be a lot of work! Always remember that Percona Server is on a different platform: perfection might not be guaranteed but can be achieved close enough.
Lastly, make sure that the targeted hardware is designed to work feasibly with Percona’s server requirements or that it is bug-free at least (see here). You may consider stress testing first with Percona Server before reliably moving off your Oracle Database.
What You Should Know
It is worth noting that in Percona Server / MySQL, you can create multiple databases whereas Oracle does not come with that same functionality as MySQL.
In MySQL, physically, a schema is synonymous with a database. You can substitute the keyword SCHEMA instead of DATABASE in MySQL SQL syntax, for example using CREATE SCHEMA instead of CREATE DATABASE; whilst Oracle has a distinction of this. A schema represents only a part of a database: the tables and other objects owned by a single user. Normally, there is a one-to-one relationship between the instance and the database.
For example, in a replication setup equivalent in Oracle (e.g. Real Application Clusters or RAC), you have your multiple instances accessing a single database. This lets you start Oracle on multiple servers, but all accessing the same data. However, in MySQL, you can allow access to multiple databases from your multiple instances and can even filter out which databases/schema you can replicate to a MySQL node.
Referencing from one of our previous blog, the same principle applies when speaking of converting your database with available tools found on the internet.
There is no such tool that can 100% convert Oracle database into Percona Server / MySQL; some of it will be manual work.
Checkout the following sections for things that you must be aware of when it comes to migration and verifying the logical SQL result.
Data Type Mapping
MySQL / Percona Server have a number of data-types that is almost the same as Oracle but not as rich as compared to Oracle. But since the arrival of the 5.7.8 version of MySQL, is supports for a native JSON data type.
Below is its data-type equivalent representation (tabular representation is taken from here):
|1||BFILE||Pointer to binary file, ⇐ 4G||VARCHAR(255)|
|2||BINARY_FLOAT||32-bit floating-point number||FLOAT|
|3||BINARY_DOUBLE||64-bit floating-point number||DOUBLE|
|4||BLOB||Binary large object, ⇐ 4G||LONGBLOB|
|5||CHAR(n), CHARACTER(n)||Fixed-length string, 1 ⇐ n ⇐ 255||CHAR(n), CHARACTER(n)|
|6||CHAR(n), CHARACTER(n)||Fixed-length string, 256 ⇐ n ⇐ 2000||VARCHAR(n)|
|7||CLOB||Character large object, ⇐ 4G||LONGTEXT|
|8||DATE||Date and time||DATETIME|
|9||DECIMAL(p,s), DEC(p,s)||Fixed-point number||DECIMAL(p,s), DEC(p,s)|
|10||DOUBLE PRECISION||Floating-point number||DOUBLE PRECISION|
|12||INTEGER, INT||38 digits integer||INT||DECIMAL(38)|
|13||INTERVAL YEAR(p) TO MONTH||Date interval||VARCHAR(30)|
|14||INTERVAL DAY(p) TO SECOND(s)||Day and time interval||VARCHAR(30)|
|15||LONG||Character data, ⇐ 2G||LONGTEXT|
|16||LONG RAW||Binary data, ⇐ 2G||LONGBLOB|
|17||NCHAR(n)||Fixed-length UTF-8 string, 1 ⇐ n ⇐ 255||NCHAR(n)|
|18||NCHAR(n)||Fixed-length UTF-8 string, 256 ⇐ n ⇐ 2000||NVARCHAR(n)|
|19||NCHAR VARYING(n)||Varying-length UTF-8 string, 1 ⇐ n ⇐ 4000||NCHAR VARYING(n)|
|20||NCLOB||Variable-length Unicode string, ⇐ 4G||NVARCHAR(max)|
|21||NUMBER(p,0), NUMBER(p)||8-bit integer, 1 <= p < 3||TINYINT||(0 to 255)|
|16-bit integer, 3 <= p < 5||SMALLINT|
|32-bit integer, 5 <= p < 9||INT|
|64-bit integer, 9 <= p < 19||BIGINT|
|Fixed-point number, 19 <= p <= 38||DECIMAL(p)|
|22||NUMBER(p,s)||Fixed-point number, s > 0||DECIMAL(p,s)|
|23||NUMBER, NUMBER(*)||Floating-point number||DOUBLE|
|25||NVARCHAR2(n)||Variable-length UTF-8 string, 1 ⇐ n ⇐ 4000||NVARCHAR(n)|
|26||RAW(n)||Variable-length binary string, 1 ⇐ n ⇐ 255||BINARY(n)|
|27||RAW(n)||Variable-length binary string, 256 ⇐ n ⇐ 2000||VARBINARY(n)|
|29||ROWID||Physical row address||CHAR(10)|
|30||SMALLINT||38 digits integer||DECIMAL(38)|
|31||TIMESTAMP(p)||Date and time with fraction||DATETIME(p)|
|32||TIMESTAMP(p) WITH TIME ZONE||Date and time with fraction and time zone||DATETIME(p)|
|33||UROWID(n)||Logical row addresses, 1 ⇐ n ⇐ 4000||VARCHAR(n)|
|34||VARCHAR(n)||Variable-length string, 1 ⇐ n ⇐ 4000||VARCHAR(n)|
|35||VARCHAR2(n)||Variable-length string, 1 ⇐ n ⇐ 4000||VARCHAR(n)|
Data type attributes and options:
|BYTE and CHAR column size semantics||Size is always in characters|
Percona Server uses XtraDB (an enhanced version of InnoDB) as its primary storage engine for handling transactional data; although various storage engines can be an alternative choice for handling transactions such as TokuDB (deprecated) and MyRocks storage engines.
Whilst there are advantages and benefits to using or exploring MyRocks with XtraDB, the latter is more robust and de facto storage engine that Percona Server is using and its enabled by default, so we’ll use this storage engine as the basis for migration with regards to transactions.
By default, Percona Server / MySQL has autocommit variable set to ON which means that you have to explicitly handle transactional statements to take advantage of ROLLBACK for ignoring changes or taking advantage of using SAVEPOINT.
It’s basically the same concept that Oracle uses in terms of commit, rollbacks and savepoints.
For explicit transactions, this means that you have to use the START TRANSACTION/BEGIN;
Otherwise, if you have to disable autocommit, you have to explicitly COMMIT all the time for your statements that requires changes to your data.
MySQL has the dual compatibility with Oracle which is meant for compatibility of databases using a dummy table, namely DUAL.
This suits Oracle’s usage of DUAL so any existing statements in your application that use DUAL might require no changes upon migration to Percona Server.
The Oracle FROM clause is mandatory for every SELECT statement, so Oracle database uses DUAL table for SELECT statement where a table name is not required.
In MySQL, the FROM clause is not mandatory so DUAL table is not necessary. However, the DUAL table does not work exactly the same as it does for Oracle, but for simple SELECT’s in Percona Server, this is fine.
See the following example below:
SQL> DESC DUAL; Name Null? Type ----------------------------------------- -------- ---------------------------- DUMMY VARCHAR2(1) SQL> SELECT CURRENT_TIMESTAMP FROM DUAL; CURRENT_TIMESTAMP --------------------------------------------------------------------------- 16-FEB-19 04.16.18.910331 AM +08:00
But in MySQL:
mysql> DESC DUAL; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DUAL' at line 1 mysql> SELECT CURRENT_TIMESTAMP FROM DUAL; +---------------------+ | CURRENT_TIMESTAMP | +---------------------+ | 2019-02-15 20:20:28 | +---------------------+ 1 row in set (0.00 sec)
Note: the DESC DUAL syntax does not work in MySQL and the results as well differ as CURRENT_TIMESTAMP (uses TIMESTAMP data type) in MySQL does not include the timezone.
Oracle’s SYSDATE function is almost the same in MySQL.
MySQL returns date and time and is a function that requires () (close and open parenthesis with no arguments required. To demonstrate this below, here’s Oracle and MySQL on using SYSDATE.
In Oracle, using plain SYSDATE just returns the date of the day without the time. But to get the time and date, use TO_CHAR to convert the date time into its desired format; whereas in MySQL, you might not need it to get the date and the time as it returns both.
See example below.
SQL> SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL; NOW ------------------- 02-16-2019 04:39:00 SQL> SELECT SYSDATE FROM DUAL; SYSDATE --------- 16-FEB-19
But in MySQL:
mysql> SELECT SYSDATE() FROM DUAL; +---------------------+ | SYSDATE() | +---------------------+ | 2019-02-15 20:37:36 | +---------------------+ 1 row in set (0.00 sec)
If you want to format the date, MySQL has a DATE_FORMAT() function.
You can check the MySQL Date and Time documentation for more info.
Oracle’s TO_DATE equivalent in MySQL is the STR_TO_DATE() function.
It’s almost identical to the one in Oracle: it returns the DATE data type, while in MySQL it returns the DATETIME data type.
SQL> SELECT TO_DATE ('20190218121212','yyyymmddhh24miss') as "NOW" FROM DUAL; NOW ------------------------- 18-FEB-19
mysql> SELECT STR_TO_DATE('2019-02-18 12:12:12','%Y-%m-%d %H:%i:%s') as "NOW" FROM DUAL; +---------------------+ | NOW | +---------------------+ | 2019-02-18 12:12:12 | +---------------------+ 1 row in set (0.00 sec)
In MySQL, there’s no such support nor any equivalence for SYNONYM in Oracle.
A possible alternative can be possible with MySQL is using VIEW.
Although SYNONYM can be used to create an alias of a remote table,
CREATE PUBLIC SYNONYM emp_table FOR [email protected]
In MySQL, you can take advantage of using FEDERATED storage engine.
CREATE TABLE hr_employees ( id INT(20) NOT NULL AUTO_INCREMENT, name VARCHAR(32) NOT NULL DEFAULT '', other INT(20) NOT NULL DEFAULT '0', PRIMARY KEY (id), INDEX name (name), INDEX other_key (other) ) ENGINE=FEDERATED DEFAULT CHARSET=utf8mb4 CONNECTION='mysql://[email protected]emote_host:9306/federated/test_table';
Or you can simplify the process with CREATE SERVER syntax, so that when creating a table acting as your SYNONYM for accessing a remote table, it will be easier. See the documentation for more info on this.
Behaviour of Empty String and NULL
Take note that in Percona Server / MySQL, empty string is not NULL whereas Oracle treats empty string as null values.
SQL> SELECT CASE WHEN '' IS NULL THEN 'Yes' ELSE 'No' END AS "Null Eval" FROM dual; Nul --- Yes
mysql> SELECT CASE WHEN '' IS NULL THEN 'Yes' ELSE 'No' END AS "Null Eval" FROM dual; +-----------+ | Null Eval | +-----------+ | No | +-----------+ 1 row in set (0.00 sec)
In MySQL, there’s no exact same approach to what Oracle does for SEQUENCE.
Although there are some posts that are simulating the functionality of this approach, you might be able to try to get the next key using LAST_INSERT_ID() as long as your table’s clustered index, PRIMARY KEY, is defined with << is there something missing? >>
Character String Functions
Unlike Oracle, MySQL / Percona Server has a handful of string functions but not as many helpful functions built-in to the database.
It would be too long to discuss it here one-by-one, but you can check the documentation from MySQL and compare this against Oracle’s string functions.
Insert/Update/Delete statements from Oracle are congruous in MySQL.
Oracle’s INSERT ALL/INSERT FIRST is not supported in MySQL.
Otherwise, you’d need to state your MySQL queries one-by-one.
SQL> INSERT ALL INTO CUSTOMERS (customer_id, customer_name, city) VALUES (1000, 'Jase Alagaban', 'Davao City') INTO CUSTOMERS (customer_id, customer_name, city) VALUES (2000, 'Maximus Aleksandre Namuag', 'Davao City') SELECT * FROM dual; 2 rows created.
2 rows created.
But in MySQL, you have to run the insert one at a time:
mysql> INSERT INTO CUSTOMERS (customer_id, customer_name, city) VALUES (1000, 'Jase Alagaban', 'Davao City'); Query OK, 1 row affected (0.02 sec) mysql> INSERT INTO CUSTOMERS (customer_id, customer_name, city) VALUES (2000, 'Maximus Aleksandre Namuag', 'Davao City'); Query OK, 1 row affected (0.00 sec)
The INSERT ALL/INSERT FIRST doesn’t compare to how it is used in Oracle, where you can take advantage of conditions by adding a WHEN keyword in your syntax; there’s no equivalent option in MySQL / Percona Server in this case.
Hence, your alternative solution on this is to use procedures.
Outer Joins “+” Symbol
In Oracle, using + operator for left and right joins is not supported at present in MySQL as + operator is only used for arithmetic decisions.
Hence, if you have + operator in your existing Oracle SQL statements, you need to replace this with LEFT JOIN or RIGHT JOIN.
You might want to check the official documentation for “Outer Join Simplification” of MySQL.
START WITH..CONNECT BY
Oracle uses START WITH..CONNECT BY for hierarchical queries.
Starting with MySQL / Percona 8.0, there is support for generating hierarchical data results which uses models such as adjacency list or nested set models. This is called Common Table Expressions (CTE) in MySQL.
Similar to PostgreSQL, MySQL uses WITH RECURSIVE syntax for hierarchical queries so translate CONNECT BY statement into WITH RECURSIVE statement.
Check down below on how it differs from ORACLE and in MySQL / Percona Server.
SELECT cp.id, cp.title, CONCAT(c2.title, ' > ' || cp.title) as path FROM category cp INNER JOIN category c2 ON cp.parent_id = c2.id WHERE cp.parent_id IS NOT NULL START WITH cp.id >= 1 CONNECT BY NOCYCLE PRIOR c2.id=cp.parent_id;
And in MySQL:
WITH RECURSIVE category_path (id, title, path) AS ( SELECT id, title, title as path FROM category WHERE parent_id IS NULL UNION ALL SELECT c.id, c.title, CONCAT(cp.path, ' > ', c.title) FROM category_path AS cp JOIN category AS c ON cp.id = c.parent_id ) SELECT * FROM category_path ORDER BY path;
PL/SQL in MySQL / Percona?
MySQL / Percona RDBMS has a different approach than Oracle’s PL/SQL.
MySQL uses stored procedures or stored functions, which is similar to PL/SQL and syntax using BEGIN..END syntax.
Oracle’s PL/SQL is compiled before execution when it is loaded into the server, while MySQL is compiled and stored in the cache when it’s invoked.
You may want to checkout this documentation as a reference guide on converting your PL/SQL to MySQL.
I did some research for any tools that could be a de facto standard for migration but I couldn’t find a good answer.
Though, I did find sqlines and it looks simple but promising.
While I didn’t deep-dive into it, the website offers a handful of insights, which could help you on migrating from Oracle to MySQL/Percona Server. There are also paid tools such as this and this.
I’ve also searched through github but found nothing much more appealing as a resolution to the problem. Hence, if you’re aiming to migrate from Oracle and to Amazon, they have AWS Schema Conversion Tool for which migrating from Oracle to MySQL is supported.
Overall, the reason why migration is not an easy thing to do is mainly because Oracle RDBMS is such a beast with lots of features that Percona Server / MySQL or MariaDB RDBMS still do not have.
Anyhow, if you find or know of any tools that you find helpful and beneficial for migrating from Oracle to MySQL / Percona Server, please leave a comment on this blog!
As part of your migration plan, testing is a vital task that plays a very important role and affects your decision with regards to migration.
The tool dbdeployer (a port of MySQL Sandbox) is a very helpful tool that you can take advantage of. This is pretty easy for you to try and test different approaches and saves you time, rather than setting up the whole stack if your purpose is to try and test the RDBMS platform first.
For testing your SQL stored routines (functions or procedures), triggers, events, I suggest you use these tools mytap or the Google’s Unit Testing Framework.
Percona as well offers a number of tools that are available for download on their website. Checkout Percona Toolkit here. You can cherry-pick the tools according to your needs especially for testing and production-usage tasks.
Overall, things that you need to keep-in-mind as your guidelines when doing a test for your MySQL Server are:
- After your installation, you need to consider doing some tuning. Checkout this Percona blog for help.
- Do some benchmarks and stress-load testing for your configuration setup on your current node. Checkout mysqlslap and sysbench which can help you with this. Also check out our blog “How to Benchmark Performance of MySQL & MariaDB using SysBench“.
- Check your DDL’s if they are correctly defined such as data-types, constraints, clustered and secondary indexes, or partitions, if you have any.
- Check your DML especially if syntax are correct and are saving the data correctly as expected.
- Check out your stored routines, events, trigger to ensure they run/return the expected results.
- Verify that your queries running are performant. I suggest you take advantage of open-source tools or try our ClusterControl product. It offers monitoring/observability especially of your MySQL / Percona Server. You can use ClusterControl here to monitor your queries and its query plan to make sure they are performant.
Subscribe to get our best and freshest content