OracleGoldenGate與AnalyticDBMySQL版相容性及使用說明 - 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>