Oracle connector intermittent ORA-00942 after ~1h (common user, Oracle 19c, Debezium 3.0.3.Final, Java 17)

[Stefan Viteza]

Hello Debezium team,

I’m experiencing an intermittent issue with the Debezium Oracle connector running in embedded mode.

The connector starts successfully and captures data for about one hour, then crashes with:

ERROR io.debezium.embedded.EmbeddedEngine - While trying to run connector class 'io.debezium.connector.oracle.OracleConnector'
Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
Caused by: oracle.jdbc.OracleDatabaseException: ORA-00942: table or view does not exist

  The same configuration works perfectly against an Azure-hosted Oracle 19c instance, but fails consistently on our on-prem production database (Oracle 19c Enterprise Edition) which is managed externally.  

debezium.connector.class=io.debezium.connector.oracle.OracleConnector
database.hostname=hostname.domain
database.port=1521
database.user=C##EXUSER
database.password=****
database.dbname=MYAPP08.domain     # CDB service
database.pdb.name=MYAPP04          # target PDB
database.server.name=hostname

snapshot.mode=when_needed
table.include.list=EXUSER.EXPORT_CARS,EXUSER.EXPORT_NOTES

debezium.schema.history.internal=io.debezium.storage.file.history.FileSchemaHistory
schema.history.internal.file.filename=./schemahistory.dat
schema.history.internal.store.only.captured.tables.ddl=true
schema.history.skip.unparseable.ddl=false

offset.storage=org.apache.kafka.connect.storage.FileOffsetBackingStore
offset.storage.file.filename=./offsets.dat
offset.flush.interval.ms=60000
heartbeat.interval.ms=30000

envrionment:

• Oracle version: 19c Enterprise Edition

• Java version: 17

• Debezium version: 3.0.3.Final

• Runtime: Embedded Engine (not Kafka Connect)

• Spring Context: 6.2.0

• SLF4J: 2.0.9The connector initializes, snapshots, and streams change events successfully for about an hour.Then it crashes with ORA-00942. Re-starting the engine restores functionality temporarily until the issue reoccurs (offsets and schemahistory files need to be deleted).

Questions

1. Is there a known issue or workaround for maintaining container context when using a common user in multitenant (CDB/PDB) setups?

2. Would removing database.pdb.name and connecting directly to the PDB service be more stable, even when using a common user?

3. Are there known timing or reconnection edge cases in 3.0.3.Final that could cause this container switch?

---

Thank you for your time and for any insights or recommendations.
Best regards,

Stefan

[jiri.p...@gmail.com]

Hi,

could you please post the full log so we see where exatly the error happens?

Jiri

[Alexandru Calin]

Hi,

Is this good enough:

io.debezium.embedded.EmbeddedEngine - stopping the task and anegnine
i.d.connector.common.basesourceTask - stopping down connector
i.d.pipeline.SIgnalProcessor - SignalProcessor stopped
i.d.service.DefaultServiceRegistry - Debezium ServiceRegistry stopped
io.debezium.jdbc.JdbcCOnnection Connection gracefully closed
io.debezium.jdc.JdbcCOnnection - Connection gracefully closed
o.a.k.c.s.FileOffsetBackingStore - stopped FileOffsetBackingStore
ERROR io.debezium.embedded.EmbeddedEngine - While whilte trying to run connector class 'io.debezium.connector.oracle.OracleCOnnector'
...

Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

...

Caused by: oracle.jdbc.OracleDatabaseException: ORA-00942: table or view does not exist

...

Thanks,

Stefan

--
You received this message because you are subscribed to a topic in the Google Groups "debezium" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/debezium/TLJ4TvSP64w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to debezium+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/debezium/305145a0-0e01-4393-91e9-8e8794b5fe6en%40googlegroups.com.

[Chris Cranford]

Hi -

In your connector configuration, you've set the `table.include.list` and `schema.history.internal.store.only.captured.tables.ddl`, and in this context, the only schemas that would be read would be those in your include list, so tables EXUSER.EXPORT_CARS and EXUSER.EXPORT_NOTES. Therefore, the only reason to see any ORA-00942 error would be if either of those tables were dropped or if the connector user's grants were changed and specific tables that the user had access to is no longer granted. So it would be prudent to have the full connector log to understand in what stage/place was the error throw by the connector so we can best help identify the root cause.

Thanks,
-cc

You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/debezium/CAEhbKs6H%2BN-Gc49WQ_LJXzyOKfgv-VTU%2Bwa4YLHNvxm-JEujmA%40mail.gmail.com.

[Alexandru Calin]

Hi,

Attaching more detailed logs of the error, please have a look and let me know if there is more I need to do.

11:15:34.913 [FileOffsetBackingStore-1] TRACE io.debezium.embedded.EmbeddedEngine - Finished flushing EmbeddedEngine{id=engine-my-objects} offsets to storage

11:15:34.913 [pool-2-thread-1] DEBUG i.d.connector.common.BaseSourceTask - Committing offset '{server=my-events}' for partition '{snapshot_scn=75476005600, snapshot=INITIAL, scn=75476005600, snapshot_completed=false}'

11:15:34.913 [pool-2-thread-1] DEBUG io.debezium.embedded.EmbeddedEngine - Embedded engine is polling task for records on thread Thread[pool-2-thread-1,5,main]

11:15:34.913 [pool-2-thread-1] DEBUG i.d.connector.base.ChangeEventQueue - polling records...

11:15:34.913 [pool-2-thread-1] INFO  io.debezium.embedded.EmbeddedEngine - Stopping the task and engine

11:15:34.913 [pool-2-thread-1] INFO  i.d.connector.common.BaseSourceTask - Stopping down connector

11:15:34.914 [pool-2-thread-1] INFO  i.d.pipeline.signal.SignalProcessor - SignalProcessor stopped

11:15:34.915 [pool-2-thread-1] INFO  i.d.service.DefaultServiceRegistry - Debezium ServiceRegistry stopped.

11:15:34.915 [pool-2-thread-1] TRACE io.debezium.jdbc.JdbcConnection - Closing prepared statement 'oracle.jdbc.driver.OraclePreparedStatementWrapper@3ee3d612' removed from cache

11:15:34.915 [pool-2-thread-1] TRACE io.debezium.jdbc.JdbcConnection - Closing prepared statement 'oracle.jdbc.driver.OraclePreparedStatementWrapper@fc47e0a' removed from cache

11:15:34.915 [pool-2-thread-1] TRACE io.debezium.jdbc.JdbcConnection - Closing prepared statement 'oracle.jdbc.driver.OraclePreparedStatementWrapper@4d6cfefc' removed from cache

11:15:34.916 [pool-2-thread-1] TRACE io.debezium.jdbc.JdbcConnection - Closing prepared statement 'oracle.jdbc.driver.OraclePreparedStatementWrapper@3182665e' removed from cache

11:15:34.916 [pool-2-thread-1] TRACE io.debezium.jdbc.JdbcConnection - Closing database connection

11:15:34.927 [pool-15137-thread-1] INFO  io.debezium.jdbc.JdbcConnection - Connection gracefully closed

11:15:34.927 [pool-2-thread-1] TRACE io.debezium.jdbc.JdbcConnection - Closing database connection

11:15:34.929 [pool-15138-thread-1] INFO  io.debezium.jdbc.JdbcConnection - Connection gracefully closed

11:15:34.929 [pool-2-thread-1] DEBUG i.d.connector.common.BaseSourceTask - Setting task state to 'STOPPED', previous state was 'RUNNING'

11:15:34.930 [pool-2-thread-1] INFO  o.a.k.c.s.FileOffsetBackingStore - Stopped FileOffsetBackingStore

11:15:34.930 [pool-2-thread-1] ERROR io.debezium.embedded.EmbeddedEngine - Error while trying to run connector class 'io.debezium.connector.oracle.OracleConnector'

org.apache.kafka.connect.errors.ConnectException: An exception occurred in the change event producer. This connector will be stopped.

              at io.debezium.pipeline.ErrorHandler.setProducerThrowable(ErrorHandler.java:67)

              at io.debezium.connector.oracle.logminer.LogMinerStreamingChangeEventSource.execute(LogMinerStreamingChangeEventSource.java:264)

              at io.debezium.connector.oracle.logminer.LogMinerStreamingChangeEventSource.execute(LogMinerStreamingChangeEventSource.java:62)

              at io.debezium.pipeline.ChangeEventSourceCoordinator.streamEvents(ChangeEventSourceCoordinator.java:324)

              at io.debezium.pipeline.ChangeEventSourceCoordinator.executeChangeEventSources(ChangeEventSourceCoordinator.java:203)

              at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:143)

              at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539)

              at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)

              at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)

              at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)

              at java.base/java.lang.Thread.run(Thread.java:842)

Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

 

              at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:630)

              at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:564)

              at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1231)

              at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:772)

              at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:299)

              at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:512)

              at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:123)

              at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:969)

              at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1271)

              at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1149)

              at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1661)

              at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1470)

              at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:2055)

              at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:394)

              at io.debezium.jdbc.JdbcConnection.queryAndMap(JdbcConnection.java:648)

              at io.debezium.jdbc.JdbcConnection.queryAndMap(JdbcConnection.java:515)

              at io.debezium.connector.oracle.OracleConnection.singleOptionalValue(OracleConnection.java:417)

              at io.debezium.connector.oracle.OracleConnection.getFirstScnInLogs(OracleConnection.java:432)

              at io.debezium.connector.oracle.logminer.LogMinerStreamingChangeEventSource.execute(LogMinerStreamingChangeEventSource.java:152)

              ... 9 common frames omitted

Caused by: oracle.jdbc.OracleDatabaseException: ORA-00942: table or view does not exist

 

              at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:637)

              ... 27 common frames omitted

Thanks,

Stefan

To view this discussion visit https://groups.google.com/d/msgid/debezium/358beb78-0128-4b1b-a230-ed96b91c9a8f%40gmail.com.

[Chris Cranford]

Hi Stefan -

Looking at the stack trace, this would indicate that the user has no ability to query V$LOG and V$ARCHIVED_LOG performance views. Can you please check that the C##EXUSER can query these tables when the user is connected to the CDB$ROOT container of the database?  It's really unusual that we'd get an error that these tables do not exist unless there is a user permissions issue or where grants are being randomly revoked.

Thanks,
-cc

To view this discussion visit https://groups.google.com/d/msgid/debezium/CAEhbKs6Sfu0brnVVrSxYyMFZht%3DhR0uXov6e16_eUr3zugHrmw%40mail.gmail.com.