It seems that there is no data from the log analysis

[XCXCXCXCX]

【env】 Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production kafka 2.1.1

【prepare】 SQL> archive log list 数据库日志模式 存档模式 自动存档 启用(enabled) 存档终点 USE_DB_RECOVERY_FILE_DEST 最早的联机日志序列 27 下一个存档日志序列 28 当前日志序列 28

OracleSourceConnector.properties name=oracle-logminer-connector connector.class=com.ecer.kafka.connect.oracle.OracleSourceConnector db.name.alias=XE tasks.max=1 topic=cdctest db.name=XE db.hostname=127.0.0.1 db.port=1521 db.user=kminer db.user.password=kminerpass db.fetch.size=1 table.whitelist=kminer.stuinfo table.blacklist= parse.dml.data=true reset.offset=true start.scn= multitenant=false

And I created topic named cdctest in the kafka

【log】 [2020-09-23 11:46:46,057] INFO Oracle Kafka Connector is starting on XE (com.ecer.kafka.connect.oracle.OracleSourceTask:113) [2020-09-23 11:46:46,602] INFO Connected to database version 112020 (com.ecer.kafka.connect.oracle.OracleSourceTask:118) [2020-09-23 11:46:46,602] INFO Starting LogMiner Session (com.ecer.kafka.connect.oracle.OracleSourceTask:121) [2020-09-23 11:46:46,622] INFO Resetting offset with new SCN (com.ecer.kafka.connect.oracle.OracleSourceTask:169) [2020-09-23 11:46:46,624] INFO Getting current scn from database 1477479 (com.ecer.kafka.connect.oracle.OracleSourceTask:184) [2020-09-23 11:46:46,624] INFO Commit SCN : 0 (com.ecer.kafka.connect.oracle.OracleSourceTask:187) [2020-09-23 11:46:46,624] INFO Log Miner will start at new position SCN : 1477479 with fetch size : 1 (com.ecer.kafka.connect.oracle.OracleSourceTask:188) [2020-09-23 11:46:56,052] INFO WorkerSourceTask{id=oracle-logminer-connector-0} Committing offsets (org.apache.kafka.connect.runtime.WorkerSourceTask:397) [2020-09-23 11:46:56,053] INFO WorkerSourceTask{id=oracle-logminer-connector-0} flushing 0 outstanding messages for offset commit (org.apache.kafka.connect.runtime.WorkerSourceTask:414) [2020-09-23 11:47:06,053] INFO WorkerSourceTask{id=oracle-logminer-connector-0} Committing offsets (org.apache.kafka.connect.runtime.WorkerSourceTask:397)

【phenomenon】 No oracle message in kafka's topic named cdctest.

[XCXCXCXCX]

May I ask which link is the problem?

[erdemcer]

Hi, Could you please write table.whitelist in uppercase and try again ? Thanks

[XCXCXCXCX]

Thanks Reply! After I deleted the test table, I used uppercase and changed the whitelist in the configuration file to uppercase. Table operations can now be monitored, but only DDL-type operations can be monitored. For DML-type operations, an error will be reported that the table or view does not exist. What should I do?

{"schema":{"type":"struct","fields":[{"type":"int64","optional":false,"field":"SCN"},{"type":"string","optional":false,"field":"SEG_OWNER"},{"type":"string","optional":false,"field":"TABLE_NAME"},{"type":"int64","optional":false,"name":"org.apache.kafka.connect.data.Timestamp","version":1,"field":"TIMESTAMP"},{"type":"string","optional":false,"field":"SQL_REDO"},{"type":"string","optional":false,"field":"OPERATION"},{"type":"struct","fields":[],"optional":true,"field":"data"},{"type":"struct","fields":[],"optional":true,"field":"before"}],"optional":false,"name":"xe.kminer.stuinfo.row"},"payload":{"SCN":1563006,"SEG_OWNER":"KMINER","TABLE_NAME":"_GENERIC_DDL","TIMESTAMP":1601013285000,"SQL_REDO":"drop table KMINER.STUINFO\n","OPERATION":"DDL","data":null,"before":null}} {"schema":{"type":"struct","fields":[{"type":"int64","optional":false,"field":"SCN"},{"type":"string","optional":false,"field":"SEG_OWNER"},{"type":"string","optional":false,"field":"TABLE_NAME"},{"type":"int64","optional":false,"name":"org.apache.kafka.connect.data.Timestamp","version":1,"field":"TIMESTAMP"},{"type":"string","optional":false,"field":"SQL_REDO"},{"type":"string","optional":false,"field":"OPERATION"},{"type":"struct","fields":[],"optional":true,"field":"data"},{"type":"struct","fields":[],"optional":true,"field":"before"}],"optional":false,"name":"xe.kminer.stuinfo.row"},"payload":{"SCN":1563047,"SEG_OWNER":"KMINER","TABLE_NAME":"_GENERIC_DDL","TIMESTAMP":1601013289000,"SQL_REDO":"create table KMINER.STUINFO (stuid varchar2(11) not null, stuname varchar2(50))","OPERATION":"DDL","data":null,"before":null}}

[2020-09-25 13:55:10,841] INFO Getting dictionary details for table: STUINFO (com.ecer.kafka.connect.oracle.OracleSourceConnectorUtils:153) [2020-09-25 13:55:10,844] ERROR SQL error during poll (com.ecer.kafka.connect.oracle.OracleSourceTask:293) java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587) at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:220)

[XCXCXCXCX]

I got the select sql.

WITH DICTONARY_CONSTRAINTS_COLUMNS AS (SELECT DC.CON_ID, DC.OWNER, DC.TABLE_NAME, DCC.COLUMN_NAME, 1 PK_COLUMN FROM CDB_CONSTRAINTS DC, CDB_CONS_COLUMNS DCC WHERE DC.CON_ID = DCC.CON_ID AND DC.OWNER=DCC.OWNER AND DC.TABLE_NAME=DCC.TABLE_NAME AND DC.CONSTRAINT_NAME=DCC.CONSTRAINT_NAME AND DC.CONSTRAINT_TYPE='P'), DICTIONARY_CONSTRAINTS_INDEXES AS (SELECT DIC.CON_ID, DIC.TABLE_OWNER, DIC.TABLE_NAME, DIC.COLUMN_NAME , 1 UQ_COLUMN FROM CDB_INDEXES DI JOIN CDB_IND_COLUMNS DIC ON DI.CON_ID = DIC.CON_ID AND DI.TABLE_OWNER=DIC.TABLE_OWNER AND DI.TABLE_NAME=DIC.TABLE_NAME AND DI.UNIQUENESS='UNIQUE'AND DI.OWNER=DIC.INDEX_OWNER AND DI.INDEX_NAME=DIC.INDEX_NAME GROUP BY DIC.CON_ID, DIC.TABLE_OWNER, DIC.TABLE_NAME, DIC.COLUMN_NAME ) SELECT DTC.CON_ID, DTC.OWNER, DTC.TABLE_NAME, DTC.COLUMN_NAME, DTC.NULLABLE, DTC.DATA_TYPE, NVL(DTC.DATA_PRECISION,DTC.DATA_LENGTH) DATA_LENGTH, NVL(DTC.DATA_SCALE,0) DATA_SCALE, NVL(DTC.DATA_PRECISION,0) DATA_PRECISION, NVL(DCC.PK_COLUMN,0) PK_COLUMN, NVL(DCI.UQ_COLUMN,0) UQ_COLUMN FROM CDB_TAB_COLS DTC LEFT OUTER JOIN DICTONARY_CONSTRAINTS_COLUMNS DCC ON DTC.CON_ID = DCC.CON_ID AND DTC.OWNER = DCC.OWNER AND DTC.TABLE_NAME = DCC.TABLE_NAME AND DTC.COLUMN_NAME=DCC.COLUMN_NAME LEFT OUTER JOIN DICTIONARY_CONSTRAINTS_INDEXES DCI ON DTC.CON_ID = DCI.CON_ID AND DCI.TABLE_OWNER = DTC.OWNER AND DCI.TABLE_NAME = DTC.TABLE_NAME AND DCI.COLUMN_NAME = DTC.COLUMN_NAME WHERE DTC.OWNER ='KMINER' AND DTC.TABLE_NAME ='STUINFO' AND DTC.HIDDEN_COLUMN ='NO' AND DTC.VIRTUAL_COLUMN='NO' ORDER BY DTC.TABLE_NAME, DTC.COLUMN_ID

ORA-00942: table or view does not exist

[erdemcer]

Did you give required privileges to kminer user mention in readme Thanks

[XCXCXCXCX]

Yes, i did.

[XCXCXCXCX]

[XCXCXCXCX]

I found that the tables beginning with CDB_ are not found

[erdemcer]

in your config multitenant is false is not it ?

[XCXCXCXCX]

name=oracle-logminer-connector connector.class=com.ecer.kafka.connect.oracle.OracleSourceConnector db.name.alias=XE tasks.max=1 topic=cdctest db.name=XE db.hostname=127.0.0.1 db.port=1521 db.user=kminer db.user.password=kminerpass db.fetch.size=1 table.whitelist=KMINER. table.blacklist=root. parse.dml.data=true reset.offset=false start.scn= multitenant=true

I changed it to true in the last test. Should I set it to false?

[erdemcer]

yes it must false if you are not using multitenancy

[XCXCXCXCX]

Thank you very much! After modifying it, it succeeded.

[erdemcer]

Great