通过OGG将Oracle中的数据同步到AnalyticDB for MySQL - 云原生数据仓库AnalyticDB

Oracle Golden Gate（OGG）可用于将Oracle中的数据同步到AnalyticDB for MySQL中。

Oracle Golden Gate数据类型与AnalyticDB for MySQL兼容性

源端	目标端		同步行为
Oracle	MySQL	AnalyticDB for MySQL	INSERT	UPDATA	DELETE
number(3)	bool或tinyint(1)	boolean	支持	支持	支持
number(3)	tinyint	tingyint	支持	支持	支持
number(5)	smallint	smallint	支持	支持	支持
number(10)	int	int/integer	支持	支持	支持
number(19)	bigint	bigint	支持	支持	支持
float(24)	float	float	支持	支持	支持
float(24)	double	double	支持	支持	支持
float(24)	decimal	decimal	支持	支持	支持
varchar2(128)	char	varchar(128)	支持	支持	支持
varchar2(2000)	varchar(255)	varchar(255)	支持	支持	支持
varchar2(4000)	text	varchar(65535)	支持	支持	支持
date	date	date	支持	支持	支持
date	time	time	不支持该功能、MySQL不适用	不支持该功能、MySQL不适用	不支持该功能、MySQL不适用
date	datetime	datetime	支持	支持	支持
date	timestamp	timestamp	支持	支持	支持

通过OGG将Oracle数据同步到AnalyticDB for MySQL

使用OGG账号登录ECS。
```
sqlplus ogg/ogg
```

执行以下SQL在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)
);

Oracle建表成功后，在OGG源端添加trandata。
```
[OGG账号登录ECS] cd /odata/ogg_o_12202
[启动OGG] ./ggsci
ggsci> dblogin userid goldengate, password ogg
ggsci> add trandata users.xqtest15
```
说明
上述命令末尾不要加分号（;），否则将报错No viable tables matched specification。

执行以下SQL在MySQL中创建表，存储Oracle中的元数据。

-- checkpoint表
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表
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';

执行以下SQL在AnalyticDB for MySQL中创建目标表，存储从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'

附录

INSERT同步

Oracle 端表结构及查询结果：

SQL> desc users.xqtest15
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                        NOT NULL 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

SQL> select * from users.xqtest15;

       C1        C2         C3         C4         C5         C6         C7
--------- --------- ---------- ---------- ---------- ---------- ----------
       C8 C9
--------- ---

C10

C11

       C12           C13           C14           C15
------------- ------------- ------------- -------------
        1          1        100        100      65535       3.14       3.14
     3.14 a
aaaaabbbbbb

aaaaabbbbbbbbbb
06-JAN-20     06-JAN-20     06-JAN-20     06-JAN-20

ADB 3.0 端建表语句、查询结果及表结构：

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(256),
    ->  `c11` varchar(65535),
    ->  `c12` date,
    ->  `c13` time,
    ->  `c14` datetime,
    ->  `c15` timestamp,
    ->  primary key (c1)
    -> ) DISTRIBUTE BY HASH(`c1`) INDEX_ALL='Y';
Query OK, 0 rows affected (3.00 sec)

mysql> select * from xqtest15;
+------+------+------+------+-------+------+------+------+------+-------------+----------------+------------+----------+---------------------+---------------------+
| c1   | c2   | c3   | c4   | c5    | c6   | c7   | c8   | c9   | c10         | c11            | c12        | c13      | c14                 | c15                 |
+------+------+------+------+-------+------+------+------+------+-------------+----------------+------------+----------+---------------------+---------------------+
|    1 |    1 |  100 |  100 | 65535 | 3.14 | 3.14 |    3 | a    | aaaaabbbbbb | aaaaabbbbbbbbbb | 2020-01-06 | 21:01:38 | 2020-01-06 21:01:38 | 2020-01-06 21:01:38 |
+------+------+------+------+-------+------+------+------+------+-------------+----------------+------------+----------+---------------------+---------------------+
1 row in set (0.05 sec)

mysql> desc xqtest15;
+-------+---------------+------+-----+---------+---------------------+
| Field | Type          | Null | Key | Default | Extra               |
+-------+---------------+------+-----+---------+---------------------+
| c1    | int           | NO   | PRI | NULL    | distribute(0) PRI(0)|
| c2    | boolean       | YES  | MUL | NULL    |                     |
| c3    | tinyint       | YES  | MUL | NULL    |                     |
| c4    | smallint      | YES  | MUL | NULL    |                     |
| c5    | bigint        | YES  | MUL | NULL    |                     |
| c6    | float         | YES  | MUL | NULL    |                     |
| c7    | double        | YES  | MUL | NULL    |                     |
| c8    | decimal(24, 0)| YES  | MUL | NULL    |                     |
| c9    | varchar       | YES  | MUL | NULL    |                     |
| c10   | varchar       | YES  | MUL | NULL    |                     |
+-------+---------------+------+-----+---------+---------------------+

UPDATE同步

SQL> update users.xqtest15 set c2=0,c3=120,c4=120,c5=32765,c6=99.99,c7=99.99,c8=99.99,c9='c',c10='ccc c',c11='cccc',c12=sysdate,c13=sysdate,c14=sysdate,c15=sysdate where c1=1;

1 row updated.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL>
SQL>
SQL>

mysql> select * from xqtest15;
+------+------+------+------+-------+-------+-------+------+------+------+------+------------+---------------------+---------------------+---------------------+
| c1   | c2   | c3   | c4   | c5    | c6    | c7    | c8   | c9   | c10  | c11  | c12        | c13                 | c14                 | c15                 |
+------+------+------+------+-------+-------+-------+------+------+------+------+------------+---------------------+---------------------+---------------------+
|    1 |    0 |  120 |  120 | 32765 | 99.99 | 99.99 |  100 | c    | cccc | cccc | 2020-01-06 | 21:28:23            | 2020-01-06 21:28:23 | 2020-01-06 21:28:23 |
+------+------+------+------+-------+-------+-------+------+------+------+------+------------+---------------------+---------------------+---------------------+
1 row in set (0.04 sec)

mysql>

DELETE同步

SQL> delete from users.xqtest15 where c1=1;

1 row deleted.

SQL> commit;

Commit complete.

SQL>

mysql> select * from xqtest15;
Empty set (0.03 sec)

mysql>
mysql>
mysql>
mysql>
mysql>
mysql>