Oracle to S3 as Parquet: Export Tables and Replicate Changes with CDC

Oracle tables reach Amazon S3 as Parquet in five ways: AWS Database Migration Service (DMS) with a Parquet target, Oracle's DBMS_CLOUD.EXPORT_DATA on Autonomous Database, scheduled Spark, AWS Glue or Python jobs, a self-run log-based CDC stack such as GoldenGate or Debezium, or a managed CDC service. DMS suits AWS-centred teams, DBMS_CLOUD suits Autonomous Database, and scheduled jobs suit custom transforms. Only DMS and the log-based CDC tools record deletes.

The hard part is the files. Oracle DATE carries a time of day, NUMBER carries up to 38 digits, LOBs get truncated or dropped, and a Parquet file can't be edited, so every update and delete arrives as another row. For backups, use RMAN or Data Pump; on Amazon RDS for Oracle the S3 integration option copies those files to a bucket, but they aren't analytics files. Estuary supports this route with an Oracle capture that reads redo through LogMiner and an Amazon S3 Parquet materialization that appends each change to Parquet files on an upload interval you set.

Choosing an Oracle to S3 method

DMS and both CDC rows read redo, so they record deletes; exports and query jobs see only surviving rows. AWS DMS alternatives compares more tools.

From redo logs to Parquet files: how Oracle to S3 replication works

Every method runs five stages:

1. Initial load: a full read of each table (DMS full load, JDBC read, export or backfill).
2. Ongoing changes: query methods re-read by a timestamp column or ORA_ROWSCN, which tracks blocks, not rows, unless the table has ROWDEPENDENCIES. Log methods read online and archived redo through LogMiner, Binary Reader or GoldenGate, in system change number (SCN) order, deletes included.
3. Type mapping: each Oracle column gets a Parquet type.
4. File writing: rows are buffered into row groups and files. A finished Parquet file is never edited, so each change is a new row in a new file.
5. Query: Athena, Spark or Trino read the files through a catalog such as AWS Glue and keep the newest row per key.

Oracle requirements

Log-based readers need archived redo, supplemental logging and a user allowed to mine; query jobs need only SELECT. For a self-managed, non-container database on 12c or later:

sql
-- LOG_MODE must be ARCHIVELOG
SELECT LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_ALL FROM V$DATABASE;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER TABLE hr.employees ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

CREATE USER s3_cdc IDENTIFIED BY "<password>";
GRANT CREATE SESSION, LOGMINING, SELECT_CATALOG_ROLE, EXECUTE_CATALOG_ROLE TO s3_cdc;
GRANT SELECT ON hr.employees TO s3_cdc;
GRANT SELECT ON V_$DATABASE TO s3_cdc;
GRANT SELECT ON V_$LOG TO s3_cdc;

Turning on ARCHIVELOG mode needs STARTUP MOUNT, ALTER DATABASE ARCHIVELOG and a fresh backup.

Three rules prevent most failures:

1. Keep archived logs longer than your worst outage, or the reader must reload tables.
2. Declare primary keys. S3 files have none, so later dedupe depends on Oracle's; DMS also wants a primary key or unique index before it carries LOBs through CDC.
3. Keep names to 30 characters, LogMiner's limit.

Amazon S3 requirements

• A bucket in the Region you query from, with one prefix per table, such as oracle/hr/employees/.
• Write access for the pipeline, read access for the query engine.s3:PutObject covers multipart uploads; with SSE-KMS the writer also needs kms:GenerateDataKey and kms:Decrypt. Prefer an IAM role to a long-lived access key.
• A lifecycle rule with AbortIncompleteMultipartUpload. S3 bills an unfinished upload's parts until it's completed or aborted; the rule deletes those parts and never touches finished objects.
• A catalog such as the AWS Glue Data Catalog. MSCK REPAIR TABLE finds only Hive-style key=value folders; other layouts need ALTER TABLE ADD PARTITION or partition projection.

Setting up Oracle to S3 replication

DMS parameter names are in brackets.

1. Prepare redo: ARCHIVELOG mode, minimal supplemental logging, and all-column logging per table. AWS asks for every column when the target is S3.
2. Create the reader's user and network path, and set archived-log retention (on RDS, rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours', …), then COMMIT).
3. Create the bucket, role, lifecycle rule and catalog database in one Region.
4. Set the file format: Parquet, not the default CSV (DataFormat=parquet); millisecond timestamps if Athena or AWS Glue will read the files (ParquetTimestampInMillisecond=true); a commit-time column to order CDC rows (TimestampColumnName; full-load rows get the transfer time).
5. Size LOBs. Limited LOB mode is DMS's only mode for S3; set the maximum above your largest value (default 32 KB, ceiling 100 MB).
6. Load existing rows and start CDC together, keeping every archived log from the load's starting SCN.
7. Register the files and build a current-state view (below).
8. Validate. Compare COUNT(*) with the view; update, delete and re-key a row and check each after one file interval; check a DATE with a time and a NUMBER(38).

What happens after the initial load?

• Updates are new rows; older versions stay in earlier files.
• Deletes are rows that log-based readers mark (DMS writes D in its operation column). DMS documents IncludeOpForFullLoad for CSV only, so expect full-load Parquet rows with no operation value.
• Primary-key updates must arrive as a delete plus an insert, or the old key's last row survives.
• TRUNCATE is DDL, not row deletes; a reader that emits only row changes leaves truncated rows in S3.
• New columns show up only in files written after the change. Athena matches Parquet columns by name; Spark merges file schemas only with mergeSchema enabled.
• Type changes leave files disagreeing on a column's type; AWS says to test your queries.
• DDL: LogMiner's online catalog knows only each table's current shape; redo from before a DDL needs an extracted dictionary.

A current-state view keeps the newest row per key and drops keys whose newest row is a delete:

sql
CREATE OR REPLACE VIEW hr.employees_current AS
SELECT * FROM (
  SELECT e.*,
         ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY commit_ts DESC) AS rn
  FROM hr.employees_changes e
) t
WHERE rn = 1 AND coalesce(op, 'I') <> 'D';

Oracle to Parquet data type mapping

Writers disagree most on NUMBER, DATE and LOBs:

dlt reads every NUMBER as a decimal. Spark writes legacy INT96 timestamps unless spark.sql.parquet.outputTimestampType is TIMESTAMP_MICROS or TIMESTAMP_MILLIS.

DATE, NUMBER and timestamp precision

Parquet's DATE counts days, so an Oracle DATE written to it loses its time; write TIMESTAMP. A DOUBLE keeps about 15 significant digits, so money and long IDs stored that way are rounded for good. Parquet DECIMAL has no global precision limit, and Athena's stops at 38 digits, matching NUMBER. Oracle TIMESTAMP holds nine fractional digits, but AWS says Athena and AWS Glue handle only milliseconds, and Athena engine 3 fails queries with SERIALIZATION_ERROR when data is more precise than its column.

Performance, latency and S3 cost

A CDC reader can follow redo continuously, but a change is queryable only once its file is uploaded and catalogued. Shorter intervals mean fresher data and more, smaller files: each costs a write request, and engines open every file they scan.

• DMS writes a CDC file every 60 seconds or at 32,000 KB, whichever comes first (CdcMaxBatchInterval, CdcMinFileSize). Raise both for fewer, larger files.
• Row groups are the Parquet slices engines read in parallel and skip by column statistics; tiny files defeat both.
• Snappy is the default compression in AWS Glue and Spark.
• Compaction: rewrite closed folders into fewer files, or use Apache Iceberg.
• Date folders: DMS can partition by transaction commit date (DatePartitionEnabled), but in a full load plus CDC task only ongoing changes are partitioned. Write-date folders don't prune queries on business dates.

Production considerations

• Recovery. Log readers resume from a checkpointed SCN while the archived logs exist; after that, tables are reloaded, and readers must switch to or dedupe each new copy.
• Source impact. Supplemental logging adds redo; LogMiner uses database CPU and PGA memory, more when it extracts dictionaries. Query jobs scan tables every run.
• Monitoring. Alert on V$DATABASE.CURRENT_SCN minus the reader's SCN, free space in the archive destination, and the age of the newest object per prefix.

Common problems and troubleshooting

LOB columns cut short or missing

A CLOB stops at exactly the configured size, or a LOB column is missing. Find the largest value:

sql
SELECT MAX(DBMS_LOB.GETLENGTH(notes)) FROM hr.employees;

In DMS, raise the maximum LOB size (up to 100 MB); longer values are truncated with a warning in the task log. Readers that don't carry LOBs through CDC need a query-based reader for those tables.

Archived logs gone before the reader caught up

Mining fails with ORA-01291: missing logfile when retention or RMAN removed a log the reader still needs. See what remains per thread:

sql
SELECT THREAD#, MIN(SEQUENCE#), MAX(SEQUENCE#), MIN(FIRST_TIME)
FROM V$ARCHIVED_LOG
WHERE DELETED = 'NO' AND NAME IS NOT NULL
GROUP BY THREAD#;

The cheapest fix is restoring the missing logs (RDS re-downloads them with rdsadmin.rdsadmin_archive_log_download inside backup retention). Failing that, re-read rows changed since the gap, or whole tables. Keys deleted during the gap never get a delete row, so remove them by hand, then lengthen retention.

Athena shows wrong timestamps or SERIALIZATION_ERROR

• Microsecond timestamps: in DMS, set ParquetTimestampInMillisecond=true.
• Spark's INT96 default: set spark.sql.parquet.outputTimestampType.
• Every DATE at midnight: it was written as a Parquet DATE; write TIMESTAMP.

Existing files keep their types: rewrite them or cast in a view.

Using Estuary for Oracle to S3

This route runs Estuary's Oracle CDC capture into its Amazon S3 Parquet materialization. The capture mines online and archived redo with LogMiner for Oracle 11g and later, including container databases and RAC; the materialization writes new Parquet files and never rewrites old ones. Against the concepts above:

• Load and redo: the backfill runs alongside mining, fenced by a watermarks table so no change is lost or doubled. Retention is checked at start: under 24 hours of archived logs is an error, under 7 days a warning.
• Schema changes: in smartDictionary Mode, the default, mining uses the online catalog and falls back to dictionaries extracted into redo when a DDL breaks decoding.
• File interval: new files land about every Upload Interval (default 5 minutes) once caught up, faster during a backfill. Each file's multipart upload completes at commit, so no reader sees part of one.
• Rows: one row per key per transaction. A delete is a row with _meta/op set to d; default field selection includes _meta/op and flow_published_at, Estuary's publish time. Changing a primary key produces a delete row for the old key and an insert for the new one, provided the key columns are in the supplemental log. TRUNCATE emits nothing.
• Types: decimal NUMBER and FLOAT become DOUBLE; NUMBER(p,0) becomes INT64, and a value above 9,223,372,036,854,775,807 stops the task; NUMBER keys stay STRING. TIMESTAMP WITH TIME ZONE becomes a UTC TIMESTAMP in microseconds, with no millisecond option. DATE, TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE arrive as offset-less ISO 8601 strings such as 1998-01-01T13:00:00: the time survives, and a view parses it. Fixes are in the "Choose paths and keep numbers exact" step.
• Skipped columns: CDC leaves CLOB, NCLOB, BLOB, LONG, LONG RAW, XMLTYPE, BINARY_FLOAT and BINARY_DOUBLE columns out of the schema. For CLOB, use the batch connector described below.
• Limits: a table name over 30 characters fails the capture; a longer column name is ignored. RDS container databases don't work. No hard deletes or merges: the bucket holds a change history.
• Audits: Estuary holds SOC 2 Type II certification and lists HIPAA and GDPR compliance on its security page.

Implementing Oracle to S3 with Estuary

These steps adapt Estuary's general data flow guide to this route.

1. Complete the prerequisites in Oracle requirements and Amazon S3 requirements. Estuary also needs:
   • A FLOW_WATERMARKS table the capture user can write, with tablespace quota (non-container setup; container databases use a common user).
   • s3:PutObject for an access key, or an IAM role in the bucket's account that Estuary assumes through OIDC.
   • Allowlisted Estuary IP addresses, or an SSH tunnel.
2. Create the Oracle capture. On the Sources page, click New Capture, choose Oracle Database, and fill in the endpoint properties:
   • Server Address: host:port, with :1521 added if no port is given. Network Tunnel sets up an SSH tunnel.
   • User and Password: s3_cdc from the SQL above.
   • Database: the service name, or the PDB name on a container database.
   • Advanced Options: leave Dictionary Mode at its default; set Watermarks Table if it isn't <user>.flow_watermarks.

3. Select tables. Click Next. Output Collections lists one binding per table. Remove tables you don't need; keyless ones arrive with Backfill Mode set to Without Primary Key. Raise Backfill Priority for tables you want first, and keep Automatically keep schemas up to date on. Click Save and publish; backfill and mining start together.
4. Create the S3 Parquet materialization. On the capture's details page, click Materialize, choose Amazon S3 Parquet, and fill in the endpoint properties:
   • Bucket and Region.
   • Authentication: Access Key (AWS Access Key ID, AWS Secret Access key) or AWS IAM (AWS Region, AWS Role ARN).
   • Prefix: optional, such as oracle/%Y/%m/%d. Date patterns use the UTC time a transaction was written, not a date in the data.

5. Set the upload interval and file sizes.Upload Interval offers 1s, 1m, 5m, 15m, 30m and 1h, default 5m; shorter means fresher and more files. File Size Limit is about 10 GiB when blank. Parquet Configuration closes row groups at Row Group Row Limit (1,000,000) or Row Group Byte Limit (512 MiB).

6. Choose paths and keep numbers exact. Click Next. Each binding under Source Collections has one setting, Path, which defaults from the collection name and accepts date patterns. In the spec editor, set castToString: true under the binding's fields.include (custom column types), the only field setting this connector takes, for:
   • money and other NUMBER(p,s) columns, which keep Oracle's exact digits as STRING to cast to DECIMAL(38,s) in Athena;
   • NUMBER(p,0) columns that can exceed 19 digits;
   • timestamp columns your Athena tables reject.
7. Publish. Click Save and publish. Publishing doesn't contact S3, so a wrong bucket, region or credential appears as a task error once it runs. Add the multipart upload cleanup rule, aborting after 1 day or more.
8. Verify that data is landing.
   • On the capture details page, Connector Status shows backfill progress and the Shard Information card reads PRIMARY; open a collection from the Collections list to see its Data Preview.
   • The materialization details page charts Data Read.
   • In S3, list <Prefix>/<Path>/v0000000000/, whose file names are zero-padded, increasing numbers. Query that folder and run the checks from Setting up Oracle to S3 replication, ordering by flow_published_at and dropping keys whose newest _meta/op is d.

How Estuary handles backfills, version folders and a second destination

The capture writes each Oracle table to a collection, a durable log of JSON documents in cloud storage. The S3 files come from the collection, so rebuilding them doesn't read Oracle again.

Each backfill gets its own version folder

Files go to <Prefix>/<Path>/v<10-digit backfill counter>/<20-digit file number>.parquet. Backfilling a binding moves it to the next v… folder, numbered from 0 again; the connector never deletes, so the old folder stays. Point readers at the newest folder, or they count rows twice.

On trial storage, collections hold roughly 20 days of history; with your own bucket, nothing ages out.

Change history and current-state tables from one capture

For a current-state table beside the history, add Estuary's Apache Iceberg materialization on the same collections; it merges updates with Spark jobs on your EMR Serverless application and a REST catalog such as AWS Glue. Oracle still serves one LogMiner read.

CDC and polling from one Oracle database

Views, LOB-heavy tables and databases without LogMiner can use Estuary's Oracle Database (Batch) connector. It polls every 5 minutes by default, by ORA_ROWSCN for tables, reads CLOB and RAW as strings, and misses hard deletes.

When another approach may make more sense

• AWS-only team, Oracle 19c or earlier: DMS does full load and CDC in one service; you run the replication instance and accept LOB truncation above 100 MB.
• Autonomous Database, simple tables:DBMS_CLOUD.EXPORT_DATA needs no extra service, but each run is a full query that never sees deletes. Outside Autonomous, Oracle AI Database 26ai exports only CSV, Data Pump, JSON or XML through it, after you install DBMS_CLOUD.
• GoldenGate already licensed: GoldenGate for Distributed Applications and Analytics turns trail files into Parquet in S3 with its File Writer, Parquet and S3 handlers, under a licence separate from GoldenGate for Oracle.
• Heavy transforms before landing: Spark or AWS Glue split a JDBC read with partitionColumn, lowerBound, upperBound and numPartitions; the bounds set the stride and don't filter rows. Glue bookmarks default to a gapless rising or falling primary key. Neither sees deletes.

Related resources

• Estuary: Oracle CDC capture, Oracle batch capture, Amazon S3 Parquet materialization, backfilling data, Oracle connector, S3 Parquet destination
• AWS: DMS Oracle source, DMS S3 target, DMS LOB support, Athena data types, aborting incomplete multipart uploads
• Oracle: LogMiner, export data as Parquet, Oracle to Parquet type mapping, GoldenGate Parquet ingestion into S3

To try the route, see the Oracle to Amazon S3 Parquet integration or create an Oracle capture in the Estuary dashboard. Pricing: estuary.dev/pricing.