AnalyticDB:Oracle Golden Gate

Compatibility of OGG data types with AnalyticDB for MySQL

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`)
   ) DISTRIBUTED 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`)
   ) DISTRIBUTED 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)
   ) DISTRIBUTED BY HASH(`c1`) INDEX_ALL='Y'

Appendix

• Synchronize the INSERT operation

• Synchronize the UPDATE operation

• Synchronize the DELETE operation