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

Compatibility of OGG data types with AnalyticDB for MySQL

SourceDestinationSynchronization operations
OracleMySQLAnalyticDB for MySQLINSERTUPDATEDELETE
NUMBER(3)BOOL or TINYINT(1)BOOLEANSupportedSupportedSupported
NUMBER(3)TINYINTTINYINTSupportedSupportedSupported
NUMBER(5)SMALLINTSMALLINTSupportedSupportedSupported
NUMBER(10)INTINT or INTEGERSupportedSupportedSupported
NUMBER(19)BIGINTBIGINTSupportedSupportedSupported
FLOAT(24)FLOATFLOATSupportedSupportedSupported
FLOAT(24)DOUBLEDOUBLESupportedSupportedSupported
FLOAT(24)DECIAMLDECIMALSupportedSupportedSupported
VARCHAR2(128)CHARVARCHAR(128)SupportedSupportedSupported
VARCHAR2(2000)VARCHAR(255)VARCHAR(255)SupportedSupportedSupported
VARCHAR2(4000)TEXTVARCHAR(65535)SupportedSupportedSupported
DATEDATEDATESupportedSupportedSupported
DATETIMETIMEN/AN/AN/A
DATEDATETIMEDATETIMESupportedSupportedSupported
DATETIMESTAMPTIMESTAMPSupportedSupportedSupported

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