Debezium SCN Recovery - Oracle LogMiner & Kubernetes

The "SCN Not Found" Visualized

This error (ORA-01291, ORA-01292, or Debezium specific exceptions) occurs when the connector requests a System Change Number (SCN) that has already been purged from the Oracle Online Redo Logs and Archived Logs.

Interactive Simulation: Click the buttons below to simulate different scenarios. Watch how the "Debezium Offset" line (Orange) falls below the "Oldest Available SCN" line (Red), triggering the crash.

Key Metrics to Watch

Milli-seconds Behind Source 0 ms

Redo Log Generation Rate High

If generation > mining speed, you will eventually lose logs.

Why does this happen in K8s?

Pod Restarts: If the pod crashes or is rescheduled, and downtime > Oracle retention window, the SCN is lost.
Network Latency: K8s to Oracle network issues can slow down LogMiner fetch rate.
PVC I/O: Slow Infinispan buffer on PVC can cause backpressure, increasing the gap.

Diagnostic Decision Tree

Select a symptom or suspicion below to reveal the specific SQL queries needed to confirm the root cause. Execute these in your Oracle Source PDB.

Is the Redo/Archive Log Retention too short?

Debezium needs the logs to exist from the last committed offset. If Oracle deletes them before Debezium reads them, you get "SCN not found".

Check Recovery File Dest Usage

SELECT * FROM v$recovery_file_dest;

Find Oldest Available Sequence

SELECT thread#, MIN(sequence#) as min_seq, MAX(sequence#) as max_seq, MIN(first_time) as oldest_log_time FROM v$archived_log WHERE deleted = 'NO' GROUP BY thread#;

Compare oldest_log_time with the timestamp of your Debezium failure.

Choose Strategy

1. Minimize Data Loss Incremental Snapshot (Watermarking). Best if logs are lost but data exists in tables.

2. Emergency Reset (Skip) Skip to current SCN. Accept data loss for the gap period. Fastest recovery.

3. Nuclear Option Full Re-Snapshot. Deletes all history and starts over. High impact.

Strategy: Incremental Snapshot Recovery

Recovers missing data by re-reading tables without a full lock.

Identify Affected Tables

Determine which tables had changes during the missing log period.

Send Ad-Hoc Snapshot Signal

Insert a signal into the Debezium signaling table to trigger a re-scan of specific tables.

INSERT INTO DEBEZIUM_SIGNAL (id, type, data) VALUES ('signal-1', 'execute-snapshot', '{"data-collections": ["PDB.SCHEMA.TABLE_A", "PDB.SCHEMA.TABLE_B"], "type": "incremental"}');

Restart Debezium (Optional)

If the connector is in a failed state, you may need to restart the Pod. However, for SCN missing errors, you usually need to combine this with advancing the offset (Strategy 2) to get the connector running, THEN run this signal to backfill.

Kubernetes & JDBC Storage Best Practices

Interact with the components below to see configuration details.

📦

Debezium Server Pod

Deployment / StatefulSet

💾

PVC (Infinispan)

Buffer Storage

🛢️

Source Oracle DB

LogMiner & JDBC Tables

Config Snippet

# deployment.yaml - Key Considerations

apiVersion: apps/v1
kind: Deployment
metadata:
  name: debezium-server
spec:
  replicas: 1 # MUST be 1. High Availability is handled by K8s rescheduling, not load balancing.
  strategy:
    type: Recreate # Kills old pod before starting new one to release locks/PVC
  template:
    spec:
      containers:
        - name: debezium-server
          image: quay.io/debezium/server:2.4
          resources:
            requests:
              memory: "1Gi" # Oracle LogMiner buffers in memory
            limits:
              memory: "2Gi"
          env:
            - name: DEBEZIUM_SOURCE_OFFSET_STORAGE
              value: "io.debezium.server.jdbc.JdbcOffsetBackingStore"
            - name: DEBEZIUM_SOURCE_DATABASE_HISTORY
              value: "io.debezium.relational.history.JdbcDatabaseHistory"

# pvc.yaml - Infinispan/Cache Store

# Why PVC? To handle Long-Running Transactions (LRT) that spill to disk.
# If Oracle transactions are huge, memory isn't enough.
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: debezium-cache-pvc
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 10Gi # Resize based on transaction size expectations
  storageClassName: premium-ssd # High IOPS crucial for performance

# application.properties - JDBC Storage Config

# Storing offsets/history in the Source DB simplifies backups (Atomic with DB)
debezium.source.offset.storage.jdbc.url=${ORACLE_JDBC_URL}
debezium.source.offset.storage.jdbc.user=${ORACLE_USER}
debezium.source.offset.storage.jdbc.offset.table.name=DEBEZIUM_OFFSETS

debezium.source.database.history.jdbc.url=${ORACLE_JDBC_URL}
debezium.source.database.history.jdbc.user=${ORACLE_USER}
debezium.source.database.history.jdbc.schema.history.table.name=DEBEZIUM_HISTORY

# LogMiner Specifics
debezium.source.log.mining.strategy=catalogue_in_redo # Or online_catalog
debezium.source.log.mining.batch.size.default=20000

Debezium Server Oracle/K8s Ops Center

The "SCN Not Found" Visualized

Key Metrics to Watch

Why does this happen in K8s?

Diagnostic Decision Tree

Is the Redo/Archive Log Retention too short?

Are there gaps in the Archive Log sequence?

RAC Cluster Issues

Verify Debezium's Last Known Position

Choose Strategy

Strategy: Incremental Snapshot Recovery

Identify Affected Tables

Send Ad-Hoc Snapshot Signal

Restart Debezium (Optional)

Strategy: Manual Offset Reset (Gap Skip)

Stop the Debezium Pod

Get Current SCN

Update JDBC Offset Table

Strategy: Full Re-Snapshot

Clean State

Clear Infinispan/Cache

Restart

Kubernetes & JDBC Storage Best Practices

Debezium Server Pod

PVC (Infinispan)

Source Oracle DB