Oracle GoldenGate and AnalyticDB for MySQL compatibility and usage instructions - AnalyticDB

Oracle GoldenGate (OGG) synchronizes Oracle data to AnalyticDB for MySQL in real time. This topic covers the data type mapping between Oracle and AnalyticDB for MySQL, and walks through how to set up end-to-end synchronization using OGG.

Prerequisites

Before you begin, make sure you have:

An Oracle database with OGG installed at /odata/ogg_o_12202
An Elastic Compute Service (ECS) instance that hosts the Oracle database
An AnalyticDB for MySQL instance
A MySQL instance for storing OGG checkpoint metadata

Data type compatibility

OGG routes data from Oracle through MySQL before writing to AnalyticDB for MySQL. The tables below show how Oracle data types map to AnalyticDB for MySQL data types at each stage, and which DML operations are supported.

Types are grouped by category. For each row, check the Exact match column: Yes means the Oracle type maps to an equivalent type with no precision or semantic loss; No means the mapping is approximate and you should verify compatibility for your data.

Numeric types

Oracle type	Oracle type notes	MySQL type	AnalyticDB for MySQL type	Exact match	INSERT	UPDATE	DELETE
NUMBER(3)	3-digit integer	BOOL or TINYINT(1)	BOOLEAN	No	Supported	Supported	Supported
NUMBER(3)	3-digit integer	TINYINT	TINYINT	Yes	Supported	Supported	Supported
NUMBER(5)	5-digit integer	SMALLINT	SMALLINT	Yes	Supported	Supported	Supported
NUMBER(10)	10-digit integer	INT	INT or INTEGER	Yes	Supported	Supported	Supported
NUMBER(19)	19-digit integer	BIGINT	BIGINT	Yes	Supported	Supported	Supported
FLOAT(24)	Single-precision float	FLOAT	FLOAT	Yes	Supported	Supported	Supported
FLOAT(24)	Single-precision float	DOUBLE	DOUBLE	No	Supported	Supported	Supported
FLOAT(24)	Single-precision float	DECIMAL	DECIMAL	No	Supported	Supported	Supported

Character types

Oracle type	Oracle type notes	MySQL type	AnalyticDB for MySQL type	Exact match	INSERT	UPDATE	DELETE
VARCHAR2(128)	Variable-length string, up to 128 bytes	CHAR	VARCHAR(128)	No	Supported	Supported	Supported
VARCHAR2(2000)	Variable-length string, up to 2000 bytes	VARCHAR(255)	VARCHAR(255)	No — truncated to 255 chars	Supported	Supported	Supported
VARCHAR2(4000)	Variable-length string, up to 4000 bytes	TEXT	VARCHAR(65535)	No	Supported	Supported	Supported

Date and time types

Oracle type	Oracle type notes	MySQL type	AnalyticDB for MySQL type	Exact match	INSERT	UPDATE	DELETE
DATE	Stores date and time (year, month, day, hour, minute, second)	DATE	DATE	No — time component is lost	Supported	Supported	Supported
DATE	Stores date and time (year, month, day, hour, minute, second)	TIME	TIME	No — date component is lost	N/A	N/A	N/A
DATE	Stores date and time (year, month, day, hour, minute, second)	DATETIME	DATETIME	Yes	Supported	Supported	Supported
DATE	Stores date and time (year, month, day, hour, minute, second)	TIMESTAMP	TIMESTAMP	Yes	Supported	Supported	Supported

Note

Oracle's DATE type stores both date and time components. Mapping Oracle DATE to MySQL TIME drops the date component and does not support DML synchronization. Map Oracle DATE to DATETIME or TIMESTAMP to preserve full date and time information.

Synchronize Oracle data to AnalyticDB for MySQL

The synchronization flow has three main phases: create the source table in Oracle and configure OGG, create the checkpoint metadata tables in MySQL, then create the target table in AnalyticDB for MySQL.

The following DDL statements show the source Oracle table and its corresponding target AnalyticDB for MySQL table side by side, so you can see the full type mapping in context before running the individual steps.

Source (Oracle):

create table users.xqtest15 (
  c1  number(10),   -- maps to int
  c2  number(1),    -- maps to boolean
  c3  number(3),    -- maps to tinyint
  c4  number(5),    -- maps to smallint
  c5  number(19),   -- maps to bigint
  c6  float(24),    -- maps to float
  c7  float(24),    -- maps to double
  c8  float(24),    -- maps to decimal(24, 0)
  c9  char(1),      -- maps to varchar(128)
  c10 varchar2(2000), -- maps to varchar(255)
  c11 varchar2(4000), -- maps to varchar(65535)
  c12 date,         -- maps to date
  c13 date,         -- maps to time (DML not supported)
  c14 date,         -- maps to datetime
  c15 date,         -- maps to timestamp
  primary key(c1)
);

Target (AnalyticDB for MySQL):

CREATE TABLE `xqtest15` (
  `c1`  int,
  `c2`  boolean,
  `c3`  tinyint,
  `c4`  smallint,
  `c5`  bigint,
  `c6`  float,
  `c7`  double,
  `c8`  decimal(24, 0),
  `c9`  varchar(128),
  `c10` varchar(255),
  `c11` varchar(65535),
  `c12` date,
  `c13` time,
  `c14` datetime,
  `c15` timestamp,
  primary key (c1)
) DISTRIBUTED BY HASH(`c1`) INDEX_ALL='Y';

Step 1: Log on to the ECS instance

Log on to the ECS instance using the Oracle account:

sqlplus ogg/ogg

Step 2: Create the source table in Oracle

Run the following SQL statement to create the source table users.xqtest15:

drop table users.xqtest15;
create table users.xqtest15 (
  c1  number(10),
  c2  number(1),
  c3  number(3),
  c4  number(5),
  c5  number(19),
  c6  float(24),
  c7  float(24),
  c8  float(24),
  c9  char(1),
  c10 varchar2(2000),
  c11 varchar2(4000),
  c12 date,
  c13 date,
  c14 date,
  c15 date,
  primary key(c1)
);

Step 3: Configure OGG trandata

After creating the source table, enable supplemental logging for it in OGG:

# Navigate to the OGG installation directory and start ggsci
cd /odata/ogg_o_12202
./ggsci

Run the following commands in the ggsci prompt:

ggsci> dblogin userid goldengate, password ogg
ggsci> add trandata users.xqtest15

Important

Do not add a semicolon (;) at the end of the add trandata command. Adding a semicolon causes the No viable tables matched specification error.

Step 4: Create the checkpoint metadata tables in MySQL

OGG requires two checkpoint tables in MySQL to track replication state. Run the following SQL statements to create them:

-- Checkpoint table
CREATE TABLE `ckpt1220` (
  `group_name`      varchar(8)      NOT NULL,
  `group_key`       decimal(19,0)   NOT NULL,
  `seqno`           decimal(10,0)   DEFAULT NULL,
  `rba`             decimal(19,0)   NOT NULL,
  `audit_ts`        varchar(29)     DEFAULT NULL,
  `create_ts`       datetime        NOT NULL,
  `last_update_ts`  datetime        NOT NULL,
  `current_dir`     varchar(255)    NOT NULL,
  `log_bsn`         varchar(128)    DEFAULT NULL,
  `log_csn`         varchar(128)    DEFAULT NULL,
  `log_xid`         varchar(128)    DEFAULT NULL,
  `log_cmplt_csn`   varchar(128)    DEFAULT NULL,
  `log_cmplt_xids`  varchar(2000)   DEFAULT NULL,
  `version`         decimal(3,0)    DEFAULT NULL,
  PRIMARY KEY (`group_name`, `group_key`)
) DISTRIBUTED BY HASH(`group_key`) INDEX_ALL='Y';

-- Checkpoint LOX table
CREATE TABLE `ckpt1220_lox` (
  `group_name`          varchar(8)      NOT NULL,
  `group_key`           decimal(19,0)   NOT NULL,
  `log_cmplt_csn`       varchar(128)    NOT NULL,
  `log_cmplt_xids_seq`  decimal(5,0)    NOT NULL,
  `log_cmplt_xids`      varchar(2000)   NOT NULL,
  PRIMARY KEY (`group_name`, `group_key`, `log_cmplt_csn`, `log_cmplt_xids_seq`)
) DISTRIBUTED BY HASH(`group_key`) INDEX_ALL='Y';

Step 5: Create the target table in AnalyticDB for MySQL

Create the target table that receives the synchronized data from Oracle. The column types follow the mapping in the Data type compatibility section above.

CREATE TABLE `xqtest15` (
  `c1`  int,
  `c2`  boolean,
  `c3`  tinyint,
  `c4`  smallint,
  `c5`  bigint,
  `c6`  float,
  `c7`  double,
  `c8`  decimal(24, 0),
  `c9`  varchar(128),
  `c10` varchar(255),
  `c11` varchar(65535),
  `c12` date,
  `c13` time,
  `c14` datetime,
  `c15` timestamp,
  primary key (c1)
) DISTRIBUTED BY HASH(`c1`) INDEX_ALL='Y';

Appendix

The following screenshots show the synchronization results for each DML operation.

INSERT synchronization
UPDATE synchronization
DELETE synchronization