kafka-connect + debezium 实时采集oracle 11 xe

oracle版本：11.2 xe

kafka版本1.1.1

准备：

1.下载oracle connect相关jar包 ：https://repo1.maven.org/maven2/io/debezium/debezium-connector-oracle/1.6.1.Final/debezium-connector-oracle-1.6.1.Final-plugin.tar.gz

2.并将解压到kafka connect的plugn 上详情参考上篇文章mysql的实时采集

3.下载oracle的ojdbc和xstream 放在kafka 的libs下 下载方法如下：下载orale instance：https://www.oracle.com/database/technologies/instant-client/downloads.html

目录下有ojdbc和xstream

oracle配置

1.sqlplus / as sysdb 配置log

alter system set db_recovery_file_dest_size = 10G;
alter system set db_recovery_file_dest = '/opt/oracle/oradata/recovery_area' scope=spfile;
shutdown immediate
startup mount
alter database archivelog;
alter database open;
-- Should now "Database log mode: Archive Mode"
archive log list

exit;

2.赋值权限

官方文档提供的为oracle12的需要配置CDB和PDB，oracle 11不存在，所有只需要配置下面的即可

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

CREATE USER debezium IDENTIFIED BY 123456
    DEFAULT TABLESPACE TEST0
    QUOTA UNLIMITED ON TEST0

  GRANT CREATE SESSION TO debezium ;
  GRANT SET CONTAINER TO debezium ;
  GRANT SELECT ON V_$DATABASE to debezium ;
  GRANT FLASHBACK ANY TABLE TO debezium ;
  GRANT SELECT ANY TABLE TO debezium ;
  GRANT SELECT_CATALOG_ROLE TO debezium ;
  GRANT EXECUTE_CATALOG_ROLE TO debezium ;
  GRANT SELECT ANY TRANSACTION TO debezium ;
  --GRANT LOGMINING TO debezium ;

  GRANT CREATE TABLE TO debezium ;
  GRANT LOCK ANY TABLE TO debezium ;
  GRANT ALTER ANY TABLE TO debezium ;
  GRANT CREATE SEQUENCE TO debezium ;

  GRANT EXECUTE ON DBMS_LOGMNR TO debezium ;
  GRANT EXECUTE ON DBMS_LOGMNR_D TO debezium ;

  GRANT SELECT ON V_$LOG TO debezium ;
  GRANT SELECT ON V_$LOG_HISTORY TO debezium ;
  GRANT SELECT ON V_$LOGMNR_LOGS TO debezium ;
  GRANT SELECT ON V_$LOGMNR_CONTENTS TO debezium ;
  GRANT SELECT ON V_$LOGMNR_PARAMETERS TO debezium ;
  GRANT SELECT ON V_$LOGFILE TO debezium ;
  GRANT SELECT ON V_$ARCHIVED_LOG TO debezium ;
  GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO debezium ;

  exit;

注意：有些权限可能报错不存在不用管

3.配置需要采集的表

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

ALTER TABLE 【库】.【表】 ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

kafka 采集

1.配置连接器

{
    "name": "inventory-connector-oracle3",
    "config": {
        "connector.class" : "io.debezium.connector.oracle.OracleConnector",
        "tasks.max" : "1",
        "database.server.name" : "oracle-service",
        "database.user" : "debezium",
        "database.password" : "123456",
        "database.url": "jdbc:oracle:thin:@ip:1521:xe",
        "database.tablename.case.insensitive": "true",
        "database.dbname" : "DEBEZIUM",
        "table.include.list":"DEBEZIUM.test",
        "database.history.kafka.bootstrap.servers" : "kafka:9092",
        "database.history.kafka.topic": "schema-changes.inventory",
        "snapshot.mode":"initial"
    }
}

2. post提交上面的json代码

http://ip:8083/connectors

3.检查连接情况

httip://ip:8083/connectors/inventory-connector-oracle3/status

4.查看kafka数据

遇到问题：

1.无效的列索引

ojdbc版本不对：debezium中的jdbcconnection类中会通过metastore获取数据，对于ojdbc6的索引不足23个但是ojdbc8才有23个

column.autoIncremented("YES".equalsIgnoreCase(columnMetadata.getString(23)));
            String autogenerated = null;
            try {
                autogenerated = columnMetadata.getString(24);
            }
            catch (SQLException e) {
                // ignore, some drivers don't have this index - e.g. Postgres
            }

测试程序

public class Test {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection dbConn = DriverManager.getConnection("jdbc:oracle:thin:test/test@ip:1521:xe","debezium","123456");
        DatabaseMetaData dbmd = dbConn.getMetaData();
        ResultSet rs = dbmd.getColumns(null, "DEBEZIUM","test", null);
        if (rs.next()) {
            rs.getString(22);
            System.out.println(rs.getString(23));
        }
    }
}

测试程序中ojdbc6会报索引的异常，但是ojdbc8不会报该异常，ojdbc8的getColumns获取的参数更加完善

2."database.tablename.case.insensitive": "true",

## For Oracle 11g

To run the Debezium Oracle connector with Oracle 11g, there are several additional parameters required.
It's important to remember, these are only required for Oracle 11 and should not be used with any other version.

```json
"database.tablename.case.insensitive": "true",
```

Additionally, the connector ignores several built-in tables and schemas in Oracle 12+ but those tables differ in Oracle 11.
When using Debezium Oracle connector with Oracle 11, its important to specify the `table.include.list` and to avoid problems with those tables.
Remember, specify your values here in lowercase, as shown below:

```json
"table.include.list": "orcl\\\\.debezium\\\\.(.*)"
```

参考文档：https://debezium.io/documentation/reference/1.6/connectors/oracle.html#oracle-example-configuration