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

An Oracle-to-PostgreSQL migration has two different jobs: convert the database objects and move the data. AWS DMS Schema Conversion can use generative AI to help with certain Oracle SQL conversion problems. A separate AWS DMS replication task 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 and the DMS source and target tables. Record the DMS version and selected Region. AWS lists supported generative AI Regions on its conversion page 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 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 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 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. 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 applies to your converted schema.

AWS documents a concrete DMS expression example: 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. 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 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 covers row loading and ongoing changes, while the current schema walkthrough's next steps 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 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 handles schema work; the older full migration walkthrough 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 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. 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 describes the supported code-conversion path and its separation from data migration. The generative AI scope page 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 and its next steps cover setup and refer data movement to DMS. The older Oracle-to-PostgreSQL walkthrough 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 establish the sample Test.ITEM transformation. The validation guide, cutover steps and rollback steps give the relevant task states and order. None reports a BIG CHANGE migration or guarantees the behavior of a reader's application.