Data Transfer Validation - Oracle to MySQL Migration

SQLines Data tool helps you validate the data transfer from Oracle to MySQL after migration.

Row Count Check

The fastest and simplest way that can reveal a critical problem in data migration is to check the number of rows in corresponding Oracle and MySQL tables.

SQLines Data tool allows you to easily compare tables by row count. The tool takes into account changes in schema and tables names.

Data Validation in Columns and Rows

The most reliable way to validate the data migration is to compare data in each column and row.

SQLines Data tool can order the data by primary key, unique constraint or index, or all columns, then retrieve rows from Oracle and MySQL and compare the data.

The tool takes into account data transformations made during the data transfer.

Validating Data in CHAR Columns

In Oracle, data from a CHAR column is retrieved with the trailing spaces, while in MySQL the trailing spaces are trimmed by default.

  -- Oracle CHAR(5) value includes the trailing spaces on retrieval
  'SFO  '
 
  -- MySQL CHAR(5) value does not include the trailing spaces on retrieval
  'SFO'

When SQLines Data validates the data from CHAR columns, it does not take into account the trailing spaces. For more information, see Trailing Spaces in CHAR on Retrieval.

Validating Data in TIMESTAMP Columns

Oracle TIMESTAMP column can contain the fractional part (up to nanoseconds) that migrated to MySQL DATETIME data type that does not support fraction until MySQL 5.6

  -- Oracle TIMESTAMP value
  '1994-05-08 09:04:37.000000'
 
  -- MySQL DATETIME value until version 5.6
  '1994-05-08 09:04:37'

When SQLines Data compares Oracle TIMESTAMP and MySQL DATETIME columns it ignores the fractional part.

Validating Data in TIMESTAMP WITH TIME ZONE Columns

Oracle TIMESTAMP WITH TIME ZONE column contains the time zone information. MySQL does not have any datetime data type that can store the time zone, so TIMESTAMP WITH TIME ZONE is migrated to MySQL DATETIME data type and all values automatically converted to the UTC time zone.

  -- Oracle TIMESTAMP WITH TIME ZONE value
  '1994-05-08 09:04:37.000000 AM -08:00'
 
  -- MySQL DATETIME value in UTC time zone
  '1994-05-08 17:04:37'

When SQLines Data validates Oracle TIMESTAMP WITH TIME ZONE and MySQL DATETIME columns it compares the UTC values.

For more information, see TIMESTAMP WITH TIME ZONE to UTC Time Zone Conversion.