Debezium Oracle CDC使用手册

一、Oracle 数据开启日志归档

• 设置数据库归档模式

SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 4999610368 bytes
Fixed Size                  8803024 bytes
Variable Size            1124076848 bytes
Database Buffers         3858759680 bytes
Redo Buffers                7970816 bytes
数据库装载完毕。
SQL> alter database archivelog;

数据库已更改。

SQL> archive log list;
数据库日志模式            存档模式
自动存档             启用
存档终点            /u01/app/oracle/product/12/db_1/dbs/arch
最早的联机日志序列     2
下一个存档日志序列   4
当前日志序列           4

SQL> alter database open;

--开启Force logging
SQL>Alter database force logging

--开启supplemental logging
SQL>Alter database add supplemental log data;

--设置数据库GoldenGate参数

SQL> show parameter enable_goldengate_replication;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication        boolean     FALSE
SQL> alter system set enable_goldengate_replication=true scope=both;

系统已更改。

• PDB设置

 -- 启动一个创建好的PDB
	alter pluggable database ORCLPDB open;
-- 用户权限赋予
	-- 登录数据库管理员 
		sqlplus C##oracle/oracle as sysdba
	-- 切换当前会话到对应的PDB
		alter session set container=ORCLPDB;
	-- 授权
		grant create user,drop user,alter user,create any view,connect,resource,dba,create session,create any sequence to C##oracle;
		
		
		GRANT CONNECT,RESOURCE TO c##oracle container=all;

二、创建XStream Admin用户和连接器用户

• Admin用户

sqlplus / as sysdba
CREATE TABLESPACE xstream_adm_tbs DATAFILE '/u01/app/oracle/oradata/orcl/pdbseed/xstream_adm_tbs.dbf'
  SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
exit;
  
  
  
  sqlplus C##admin/admin@//localhost:1521/ORCLCDB
  CREATE TABLESPACE xstream_adm_tbs DATAFILE '/u01/app/oracle/oradata/orcl/orclpdb/xstream_adm_tbs.dbf'
  SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
  exit;
  
CREATE USER c##xstrmadmin IDENTIFIED BY xsa
  DEFAULT TABLESPACE xstream_adm_tbs
  QUOTA UNLIMITED ON xstream_adm_tbs
  CONTAINER=ALL;

GRANT CREATE SESSION, SET CONTAINER TO c##xstrmadmin CONTAINER=ALL;

BEGIN
   DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE(
      grantee                 => 'c##xstrmadmin',
      privilege_type          => 'CAPTURE',
      grant_select_privileges => TRUE,
      container               => 'ALL'
   );
END;
/

exit;  

• 连接器用户

  sqlplus / as sysdba
  CREATE TABLESPACE xstream_tbs DATAFILE '/u01/app/oracle/oradata/orcl/pdbseed/xstream_tbs.dbf'
  SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
   exit;
  
   sqlplus C##admin/admin@//localhost:1521/ORCLCDB
  CREATE TABLESPACE xstream_tbs DATAFILE '/u01/app/oracle/oradata/orcl/orclpdb/xstream_tbs.dbf'
  SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
   exit;

 sqlplus / as sysdba
CREATE USER c##xstrm IDENTIFIED BY xs
  DEFAULT TABLESPACE xstream_tbs
  QUOTA UNLIMITED ON xstream_tbs
  CONTAINER=ALL;

GRANT CREATE SESSION TO c##xstrm CONTAINER=ALL;
GRANT SET CONTAINER TO c##xstrm CONTAINER=ALL;
GRANT SELECT ON V_$DATABASE to c##xstrm CONTAINER=ALL;
GRANT FLASHBACK ANY TABLE TO c##xstrm CONTAINER=ALL;

exit;

三、XStream出站服务器

• 启动服务器

sqlplus c##xstrmadmin/xsa@//localhost:1521/ORCLPDB

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

exit;

• 授权连接用户

 sqlplus / as sysdba

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

exit;

四、kafka connector

• 插件部署

参考连接器部署

• 配置示例

{
    "name": "inventory-connector",
    "config": {
        "connector.class" : "io.debezium.connector.oracle.OracleConnector",
        "tasks.max" : "1",
        "database.server.name" : "server1",
        "database.hostname" : "oracle—hostname",
        "database.port" : "1521",
        "database.user" : "c##xstrm",
        "database.password" : "xs",
        "database.dbname" : "ORCL",
        "database.pdb.name" : "ORCLPDB",
        "database.out.server.name" : "dbzxout",
        "database.history.kafka.bootstrap.servers" : "kafkaServer:9092",
        "database.history.kafka.topic": "schema-changes.inventory"
    }
}

五、oracle 表创建

• 创建用户

 - 创建用户
 create user debezium identified by 口令[即密码]；
 - 授权
 grant connect, resource，dba to 用户名;

• 创建表并设置

ALTER SESSION SET CURRENT_SCHEMA = debezium;
create table CUSTOMERS (
id NUMBER(10),
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(50),
gender VARCHAR(50),
club_status VARCHAR(8),
comments VARCHAR(90),
create_ts timestamp DEFAULT CURRENT_TIMESTAMP ,
update_ts timestamp
); — Enable supplemental log on CUSTOMERS
ALTER TABLE debezium.customers ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; — Grant select to debezium connector user
GRANT SELECT ON debezium.customers to c##xstrm;

• 添加数据观察数据监听

• 通过查看kafka数据
  

六、文档参考

• 1.1 Centors7+静默安装Oracle12C
• 1.2 Debezium官方文档
• 1.3 Oracle Instant Client

注意在安装oracle时使用英文模式。