Recover a SQL Server to Oracle Load After the VPN Drops

A cross-database transfer dies when the VPN drops, and "run it again" is not a recovery plan. The source extraction may be complete, the target may already have committed, or the reply may simply have been lost on the way back. Those three states need different actions, and a client that only knows "error" cannot tell them apart.

The fix is architectural: give every run a durable batch identity, and let the databases answer questions instead of letting the client guess.

What the database actually guarantees

SQL Server and Oracle both give atomicity inside a transaction. Neither gives you atomicity across the two of them. A lost connection does not corrupt the database, and it does not force a manual rollback either, because the server resolves its own transaction without you. What breaks is the caller's knowledge of the outcome.

Committed separately means committed separately. A VPN carrying both connections does not merge two commits into one unit of work.

The commit you never heard about

One failure mode punishes naive retry logic: the server commits, and the acknowledgment never reaches the caller. Oracle documents this as the unknown-outcome problem and provides Transaction Guard for it in the transaction documentation. A communication error is not evidence that a submitted commit failed, and treating it as evidence is how a batch gets posted twice. The distinction matters most on the retry path, where the operator has an error message and no other signal to act on.

So the recovery step has to read the outcome from the target rather than infer it from the error text.

Give the transfer a batch identity

Every run gets an immutable batch ID, and the target records what it did with that ID. The lifecycle is four steps, and it looks the same regardless of vendor:

1. Extract an identified, immutable source batch with a recorded schema and boundary.
2. Transfer it into target staging and detect incomplete or conflicting content before declaring it ready.
3. Apply it under the target's own uniqueness or locking rules and record completion in the same local transaction.
4. Reconcile the durable outcome through the supported protocol before acknowledging or retrying.

Retries reuse the ID. A new ID per attempt defeats the duplicate prevention you just built, which sounds obvious until a retry wrapper generates one automatically.

The ID also gives you something to reconcile against later, long after the client process is gone.

Claim the batch, then apply

The apply step must claim the batch ID, make the business changes, and record completion in one local transaction. Doing the check and the insert as separate statements reintroduces the race you were trying to remove. If two workers race, exactly one claim wins and the other is told so.

An absent completion record is not proof that an in-flight transaction will never commit. The claim has to serialize competing attempts, or the protocol has to read the durable outcome before deciding anything.

Use the database's supported mechanism for that serialization rather than a check-then-repeat loop in application code.

What the design still cannot promise

Staging makes the work restartable and reduces dependence on one long-lived session. It does not create a single atomic commit across SQL Server, Oracle and whatever external side effect the job performs. If the business genuinely needs that, design and qualify a supported distributed transaction or a reconciliation process, including who owns in-doubt work. Anything weaker than that belongs in a reconciliation runbook, not in the transfer code.

Accept interruption in three places: during transfer, during apply, and after commit but before acknowledgment. Then check the business result rather than the client's message, and let the batch ledger say whether the work is incomplete, committed, or still unresolved.