Oracle - Wirekite Docs

Overview

Wirekite supports Oracle 11g and above as a source database for:

Schema Extraction - Extract table definitions to Wirekite’s intermediate format
Data Extraction - Bulk extract table data for initial load
Change Extraction (CDC) - Capture ongoing inserts, updates, and deletes via LogMiner

Oracle Change Extraction uses LogMiner to read archive logs and capture changes. This requires archivelog mode to be enabled and supplemental logging configured on the tables being extracted.

Prerequisites

Before configuring Oracle as a Wirekite source, ensure the following requirements are met:

Database Configuration

Version: Oracle 12c or later (12c+ required for the LOGMINING privilege)
Archivelog Mode (CDC only): Must be enabled for change capture
Supplemental Logging (CDC only): Database-level and table-level supplemental logging must be configured (see Source Database Setup below)

File System Access

Ensure the user running Wirekite has write permissions to the output directory
Verify sufficient disk space for extracted data files

Replication Considerations

When performing initial data extraction, stop replication and quiesce the database. This ensures the dump is consistent to a specific Oracle SCN (System Change Number) and all transactions have either committed or rolled back. It is highly recommended to run Wirekite against a Data Guard replica rather than the production primary.

Source Database Setup

Wirekite requires a dedicated WIREKITE schema (user) for internal tracking tables and specific privileges on the application schema and system views. Follow these steps to configure your Oracle source.

Step 1: Create the Wirekite User/Schema

In Oracle, a user and a schema are the same thing. Create a dedicated WIREKITE user:

-- Connect as SYS or a DBA user

CREATE USER wirekite IDENTIFIED BY "<your_password>"
    DEFAULT TABLESPACE users
    QUOTA UNLIMITED ON users;

Step 2: Grant Basic Session Privileges

GRANT CREATE SESSION TO wirekite;

Step 3: Grant Table Creation Privileges

Wirekite creates two internal tracking tables (wirekite_progress and wirekite_action) in the WIREKITE schema for progress tracking and crash recovery:

GRANT CREATE TABLE TO wirekite;

Step 4: Grant Read Access to Application Tables

For each application table that needs to be extracted:

GRANT SELECT ON <app_schema>.table1 TO wirekite;
GRANT SELECT ON <app_schema>.table2 TO wirekite;
-- repeat for each table to be extracted

Alternatively, to grant SELECT on all current and future tables in a schema:

GRANT SELECT ANY TABLE TO wirekite;

SELECT ANY TABLE is a broad privilege. Use individual table grants for tighter security.

Step 5: Grant Data Dictionary Access

Wirekite queries Oracle data dictionary views for schema metadata (columns, constraints, indexes, etc.):

GRANT SELECT ANY DICTIONARY TO wirekite;

This covers all of the following views that Wirekite uses:

DBA_TAB_COLUMNS — column metadata
DBA_CONSTRAINTS / DBA_CONS_COLUMNS — primary keys, unique keys, foreign keys, check constraints
DBA_INDEXES / DBA_IND_COLUMNS — index metadata
DBA_SEQUENCES — sequences (full schema extraction)
DBA_VIEWS — views (full schema extraction)
DBA_MVIEWS / DBA_JOBS / DBA_REFRESH — materialized views (full schema extraction)
DBA_SYNONYMS / DBA_OBJECTS — synonyms (full schema extraction)
DBA_TAB_PRIVS — privilege extraction (full schema extraction)
DBA_ROLES / DBA_USERS / DBA_ROLE_PRIVS / DBA_SYS_PRIVS — roles/users (full schema extraction)
DBA_TRIGGERS — triggers (full schema extraction)
DBA_SOURCE — procedure/function/package source (full schema extraction)

If you prefer individual grants instead of SELECT ANY DICTIONARY, grant SELECT on each of the views listed above.

Step 6 (CDC Only): Enable Supplemental Logging

For change data capture (CDC), Wirekite uses Oracle LogMiner to read redo/archive logs. Enable database-level supplemental logging:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Enable table-level primary key supplemental logging for each table that needs change tracking:

ALTER TABLE <app_schema>.table1 ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER TABLE <app_schema>.table2 ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
-- repeat for each table to be tracked

All tables used with CDC must have a primary key.

Verify supplemental logging:

-- Database level
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
-- Should return YES

-- Table level
SELECT OWNER, TABLE_NAME, LOG_GROUP_TYPE
FROM DBA_LOG_GROUPS
WHERE LOG_GROUP_TYPE = 'PRIMARY KEY LOGGING'
  AND OWNER = '<APP_SCHEMA>';

Step 7 (CDC Only): Grant LogMiner Privileges

-- V$ dynamic performance views for LogMiner
GRANT SELECT ON SYS.V_$DATABASE TO wirekite;
GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO wirekite;
GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO wirekite;
GRANT SELECT ON SYS.V_$TRANSACTION TO wirekite;

-- LogMiner packages
GRANT EXECUTE ON SYS.DBMS_LOGMNR TO wirekite;
GRANT EXECUTE ON SYS.DBMS_LOGMNR_D TO wirekite;

-- LogMiner privilege (Oracle 12c+)
GRANT LOGMINING TO wirekite;

Step 8 (CDC Only, Optional): Grant Log Switch Privilege

If you want Wirekite to automatically flush redo logs to archive logs (the autoFlushLogs option):

GRANT ALTER SYSTEM TO wirekite;

This allows Wirekite to execute ALTER SYSTEM SWITCH LOGFILE and ALTER SYSTEM ARCHIVE LOG CURRENT. If you prefer not to grant this, ensure that archive logs are generated frequently enough by your existing Oracle configuration.

Step 9 (Standby Handover Only): SYSDBA Access

If you are extracting from an Oracle Data Guard standby and need to stop/start managed recovery:

-- SYSDBA is required for:
--   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
--   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

This is only needed for Data Guard standby operations. For normal primary database extraction, SYSDBA is not required.

Complete Setup Script

Data Extraction Only

-- Connect as SYS or DBA

-- 1. Create wirekite user
CREATE USER wirekite IDENTIFIED BY "<your_password>"
    DEFAULT TABLESPACE users
    QUOTA UNLIMITED ON users;

-- 2. Basic session access
GRANT CREATE SESSION TO wirekite;

-- 3. Allow internal table creation
GRANT CREATE TABLE TO wirekite;

-- 4. Grant read access to application tables
GRANT SELECT ON <app_schema>.table1 TO wirekite;
GRANT SELECT ON <app_schema>.table2 TO wirekite;
-- repeat for each table to be extracted

-- 5. Data dictionary access
GRANT SELECT ANY DICTIONARY TO wirekite;

Data Extraction + CDC (Change Data Capture)

-- Connect as SYS or DBA

-- 1. Create wirekite user
CREATE USER wirekite IDENTIFIED BY "<your_password>"
    DEFAULT TABLESPACE users
    QUOTA UNLIMITED ON users;

-- 2. Basic session access
GRANT CREATE SESSION TO wirekite;

-- 3. Allow internal table creation
GRANT CREATE TABLE TO wirekite;

-- 4. Grant read access to application tables
GRANT SELECT ON <app_schema>.table1 TO wirekite;
GRANT SELECT ON <app_schema>.table2 TO wirekite;
-- repeat for each table to be extracted

-- 5. Data dictionary access
GRANT SELECT ANY DICTIONARY TO wirekite;

-- 6. LogMiner privileges
GRANT SELECT ON SYS.V_$DATABASE TO wirekite;
GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO wirekite;
GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO wirekite;
GRANT SELECT ON SYS.V_$TRANSACTION TO wirekite;
GRANT EXECUTE ON SYS.DBMS_LOGMNR TO wirekite;
GRANT EXECUTE ON SYS.DBMS_LOGMNR_D TO wirekite;
GRANT LOGMINING TO wirekite;

-- 7. (Optional) Auto log switch
GRANT ALTER SYSTEM TO wirekite;

-- 8. Enable supplemental logging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- 9. Enable PK supplemental logging per table
ALTER TABLE <app_schema>.table1 ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER TABLE <app_schema>.table2 ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
-- repeat for each table to be tracked

Verify the Setup

Connect as the wirekite user and verify access:

-- Should succeed
SELECT * FROM <app_schema>.table1 WHERE ROWNUM = 1;

-- Should succeed
SELECT column_name FROM DBA_TAB_COLUMNS
WHERE owner = UPPER('<app_schema>') AND table_name = 'TABLE1';

-- Should succeed (CDC only)
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

What Wirekite Creates

Object	Type	Schema	Purpose
`wirekite_progress`	Table	`WIREKITE`	Tracks extraction progress, crash recovery
`wirekite_action`	Table	`WIREKITE`	Run/pause/stop control
`wkp_finish_time`	Index	`WIREKITE`	Index on wirekite_progress
`wkp_start_time`	Index	`WIREKITE`	Index on wirekite_progress

Wirekite does NOT create any triggers, stored procedures, packages, or other PL/SQL objects on the source database. CDC uses Oracle LogMiner exclusively.

Privilege Summary

Privilege	Required For
`CREATE SESSION`	Basic connectivity
`CREATE TABLE`	Internal tracking tables in WIREKITE schema
`SELECT ON <app_schema>.<table>`	Reading application table data
`SELECT ANY DICTIONARY`	Schema metadata from data dictionary views
`SELECT ON V_$DATABASE`	CDC: supplemental logging check, current SCN
`SELECT ON V_$ARCHIVED_LOG`	CDC: archive log discovery for LogMiner
`SELECT ON V_$LOGMNR_CONTENTS`	CDC: reading LogMiner output
`SELECT ON V_$TRANSACTION`	CDC: transaction checkpoint for crash recovery
`EXECUTE ON SYS.DBMS_LOGMNR`	CDC: LogMiner session management
`EXECUTE ON SYS.DBMS_LOGMNR_D`	CDC: LogMiner dictionary
`LOGMINING`	CDC: LogMiner access (Oracle 12c+)
`ALTER SYSTEM`	CDC (optional): automatic redo log switch

Schema Extractor

The Schema Extractor reads table definitions from Oracle and outputs them to Wirekite’s intermediate schema format (.skt file). This schema file is used by target loaders to create corresponding tables in the destination database.

The Schema Extractor can run standalone or as part of the Orchestrator workflow. The configuration parameters are the same in both cases.

Required Parameters

string

required

Path to a file containing the Oracle connection string. The file should contain exactly one line with the DSN.

Connection string format:

username/password@host:port/service_name

Example DSN file contents:

wirekite/secretpass@oracle-replica.example.com:1521/ORCL

string

required

Path to a file listing the tables to extract, one per line in schema.table format.

Example tablesFile contents:

HR.EMPLOYEES
HR.DEPARTMENTS
SALES.ORDERS
INVENTORY.PRODUCTS

string

required

Absolute path to the directory where Wirekite will write the schema file (wirekite_schema.skt). The directory must exist and be writable.

string

required

Absolute path to the log file where the Schema Extractor will write operational logs.

Optional Parameters

string

Rename the source schema in the output. Format: sourceSchema:targetSchema. Useful when the target database uses a different schema name.

boolean

default:"false"

When true, extracts all database objects including views, indexes, and constraints in addition to table definitions.

Data Extractor

The Data Extractor performs bulk extraction of table data, writing records to Wirekite’s intermediate data format (.dkt files). Large tables are automatically split across multiple files based on the maxRowsPerDump setting.

Required Parameters

string

required

Path to a file containing the Oracle connection string.

Connection string format:

username/password@host:port/service_name

string

required

Path to a file listing the tables to extract, one per line in schema.table format.

string

required

Absolute path to the directory where Wirekite will write data files. Files are named schema.table.N.dkt where N is a sequence number.

string

required

Absolute path to the log file for Data Extractor operations.

Optional Parameters

integer

default:"5"

Number of parallel extraction threads. Each thread consumes one database connection and approximately one CPU core. Set this based on available CPU cores and acceptable database load.

integer

default:"200000"

Maximum number of rows written to each output file. Large tables are split into multiple files. Adjust based on available memory and disk space.

boolean

default:"false"

When true, the data extractor captures the current SCN at the end of the load and writes it to position.pkt. Used to hand off to a subsequent change extractor without losing any commits. The orchestrator sets this automatically in data+change mode — only set it explicitly when invoking the data extractor outside the orchestrator.

boolean

default:"false"

When true, binary and string data is encoded as hexadecimal instead of base64. Hex encoding produces larger files but may be required for certain target databases (e.g., Snowflake).

boolean

default:"true"

When true, sorts output files by table name for easier management and verification.

string

Rename the source schema in the output. Format: sourceSchema:targetSchema.

For best performance on dedicated extraction hosts, set maxThreads equal to the number of CPU cores available.

Change Extractor

The Change Extractor captures ongoing data changes (INSERT, UPDATE, DELETE) by reading Oracle archive logs via LogMiner. It outputs change events to Wirekite’s change format (.ckt files) for loading into target databases.

The Change Extractor requires archivelog mode to be enabled. It uses Oracle LogMiner to read and parse archive log files.

Required Parameters

string

required

Path to a file containing the Oracle connection string.

Connection string format:

username/password@host:port/service_name

string

required

Path to a file listing the tables to track for changes, one per line in schema.table format.

string

required

Absolute path to the directory where Wirekite will write change files. Files are named with sequential numbers (e.g., 0.ckt, 1.ckt, etc.).

string

required

Absolute path to the log file for Change Extractor operations.

Position Parameters

string

default:"none"

The Oracle System Change Number (SCN) from which to start extracting changes. This is typically the SCN captured when the data extraction was taken. When running via the Orchestrator in data+change mode, this is automatically captured. When not specified, defaults to "none" meaning no starting position is set.

In change-only mode (without data extraction), you must specify the correct startScn to define where change capture should begin. Using incorrect values may result in missing changes or duplicate data.

Optional Parameters

boolean

default:"false"

When true, enables detailed logging including metadata files that can help troubleshoot issues.

boolean

default:"false"

When true, processes only one archive log file and then exits. Useful for testing or manual incremental processing.

boolean

default:"false"

When true, includes BEGIN and COMMIT markers in the output. Wirekite guarantees that transactions never span multiple output files regardless of this setting.

integer

default:"5000"

Number of change events to buffer before flushing to disk. Lower values reduce memory usage but increase I/O operations. The buffer is always flushed at transaction boundaries.

boolean

default:"false"

When true, the extractor exits after processing all available archive logs. When false (default), it runs continuously, waiting for new archive logs indefinitely.

integer

default:"30"

Number of seconds to wait between checks for new archive logs when idle. Only applies when exitWhenIdle is false.

boolean

default:"false"

When true, automatically triggers archive log switches to ensure changes are captured promptly.

integer

default:"30"

Interval in seconds between automatic log flushes when autoFlushLogs is enabled.

integer

default:"20"

Maximum number of archive logs to process in a single LogMiner session before restarting. Parsed for backward compatibility but no longer used in the current implementation.

integer

default:"5"

Number of previous archive logs to include in each mining session. Parsed for backward compatibility but no longer used in the current implementation.

string

Rename the source schema in the output. Format: sourceSchema:targetSchema.

Orchestrator Configuration

When using the Wirekite Orchestrator, prefix source parameters with source.schema., source.data., or source.change. depending on the operation mode. Example orchestrator configuration for Oracle source:

# Main configuration
source=oracle
target=snowflake

# Schema extraction
source.schema.dsnFile=/opt/wirekite/config/oracle.dsn
source.schema.tablesFile=/opt/wirekite/config/tables.txt
source.schema.outputDirectory=/opt/wirekite/output/schema
source.schema.logFile=/var/log/wirekite/schema-extractor.log

# Data extraction
source.data.dsnFile=/opt/wirekite/config/oracle.dsn
source.data.tablesFile=/opt/wirekite/config/tables.txt
source.data.outputDirectory=/opt/wirekite/output/data
source.data.logFile=/var/log/wirekite/data-extractor.log
source.data.maxThreads=8
source.data.maxRowsPerDump=100000
source.data.hexEncoding=false

# Change extraction (CDC)
source.change.dsnFile=/opt/wirekite/config/oracle.dsn
source.change.tablesFile=/opt/wirekite/config/tables.txt
source.change.outputDirectory=/opt/wirekite/output/changes
source.change.logFile=/var/log/wirekite/change-extractor.log
source.change.eventsPerFlush=10000
source.change.exitWhenIdle=false
source.change.autoFlushLogs=true
source.change.flushLogIntervalSeconds=300

When running in data+change mode, the Orchestrator automatically captures the SCN after data extraction and passes it to the Change Extractor. You do not need to manually specify startScn in this mode.

For complete Orchestrator documentation, see the Execution Guide.

​Overview

​Prerequisites

​Database Configuration

​File System Access

​Replication Considerations

​Source Database Setup

​Step 1: Create the Wirekite User/Schema

​Step 2: Grant Basic Session Privileges

​Step 3: Grant Table Creation Privileges

​Step 4: Grant Read Access to Application Tables

​Step 5: Grant Data Dictionary Access

​Step 6 (CDC Only): Enable Supplemental Logging

​Step 7 (CDC Only): Grant LogMiner Privileges

​Step 8 (CDC Only, Optional): Grant Log Switch Privilege

​Step 9 (Standby Handover Only): SYSDBA Access

​Complete Setup Script

​Data Extraction Only

​Data Extraction + CDC (Change Data Capture)

​Verify the Setup

​What Wirekite Creates

​Privilege Summary

​Schema Extractor

​Required Parameters

​Optional Parameters

​Data Extractor

​Required Parameters

​Optional Parameters

​Change Extractor

​Required Parameters

​Position Parameters

​Optional Parameters

​Orchestrator Configuration

Overview

Prerequisites

Database Configuration

File System Access

Replication Considerations

Source Database Setup

Step 1: Create the Wirekite User/Schema

Step 2: Grant Basic Session Privileges

Step 3: Grant Table Creation Privileges

Step 4: Grant Read Access to Application Tables

Step 5: Grant Data Dictionary Access

Step 6 (CDC Only): Enable Supplemental Logging

Step 7 (CDC Only): Grant LogMiner Privileges

Step 8 (CDC Only, Optional): Grant Log Switch Privilege

Step 9 (Standby Handover Only): SYSDBA Access

Complete Setup Script

Data Extraction Only

Data Extraction + CDC (Change Data Capture)

Verify the Setup

What Wirekite Creates

Privilege Summary

Schema Extractor

Required Parameters

Optional Parameters

Data Extractor

Required Parameters

Optional Parameters

Change Extractor

Required Parameters

Position Parameters

Optional Parameters

Orchestrator Configuration