Oracle GoldenGate (OGG) can be used to synchronize Oracle data to AnalyticDB for MySQL.

Compatibility of OGG data types with AnalyticDB for MySQL

Source Destination Synchronization operations
Oracle MySQL AnalyticDB for MySQL INSERT UPDATE DELETE
NUMBER(3) BOOL or TINYINT(1) BOOLEAN Supported Supported Supported
NUMBER(3) TINYINT TINYINT Supported Supported Supported
NUMBER(5) SMALLINT SMALLINT Supported Supported Supported
NUMBER(10) INT INT or INTEGER Supported Supported Supported
NUMBER(19) BIGINT BIGINT Supported Supported Supported
FLOAT(24) FLOAT FLOAT Supported Supported Supported
FLOAT(24) DOUBLE DOUBLE Supported Supported Supported
FLOAT(24) DECIAML DECIMAL Supported Supported Supported
VARCHAR2(128) CHAR VARCHAR(128) Supported Supported Supported
VARCHAR2(2000) VARCHAR(255) VARCHAR(255) Supported Supported Supported
VARCHAR2(4000) TEXT VARCHAR(65535) Supported Supported Supported
DATE DATE DATE Supported Supported Supported
DATE TIME TIME N/A N/A N/A
DATE DATETIME DATETIME Supported Supported Supported
DATE TIMESTAMP TIMESTAMP Supported Supported Supported

Use OGG to synchronize Oracle data to AnalyticDB for MySQL

  1. Use an Oracle account to log on to an ECS instance.
    sqlplus ogg/ogg
  2. Execute the following SQL statement to create a source table in Oracle:
    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)
    );
  3. After the source Oracle table is created, add trandata to OGG.
    [Use an Oracle account to log on to an ECS instance] cd /odata/ogg_o_12202
    [Start OGG] ./ggsci
    ggsci> dblogin userid goldengate, password ogg
    ggsci> add trandata users.xqtest15
    Note Do not add a semicolon (;) to the end of the preceding command. Otherwise, the No viable tables matched specification error will be returned.
  4. Execute the following SQL statement to create tables in MySQL to store the metadata of the source Oracle table:
    -- The ckpt1220 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`)
    ) DISTRIBUTE BY HASH(`group_key`) INDEX_ALL='Y';
    -- The ckpt1220_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`)
    ) DISTRIBUTE BY HASH(`group_key`) INDEX_ALL='Y';
  5. Execute the following SQL statement to create a destination table in AnalyticDB for MySQL to store the data synchronized from Oracle:
    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)
    ) DISTRIBUTE BY HASH(`c1`) INDEX_ALL='Y'

Appendix

  • Synchronize the INSERT operationinsert
  • Synchronize the UPDATE operationupdate
  • Synchronize the DELETE operationdelete