SQLines Data Tool - Database Migration from Oracle to MySQL

SQLines Data is a scalable, high performance data transfer, schema conversion and validation tool for Oracle to MySQL migration.

The tool requires connections to Oracle (OCI) and MySQL (MySQL C API) databases. To convert standalone SQL statements and SQL scripts, use SQLines SQL Converter tool.

SQLines Data benefits:

Migration Features

• All Data Types
• NOT NULL Constraints
• DEFAULT Clauses
• CHECK Constraints
• Primary and Foreign Keys
• Unique Constraints
• Indexes
• Comments on Tables and Columns
• Schema Name Mapping
• Validation by Row Count

You can use SQLines SQL Converter tool to convert stored procedures, functions, triggers, views and other objects.

Advanced Database Schema Conversion and Data Transfer Features

• TIMESTAMP WITH TIME ZONE to UTC time zone conversion
• Option to disable/enable foreign key constraint checks

Migration Validation

• Comparing column data in each row taking into account data transformations
• Comparing the number of rows in Oracle and MySQL tables

Scalability and High-Performance

• Transferring and validating tables in multiple concurrent sessions
• Performing concurrent read and write operations in each session
• Using Oracle OCI with array processing to extract data from Oracle
• Using MySQL C API with LOAD DATA INFILE command to load data to MySQL
• Direct in-memory data transfer, no disk space required for export
• Multi-threaded C/C++ application
• Linux and Windows, 32-bit and 64-bit

Designed for DBAs and Enterprise-Class Migrations

• Easy to set up and configure the transfer of a large number of tables
• Command line with powerful and easy to use options
• Wildcards to select and exclude tables
• Comprehensive performance stats to help you tune the data transfer
• No space required to hold Oracle export
• No ODBC drivers and ADO.NET providers required
• No .NET dependency
• No installation required

Logging and Statistics

• Comprehensive performance stats to help you tune the data transfer
• Execution time for each DDL statement
• Read and write performance for each table (rows/sec and bytes/sec)
• Saving all executed DDL SQL statements into .sql file
• Saving all failed DDL SQL statements into a separate .sql file
• Summary performance information

You can use SQLines Data tool in command line. Just launch sqldata.exe on Windows or ./sqldata on Linux with the specified options.

For information how to set up Oracle and MySQL connections, see SQLines Data Connection String Formats.

Examples:

• Transfer a single table

-t option defines the table name, -sd and -td options (source and target databases) specify the connection strings for Oracle and MySQL, respectively.

This command transfers table emp from Oracle database to MySQL db_name database located on host.

For list of options, see Command Line Reference.

Troubleshooting SQLines Data for Oracle to MySQL migration:

• Data Transfer - Loading local data is disabled
• Duplicate entry for key 'PRIMARY'

There are two main sources that can help you troubleshoot SQLines Data:

• sqldata.log file

sqldata.log file contains the detailed information about MySQL to Oracle migration process.

By default, sqldata.log file is located in the current working directory. You can use -log command line option to change its location and file name.

• sqldata_ddl.sql file

sqldata_ddl.sql file contains information about all DDL statements executed in MySQL during migration.

In this file you can see the full SQL statements, the execution result (success or failure) and the execution time. In case of an error, you can find the error message returned by MySQL.

By default, sqldata_ddl.sql file is located in the current working directory. You can use -out command line option to change its location.

• sqldata_failed.sql file

If there are any failed SQL statements, you can find them in sqldata_failed.sql file.

Sometimes you can receive Loading local data is disabled; this must be enabled on both the client and server sides during the data transfer:

  USERS - Data transfer failed
	Level: Error; Code: 3948; Message: Loading local data is disabled; 
        this must be enabled on both the client and server sides
     Rows read:     17 (274 rows/sec)
     Rows written:  0 (0 rows/sec, 0 bytes, 0 bytes/sec)
     Transfer time: 812 ms (62 ms read, 0 ms write)

The tool uses in-memory LOAD DATA LOCAL INFILE command, and possible reason is that it is not allowed by your MySQL server configuration.

Edit my.cnf (or my.ini on Windows, for example, at C:\ProgramData\MySQL\MySQL Server 8.0) and set local-infile=1 in [mysqld] section:

[mysqld]
local-infile=1

You have to restart the MySQL server for the change to take effect.

When creating a primary key or unique constraint after loading the data, you can get a “Duplicate entry for key 'PRIMARY'” error. See this article for details.