Skip to main content

​
Oracle Sources FAQ for Streamkap

This FAQ focuses on using Oracle Database as a source in Streamkap, including self-hosted setups and cloud variants (AWS RDS Oracle). Streamkap’s Oracle connector provides real-time CDC with managed features like automatic scaling, UI setup, and ETL transformations.
An Oracle source in Streamkap enables real-time Change Data Capture (CDC) from Oracle databases, capturing row-level inserts, updates, and deletes with sub-second latency. It uses LogMiner to read the redo logs and stream changes to destinations, supporting snapshots for initial loads, schema evolution, and heartbeats for low-traffic databases. Streamkap abstracts complexity, offering a serverless setup via UI or API.
  • Oracle Database 11g+ for basic CDC; 12c+ for enhanced features like multitenant architecture and JSON support; 19c+ for latest optimizations and cloud compatibility.
  • Compatible with Oracle Database 11.2+
  • Oracle RAC (Real Application Clusters) supported
Streamkap supports:
  • Self-hosted (on-prem/VM)
  • AWS RDS Oracle (Standard Edition, Enterprise Edition)
  • Oracle Cloud Infrastructure (OCI)
  • Oracle RAC (Real Application Clusters)
Streamkap also supports standalone instances, Data Guard configurations, and Oracle Exadata.
  • CDC: LogMiner-based via redo logs; captures inserts/updates/deletes with SCN (System Change Number) tracking
  • Snapshots: Ad-hoc/initial backfills using signal table methods; locking or non-locking modes
  • Schema Evolution: Automatic handling of DDL changes when enabled; tracks table structure modifications
  • Heartbeats: Enabled by default to prevent redo log buildup in low-traffic databases
  • Data Types: Standard mappings (numerics, temporals, strings, LOBs, RAW as bytes/base64/hex, XML, JSON in 12c+)
  • Ingestion Modes: Inserts (append) or upserts
  • Security: SSL/TLS, Oracle wallet, IP allowlisting
  • Monitoring: Latency, lag, redo log metrics, LogMiner session stats in-app
  • Streamkap adds transaction metadata and ad-hoc snapshots with filters
Streamkap uses Oracle LogMiner to read and parse redo log files, extracting committed change events. LogMiner queries are executed against redo logs to retrieve SQL statements and change data, which are then emitted as change events. The connector tracks progress using SCN (System Change Number).Requirements:
  • Supplemental logging must be enabled (database and table level)
  • Archive log mode must be enabled
  • LogMiner privileges required for the Streamkap user
Supplemental logging instructs Oracle to include additional information in redo logs beyond what’s needed for database recovery. This extra data is essential for CDC.Types:
  • Database-level: ADD SUPPLEMENTAL LOG DATA
  • Table-level: ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS (recommended for complete change records)
Why it matters:
  • Without supplemental logging, redo logs may not contain complete before/after images
  • Affects ability to capture deletes and updates with full column data
  • Must be enabled before CDC starts capturing
Setting:
-- Database level
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- Table level (for complete records)
ALTER TABLE schema.table ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
  • Trigger ad-hoc at source/table level
  • Requires a signal table (streamkap_signal)
  • Methods: Blocking (uses locks) or incremental (chunked by ROWID or primary key)
  • Modes: initial (default), always, initial_only, no_data, when_needed, configuration_based, custom
Streamkap simplifies triggering via UI.
Heartbeats prevent redo log buildup in low-traffic databases by periodically updating a heartbeat table, generating change events to keep the connector active and advancing the SCN position.
  • Setup: Create heartbeat table; connector updates it automatically
  • How it Works: Generates small transactions that appear in redo logs
  • Verification: Check connector metrics for heartbeat activity
  • Limitations: Requires heartbeat table with supplemental logging enabled
See Oracle Heartbeats for setup instructions.
  • Basics: Numbers (NUMBER, INTEGER, FLOAT), strings (VARCHAR2, CHAR, NVARCHAR2, NCHAR, CLOB), dates/timestamps (DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE)
  • Advanced: Binary (RAW, LONG RAW, BLOB as bytes/hex), XML (XMLTYPE), JSON (12c+, stored as VARCHAR2/CLOB)
  • Custom: ROWID, UROWID, spatial types (SDO_GEOMETRY) with limitations
  • Unsupported: BFILE (external file pointers), deprecated LONG types (use CLOB instead), nested tables, VARRAYs without flattening, Oracle object types without conversion
  • Check archive log generation rate: 
    SELECT name, sequence#, first_time, next_time 
FROM v$archived_log 
WHERE first_time > SYSDATE - 1
ORDER BY first_time;
  • Monitor LogMiner sessions: 
    SELECT * FROM v$logmnr_contents WHERE ROWNUM <= 10;
  • Check redo log retention and space: 
    SELECT * FROM v$log;
Best Practices:
  • Retain archive logs for at least 3-5 days
  • Alert on archive log destination disk space
  • Monitor supplemental logging overhead
  • Track LogMiner query performance
No, CDC cannot capture Views or most virtual database objects.Why Views cannot be captured:
CDC captures changes by reading the database transaction log (binlog, WAL, oplog, redo log, etc.). Views are query-time computations over base tables—they don’t store data or generate transaction log entries. When you query a view, the database engine executes the underlying SELECT statement against the base tables. Since views don’t store data, they don’t generate transaction log entries.What cannot be captured:
  • Views: Virtual tables with no physical storage or redo log entries
  • Materialized Views (MVs): While they have physical storage, their refresh operations may not generate standard DML redo logs, or may generate bulk operations that are difficult to track incrementally. Solution: Capture the base tables that feed the materialized view, then recreate the MV logic downstream.
  • Materialized View Logs: Internal structures for fast refresh; not user data
  • Global Temporary Tables (GTT): Session or transaction-scoped, redo logging depends on ON COMMIT setting, generally excluded from CDC
  • External Tables: Reference files outside the database, no redo logging
  • Index-Organized Tables (IOT): Can be captured, but with special considerations for ROWID-based snapshots
  • System Tables (DBA_*, ALL_*, USER_*, V$*): Metadata views, not user data
  • CTEs (Common Table Expressions): Query-time constructs with no persistent storage
  • Object Tables: Tables based on Oracle object types; require special handling
Solution:
Configure CDC on the underlying base tables that power your views. The view logic can be recreated in your destination or transformation layer.Oracle-specific notes:
  • Materialized Views: Depending on refresh method (COMPLETE, FAST, FORCE), may generate redo logs, but tracking incremental changes is problematic. Always capture the base tables instead.
  • Materialized View with ON COMMIT refresh: Generates redo logs but as part of transaction commit; difficult to associate with source changes
  • Partitioned Views: Capture each base table in the partition view union
  • Index-Organized Tables: Can be captured, but ensure supplemental logging includes the primary key (which is the physical organization key)
Example:
If you have a view V_CUSTOMER_ORDERS that joins tables CUSTOMERS and ORDERS, enable supplemental logging and CDC on the CUSTOMERS and ORDERS base tables, then recreate the join logic in your destination or transformation layer.If you have a materialized view MV_DAILY_SALES that aggregates from SALES table, capture the SALES table and perform the aggregation downstream.
  • Archive log mode must be enabled (not enabled by default in many deployments)
  • Supplemental logging overhead on write performance
  • LogMiner has performance impact on high-transaction databases
  • Large transactions can cause memory pressure in LogMiner sessions
  • Redo log space management critical for continuous CDC
  • Schema changes may require connector restart to refresh table metadata
  • Long-running transactions can delay change event visibility
  • Oracle RAC requires all nodes to have archive logs accessible
Captures deletes as events with before-images, provided supplemental logging is enabled with ALL COLUMNS. Without proper supplemental logging, delete events may only contain primary key values.
Encrypted connections (SSL/TLS), Oracle wallet for credential management, role-based access, IP allowlisting, support for Oracle Network Encryption.
  • CDC Not Working: Verify archive log mode enabled; check supplemental logging at database and table levels
  • Redo Log Buildup: Enable heartbeats; monitor archive log destination space; adjust retention
  • Missing Events: Ensure supplemental logging includes ALL COLUMNS; verify LogMiner privileges
  • Performance Issues: Monitor LogMiner session resource usage; limit captured tables; tune redo log size
  • Connection Failures: Check listener status, TNS configuration, firewall rules
  • Schema Changes Not Reflected: Restart connector to refresh metadata after DDL operations
  • Use dedicated CDC user with minimal required privileges (LogMiner, SELECT on tables)
  • Enable supplemental logging with ALL COLUMNS for complete change records
  • Ensure archive log mode is enabled and monitored
  • Set appropriate archive log retention (3-5 days minimum)
  • Limit capture to needed schemas/tables to reduce LogMiner overhead
  • Monitor redo log generation rate and space
  • Test schema evolution procedures in staging
  • For cloud: Use provider monitoring; enable heartbeats for low traffic
  • Document supplemental logging configuration for team reference
  • Consider impact of supplemental logging on write-heavy workloads