Mastering Oracle‑to‑MySQL Migration: Tools, Pitfalls, and Performance Tweaks

Pre‑migration preparation

Before decommissioning Oracle, decide on a replacement database, select migration tools, plan incremental data capture, and map differences in data types, objects (views, procedures, triggers) and performance impact. Post‑migration data comparison and performance testing are essential.

Target database selection

MySQL 5.7 was chosen over PostgreSQL because it delivered higher QPS on identical hardware. The production architecture uses MySQL 5.7 (official release) with optional Percona or MariaDB patches, MyCat 5.6 for read/write splitting, and a dual‑datacenter MHA setup.

Table and data‑object migration tools

SQL LOAD

Export Oracle tables to CSV or SQL using spool (SQL*Plus) or tools such as SQL Developer/TOAD, then import with LOAD DATA INFILE in MySQL.

-- Export from Oracle
spool data.csv
set sqlformat csv
select * from my_table;
spool off;

-- Load into MySQL
LOAD DATA INFILE 'data.csv' INTO TABLE my_table
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '
';

Pros: fast, simple. Cons: no automation, poor LOB handling, empty‑string conversion (numeric → 0, date → zero value).

Python scripts

Custom Python code can map source to target tables, support full and incremental loads, and perform data transformations. Typical workflow:

Create a configuration table that maps Oracle tables to MySQL tables and flags full vs. incremental load.

Generate extraction, transformation, and load code based on the mapping.

Schedule the script with cron for regular execution.

Drawback: slower than bulk LOAD DATA and requires extensive error‑handling code.

Oracle GoldenGate (OGG)

Provides real‑time change data capture and replication, allowing Oracle to stay online during migration. Required setup:

Oracle 12.1.2 paired with MySQL 5.6 (or compatible version).

Configure defgen and place parameter files in the same directory.

Set MySQL binlog_format=ROW and use InnoDB tables only.

DDL is not replicated; apply DDL manually on both sides.

MySQL Migration Toolkit

Java‑based tool (also integrated into MySQL Workbench) that can automatically create target tables and migrate data. Download URL: https://downloads.mysql.com/archives/migration/

Best for small batches on Windows; may hang with large volumes.

Kettle (Pentaho Data Integration)

Open‑source Java ETL platform with a graphical job designer. Typical steps:

Load the MySQL JDBC driver JAR.

Configure Oracle and MySQL connections.

Drag‑and‑drop input and output steps, map columns, and run the job.

Supports real‑time sync, automatic table creation, and detailed logging.

Migration of other objects

Views : MySQL does not allow subqueries in the FROM clause of a view. Split nested views into separate layers.

Materialized views : Not natively supported. Simulate with scheduled events and stored procedures, but real‑time updates may impact performance.

Triggers, stored procedures, packages : Syntax differences include variable declaration location, lack of %TYPE, different loop constructs, and no cursor returns. Use SET instead of := for assignment and ensure a space after -- comments.

Pagination : Oracle uses ROWNUM; MySQL uses LIMIT.

JOIN syntax : Oracle’s (+) outer‑join operator must be rewritten to standard LEFT/RIGHT JOIN syntax.

GROUP BY : Oracle enforces that all non‑aggregated columns appear in the GROUP BY clause. MySQL’s default mode does not; enable ONLY_FULL_GROUP_BY to obtain strict behavior.

Bitmap indexes : Not available in MySQL; consider B‑tree indexes as a substitute.

Partitioned tables : MySQL treats the partition key as part of the primary key; redesign the schema accordingly.

Roles : Supported from MySQL 8.0 onward; older versions require manual privilege scripts.

Emoji and special characters : Use utf8mb4 charset to preserve Unicode symbols.

Data validation

Validate migrated data using multiple methods:

Compare COUNT(*) results between Oracle and MySQL.

Extract data from both systems with an ETL tool, generate cubes, and compare metrics.

Leverage MySQL sql_mode settings (e.g., TRADITIONAL, ONLY_FULL_GROUP_BY) to enforce strict validation.

Reference for sql_mode: https://dev.mysql.com/doc/refman/5.5/en/sql-mode.html

Performance tuning parameters

Adjust the following MySQL settings before bulk loading to maximize throughput:

innodb_flush_log_at_trx_commit : 0 (flush once per second), 1 (flush on every commit), 2 (write to log buffer, flush once per second).

sync_binlog : 0 (OS handles sync) or >0 (fsync after N writes).

max_allowed_packet : Increase to accommodate large CLOBs, e.g., max_allowed_packet=16777216.

innodb_log_file_size : Larger values reduce checkpoint overhead but increase recovery time.

innodb_log_buffer_size : Bigger buffers allow larger transactions without frequent disk writes.

innodb_buffer_pool_size and innodb_buffer_pool_instances : Size the pool to fit most data and split into instances for high concurrency.

Conclusion

Select the migration tool that best fits the specific scenario—no single tool solves every problem. Thorough data verification is critical; errors discovered after migration may require a full redo. Allocate sufficient time for multiple iterations and troubleshooting.