SQLines Data Tool - Database Migration from Oracle to MariaDB

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

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

Databases:

• Oracle 19c, 18c, 12c, 11g, 10g and 9i
• MariaDB 10.x and 5.x

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.

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 MariaDB C API with LOAD DATA INFILE command to load data to MariaDB
• 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 MariaDB 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 MariaDB, respectively.

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

For list of options, see Command Line Reference.

Troubleshooting SQLines Data for Oracle to MariaDB migration:

• SQLines Data Logs
• Data Transfer - The used command is not allowed with this MariaDB version
• Data Transfer - ORA-01406: fetched column value was truncated
• Chinese Characters Converted to Question Marks ???
• Row Size Too Large. The maximum row size for the used table type, not counting BLOBs, is 65535

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

• sqldata.log file

sqldata.log file contains the detailed information about MariaDB 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 MariaDB 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 MariaDB.

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.

• sqldata.trc file

You can enable trace by specifying -trace=yes in the sqldata.cfg file. The trace file can be helpful for SQLines Data developers to resolve crashes or specific data issues.

Sometimes you can receive The used command is not allowed with this MariaDB version during the data transfer:

  USERS - Data transfer failed
	The used command is not allowed with this MariaDB version
     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 MariaDB server configuration.

Edit my.cnf (or my.ini on Windows) and set local-infile=1 in [mysqld] section:

[mysqld]
local-infile=1

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

During data export from Oracle you can face “ORA-01406: fetched column value was truncated” error. Most likely reason is that the length in bytes of CHAR or VARCHAR2 column stored in the Oracle database is smaller than the length of the column after the conversion at the client side for loading to MariaDB.

This can happen when, for example, for data in single byte ASCII character set that are converted to UTF8mb4. To fix this issue you have to increase the maximum length in bytes of CHAR and VARCHAR columns. To do this automatically, edit sqldata.cfg and set -char_length_ratio option.

For example, if you set -char_length_ratio=1.5, the maximum length of all CHAR and VARCHAR columns will be increased by 2x, so CHAR(10) in Oracle becomes CHAR(15) in MariaDB.

For examaple, let's assume you have an Oracle database with NLS_LANG set to TRADITIONAL CHINESE_HONG KONG.ZHT16HKSCS. If you are migrating to MariaDB having utf8mb4 character set, you can see ??? characters after migration.

What you need to do is to force Oracle to convert data to UTF at the client side when fetching the data (Oracle server character set remains unchanged). In our example you can set NLS_LANG to TRADITIONAL CHINESE_HONG KONG.AL32UTF8 at the Oracle client. Now you should Chinese characters in MariaDB.

You can get the following error when SQLines Data creates a table in MariaDB:

   TABLE_NAME - Create target table (Failed, 0 ms, session 1)
   
    Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. 
    This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

There is the option -mysql_varchar_to_text_size=4000 that can be set in the command line or sqldata.cfg configuration file that defines the size of VARCHAR columns when they are mapped to TEXT data type. This option is applied for MariaDB and MySQL.

Your Oracle table may have many VARCHAR2(4000) columns and their total length exceeds 65,535 bytes. You can use global data type mapping to change VARCHAR2(4000) conversion to TEXT:

in the sqlines_dtmap.txt. For more details, see -dtmapf configuration option.

For more details about row siz limit, see Row size too large.