6.2.1. Prepare: MySQL to Oracle Replication

When replicating from MySQL to Oracle there are a number of datatype differences that should be accommodated to ensure reliable replication of the information. The core differences are described in Table 5.1, “Data Type differences when replicating data from MySQL to Oracle”.

Table 6.1. Data Type differences when replicating data from MySQL to Oracle

MySQL Datatype Oracle Datatype Notes
INT NUMBER(10, 0)  
BIGINT NUMBER(19, 0)  
TINYINT NUMBER(3, 0)  
SMALLINT NUMBER(5, 0)  
MEDIUMINT NUMBER(7, 0)  
DECIMAL(x,y) NUMBER(x, y)  
FLOAT FLOAT  
CHAR(n) CHAR(n)  
VARCHAR(n) VARCHAR2(n) For sizes less than 2000 bytes data can be replicated. For lengths larger than 2000 bytes, the data will be truncated when written into Oracle
DATE DATE  
DATETIME DATE  
TIMESTAMP DATE  
TEXT CLOB Replicator can transform TEXT into CLOB or VARCHAR(N). If you choose VARCHAR(N) on Oracle, the length of the data accepted by Oracle will be limited to 4000. This is limitation of Oracle. The size of CLOB columns within Oracle is calculated in terabytes. If TEXT fields on MySQL are known to be less than 4000 bytes (not characters) long, then VARCHAR(4000) can be used on Oracle. This may be faster than using CLOB.
BLOB BLOB  
ENUM(...) VARCHAR(255) Use the EnumToString filter
SET(...) VARCHAR(255) Use the SetToString filter

When replicating from MySQL to Oracle, the ddlscan command can be used to generate DDL appropriate for the supported data types in the target database. In MySQL to Oracle deployments the DDL can be read from the MySQL server and generated for the Oracle server so that replication can begin without manually creating the Oracle specific DDL.

In addition, the following DDL differences and requirements exist:

  • Column orders on MySQL and Oracle must match, but column names do not have to match.

    Using the dropcolumn filter, columns can be dropped and ignored if required.

  • Each table within MySQL should have a Primary Key. Without a primary key, full-row based lookups are performed on the data when performing UPDATE or DELETE operations. With a primary key, the pkey filter can add metadata to the UPDATE/DELETE event, enabling faster application of events within Oracle.

  • Indexes on MySQL and Oracle do not have to match. This allows for different index types and tuning between the two systems according to application and dataserver performance requirements.

  • Keywords that are restricted on Oracle should not be used within MySQL as table, column or database names. For example, the keyword SESSION is not allowed within Oracle. Tungsten Replication determines the column name from the target database metadata by position (column reference), not name, so replication will not fail, but applications may need to be adapted. For compatibility, try to avoid Oracle keywords.

For more information on differences between MySQL and Oracle, see Oracle and MySQL Compared.

To make the process of migration from MySQL to Oracle easier, Tungsten Replication includes a tool called ddlscan which will read table definitions from MySQL and create appropriate Oracle table definitions to use during replication.

For reference information on the ddlscan tool, see Section 9.5, “The ddlscan Command”.