> ## Documentation Index
> Fetch the complete documentation index at: https://docs.wirekite.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Oracle

> This guide explains how to configure Oracle as a source database for Wirekite data extraction and replication.

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

<Note>
  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.
</Note>

## Prerequisites

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

### Database Configuration

1. **Version**: Oracle 12c or later (12c+ required for the `LOGMINING` privilege)
2. **Archivelog Mode** (CDC only): Must be enabled for change capture
3. **Supplemental Logging** (CDC only): Database-level and table-level supplemental logging must be configured (see [Source Database Setup](#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

<Warning>
  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.
</Warning>

***

## 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:

```sql theme={null}
-- 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

```sql theme={null}
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:

```sql theme={null}
GRANT CREATE TABLE TO wirekite;
```

### Step 4: Grant Read Access to Application Tables

For each application table that needs to be extracted:

```sql theme={null}
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:

```sql theme={null}
GRANT SELECT ANY TABLE TO wirekite;
```

<Warning>
  `SELECT ANY TABLE` is a broad privilege. Use individual table grants for tighter security.
</Warning>

### Step 5: Grant Data Dictionary Access

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

```sql theme={null}
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)

<Tip>
  If you prefer individual grants instead of `SELECT ANY DICTIONARY`, grant SELECT on each of the views listed above.
</Tip>

### 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:**

```sql theme={null}
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
```

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

```sql theme={null}
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
```

<Warning>
  All tables used with CDC must have a primary key.
</Warning>

**Verify supplemental logging:**

```sql theme={null}
-- 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

```sql theme={null}
-- 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):

```sql theme={null}
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:

```sql theme={null}
-- SYSDBA is required for:
--   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
--   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
```

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

### Complete Setup Script

#### Data Extraction Only

```sql theme={null}
-- 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)

```sql theme={null}
-- 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:

```sql theme={null}
-- 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                |

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

### 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.

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

### Required Parameters

<ResponseField name="dsnFile" type="string" required>
  Path to a file containing the Oracle connection string. The file should contain exactly one line with the DSN.
</ResponseField>

**Connection string format:**

```
username/password@host:port/service_name
```

**Example DSN file contents:**

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

<ResponseField name="tablesFile" type="string" required>
  Path to a file listing the tables to extract, one per line in `schema.table` format.
</ResponseField>

**Example tablesFile contents:**

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

<ResponseField name="outputDirectory" type="string" required>
  Absolute path to the directory where Wirekite will write the schema file (`wirekite_schema.skt`). The directory must exist and be writable.
</ResponseField>

<ResponseField name="logFile" type="string" required>
  Absolute path to the log file where the Schema Extractor will write operational logs.
</ResponseField>

### Optional Parameters

<ResponseField name="schemaRename" type="string">
  Rename the source schema in the output. Format: `sourceSchema:targetSchema`. Useful when the target database uses a different schema name.
</ResponseField>

<ResponseField name="extractAllObjects" type="boolean" default="false">
  When `true`, extracts all database objects including views, indexes, and constraints in addition to table definitions.
</ResponseField>

***

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

<ResponseField name="dsnFile" type="string" required>
  Path to a file containing the Oracle connection string.
</ResponseField>

**Connection string format:**

```
username/password@host:port/service_name
```

<ResponseField name="tablesFile" type="string" required>
  Path to a file listing the tables to extract, one per line in `schema.table` format.
</ResponseField>

<ResponseField name="outputDirectory" type="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.
</ResponseField>

<ResponseField name="logFile" type="string" required>
  Absolute path to the log file for Data Extractor operations.
</ResponseField>

### Optional Parameters

<ResponseField name="maxThreads" type="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.
</ResponseField>

<ResponseField name="maxRowsPerDump" type="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.
</ResponseField>

<ResponseField name="capturePosition" type="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.
</ResponseField>

<ResponseField name="hexEncoding" type="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).
</ResponseField>

<ResponseField name="sortFiles" type="boolean" default="true">
  When `true`, sorts output files by table name for easier management and verification.
</ResponseField>

<ResponseField name="schemaRename" type="string">
  Rename the source schema in the output. Format: `sourceSchema:targetSchema`.
</ResponseField>

<Tip>
  For best performance on dedicated extraction hosts, set `maxThreads` equal to the number of CPU cores available.
</Tip>

***

## 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.

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

### Required Parameters

<ResponseField name="dsnFile" type="string" required>
  Path to a file containing the Oracle connection string.
</ResponseField>

**Connection string format:**

```
username/password@host:port/service_name
```

<ResponseField name="tablesFile" type="string" required>
  Path to a file listing the tables to track for changes, one per line in `schema.table` format.
</ResponseField>

<ResponseField name="outputDirectory" type="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.).
</ResponseField>

<ResponseField name="logFile" type="string" required>
  Absolute path to the log file for Change Extractor operations.
</ResponseField>

### Position Parameters

<ResponseField name="startScn" type="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.
</ResponseField>

<Warning>
  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.
</Warning>

### Optional Parameters

<ResponseField name="verbose" type="boolean" default="false">
  When `true`, enables detailed logging including metadata files that can help troubleshoot issues.
</ResponseField>

<ResponseField name="processOneArchivelog" type="boolean" default="false">
  When `true`, processes only one archive log file and then exits. Useful for testing or manual incremental processing.
</ResponseField>

<ResponseField name="processCommits" type="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.
</ResponseField>

<ResponseField name="eventsPerFlush" type="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.
</ResponseField>

<ResponseField name="exitWhenIdle" type="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.
</ResponseField>

<ResponseField name="idleWaitSeconds" type="integer" default="30">
  Number of seconds to wait between checks for new archive logs when idle. Only applies when `exitWhenIdle` is `false`.
</ResponseField>

<ResponseField name="autoFlushLogs" type="boolean" default="false">
  When `true`, automatically triggers archive log switches to ensure changes are captured promptly.
</ResponseField>

<ResponseField name="flushLogIntervalSeconds" type="integer" default="30">
  Interval in seconds between automatic log flushes when `autoFlushLogs` is enabled.
</ResponseField>

<ResponseField name="maxLogsPerSession" type="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.
</ResponseField>

<ResponseField name="lookbackLogs" type="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.
</ResponseField>

<ResponseField name="schemaRename" type="string">
  Rename the source schema in the output. Format: `sourceSchema:targetSchema`.
</ResponseField>

***

## 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
```

<Note>
  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.
</Note>

For complete Orchestrator documentation, see the [Execution Guide](/run/execution).