Oracle as a data source

​

0. Required pre work

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

-- 2. Enable supplemental logging at database level
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

-- 3. Grant LogMiner permissions to your extraction user
GRANT EXECUTE ON DBMS_LOGMNR TO your_username;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO your_username;
GRANT SELECT ON V_$LOGMNR_LOGS TO your_username;
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;

-- 4. For specific tables, 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);

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)

-- 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;

​

1. Add your Oracle access

1. Once you have done the pre work defined in section 0, you can inform your database accesses. In the Sources tab, click on the “Add source” button located on the top right of your screen. Then, select the Oracle option from the list of connectors.
2. Click Next and you’ll be prompted to add your database 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

   ​

   Advanced Configuration

   • SID: Alternative to Service Name for older Oracle configurations
   • Filter Schemas: Array of schema names to include (if empty, all schemas are discovered)
   • SSH Tunnel: Configuration for secure connections through a bastion server
   • SSL/TLS: Enable encrypted connections with certificate configuration
3. Once you are done configuring, click Next.

​

2. Select your Oracle DB streams

1. The next step is letting us know which streams you want to bring. You can select entire groups of streams or only a subset of them.

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

2. Click Next.

​

3. Configure your Oracle DB data streams

1. Customize how you want your data to appear in your catalog. Select a name for each table (which will contain the fetched data) and the type of sync.

• Table name: we suggest a name, but feel free to customize it. You have the option to add a prefix 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.

2. Click Next.

​

Replication Methods

​

Full Table Sync

• Small reference/lookup tables
• Tables where you need only the current state
• Initial data loads

​

Incremental Sync

• Large tables with timestamp columns
• Append-only tables (logs, events)
• When you want to preserve historical data

​

Log-Based Sync (Oracle LogMiner)

• 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 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 your Oracle DB data source

1. Describe your data source for easy identification within your organization. You can inform things like what data it brings, to which team it belongs, etc.
2. To define your Trigger, consider how often you want data to be extracted from this source. This decision usually depends on how frequently you need the new table data updated (every day, once a week, or only at specific times).

​

Check your new source!

1. Click Done to finalize the setup. Once completed, you’ll receive confirmation that your new source is set up!
2. You can view your new source on the Sources page. Now, for you to be able to see it on your Catalog, you have to wait for the pipeline to run. You can now monitor it on the Sources page to see its execution and completion. If needed, manually trigger the pipeline by clicking on the refresh icon. Once executed, your new table will appear in the Catalog section.

​

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

​

Performance Considerations

​

Troubleshooting

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