# Oracle

Oracle is BackWave's Networked Adapter for shops that run Oracle Database. It requires Oracle 19c or later and connects through Oracle's managed ODP.NET driver, `Oracle.ManagedDataAccess.Core`. The package targets net8.0, net9.0, and net10.0. It is a full store: transactional enqueue, lease-based claims that let many worker processes share one database, and a fail-stop schema check. It is also the one Networked Adapter with an opt-in wake-up channel, built on `DBMS_ALERT`. The package is not Native AOT compatible. The Oracle managed driver loads types by name and reads assembly locations on its connection paths, and the adapter serializes its batch payloads in reflection mode. A Native AOT publish reports both as trim warnings.

## Registering the store

There is no `AddBackWaveOracle` extension. You construct an `OracleJobStore` from an `OracleStoreOptions` and hand it to the builder's `UseStore`:

```csharp title="Program.cs"
builder.Services.AddBackWave(bw => bw
    .UseStore(new OracleJobStore(new OracleStoreOptions
    {
        ConnectionString = builder.Configuration.GetConnectionString("BackWave")!,
        AutoMigrate = true, // development only
    }))
    .UseJobs(BackWaveJobs.Module)
    .AddWorkerGroup(new WorkerGroupOptions { Name = "default" }));
```

`AddBackWave` needs a store, a job registry, and at least one Worker Group. If you leave one of them out, `AddBackWave` throws an `InvalidOperationException` at startup. The `BackWave.Hosting` package is required.

When the connection string comes from the container, use the factory overload of `UseStore`. The factory runs once, when the store is first resolved:

```csharp title="Program.cs"
backwave.UseStore(sp =>
{
    var connectionString = sp.GetRequiredService<IConfiguration>()
        .GetConnectionString("BackWave")!;
    return new OracleJobStore(new OracleStoreOptions
    {
        ConnectionString = connectionString,
    });
});
```

The store has one constructor and it takes the options record. `ConnectionString` is a required init-only property. The store opens a fresh connection per operation and uses ODP.NET connection pooling, so there is no pool object to dispose.

### The connection string

The adapter uses an ODP.NET connection string. Point it at the pluggable database that holds, or will hold, the BackWave schema:

```text
User Id=backwave;Password=backwave;Data Source=localhost:15210/FREEPDB1;
```

On Oracle the user in the connection string is also the schema owner. See [Schema and migrations](#schema-and-migrations) below.

## Options and their defaults

`OracleStoreOptions` is a small record. Apart from the required connection string, every property has a default.

| Property | Type | Default | What it controls |
|---|---|---|---|
| `ConnectionString` | `string` | required | The ODP.NET string used for every database operation. |
| `AutoMigrate` | `bool` | `false` | When `true`, the schema is applied on first use. When `false`, a missing schema is a fail-stop. |
| `CoordinateMigration` | `bool` | `true` | When `true`, an auto-migrate retries a transient connection fault during a fleet cold boot. Only applies when `AutoMigrate` is on. |
| `SchemaName` | `string` | `"backwave"` | The Oracle schema, which is the owning user. It must be a valid unqualified identifier. |
| `Bounds` | `StoreBounds` | `StoreBounds.Default` | The size and batch caps the store enforces. The tag bounds can only be tightened on Oracle (see below). |
| `HistoryPolicy` | `JobHistoryPolicy` | `TransitionsAndFailureDetail` | How much per-job history is recorded: off, transitions only, or transitions plus failure detail. |
| `EnableWakeUpHints` | `bool` | `false` | Turns on the `DBMS_ALERT` wake-up channel (see below). |
| `LoggerFactory` | `ILoggerFactory?` | `null` | Enables the migration log and the one-shot wake-up hint warning. With `null`, both are silent. |

`HistoryPolicy` is an option, never a schema change. A lower policy cuts write volume for hosts that do not need the failure-detail trail. The store also reports the effective policy it runs under, so an environment kill-switch on failure detail is visible at runtime.

The store reports `SupportsTransactionalEnqueue` as `true`.

## Schema and migrations

On Oracle a schema is the owning user. `SchemaName` names that user, and its default is `backwave`. Connect as that user. The user needs the rights to create tables, sequences, and indexes. The `CONNECT` and `RESOURCE` roles plus `UNLIMITED TABLESPACE` are enough. The migration takes no database lock and needs no `DBMS_LOCK` grant. The only extra grant is `EXECUTE ON SYS.DBMS_ALERT`, and only when `EnableWakeUpHints` is on.

`SchemaName` must be a valid unqualified identifier: 1 to 128 characters, a letter or underscore, then letters, digits, or underscores. An invalid name throws an `ArgumentException` when you create the store. Pass the same name to the migrator's name-taking overload, as [Naming the schema](/docs/storage/schema-migrations#naming-the-schema) describes.

You apply the schema in one of two ways. For local development and tests, set `AutoMigrate = true`. The store then applies the embedded schema on its first operation, not when you construct it. For production, leave `AutoMigrate` off and apply the schema from a deployment pipeline with the public migrator:

```csharp title="Deploy.cs"
await OracleMigrator.MigrateAsync(connectionString, cancellationToken);
```

The whole script is one PL/SQL block and one round trip. It is idempotent, so it is safe to run on every deploy. A name-taking overload, `MigrateAsync(connectionString, schemaName, coordinate, cancellationToken)`, targets a custom schema. A companion `VerifySchemaVersionAsync` makes sure that a database is at the version this adapter expects. `OracleMigrator.ExpectedSchemaVersion` is that version constant, and it is `1`.

`CoordinateMigration` decides what an auto-migrate does on a transient fault. The script is safe to run from many nodes at once, so no lock is taken. Instead, with `CoordinateMigration = true`, the store runs the script up to 8 times before it gives up, and waits 50 ms times the attempt number between attempts. With `false`, the script runs once with no retry. The option only applies when `AutoMigrate` is on. A successful auto-migrate logs Information event 1302 when you supply a `LoggerFactory`.

The version check runs on the store's first operation, after the optional auto-migrate. There are two fail-stop cases, both `InvalidOperationException`. A missing schema, with `AutoMigrate` off, throws:

> BackWave schema not found. Apply src/BackWave.Oracle/Schema/*.sql (or opt in to AutoMigrate).

A schema older than this adapter expects halts the Worker Group. The message names the version in the database and the version the adapter requires:

> BackWave schema version mismatch: database has 0, this adapter requires 1. Fail-stopping the Worker Group - version skew must never corrupt job state.

A schema newer than this adapter expects is accepted, because every BackWave migration is additive. The store logs Warning event 1303 and continues:

> BackWave schema version 2 is newer than the 1 this adapter requires; continuing on the additive schema until this node is upgraded.

That is what keeps a rolling upgrade safe. Upgrade the schema first, or let `AutoMigrate` on the first new node do it. Old nodes log the warning and keep running. The [schema migrations](/docs/storage/schema-migrations) page covers the deploy-pipeline story in full.

## Transactional enqueue

Oracle is a full transactional-enqueue adapter. You can write a job on the same transaction as your business data, so the two commit or roll back as a unit. `EnqueueAsync` and the workflow enqueue take an optional transaction argument. Leave it out and the adapter opens its own connection and commits. Pass one and the adapter enlists in it: your rollback means the job never existed, and your commit publishes the job with the business write.

The transaction you hand BackWave must be an `OracleTransaction` with a live connection. Any other `DbTransaction` type, or one whose connection is detached, throws an `ArgumentException`:

> The Oracle adapter enlists in OracleTransaction instances only.

The job row, its parent edges, its tags, and its first transition-log row all ride the caller's transaction. When wake-up hints are on, the hint rides it too: `DBMS_ALERT.SIGNAL` is transactional, so the hint fires on your commit or never. For EF Core, the [Transactional Enqueue with EF Core](/docs/guides/transactional-enqueue-with-ef-core) guide walks through the whole pattern.

## Wake-Up Hints

Polling is the only correctness mechanism on every adapter. A wake-up hint only brings the next poll forward. On Oracle the hint channel is `DBMS_ALERT`, and it is off by default. Set `EnableWakeUpHints = true` to turn it on. With the channel on, pickup latency for a newly enqueued job drops from the poll interval to a fraction of a second.

The channel has three costs, which is why it is opt-in. The connecting user needs an `EXECUTE` grant on `SYS.DBMS_ALERT`. Each pump parks one extra dedicated session that waits for signals, so `Pumps = 4` parks four sessions. And `DBMS_ALERT.SIGNAL` holds a lock on the alert until the enclosing transaction commits, so concurrent hinted enqueues serialize on it. A long transactional enqueue holds that lock for the whole transaction.

The channel fails safe. If a signal fails, for example on a missing grant, the enqueue still succeeds. The adapter logs Warning event 1501 once for the first failed signal, and polling carries everything at the poll interval:

> Wake-Up Hint channel for oracle is unavailable; falling back to polling until it recovers.

Some managed Oracle offerings restrict `DBMS_ALERT`, Autonomous Database among them. Leave the option off there. The [wake-up hints](/docs/storage/wakeup-hints) page covers the mechanism and the latency story across adapters.

## Enforced limits

The store enforces a set of size and batch caps from `StoreBounds`. Some reject an over-limit operation, some clamp it, and one truncates. The defaults are:

| Bound | Default | Behavior over the limit |
|---|---|---|
| `MaxPayloadBytes` | 65,536 | Enqueue rejected |
| `MaxWireNameLength` | 128 | Enqueue rejected |
| `MaxTagKeyLength` | 200 | Tag rejected on every write path (never truncated) |
| `MaxTagValueLength` | 200 | Tag rejected on every write path (never truncated) |
| `MaxParentsPerJob` | 16 | Enqueue rejected |
| `MaxOutputBytes` | 65,536 | Job output rejected (see below) |
| `MaxClaimBatch` | 32 | Claim request clamped |
| `MaxMonitorPageSize` | 200 | Listing page size clamped |
| `MaxPurgeBatch` | 500 | Purge batch clamped |
| `MaxTransitionsPerJob` | 64 | Oldest transition dropped |
| `MaxFailureDetailBytes` | 8,192 | Failure detail truncated |

Oracle adds one cap of its own. The tag key and value columns are `VARCHAR2(256 CHAR)`, so `MaxTagKeyLength` and `MaxTagValueLength` can only be tightened, never widened past 256. A wider bound throws an `ArgumentException` when you create the store:

> MaxTagKeyLength and MaxTagValueLength cannot exceed 256 on Oracle: the job_tags key and value columns are VARCHAR2(256 CHAR). These bounds can only be tightened on this adapter.

Oracle 19c and 21c cap an `IN` list at 1,000 expressions. The adapter slices every id list internally. A heartbeat over 1,200 leases or a workflow with 1,001 members works without any action on your part.

The distinction between reject and truncate matters. Failure detail is write-only diagnostics, so over-limit detail is truncated rather than failing the operation. Job output is data a descendant job can deserialize, so an over-limit output is rejected and never clipped. The [limits and defaults](/docs/reference/limits-and-defaults) reference lists every bound.

## Exceptions you can hit

A few exceptions are part of the surface an Oracle user touches:

- `JobOutputTooLargeException` is thrown when a successful job reports an output blob larger than `MaxOutputBytes` (64 KiB by default). The check runs before any write, and the exception carries the job id, the actual byte count, and the limit. Store a reference, an id or a blob key, instead of the data itself.
- `JobTagTooLongException` is thrown when an outcome adds a tag whose key or value passes `MaxTagKeyLength` or `MaxTagValueLength`. Tags are rejected, never truncated.
- `ArgumentException` is thrown in three cases: a transaction that is not an `OracleTransaction`, an invalid `SchemaName`, or a tag bound wider than 256.
- `InvalidOperationException` is thrown by the migrator and the first-use check on a missing or older schema.
- `OracleException` surfaces from the driver. The adapter classifies these as transient, so the Worker Group degrades and retries instead of halting: ORA-00060 (deadlock), ORA-00028, ORA-01033, ORA-03113, ORA-03114, ORA-12154, ORA-12170, ORA-12514, ORA-12518, ORA-12537, ORA-12541, and ORA-12570. A `TimeoutException` is transient too. Every classified fault increments the `backwave.store.faults` counter, tagged `backwave.store.fault_kind` as `transient` or `terminal`.

## How it behaves under load

Claims lock candidate rows with `FOR UPDATE SKIP LOCKED` and update them in one transaction, so many worker processes share one database without claiming the same job twice. Oracle's READ COMMITTED isolation is multi-version by default. Monitor reads never block on an in-flight transactional enqueue, and there is no database setting to turn on.

Outcome writes are fenced to the worker, attempt, and live lease that produced them. A worker that lost its lease cannot overwrite a result that a fresh worker has since produced. The stale write changes nothing and reports a stale-lease result. Batched outcome reports, lease-expiry sweeps, and clean-shutdown hand-backs each run as a small fixed number of set-based statements, not one statement per job. See [execution guarantee](/docs/core-concepts/execution-guarantee) for the model these properties add up to.

The adapter pins a round-trip budget per operation, and a test fails if the count moves in either direction:

| Operation | Statements |
|---|---|
| Claim a batch of 32 jobs | 9, with no LOB reads |
| Report 32 outcomes | 5, or 6 when every row carries output |
| Expire 32 leases | 4 |
| List a 200-job page | 2, with no LOB reads |
| Enqueue one job | 2, or 3 with three tags |

The zero LOB reads come from prefetch. The adapter prefetches payload, output, and failure detail up to their bounds, so a page read needs no per-row LOB round trip. A value larger than the prefetch still reads correctly, at one extra round trip.

## Local development

The fastest local instance is Oracle Free in Docker. Add this service to your compose file:

```yaml title="docker-compose.yml"
services:
  oracle:
    image: gvenzl/oracle-free:23-slim
    shm_size: 2gb
    environment:
      ORACLE_PASSWORD: backwave
      APP_USER: backwave
      APP_USER_PASSWORD: backwave
    ports:
      - "15210:1521"
    healthcheck:
      test: ["CMD", "healthcheck.sh"]
      interval: 5s
      timeout: 5s
      retries: 60
```

`shm_size: 2gb` is not optional. Oracle needs far more shared memory than Docker's 64 MB default, and without it the instance dies on first boot with ORA-00600 and ORA-29701. Start it with `docker compose up -d oracle` and wait for the health check to pass. The connection string shown earlier on this page matches this service: `APP_USER` is the owning user, so `SchemaName` stays at its default.

If you want wake-up hints locally, connect as `SYS` and grant `EXECUTE ON SYS.DBMS_ALERT` to the app user. Only the grantor can revoke a grant, so issue it from `SYS` rather than from `SYSTEM`.

## Where to go next

- [Choosing an adapter](/docs/storage/choosing-an-adapter) to weigh Oracle against the other stores.
- [PostgreSQL](/docs/storage/postgres) for the sibling Networked Adapter whose wake-up path is on by default.
- [SQL Server](/docs/storage/sql-server) for the polling-only Networked Adapter.
- [Wake-up hints](/docs/storage/wakeup-hints) for the `DBMS_ALERT` channel in the cross-adapter picture.
- [Schema migrations](/docs/storage/schema-migrations) for applying the schema from a deployment pipeline.
- [Transactional Enqueue with EF Core](/docs/guides/transactional-enqueue-with-ef-core) for enlisting a job in your own transaction.
- [Storage contract](/docs/reference/storage-contract) for the behavior every adapter must honor.
- [Configuration reference](/docs/reference/configuration) for the full set of options.