Oracle Golden Gate (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
- Use an Oracle account to log on to an ECS instance.
sqlplus ogg/ogg
- 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) );
- 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. - 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';
- 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 operation
- Synchronize the UPDATE operation
- Synchronize the DELETE operation