Configure the source and target hosts following the prerequisites outlined in Appendix B, Prerequisites followed by the additional prerequisites specific to Oracle Targets outlined in Section 4.7.1.1, “Additional Prerequisites for Oracle Targets” then finally follow the appropriate steps for the required extractor topology outlined in Chapter 3, Deploying MySQL Extractors.
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 4.3, “Data Type differences when replicating data from MySQL to Oracle”.
Table 4.3. 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 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 Cluster 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 Cluster 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 8.6, “The ddlscan Command”.
When replicating to Oracle there are a number of key steps that must be performed. The primary process is the preparation of the Oracle database and DDL for the database schema that are being replicated. Although DDL statements will be replicated to Oracle, they will often fail because of SQL language differences. Because of this, tables within Oracle must be created before replication starts.