使用Pulsar+Debezium Connector获取oracle数据变更

所需软件及版本信息：

• apache-pulsar-2.9.0-bin.tar.gz
• pulsar-io-debezium-oracle-2.9.0.nar
• instantclient-basic-linux.x64-19.13.0.0.0dbru.zip
• oracle 11gR2

 下载：apache-pulsar-2.9.0-bin.tar.gz　pulsar-io-debezium-oracle-2.9.0.nar

oracle软件安装略。

配置Oracle CDC（XStream）

=====================

前提条件

• Oracle数据库为11.2.0.4或以上版本。
• Oracle数据库需具备OGG License（无需安装OGG）。

开启日志归档和XStream

sqlplus /nolog CONNECT sys/password@127.0.0.1/orcl AS SYSDBA; 

开启Xstream

alter system set enable_goldengate_replication=true; 

检查日志归档是否已开启

archive log list; 

开启归档

shutdown immediate; 
startup mount; 
alter database archivelog; 
alter database open;

创建XStream用户并给用户赋予权限

sqlplus sys/password@127.0.0.1/orcl as sysdba 

CREATE TABLESPACE xstream_adm_tbs DATAFILE '/opt/oracle/oradata/orcl/xstream_adm_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; 

CREATE USER xstrmadmin IDENTIFIED BY xstrmadmin DEFAULT TABLESPACE xstream_adm_tbs QUOTA UNLIMITED ON xstream_adm_tbs; 

GRANT CREATE SESSION TO xstrmadmin; 

BEGIN 
    DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE(grantee => 'xstrmadmin', privilege_type => 'CAPTURE', grant_select_privileges => TRUE ); 
END; 

创建连接XStream的用户并配置权限

CREATE TABLESPACE xstream_tbs DATAFILE '/opt/oracle/oradata/orcl/xstream_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; 

CREATE USER xstrm IDENTIFIED BY xstrm DEFAULT TABLESPACE xstream_tbs QUOTA UNLIMITED ON xstream_tbs; 

GRANT CREATE SESSION TO xstrm; 
GRANT SELECT ON V_$DATABASE to xstrm; 
GRANT FLASHBACK ANY TABLE TO xstrm; 
GRANT SELECT ANY TABLE to xstrm; 
GRANT LOCK ANY TABLE TO xstrm; 
grant select_catalog_role to xstrm; 

修改日志记录参数

alter database add supplemental log data (all) columns; 

创建XStream出站服务器

sqlplus xstrmadmin/xstrmadmin@127.0.0.1/orcl 

DECLARE 
    tables DBMS_UTILITY.UNCL_ARRAY; 
    schemas DBMS_UTILITY.UNCL_ARRAY; 
BEGIN 
    tables(1) := NULL; 
    schemas(1) := 'scott'; 
    DBMS_XSTREAM_ADM.CREATE_OUTBOUND(server_name => 'dbzxout', table_names => tables, schema_names => schemas); 
END;

允许xstrm用户连接XStream出站服务器

sqlplus sys/password@127.0.0.1/orcl as sysdba 

BEGIN 
DBMS_XSTREAM_ADM.ALTER_OUTBOUND(server_name => 'dbzxout', connect_user => 'xstrm'); 
END;

配置Pulsar服务

解压pulsar二进制包,启动pulsar服务（演示使用standalone模式）

unzip -q instantclient-basic-linux.x64-19.13.0.0.0dbru.zip 
export LD_LIBRARY_PATH=~/instantclient_19_13 
tar -zxf apache-pulsar-2.9.0-bin.tar.gz 
cd apache-pulsar-2.9.0 
mkdir connectors 
cp ~/pulsar-io-debezium-oracle-2.9.0.nar connectors/ 
cp ~/instantclient_19_13/ojdbc8.jar ~/instantclient_19_13/xstreams.jar lib/ 

bin/pulsar-daemon start standalone 
netstat -nlp | grep 6650 
tcp 0 0 0.0.0.0:6650 0.0.0.0:* LISTEN 18368/java 

配置并启动debezium-orcle

创建配置文件

tenant: "public" 
namespace: "default" 
name: "debezium-oracle-source" 
topicName: "debezium-oracle-topic" 
archive: "connectors/pulsar-io-debezium-oracle-2.9.0.nar" 

parallelism: 1 
configs: 
    tasks.max : "1" 
    database.hostname : "localhost" 
    database.port : "1521" 
    database.user : "xstrm" #连接XStream的用户 
    database.password : "xstrm" 
    database.dbname : "orcl" 
    database.server.name : "server1" #逻辑数据库名，自定义 
    database.connection.adapter : "xstream" #连接模式 
    database.out.server.name : "dbzxout" #XStream 服务器名
    database.tablename.case.insensitive: "true"
    #debezium 针对oracle12及以上版本做了表过滤，11g最好指定include.list，防止出现“ORA-22812 无法引用嵌套表列...” 
    table.include.list: "scott.emp,scott.dept" 
    database.history.pulsar.service.url: "pulsar://127.0.0.1:6650"

更多属性配置参照：Debezium Connector for Oracle

启动

bin/pulsar-admin source localrun --source-config-file debezium-oracle-source-config.yaml 

查看topic信息

bin/pulsar-admin topics list public/default

出现emp、dept对应的topic，如果没有，尝试修改一下表数据

"persistent://public/default/server1.SCOTT.DEPT"
"persistent://public/default/server1"
"persistent://public/default/debezium-oracle-source-debezium-history-topic"
"persistent://public/default/debezium-oracle-source-debezium-offset-topic"
"persistent://public/default/debezium-oracle-topic"
"persistent://public/default/server1.SCOTT.EMP"

通过pulsar自带客户端测试数据订阅

启动一个consume

bin/pulsar-client --url pulsar://localhost:6650 consume -s "test-subscription-oracle" server1.SCOTT.EMP -n 0

修改数据

UPDATE emp SET comm=1500 WHERE empno = 7839; 
COMMIT; 

consume会收到对应的数据修改信息

----- got message -----
key:[eyJFTVBOTyI6NzgzOX0=], properties:[], content:{"before":{"EMPNO":7839,"ENAME":"KING","JOB":"PRESIDENT","MGR":null,"HIREDATE":374803200000,"SAL":"B6Eg","COMM":"Aknw","DEPTNO":10},"after":{"EMPNO":7839,"ENAME":"KING","JOB":"PRESIDENT","MGR":null,"HIREDATE":374803200000,"SAL":"B6Eg","COMM":"Aknw","DEPTNO":10},"source":{"version":"1.7.0.Final","connector":"oracle","name":"server1","ts_ms":1639032982000,"snapshot":"false","db":"ORCL","sequence":null,"schema":"SCOTT","table":"EMP","txId":"2.14.952","scn":"1112762","commit_scn":null,"lcr_position":"00000010fabb000000010000000100000010faba000000010000000101"},"op":"u","ts_ms":1639032985503,"transaction":null}