Markdown edition

# How to verify an AI-assisted Oracle-to-PostgreSQL migration

> Use AWS's scoped AI schema conversion with separate DMS row migration, matched-checkpoint validation and a tested reverse-CDC cutover plan. A documentation-based guide for authorized DBA teams.

By BIG CHANGE Editorial

Published: 2026-10-21T19:03:43.963Z
Updated: 2026-10-21T19:03:43.963Z
Canonical: https://bigchange.ai/blog/oracle-postgresql-ai-schema-data-migration-guide

![Conceptual charcoal illustration of generic server cabinets and a patch cabinet in a data-centre aisle, with an orange cable held in a floor raceway and plugged into the patch panel.](https://bigchange.ai/api/media/file/oracle-postgresql-reverse-path-hero-v1.png)
AI-generated conceptual illustration by BIG CHANGE.

An Oracle-to-PostgreSQL migration has two different jobs: convert the database objects and move the data. [AWS DMS Schema Conversion](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_SchemaConversion.html) can use generative AI to help with certain Oracle SQL conversion problems. A separate [AWS DMS replication task](https://docs.aws.amazon.com/dms/latest/sbs/chap-rdsoracle2postgresql.steps.createmigrationtask.html) loads rows and follows later changes. This guide brings those documented steps together for an authorized DBA and application team moving its own Oracle database to Amazon RDS for PostgreSQL. It is a documentation-based procedure, checked October 21, 2026; BIG CHANGE has not run a migration or tested a reader's environment.

## The big change

- **What changed:** DMS Schema Conversion offers generative AI for specified SQL action items on the Oracle-to-RDS PostgreSQL path. The converted code still needs review and testing.
- **Why it matters:** A team can pair that code review with a separately configured full load and ongoing replication, then compare the resulting data against the source at recorded checkpoints.
- **What to watch:** The route joins current Schema Conversion documentation to an older AWS migration walkthrough that uses AWS SCT for schema work. Rehearse the combined configuration. After PostgreSQL starts accepting writes, rollback depends on a working reverse change stream, not a snapshot alone.

## 1. Check the route and define the evidence

Confirm the actual Oracle and RDS for PostgreSQL engine versions against both [Schema Conversion support](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_SchemaConversion.html) and the [DMS source](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Introduction.Sources.html) and [target](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Introduction.Targets.html) tables. Record the DMS version and selected Region. AWS lists supported generative AI Regions on its [conversion page](https://docs.aws.amazon.com/dms/latest/userguide/schema-conversion-convert.databaseobjects.html) and says the feature uses cross-Region inference. Get the data owner's approval for that processing before enabling it; do not place confidential schema, SQL or credentials in a shared article or review packet.

Inventory the source tables and primary or unique keys, code objects, sequences, triggers, jobs, LOBs, data types, application queries and write paths. Record which objects must work on PostgreSQL and who accepts each exception. A table without a usable key needs a deliberate plan because [DMS validation](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Validating.html) cannot validate it in the ordinary way. Define a recovery point, permitted outage, rollback decision deadline and the people who can approve the switch.

The schema project needs an instance profile, source and target data providers, Secrets Manager credentials, S3 and the required IAM and database privileges. AWS's [Oracle-to-PostgreSQL Schema Conversion walkthrough](https://docs.aws.amazon.com/dms/latest/sbs/schema-conversion-oracle-postgresql.html) specifies network and target setup, including subnets in two Availability Zones and RDS `rds_superuser` privileges for the conversion account. DMS replication needs its own reachable endpoints, Oracle change-log access and target write privileges. Test those paths separately; a successful schema connection says nothing about CDC capture. Store credentials in Secrets Manager and keep them out of exported evidence.

Take a source-native backup and prove it can be restored under the Oracle operator's procedure. Take and [restore-test an RDS snapshot](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_CreateSnapshot.html) for the target. Record restore times and the agreed recovery point. Estimate RDS, DMS, storage, Secrets Manager, logging and transfer charges for the chosen Region and expected run time using [current AWS pricing](https://aws.amazon.com/dms/pricing/). Running resources may keep accruing charges during a long validation or rollback window.

**Expected result:** a versioned inventory, compatibility and access checks, backup/restore evidence, named approvers and a written acceptance threshold. Stop here if a required engine version, object, CDC permission or data policy cannot be supported.

## 2. Approve the schema and column map

Map every included source schema, table, key and column to its target. Record type and precision choices, null and time-zone behavior, LOB handling, sequences, defaults and ownership. Specify the query or application operation that will demonstrate each important mapping. The mapping must match the schema actually applied to PostgreSQL, including any case changes. Do not assume the lowercase naming rule in [AWS's older Oracle migration walkthrough](https://docs.aws.amazon.com/dms/latest/sbs/chap-rdsoracle2postgresql.html) applies to your converted schema.

AWS documents a concrete [DMS expression example](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions.html): on sample source `Test.ITEM`, it joins `FIRST_NAME`, an underscore and `LAST_NAME` into target `FULL_NAME`, declared as a string of length 50. These are AWS sample identifiers, not BIG CHANGE data or a proposed mapping for your tables. If your approved source has an equivalent requirement, test the actual expression against permitted rows, including nulls and values near the length limit. Decide how a target-only derived column would map back to Oracle; it does not become reversible merely because forward DMS created it. Keep the expression and expected results in the private mapping record.

**Expected result:** a signed source-to-target map and transformation tests based on your own authorized data. A value that truncates, changes meaning or lacks an agreed reverse interpretation is an exception to resolve before cutover.

## 3. Convert eligible SQL, then test the target schema

Create a DMS Schema Conversion migration project using the [current AWS walkthrough](https://docs.aws.amazon.com/dms/latest/sbs/schema-conversion-oracle-postgresql.html). Run its assessment and save the action-item export. Once the Region and processing permission are confirmed, use the conversion dialog's **Convert schema with Generative AI** option. AWS lists Oracle `SELECT` action item `5578` among eligible SQL elements. Its [scope and limitations](https://docs.aws.amazon.com/dms/latest/userguide/schema-conversion-convert.databaseobjects.html) leave other elements to rules or manual work; generated SQL can vary and requires human review.

Compare converted code with its Oracle source and the action-item list. Test the SQL and application queries on an isolated PostgreSQL target. Assign unresolved types, triggers, indexes, constraints, dynamic SQL, procedures, sequences and jobs to named owners. Apply only the approved schema to the migration target. Keep Oracle authoritative and intact.

**Expected result:** a reviewed schema, test results and a disposition for every material action item. AI conversion is a code aid; it neither moves rows nor proves that the target data matches.

## 4. Load rows and keep Oracle changes flowing

Configure separate Oracle and PostgreSQL DMS endpoints, replication capacity and network access. Create an AWS DMS task with migration type **full load and CDC** for the approved tables. Make its selection and transformation rules match the applied target schema. Review target preparation mode, LOB mode and length, log retention, error handling and task logging before starting. If using a derived-column rule like AWS's `FULL_NAME` illustration, use your tested table and column map. The [older AWS task walkthrough](https://docs.aws.amazon.com/dms/latest/sbs/chap-rdsoracle2postgresql.steps.createmigrationtask.html) covers row loading and ongoing changes, while the [current schema walkthrough's next steps](https://docs.aws.amazon.com/dms/latest/sbs/schema-conversion-oracle-postgresql-next-steps.html) directs readers to DMS for data movement.

Observe each table's load result, task errors and CDC latency. The DMS data task does not migrate all the code objects the application may need, including secondary indexes, sequences, defaults, stored procedures, triggers, synonyms and views; account for them in the reviewed schema and application tests. A task marked running is evidence of a running process, not of complete or correct data.

**Expected result:** task configuration and IDs, endpoint test results, table-load status, error logs and a live forward change stream from Oracle to PostgreSQL.

## 5. Reconcile tables at matched checkpoints

After full load, record per-table source and target counts, the compared source position or other agreed consistent checkpoint, expected filter differences and key coverage. Sample approved real rows by key, including nulls, boundary lengths, dates and LOBs. Compare unchanged source columns with their mapped target columns, and compare derived columns with the approved expression. Counts observed while Oracle keeps accepting writes can disagree simply because the two queries saw different moments; do not label such counts a pass or a failure without aligning the checkpoint.

Enable [DMS data validation](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Validating.html) and inspect **Table statistics** for every included table. Record validation state and pending, suspended and failed counts. AWS reports states including Validated, pending records, mismatched records, suspended records, no primary key and error. For target-side failure details, inspect the table created for your DMS version and target schema: DMS 3.6.1 and later creates `awsdms_validation_failures_v2` on PostgreSQL targets; `awsdms_validation_failures_v1` applies where that earlier control table is used. Confirm its schema in your configuration rather than assuming `awsdms_control`. Do not drop or truncate v2; AWS says that can cause errors for tasks with validation and resync enabled. Check these details alongside load and CDC errors. DMS validation has key, type, collation, LOB and target-write limits; a Validated state does not certify application code or objects outside its comparison.

For each exception, record the affected table and key or object, cause, proposed repair, owner, rerun evidence and final disposition. An unexplained row difference, truncated LOB, suspended validation or unsupported type blocks the final go/no-go. If a transformation intentionally changes a value, document the expected difference and use the appropriate reviewed validation treatment; do not hide a mismatch by turning validation off.

**Expected result:** a per-table reconciliation register with matched positions, counts, key and row comparisons, transformation checks, DMS states and closed exceptions.

## 6. Rehearse the switch and the way back

On a restored or isolated environment, exercise the actual application operations against PostgreSQL: reads, writes, constraints, sequences, jobs and downstream consumers. Test the connection switch, target behavior and backup restores. Rehearse a PostgreSQL-to-Oracle **CDC-only** task with its own endpoint permissions, mapping and Oracle trigger handling. Confirm what its change stream captures and which target-only fields or external effects, such as actions outside the databases, it cannot reverse. Set a bounded rollback window and a decision owner.

This is a synthesis of two AWS paths. The [current DMS Schema Conversion walkthrough](https://docs.aws.amazon.com/dms/latest/sbs/schema-conversion-oracle-postgresql.html) handles schema work; the [older full migration walkthrough](https://docs.aws.amazon.com/dms/latest/sbs/chap-rdsoracle2postgresql.html) uses AWS SCT for that phase. AWS has not certified this exact combined run for your versions, application and data. Rehearsal is where the team proves its own configuration. If reverse replay cannot be demonstrated, redesign the rollback contract or keep PostgreSQL write-free during validation.

**Expected result:** application acceptance, tested restore, reverse-task rehearsal and a signed plan for what a rollback can and cannot recover.

## 7. Cut over only after the final checkpoint and live reverse capture

At the approved maintenance window, stop Oracle application writes and relevant jobs. Keep Oracle authoritative while forward CDC applies the last changes. Record the final source checkpoint and verify DMS latency, matched per-table counts, keys, transformed fields and validation states at that point. Resolve blocking exceptions. The DBA and application owner record an explicit go/no-go decision.

On approval, stop the forward task. Before **any PostgreSQL application writes**, create or confirm the production PostgreSQL-to-Oracle task as **CDC-only**, with target table preparation **Do nothing**, reviewed reverse mappings and Oracle trigger handling. Start that actual task at the agreed pre-write checkpoint. Verify its endpoints and running state. Where the approved change-control plan permits a controlled DBA probe on an authorized mapped row, confirm that the reverse stream captures and applies it to Oracle, then reconcile the probe and checkpoint before releasing the application. Otherwise, obtain another defensible live capture check; task status alone does not prove the change path. A configured task left stopped, or a successful rehearsal alone, does not protect new PostgreSQL writes. [AWS's cutover steps](https://docs.aws.amazon.com/dms/latest/sbs/chap-rdsoracle2postgresql.steps.cutover.html) start the reverse task before starting application services on PostgreSQL. Halt the switch if the live task, checkpoint or mapping is unhealthy.

Only then point applications to PostgreSQL and enable writes. Monitor application errors, target health, reverse CDC latency and validation evidence. Preserve Oracle, logs, backups and task configuration during the rollback window. Do not promise zero downtime or zero loss: those outcomes depend on the tested environment and the state of both change streams.

**Expected result:** a signed cutover record containing the final forward checkpoint, validation and exception decisions, stopped forward-task state, live reverse-task health and time of the first PostgreSQL write.

## 8. Roll back conditionally, or preserve evidence for recovery

If a serious fault occurs within the tested rollback window, stop PostgreSQL application writes. Let the reverse task drain to Oracle, reconcile its final checkpoint and critical records, then stop it and restore Oracle application connections. This follows [AWS's rollback order](https://docs.aws.amazon.com/dms/latest/sbs/chap-oracle2postgresql.rollback.html). Check external effects separately because database CDC cannot undo work already done in another system.

If reverse capture failed after PostgreSQL accepted writes, stop writes and preserve both databases, task logs and checkpoint evidence. Escalate to the incident owner to choose a reconciled recovery or a restore to the declared recovery point. A pre-cutover snapshot cannot include those later PostgreSQL writes; blindly replaying them can worsen the inconsistency. Close the rollback window only when the application owner accepts the PostgreSQL behavior and final data evidence.

## Sources & further reading

The [AWS DMS Schema Conversion overview](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_SchemaConversion.html) describes the supported code-conversion path and its separation from data migration. The [generative AI scope page](https://docs.aws.amazon.com/dms/latest/userguide/schema-conversion-convert.databaseobjects.html) lists eligible SQL action items, Regions, cross-Region inference and exclusions. Check both against the current service state before using the option.

The [Schema Conversion walkthrough](https://docs.aws.amazon.com/dms/latest/sbs/schema-conversion-oracle-postgresql.html) and its [next steps](https://docs.aws.amazon.com/dms/latest/sbs/schema-conversion-oracle-postgresql-next-steps.html) cover setup and refer data movement to DMS. The [older Oracle-to-PostgreSQL walkthrough](https://docs.aws.amazon.com/dms/latest/sbs/chap-rdsoracle2postgresql.html) describes migration, cutover and rollback with AWS SCT as its schema tool. Together they support this documented route, subject to a reader-run rehearsal.

AWS's [expression examples](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions.html) establish the sample `Test.ITEM` transformation. The [validation guide](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Validating.html), [cutover steps](https://docs.aws.amazon.com/dms/latest/sbs/chap-rdsoracle2postgresql.steps.cutover.html) and [rollback steps](https://docs.aws.amazon.com/dms/latest/sbs/chap-oracle2postgresql.rollback.html) give the relevant task states and order. None reports a BIG CHANGE migration or guarantees the behavior of a reader's application.

## Sources

- [AWS DMS Schema Conversion overview](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_SchemaConversion.html) — Current AWS user guide checked October 21, 2026. Establishes the Oracle-to-RDS PostgreSQL schema route and that Schema Conversion assesses, converts and applies or exports database code; moving table data is a separate DMS task. Support and configuration must be rechecked for the reader's versions.
- [AWS DMS Schema Conversion with generative AI](https://docs.aws.amazon.com/dms/latest/userguide/schema-conversion-convert.databaseobjects.html) — Current AWS scope page checked October 21, 2026. Lists eligible action items including Oracle SELECT 5578, supported Regions and paths, cross-Region inference, exclusions and human-review caveats. AWS describes feature behavior; BIG CHANGE did not run it.
- [AWS Oracle to RDS PostgreSQL Schema Conversion walkthrough](https://docs.aws.amazon.com/dms/latest/sbs/schema-conversion-oracle-postgresql.html) — Current AWS schema tutorial checked October 21, 2026. Supports project prerequisites, assessment, conversion and schema application. Its example is not evidence that a complete reader-specific migration worked.
- [AWS Schema Conversion next steps](https://docs.aws.amazon.com/dms/latest/sbs/schema-conversion-oracle-postgresql-next-steps.html) — AWS tutorial continuation checked October 21, 2026. Explicitly directs readers to DMS for source data movement; it does not establish a completed full-load or CDC migration.
- [AWS DMS source engines](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Introduction.Sources.html) — AWS source support table checked October 21, 2026. Reader must verify its exact Oracle engine version and CDC prerequisites; supported-version tables can change.
- [AWS DMS target engines](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Introduction.Targets.html) — AWS target support table checked October 21, 2026. Reader must verify its exact RDS PostgreSQL engine and DMS version, separately from Schema Conversion support.
- [AWS Oracle data provider](https://docs.aws.amazon.com/dms/latest/userguide/data-providers-oracle.html) — AWS Schema Conversion data-provider guidance in approved source packet. Supports Oracle catalog/credential setup; it is separate from replication log access and endpoint permissions.
- [AWS PostgreSQL data provider](https://docs.aws.amazon.com/dms/latest/userguide/data-providers-postgresql.html) — AWS target data-provider guidance in approved source packet. Covers target connection and privileges, including RDS-specific setup. Account-specific permissions need verification.
- [AWS DMS Schema Conversion network profiles](https://docs.aws.amazon.com/dms/latest/userguide/instance-profiles-network.html) — AWS network guidance in approved source packet. Supports two subnets in separate Availability Zones and reachability requirements; a schema project connection does not verify replication CDC reachability.
- [AWS Oracle to PostgreSQL migration walkthrough](https://docs.aws.amazon.com/dms/latest/sbs/chap-rdsoracle2postgresql.html) — Older AWS full migration walkthrough checked October 21, 2026. Uses AWS SCT for schema conversion, then AWS DMS for data and ongoing changes. This guide substitutes current DMS Schema Conversion for the schema phase as an editorial documentary synthesis, not a vendor-certified combined test.
- [AWS DMS migration task steps](https://docs.aws.amazon.com/dms/latest/sbs/chap-rdsoracle2postgresql.steps.createmigrationtask.html) — AWS older walkthrough's task step checked October 21, 2026. Supports full load and CDC and cautions that a data task does not migrate certain schema/code objects. Target mappings, LOB handling and task state require reader testing.
- [AWS DMS transformation expressions](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions.html) — Current AWS expression examples checked October 21, 2026. Includes an illustrative Test.ITEM add-column rule joining FIRST_NAME and LAST_NAME with an underscore into FULL_NAME, string length 50. These are sample identifiers and unobserved values, not reader data or proof of reverse mapping.
- [AWS DMS data validation](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Validating.html) — Current AWS validation guide checked October 21, 2026. Defines Table statistics, pending/mismatched/suspended/no-key/error states and target validation-failure detail, plus limitations. For PostgreSQL targets, DMS 3.6.1 and later creates awsdms_validation_failures_v2; v1 applies where used by the recorded version, and the actual target schema must be checked. AWS warns not to drop or truncate v2 because validation/resync tasks can fail. Matched-checkpoint counts and application acceptance are additional editorial go/no-go requirements, not AWS results observed by BIG CHANGE.
- [AWS Oracle to PostgreSQL cutover](https://docs.aws.amazon.com/dms/latest/sbs/chap-rdsoracle2postgresql.steps.cutover.html) — AWS walkthrough checked October 21, 2026. Orders Oracle activity stop, final forward drain, forward-task stop, reverse PostgreSQL-to-Oracle CDC-only task with Do nothing target preparation and trigger handling, then target application start. This guide adds a human verification gate for production reverse capture before any target write.
- [AWS Oracle to PostgreSQL rollback](https://docs.aws.amazon.com/dms/latest/sbs/chap-oracle2postgresql.rollback.html) — AWS rollback walkthrough checked October 21, 2026. Orders target application stop, reverse CDC drain, reverse-task stop and Oracle application restart. It assumes rollback was prepared; it cannot guarantee recovery if reverse capture or mappings fail.
- [AWS DMS pricing](https://aws.amazon.com/dms/pricing/) — Current AWS pricing entry checked October 21, 2026. Resource and usage charges depend on configuration, Region and duration; the article gives charge categories without an invented price or savings estimate.
- [Amazon RDS DB snapshots](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_CreateSnapshot.html) — AWS RDS snapshot documentation checked October 21, 2026. Supports a target snapshot/restore rehearsal. A pre-cutover snapshot cannot contain writes later made on PostgreSQL and is not a substitute for source-native backup or working reverse CDC.