Oracle-to-Hive Loading Without Sqoop (2) — Keeping Parallel Extraction Consistent with Oracle SCN

In Part 1 we explained that NiFi PG-20 takes over Sqoop's parallel Mapper extraction. But NiFi queries partitions in parallel on several nodes, so each SELECT actually starts and ends at a different time. The source table keeps changing through business transactions during extraction. This post covers how we make queries that run at different times read one and the same point in time.

1. What happens if you read in parallel without any precautions

Suppose a row changes in the source after the query for partition P0 starts but before the query for P3 starts.

P0 reads the state before the change, and P3 reads the state after it. Data from different points in time gets mixed in a single load result. This project also calculates the source row count and the expected row count per partition (the manifest) before extraction, which makes the problem worse.

• The source row count in the manifest does not match the actual extracted row count.
• The minimum and maximum of the split column, or the expected row count per range, change.
• Rows moved or deleted during extraction go missing, or unexpected rows are added.
• Quality metrics such as amount totals and minimum/maximum timestamps do not match.

The API's row count checks can catch these mismatches as failures. But by design it is safer to read the same snapshot from the start than to rerun after a failure.

2. What is an SCN

An SCN (System Change Number) is a logical version number that represents the order of changes inside an Oracle database. It orders transaction commits and database changes, and a Flashback Query (AS OF SCN) returns the data that was committed as of the given SCN.

An SCN is not a date or time, so do not use it like a business date. SCN_TO_TIMESTAMP only gives an approximate time for operational tracking, and the mapping data is kept for a limited period. The reference value for data consistency is the one original SCN stored in the run.

With a fixed SCN of 101, business DML after that point looks like this.

The important point is that business DML can continue during extraction. NiFi does not lock the source or keep a long transaction open. It relies on Oracle's multiversion read consistency and Undo.

3. How this project applies the SCN

At the start of a run, we query the SCN only once and use the same value for the manifest calculation and every partition query.

In terms of Processors and ledger fields, it looks like this.

The SQL to query the SCN is one of the following two. If you cannot grant query privileges on V$DATABASE, use the second.

SELECT TO_CHAR(CURRENT_SCN) AS SNAPSHOT_SCN FROM V$DATABASE;
 
SELECT TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER) AS SNAPSHOT_SCN FROM DUAL;

Treat the SCN as a string

An SCN is a large integer in the Oracle NUMBER(38) range. To avoid losing precision when converting to a JSON number, we read it with TO_CHAR and pass it as a numeric string in API requests and responses, and store it in PostgreSQL as NUMERIC(38,0). Partition boundaries and row counts are handled as strings for the same reason.

The SCN and boundary values go into the PG-20 SQL as is, without quotes. So before inserting them into the SQL, we use NiFi Expression Language to check that they are numeric (^[0-9]+$), and skip the query if they are not.

4. Partition boundaries and the manifest

PG-10's 16_Query_Source_Manifest calculates all of the following in one SQL statement, with the same SCN.

• Source row count (SOURCE_COUNT)
• NULL count, minimum and maximum of the split column
• Amount total and minimum/maximum timestamps (the validation metrics from Part 3)
• PARTITION.COUNT contiguous ranges and the expected row count per range

The boundaries are [lo, hi) intervals that divide the split column's [min, max] into N equal parts. Only the last partition includes its upper bound, so the maximum value is not left out.

WHERE split_column >= lower_bound
  AND split_column <  upper_bound  -- <= for the last partition only

A PG-20 partition query is this boundary with the same SCN attached.

SELECT INSP_DTL_SEQ, BASE_DT, ITEM_CD, AMOUNT, REG_TS, NOTE
  FROM APP.INSP_DTL AS OF SCN 2388556
 WHERE BASE_DT = DATE '2026-09-28'
   AND INSP_DTL_SEQ >= 1
   AND INSP_DTL_SEQ < 15001;

Properties of equal-range splitting

The boundaries split the value range, so if the split column values are not evenly distributed, row counts differ between partitions. The test data deliberately left the seq range 30001–45000 empty, and as a result 1 of the 8 partitions had 0 rows.

• Partitions with an expected row count of 0 are marked SUCCESS by the API right when the manifest is registered, and are not sent to PG-20.
• NULLs in the split column are not caught by range conditions. The current builder does not create a NULL-only partition, so if there are NULLs within the business range, the API rejects the manifest. The split column must be NOT NULL, or have no NULLs within the business condition.

What the API checks in the manifest

When the API receives a manifest, it checks the following.

• Duplicate partition IDs and the planned number of partitions
• Sum of expected row counts = source row count
• Whether 0-row partitions are allowed, and the NULL count
• Whether the ranges are contiguous with no gaps, and whether only the last partition includes its upper bound

On a violation, it commits the run as FAILED_MANIFEST and then returns 422. It does not roll back via an exception, so that the cause of the failure stays in the ledger.

5. Undo: the cost of reading a past point in time

AS OF SCN is not free. When Oracle cannot build the state as of a past SCN from the current block alone, it applies earlier information from Undo to reconstruct a read-consistent block. The older the fixed SCN, the more source DML there is, and the longer partition queries wait or run, the more Undo is needed.

The retention window you need is not the run time of the single longest SELECT. It is from the moment the SCN is obtained until the last partition finishes.

SCN obtained
  → manifest calculation
  → wait in partition queue
  → last partition SELECT completes
  + margin for failures and load

So the operating standard must satisfy this relationship.

Effective Undo retention time > worst-case time from obtaining the SCN to completing the last partition + safety margin

• UNDO_RETENTION is a target retention time. If the tablespace runs short of space, needed Undo can be reused first.
• Enabling Retention Guarantee makes retention stronger, but when Undo space runs short, business DML itself can fail. So this is a decision for the DBA, together with the business workload.

Here are check queries to review with the DBA.

SELECT name, value FROM V$PARAMETER WHERE name = 'undo_retention';
 
SELECT begin_time, end_time, tuned_undoretention, maxquerylen, ssolderrcnt
  FROM V$UNDOSTAT
 ORDER BY begin_time DESC
 FETCH FIRST 24 ROWS ONLY;
 
SELECT tablespace_name, retention FROM DBA_TABLESPACES WHERE contents = 'UNDO';

Do not look only at the longest run time. Check tuned_undoretention, Undo usage and ssolderrcnt during the hours when business DML peaks.

6. ORA-01555: no retry, start a new run

When Undo runs short, snapshot errors occur.

PG-20 does not automatically retry a long query that hit a snapshot error with the same SCN. If the needed Undo is already gone, retrying with the same SCN is unlikely to succeed, and it holds the other partitions even longer. The failure is reported straight to the API through PG-90, and if the error code is ORA-01555/ORA-08180, the API sets the run to FAILED_SNAPSHOT_EXPIRED.

The recovery procedure is as follows.

1. Confirm that publishing has not started for the run.
2. Check Oracle Undo usage, tuned_undoretention, long-running queries and DML spikes.
3. Adjust the Undo size and retention policy, or NiFi parallelism and partition size.
4. Start a new run. A new run uses a new SCN, a new HDFS path and a new staging table.

Re-reading only the failed partitions with a new SCN and appending them to the existing files is forbidden. That would mix different snapshots in one HDFS run and defeat the purpose of fixing the SCN.

We triggered ORA-01555 in the test environment to confirm this. The partition query failed without a retry, and the run became FAILED_SNAPSHOT_EXPIRED. The validation step was not called, and the new run we started next succeeded with a new SCN.

7. A related pitfall: NUMBER without precision

This is not directly related to the SCN, but there is one Oracle extraction pitfall we actually hit during testing. PG-20's ExecuteSQLRecord has Use Avro Logical Types enabled, so it writes Parquet while keeping decimal types. In this case, NUMBER columns declared without precision are written with the Processor's Default Decimal Precision/Scale.

The second row is the most dangerous. Values change with no error, and row count checks do not catch it. This is why we compare quality metrics such as amount totals at every stage (Part 3). Where possible, the surest fix is to specify precision in the extracted column, as in CAST(AMOUNT AS NUMBER(18,2)).

8. What is guaranteed and what is not

SCN boundaries across multiple tables

Each source table has its own Job PG, and each PG-10 queries the SCN on its own. So partitions within one table are consistent, but SCNs can differ slightly between tables.

If the business requirement is "a consistent snapshot of each table", the current structure is enough. But if "all tables must be at exactly the same point in time", as with PK/FK relationships or accounting close, an extension is needed. A higher-level coordinator would query a common SCN once and pass it to every Job run, and we would add batch-level state that ties overall success together, plus a failure policy. In that case, the Undo retention time for the common SCN must be calculated up to the table that finishes last. This feature is not in the current implementation scope, so it must be stated as a business requirement before adoption.

9. Pre-production checklist

• Query account privileges: SELECT and FLASHBACK on target tables, SELECT on SYS.V_$DATABASE
• Confirm with the DBA that UNDO_RETENTION and Undo size leave enough room over the maximum run time
• For each target table, check the (business condition column, split column) index and the manifest SQL execution plan
• Set the Oracle connection pool size and PG-20 Concurrent Tasks to match the approved number of concurrent sessions
• Identify tables with NULLs in the split column
• Specify precision with CAST for NUMBER columns without precision

Wrapping up

You cannot guarantee consistency in parallel extraction by "reading fast to narrow the gap". This project separates one logical point in time (the SCN) from the many actual parallel execution times. Each partition reads the same committed state no matter which node runs it or when, and in return we made the required Undo retention an explicit operating condition. If Undo runs short, we do not force the pieces together. We start over with a new run.

Next, in Part 3, we cover the design of the Load Control API: who decides, and how, that the partitions finished in parallel are complete exactly once, and hands them to the next stage.