​

Required pre-work

-- 1. Enable archive log mode (requires database restart)
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

-- 2. Enable minimal supplemental logging at database level
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- 3. Grant common LogMiner permissions to your extraction user
GRANT SELECT ON V_$ARCHIVED_LOG     TO your_username;
GRANT SELECT ON V_$LOG              TO your_username;
GRANT SELECT ON V_$LOGFILE          TO your_username;
GRANT SELECT ON V_$DATABASE         TO your_username;
GRANT SELECT ON V_$LOGMNR_CONTENTS  TO your_username;
GRANT EXECUTE ON DBMS_LOGMNR        TO your_username;
GRANT EXECUTE ON DBMS_LOGMNR_D      TO your_username;

-- 4. Version-specific grants
-- For Oracle 12c and later:
GRANT LOGMINING TO your_username;

-- For Oracle 11g (LOGMINING role does not exist):
GRANT EXECUTE_CATALOG_ROLE   TO your_username;
GRANT SELECT ANY TRANSACTION TO your_username;

-- 5. For specific tables you want to sync, enable supplemental logging
ALTER TABLE schema.table_name ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

-- 1. Enable supplemental logging
exec rdsadmin.rdsadmin_util.alter_supplemental_logging(p_action=>'ADD');

-- 2. Enable force logging
exec rdsadmin.rdsadmin_util.force_logging(p_enable => true);

-- 3. Grant LogMiner access to your extraction user
exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_LOGMNR', 'your_username', 'EXECUTE', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_LOGMNR_D', 'your_username', 'EXECUTE', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGMNR_CONTENTS', 'your_username', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGMNR_LOGS', 'your_username', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$ARCHIVED_LOG', 'your_username', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOG', 'your_username', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGFILE', 'your_username', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$DATABASE', 'your_username', 'SELECT', true);

-- 4. Version-specific grants (AWS RDS is typically 12c+)
GRANT LOGMINING TO your_username;

aws rds modify-db-instance --db-instance-identifier your-instance --backup-retention-period 1

-- Connect to CDB root as SYSDBA
-- Enable supplemental logging at CDB level
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

-- Grant permissions to common user (prefixed with C##)
GRANT EXECUTE ON DBMS_LOGMNR TO C##your_username CONTAINER=ALL;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO C##your_username CONTAINER=ALL;
-- ... (same grants as above with CONTAINER=ALL)
GRANT LOGMINING TO C##your_username CONTAINER=ALL;

-- Configure pdb_services in Nekt to discover PDB schemas

-- Check if archivelog mode is enabled
SELECT LOG_MODE FROM V$DATABASE;
-- Should return: ARCHIVELOG

-- Check supplemental logging status
SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_ALL FROM V$DATABASE;
-- Should show YES for at least MIN

-- Check archive logs are being generated
SELECT SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#, STATUS FROM V$ARCHIVED_LOG WHERE ROWNUM <= 5 ORDER BY SEQUENCE# DESC;

-- Validate privileges
SELECT * FROM session_roles WHERE role IN ('LOGMINING', 'EXECUTE_CATALOG_ROLE');
SELECT * FROM session_privs WHERE privilege = 'SELECT ANY TRANSACTION';

​

Configuring Oracle as a Source

​

1. Add account access

• Host (required): The hostname or IP address of your Oracle database server
• Port (required): The port for Oracle connection (default: 1521)
• User (required): Database user for authentication
• Password (required): Password for authentication
• Service Name (required): Oracle service name for the connection (also referred to as schema name)
• Pluggable Database Services (optional): List of Oracle PDB service names for multitenant (CDB/PDB) environments
• Thick Mode (optional, default: true): Enable Oracle thick mode for enhanced performance. Required for LogMiner operations
• Chunk Size (optional, default: 25000): Number of rows to fetch at a time. Reduce if your row data is too large

• SID: Alternative to Service Name for older Oracle configurations
• Filter Schemas: Array of schema names to include (if empty, all schemas are discovered)
• Use batch query: Enable keyset pagination with retry logic. Breaks large table extraction into smaller batches ordered by primary key, each with a fresh connection. Tables without an Oracle primary key need their unique key declared under Primary key overrides, otherwise this falls back to a single long-running cursor.
• Primary key overrides: For tables that have no Oracle PRIMARY KEY, declare the unique key here. It is used to paginate the table safely when ‘Use batch query’ is on (avoiding ORA-01555 ‘snapshot too old’), as the key_properties sent to downstream targets so they can deduplicate/merge instead of only appending, and to retype number key columns as integer (a primary key can’t be a float downstream).
• Invalid date handling: How to handle Oracle date/timestamp values outside Python’s representable year range (1–9999). Options are coerce (coerce to nearest valid boundary), null (convert to null), or error (raise error and stop pipeline).
• SSH Tunnel: Configuration for secure connections through a bastion server
• SSL/TLS: Enable encrypted connections with certificate configuration
• Connect timeout: Oracle Net CONNECT_TIMEOUT in seconds (listener-level handshake budget).
• Transport connect timeout: Oracle Net TRANSPORT_CONNECT_TIMEOUT in seconds (TCP-level connect budget).
• TNS retry count: Oracle Net RETRY_COUNT — driver-level retries on transient TNS errors before surfacing them.
• TNS retry delay: Oracle Net RETRY_DELAY in seconds between driver-level connect retries.
• Keepalive interval (minutes): Oracle Net EXPIRE_TIME in minutes — TCP keepalive interval to detect dead connections in long runs. Set to 0 to disable.
• Discovery retry max: Maximum attempts for discovery-phase operations on transient Oracle errors.
• Discovery retry delay: Initial delay in seconds between discovery retries. Uses exponential backoff (capped at 60s).
• Connection pool size: SQLAlchemy QueuePool pool_size. Raise for high-throughput workloads.
• Connection pool max overflow: SQLAlchemy QueuePool max_overflow.
• Connection pool recycle (seconds): Seconds after which a pooled connection is recycled.
• Pool pre-ping: Whether SQLAlchemy should pre-ping pooled connections before use.

​

2. Select streams

Tip: The stream can be found more easily by typing its name.

​

3. Configure data streams

• Layer: choose between the existing layers on your catalog. This is where you will find your new extracted tables as the extraction runs successfully.
• Folder: a folder can be created inside the selected layer to group all tables being created from this new data source.
• Table name: we suggest a name, but feel free to customize it. You have the option to add a prefix to all tables at once and make this process faster!
• Sync Type: you can choose between INCREMENTAL, FULL_TABLE, and LOG_BASED:
  • Incremental: Every time the extraction happens, we’ll get only the new data based on a replication key column. Good for append-only tables or when you want to keep historical records.
  • Full Table: Every time the extraction happens, we’ll get the current state of the data. Good if you don’t want to have deleted data in your catalog or for small reference tables.
  • Log Based: Uses Oracle LogMiner to capture data changes (inserts, updates, deletes) from the database transaction logs. This is the most efficient method for capturing all changes including deletes, with minimal impact on source database performance.

• INSERT: New records with all column values
• UPDATE: Modified records with new values
• DELETE: Removed records (marked with _sdc_deleted_at timestamp)

• Near real-time change data capture (CDC)
• Captures deletes (not possible with incremental sync)
• Minimal impact on source database performance
• Supports both traditional (non-CDB) and Oracle Multitenant (CDB/PDB) environments
• AWS RDS Oracle compatibility with automatic fallback to archived logs

• _sdc_lsn: The Oracle System Change Number (SCN) when the change was committed
• _sdc_deleted_at: Timestamp when the record was deleted (null for inserts/updates)

​

4. Configure data source

• Configure Delta Log Retention and determine for how long we should store old states of this table as it gets updated. Read more about this resource here.
• Determine when to execute an Additional Full Sync. This will complement the incremental data extractions, ensuring that your data is completely synchronized with your source every once in a while.

​

5. Check your new source

​

Streams and Fields

​

Implementation Notes

​

Oracle Multitenant Support

• Connect to each PDB to discover schemas and tables
• Properly switch containers during extraction
• Handle LogMiner operations across the multitenant environment seamlessly, safely fetching System Change Numbers (SCN) regardless of the architecture
• Automatically detect CDB/non-CDB environments to ensure compatibility

​

Data Type Mapping and Primary Key Overrides

• Scale-0 NUMBER Columns: Oracle NUMBER columns with a scale of 0 (e.g., NUMBER(p,0) or NUMBER(p,-s)) are automatically mapped to JSON integer to ensure they are stored correctly as INT64 (instead of FLOAT) in downstream targets.
• Primary Key Overrides Coercion: For tables missing a native Oracle primary key, using the primary_key_overrides setting guarantees the correct keys are defined. If any of the declared key columns are discovered as bare NUMBER (which might otherwise default to a float), they will be forcibly retyped to integer. A primary key cannot be a float downstream as engines rely on absolute equality for deduplication and merges.

​

Column Name Sanitization & Preservation

​

Connection Resilience

​

Performance Considerations

​

Troubleshooting

If you encounter any issues, reach out to us via Slack, and we’ll gladly assist you!

​

Skills for agents