實(shí)驗(yàn)環(huán)境

• Debezium 版本 1.9 (2022-04-05)
• Debezium Tested Versions
  
• Oracle 版本是單機(jī)的 19.3
• 本測(cè)試參考文檔：https://debezium.io/documentation/reference/1.9/
• 基于 Debezium 的變更數(shù)據(jù)捕獲的架構(gòu):
  

啟動(dòng) Zookeeper

# 后臺(tái)運(yùn)行
docker run -d --name zookeeper -p 2181:2181 -p 2888:2888 -p 3888:3888 quay.io/debezium/zookeeper:1.9

# 實(shí)時(shí)查看 zookeeper 的日志信息
docker logs -f -t --tail 10 zookeeper

啟動(dòng) Kafka

# 后臺(tái)運(yùn)行
docker run -d --name kafka -p 9092:9092 --link zookeeper:zookeeper quay.io/debezium/kafka:1.9

# 實(shí)時(shí)查看 kafka 的日志信息
docker logs -f -t --tail 10 kafka

啟動(dòng) Oracle 19C 數(shù)據(jù)庫(kù)

• 參考文章：使用Docker裝一個(gè)Oracle 19C的單機(jī)測(cè)試環(huán)境
• 19C 數(shù)據(jù)庫(kù)里面創(chuàng)建 SCOTT 測(cè)試用戶(hù)和數(shù)據(jù)。

[oracle@ora11g ~]$ sqlplus scott/scott@192.168.0.40:1521/pdbtt
SQL> set line 100 pages 100
SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

配置 Oracle 19C 數(shù)據(jù)庫(kù)

• 確保數(shù)據(jù)庫(kù)啟動(dòng)歸檔模式，使用CDB的服務(wù)登錄

[root@docker ~]# sqlplus sys/oracle@192.168.0.40:1521/orcl as sysdba

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/oradata/ORCL/archive_logs
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence           7

• 啟用最小補(bǔ)充日志，使用CDB的服務(wù)登錄

[root@docker ~]# sqlplus sys/oracle@192.168.0.40:1521/orcl as sysdba
-- 啟用最小補(bǔ)充日志
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
-- 切換到 PDB 中，為表啟用補(bǔ)充日志
SQL> alter session set container=pdbtt;
ALTER TABLE scott.DEPT ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE scott.EMP ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE scott.BONUS ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE scott.SALGRADE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

• 在 CDB 和 PDB 中創(chuàng)建 LogMiner 用戶(hù)使用的表空間

[root@docker ~]# sqlplus sys/oracle@192.168.0.40:1521/orcl as sysdba
CREATE TABLESPACE logminer_tbs DATAFILE '/opt/oracle/oradata/ORCL/logminer_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
-- 切換到 PDB 中，創(chuàng)建表空間
SQL> alter session set container=pdbtt;
CREATE TABLESPACE logminer_tbs DATAFILE '/opt/oracle/oradata/ORCL/PDBTT/logminer_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

• 在 CDB 中創(chuàng)建 LogMiner 用戶(hù)并授予相關(guān)權(quán)限

[root@docker ~]# sqlplus sys/oracle@192.168.0.40:1521/orcl as sysdba
CREATE USER c##dbzuser IDENTIFIED BY dbz DEFAULT TABLESPACE logminer_tbs QUOTA UNLIMITED ON logminer_tbs CONTAINER=ALL;
GRANT CREATE SESSION TO c##dbzuser CONTAINER=ALL; 
GRANT SET CONTAINER TO c##dbzuser CONTAINER=ALL; 
GRANT SELECT ON V_$DATABASE to c##dbzuser CONTAINER=ALL; 
GRANT FLASHBACK ANY TABLE TO c##dbzuser CONTAINER=ALL; 
GRANT SELECT ANY TABLE TO c##dbzuser CONTAINER=ALL; 
GRANT SELECT_CATALOG_ROLE TO c##dbzuser CONTAINER=ALL; 
GRANT EXECUTE_CATALOG_ROLE TO c##dbzuser CONTAINER=ALL; 
GRANT SELECT ANY TRANSACTION TO c##dbzuser CONTAINER=ALL; 
GRANT LOGMINING TO c##dbzuser CONTAINER=ALL; 
GRANT CREATE TABLE TO c##dbzuser CONTAINER=ALL; 
GRANT LOCK ANY TABLE TO c##dbzuser CONTAINER=ALL; 
GRANT CREATE SEQUENCE TO c##dbzuser CONTAINER=ALL; 
GRANT EXECUTE ON DBMS_LOGMNR TO c##dbzuser CONTAINER=ALL; 
GRANT EXECUTE ON DBMS_LOGMNR_D TO c##dbzuser CONTAINER=ALL; 
GRANT SELECT ON V_$LOG TO c##dbzuser CONTAINER=ALL; 
GRANT SELECT ON V_$LOG_HISTORY TO c##dbzuser CONTAINER=ALL; 
GRANT SELECT ON V_$LOGMNR_LOGS TO c##dbzuser CONTAINER=ALL; 
GRANT SELECT ON V_$LOGMNR_CONTENTS TO c##dbzuser CONTAINER=ALL; 
GRANT SELECT ON V_$LOGMNR_PARAMETERS TO c##dbzuser CONTAINER=ALL; 
GRANT SELECT ON V_$LOGFILE TO c##dbzuser CONTAINER=ALL; 
GRANT SELECT ON V_$ARCHIVED_LOG TO c##dbzuser CONTAINER=ALL; 
GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO c##dbzuser CONTAINER=ALL; 
GRANT SELECT ON V_$TRANSACTION TO c##dbzuser CONTAINER=ALL; 

測(cè)試數(shù)據(jù)庫(kù)層面 Logminer

[root@docker ~]# sqlplus sys/oracle@192.168.0.40:1521/orcl as sysdba

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/opt/oracle/oradata/ORCL/redo03.log
/opt/oracle/oradata/ORCL/redo02.log
/opt/oracle/oradata/ORCL/redo01.log

SQL> execute dbms_logmnr.add_logfile('/opt/oracle/oradata/ORCL/redo01.log',dbms_logmnr.new);
SQL> execute dbms_logmnr.add_logfile('/opt/oracle/oradata/ORCL/redo02.log',dbms_logmnr.addfile);
SQL> execute dbms_logmnr.add_logfile('/opt/oracle/oradata/ORCL/redo03.log',dbms_logmnr.addfile);
SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog+dbms_logmnr.committed_data_only);
SQL> select sql_redo,sql_undo from v$logmnr_contents where table_name like '%DEPT%' and OPERATION='INSERT';

SQL_REDO
----------------------------------------------------------------------------------------------------
SQL_UNDO
---------------------------------------------------------------------------------------------------------------
insert into "SCOTT"."DEPT"("DEPTNO","DNAME","LOC") values ('10','ACCOUNTING','NEW YORK');
delete from "SCOTT"."DEPT" where "DEPTNO" = '10' and "DNAME" = 'ACCOUNTING' and "LOC" = 'NEW YORK' and ROWID = 'AAAR1DAAMAAAACDAAA';
insert into "SCOTT"."DEPT"("DEPTNO","DNAME","LOC") values ('20','RESEARCH','DALLAS');
delete from "SCOTT"."DEPT" where "DEPTNO" = '20' and "DNAME" = 'RESEARCH' and "LOC" = 'DALLAS' and ROWID = 'AAAR1DAAMAAAACDAAB';
insert into "SCOTT"."DEPT"("DEPTNO","DNAME","LOC") values ('30','SALES','CHICAGO');
delete from "SCOTT"."DEPT" where "DEPTNO" = '30' and "DNAME" = 'SALES' and "LOC" = 'CHICAGO' and ROWID = 'AAAR1DAAMAAAACDAAC';
insert into "SCOTT"."DEPT"("DEPTNO","DNAME","LOC") values ('40','OPERATIONS','BOSTON');
delete from "SCOTT"."DEPT" where "DEPTNO" = '40' and "DNAME" = 'OPERATIONS' and "LOC" = 'BOSTON' and ROWID = 'AAAR1DAAMAAAACDAAD';

-- 下面語(yǔ)句為結(jié)束語(yǔ)句
SQL> execute dbms_logmnr.end_logmnr;

啟動(dòng) Kafka Connect

# 后臺(tái)運(yùn)行
docker run -d --name connect \
-p 8083:8083 \
-e GROUP_ID=1 \
-e CONFIG_STORAGE_TOPIC=my_connect_configs \
-e OFFSET_STORAGE_TOPIC=my_connect_offsets \
-e STATUS_STORAGE_TOPIC=my_connect_statuses \
--link zookeeper:zookeeper \
--link kafka:kafka \
quay.io/debezium/connect:1.9

# 實(shí)時(shí)查看 Kafka Connect 的日志信息
docker logs -f -t --tail 10 connect 

Debezium Oracle connector

• 下載 ojdbc8.jar 連接驅(qū)動(dòng)
  https://repo1.maven.org/maven2/com/oracle/database/jdbc/ojdbc8/19.3.0.0/ojdbc8-19.3.0.0.jar
• 將驅(qū)動(dòng)上傳到 connect 容器中，重啟 connect 容器

[root@docker ~]# mv ojdbc8-19.3.0.0.jar ojdbc8.jar
[root@docker ~]# docker cp ojdbc8.jar connect:/kafka/libs
[root@docker ~]# docker restart connect

• 準(zhǔn)備 Debezium Oracle connector 配置文件
  將配置文件創(chuàng)建在 docker 宿主機(jī)上即可，connect 容器開(kāi)放了 REST API 來(lái)管理 Debezium 的連接器
  database.hostname 需要使用容器內(nèi)的IP地址，不然加不上

[root@docker ~]# vi oracle-scott-connector.json

{
    "name": "oracle-scott-connector",  
    "config": {
        "connector.class" : "io.debezium.connector.oracle.OracleConnector",  
        "database.hostname" : "172.17.0.3",  
        "database.port" : "1521",  
        "database.user" : "c##dbzuser",  
        "database.password" : "dbz",   
        "database.dbname" : "ORCL",
        "database.pdb.name" : "PDBTT",		
        "database.server.name" : "oracle19c",  
        "tasks.max" : "1",
        "schema.include.list": "SCOTT",
        "database.history.kafka.bootstrap.servers" : "192.168.0.40:9092", 
        "database.history.kafka.topic": "schema-changes.inventory"  
    }
}

• 向 Kafka 連接器注冊(cè) Debezium Oracle connector

[root@docker ~]# curl -i -X POST -H "Accept:application/json" -H "Content-Type:application/json" 192.168.0.40:8083/connectors/ -d @oracle-scott-connector.json
HTTP/1.1 201 Created
Date: Sat, 16 Apr 2022 17:03:12 GMT
Location: http://192.168.0.40:8083/connectors/oracle-scott-connector
Content-Type: application/json
Content-Length: 534
Server: Jetty(9.4.43.v20210629)

{"name":"oracle-scott-connector","config":{"connector.class":"io.debezium.connector.oracle.OracleConnector","database.hostname":"172.17.0.3","database.port":"1521","database.user":"c##dbzuser","database.password":"dbz","database.dbname":"ORCL","database.pdb.name":"PDBTT","database.server.name":"oracle19c","tasks.max":"1","schema.include.list":"SCOTT","database.history.kafka.bootstrap.servers":"192.168.0.40:9092","database.history.kafka.topic":"schema-changes.inventory","name":"oracle-scott-connector"},"tasks":[],"type":"source"}

核對(duì)捕獲到的數(shù)據(jù)

• 進(jìn)入到connect容器內(nèi)部 執(zhí)行

[root@docker ~]# docker exec -it connect bash
[kafka@839c4a43b889 ~]$ bin/kafka-topics.sh --list --bootstrap-server kafka:9092
__consumer_offsets
my_connect_configs
my_connect_offsets
my_connect_statuses
oracle19c
oracle19c.SCOTT.DEPT
oracle19c.SCOTT.EMP
oracle19c.SCOTT.SALGRADE
schema-changes.inventory
 
[kafka@839c4a43b889 ~]$ bin/kafka-console-consumer.sh --bootstrap-server kafka:9092 --topic schema-changes.inventory --from-beginning
[kafka@839c4a43b889 ~]$ bin/kafka-console-consumer.sh --bootstrap-server kafka:9092 --topic oracle19c.SCOTT.DEPT --from-beginning

• DDL
  

• DML
  

模擬業(yè)務(wù)

• INSERT
  
  

使用 kafka-ui 查看 Kafka 里的消息

kafka-ui：Open-Source Web GUI for Apache Kafka Management：https://github.com/provectus/kafka-ui

docker run -p 8811:8080 \
 -e KAFKA_CLUSTERS_0_NAME=oracle-scott-connector \
 -e KAFKA_CLUSTERS_0_BOOTSTRAPSERVERS=192.168.0.40:9092 \
 -d provectuslabs/kafka-ui:latest 

網(wǎng)頁(yè)登錄：http://192.168.0.40:8811/