Tutorial: Migrate Oracle data to Azure Database for PostgreSQL flexible server

Azure Database Migration Service (DMS) is a managed migration service that moves data between databases. DMS provisions and manages the migration infrastructure, so you don't need to deploy migration VMs or install migration software.

DMS supports offline data migration from Oracle to Azure Database for PostgreSQL Flexible Server through the Azure portal. In an offline migration, you stop applications and scheduled jobs that modify the source data while keeping the Oracle database and listener running. DMS copies a point-in-time snapshot to the target and lets you monitor progress.

Applications remain unavailable until copying, validation, and the switch to Azure Database for PostgreSQL Flexible Server are complete. Changes made to the source after the snapshot aren't synchronized. Rehearse the migration to plan your production migration window, including time for data copying, validation, application switching, and contingency.

In this tutorial, you learn how to:

  • Prepare the source database, destination database, and connectivity.
  • Create an Azure DMS resource.
  • Connect DMS to your Oracle source and Flexible Server.
  • Choose the Oracle schemas to copy and specify their destination schemas.
  • Kick off an offline migration.
  • Monitor the migration, validate the results, and switch your application to Flexible Server.

For an explanation of the architecture and migration scope, see the data migration overview

Prerequisites

Before you begin, complete the following preparation.

Supported configurations and regions

Use the following table to check the migration scenario before preparing your databases.

Component Supported configuration
Source database Oracle Database 19c and later.
Target database Supported versions of Azure Database for PostgreSQL Flexible Server. Create the destination database and complete schema conversion using the PostgreSQL extension for Visual Studio Code to deploy the destination schema objects before migrating data.
Migration mode Offline, snapshot-based data migration. Changes after the snapshot aren't synchronized.
Selection scope Oracle schemas. Individual-table selection isn't available.
DMS regions East US, Central US, East US 2, West US 2, West US 3, Indonesia Central, Central India, Japan East, Korea Central, South India, Australia East, Canada Central, France Central, Sweden Central, West Europe, Germany West Central, UK South, Switzerland North, and West Central US.

Create DMS in the same region as your source and target when possible. If they're in different regions, or DMS isn't available in their region, choose a supported region that's geographically close to the database endpoints to minimize network latency and avoid unnecessarily long migration times.

Ensure that you have Azure permissions to create or use DMS and access the required target and networking resources.

Prepare your Oracle server

  • Keep the Oracle database running and configure the listener to accept connections on the port Azure DMS uses.

  • Identify the Oracle schemas to migrate.

  • Connect by using an Oracle user account with access to the schemas you want to migrate and the permissions required for snapshot reads. If the account owns the schema you want to migrate, it can read its own tables. To migrate schemas owned by other users, grant the following permissions.

    Oracle privilege Purpose
    CREATE SESSION Connect to Oracle.
    SELECT ANY TABLE Read source table data.
    SELECT ANY SEQUENCE Read sequence metadata or values used by the migration implementation.
    SELECT ANY DICTIONARY Read required database metadata.
    FLASHBACK ANY TABLE Read table data at the migration snapshot.
    EXECUTE ON SYS.DBMS_FLASHBACK Access the flashback functionality used by the migration implementation.

    These privileges are broad. Have your DBA review their scope and remove temporary access after migration when appropriate.

Prepare your Flexible Server

  • Provision an Azure Database for PostgreSQL Flexible Server in a supported configuration.
  • Create the destination database.
  • Use the Oracle schema conversion tool to convert and deploy the required schema objects.
  • Ensure that destination table names match the table names in the selected Oracle schemas. DMS matches table names case-insensitively.
  • Verify that destination columns, data types, and schema mappings are compatible with the migration.
  • Use the password-based admin user you created when you provisioned the Flexible Server cluster, not the Microsoft Entra admin user. To use a different password-based user for migration, ask the administrator of the server to grant the azure_pg_admin role to the user by running the following command.

Run the following command on the target as the administrator, replacing migration_user with your migration user's name:

GRANT azure_pg_admin TO migration_user;

Configure connectivity

DMS can connect to your Oracle source and Azure Database for PostgreSQL flexible server target through public or private networks. Plan the network path to each endpoint and configure name resolution, routing, firewall rules, and connection security before starting the migration.

Choose public or private connectivity

Connectivity How DMS reaches your databases What you configure
Public DMS connects to publicly accessible database endpoints. Obtain the DMS outbound IP addresses from the Network tab in the Azure portal migration wizard and allow list them in the database firewalls and any applicable network firewalls for the required database ports.
Private DMS migration resources run inside a virtual network and attempt to establish peering with the configured source and target virtual networks. Select the source and target virtual networks and private DNS settings in the migration wizard. Provide private access to the databases through private endpoints where supported, VM private IP addresses, or a VPN or ExpressRoute connection for Oracle sources outside Azure. Ensure the required peering permissions, DNS resolution, routes, and firewall access are in place.

Configure database ports and Oracle transport security

Endpoint Connection information
Oracle source Hostname, TCP or TCPS listener port, Oracle service name, and migration credentials.
Azure Database for PostgreSQL Flexible Server target Hostname, port, database name, and migration credentials.

DMS supports both TCP and TCPS connections to Oracle:

  • TCPS is recommended. It uses TLS to encrypt the Oracle connection. Configure an Oracle TCPS listener and the certificate/trust settings required by the migration connection.
  • TCP can be used if your Oracle server isn't configured for TCPS. Selecting TCP doesn't itself provide TLS encryption. Review your organization's security requirements before using it.

Migration process

This section walks through the offline data migration workflow. You create or select a DMS resource, configure connectivity, and connect to the Oracle source and PostgreSQL target. You then select the Oracle schemas to migrate and map them to schemas in the target database. After manually stopping writes to the source, you start the migration, monitor its progress, and review the schema- and table-level copy results.

Create or select Azure Database Migration Service

If you already have a compatible DMS resource, open it and continue to Configure the migration. Otherwise, create a service using the following steps.

Select the source and target types

  1. Sign in to the Azure portal.
  2. Search for Azure Database Migration Service.
  3. Select Create to open the migration scenario selection page.
  4. Select Oracle for Source server type and Azure Database for PostgreSQL for Target server type.
  5. Select Database Migration Service as the migration service.

Screenshot of the migration scenario page with Oracle selected as the source, Azure Database for PostgreSQL selected as the target, and Azure Database for PostgreSQL Flexible Server listed among the supported destinations.

Enter the service details

  1. On the Basics tab, select your subscription and resource group.
  2. Select a supported Location, following the regional placement guidance.
  3. Enter a Migration service name.
  4. Add tags if required, and select Review + create.

The following example uses Demo subscription, Demo-resource-group, East US, and the service name eastus-dms.

Screenshot of the service Basics tab showing Demo subscription, Demo-resource-group, East US, and eastus-dms as the migration service name.

Review and create the service

  1. On Review + create, verify the subscription, resource group, migration service name, and region.
  2. Review the details and select Create.
  3. Wait for provisioning to complete, and then open the DMS resource.

Screenshot of the service Review + create tab showing Demo subscription, Demo-resource-group, eastus-dms, East US, and the Create button.

Configure the migration

On the DMS resource, select Get Started and select Start Migrations.

1. Select the migration scenario

  1. Select Oracle as the source type.
  2. Select Azure Database for PostgreSQL Flexible Server as the Target server type.
  3. Select Offline as the migration mode.
  4. Review the migration prerequisites and select Continue.

Screenshot of the migration scenario with Oracle selected, On-premises server as the source server type, Azure Database for PostgreSQL Flexible Server as the target server type, and Offline migration mode.

2. Configure network access

On the Network tab, choose how the migration runtime reaches your source and target. Complete the public or private access configuration.

Public access
  1. Select Public access - connect using allowed IP addresses.
  2. Review the Azure DMS service IP addresses shown for your configuration. Use Copy all to copy them.
  3. Allow list the displayed addresses in the source and target firewalls and any applicable network access rules.
  4. Select Set up runtime after preparing access.

The IP addresses in the following screenshot are illustrative replacements. Don't use them in your firewall rules. Use the actual addresses displayed in your migration wizard.

Screenshot of public network access showing the DMS service IP address list, Copy all action, and Set up runtime button. The displayed addresses are illustrative.

Private access
  1. Select Private access - private connection using virtual network.
  2. Under Source network, select the virtual network subscription, region, and virtual network.
  3. Select the DNS zone subscription and private DNS zone linked to the source virtual network, as required for hostname resolution.
  4. Under Target network, select Use source network settings if the same settings apply. Otherwise, configure the target virtual network and private DNS settings separately.
  5. Confirm that the selected networks provide the routes, DNS resolution, and database-port access described in Configure connectivity.
  6. Select Set up runtime.

Screenshot of private network access with separate source and target virtual network and private DNS settings, plus the option to use the source network settings for the target.

3. Wait for the migration runtime

The Runtime tab shows progress while DMS prepares the compute environment that connects to your databases and copies data. This process can take several minutes.

Keep the browser tab open while the runtime is being set up. Review the next steps and continue to the source connection step when setup finishes.

Screenshot of the Runtime tab showing Preparing runtime, the instruction not to close the browser tab, and the offline Oracle-to-Flexible Server migration scenario.

4. Connect to Oracle

  1. Enter the source hostname, listener port, and service name.
  2. Enter the Oracle migration account credentials.
  3. Configure the supported connection-security settings.
  4. Select Test connection and resolve any reported errors.
  5. When the source connection succeeds, select Next.

Use the Oracle service name or SID that your connection expects in SID or Service Name.

The following example connects to oracle-host.eastus.cloudapp.azure.com on port 1521, using service ORAPDB1 and account HR.

Screenshot of the Oracle Source tab showing the hostname, port, SID or Service Name, username, masked password, and a successful source connection message.

5. Connect to Azure Database for PostgreSQL Flexible Server

  1. On the Target tab, select the subscription, resource group, and location of your target.
  2. Select the Server name and verify the FQDN name.
  3. Enter the target migration account in Admin username and enter its password. Use the details mentioned in Prepare your flexible server.
  4. Select Test connection and resolve any reported errors.
  5. When the target connection succeeds, select Next.

Screenshot of the Target tab showing Demo subscription, Demo rg, East US, the target server and FQDN, masked credentials, and a successful target connection message.

6. Select schemas and review mappings

  1. On the Schemas tab, select the Oracle schemas to migrate.
  2. For each selected schema, choose the Target database and Target database schema.
  3. Verify that the corresponding destination objects exist and are ready for data loading.
  4. Review the mappings and select Next.

Selection is at schema level, not individual-table level. Review all tables within each selected schema before proceeding.

In the following example, the selected Oracle schema HR maps to the public schema in the target database hrjavawebapp. The SAMPLE_SCHEMA schema isn't selected.

Screenshot of schema selection showing Oracle schema HR selected and mapped to database hrjavawebapp and schema public, with SAMPLE_SCHEMA unselected.

Start the offline migration

  1. Stop application writes and pause scheduled jobs, integrations, and other writers that modify the selected source data.
  2. Confirm with the application owner that writes have stopped.
  3. On Review, verify the source, target, selected schema count, connection status, migration mode, and DMS resource. Recheck the schema mappings in the preceding steps.
  4. Select Migrate.
  5. Record the migration identifier for monitoring and support.

Screenshot of the Review tab showing Offline mode, Oracle source connection details, Azure Database for PostgreSQL Server target details, the DMS resource, and the Migrate button.

Monitor the migration

Open the migration details and monitor progress until the migration reaches a terminal state.

Review overall progress

The Essentials section shows connection information, migration mode, timestamps, duration, and overall status. Below it, Instance validation shows validation progress, and Schema migration lists the selected schema mappings and their validation and data migration status.

Screenshot of migration monitoring showing an overall In progress status, completed instance validation, and schema HR with data migration In progress.

Outcome What to do next
In progress Keep source writes stopped and monitor for errors and resource pressure.
Succeeded Review the object-level results available in the service, then validate data and the application.
Failed Review the reported error and affected scope. Resolve the underlying cause before starting a new migration. If the cause is unclear, submit a support request for assistance.
Canceled Confirm that processing has stopped. Assess any data already copied before deciding whether to rerun.

The current Oracle workflow doesn't provide a resume capability. Treat a new migration as a fresh operation, not a continuation from the previous migration's checkpoints.

If you need to cancel, use the migration's cancellation action and wait for confirmation before starting another operation against the same target.

Inspect validation results

Select the schema entry to open its details pane. On the Migration tab, expand Validations to review checks for migration prerequisites and compatibility. If a check fails, the migration enters the Failed state and data copying doesn't begin. Warnings don't block the migration, but review them even if the migration succeeds.

Screenshot of the schema migration details pane with expanded validation checks, including successful checks and a statistics-related warning.

Review schema copy results

In the schema's Migration details, review the Data Migration section for rows copied, data copied, and table completion counts.

The following example shows a successful copy of 215 rows across 7 tables, with no failed tables.

Screenshot of a successful schema migration showing 215 rows copied, 13 KB copied, and all seven tables completed successfully.

Inspect individual table results

Select the Table details tab to view the copied tables and their status, row counts, and copied data sizes. Expand a table to inspect its copy summary.

Screenshot of Table details showing seven copied tables and a table name expanded with a successful status, 25 rows copied, and 348 B copied.

Validate and switch applications

Keep source writes stopped while completing validation.

Validation area Checks
Migration scope Account for every expected table, including empty tables and any failures or exclusions.
Data completeness Compare row counts for the selected tables against the quiesced source. Investigate every unexplained difference.
Data correctness Check representative rows and business aggregates, including nulls, numeric precision, timestamps, character data, and large objects where supported.
Schema readiness Verify required constraints, indexes, defaults, and other converted objects.
Generated values Verify sequence and identity state so new application inserts don't conflict with migrated keys. Don't assume data copying adjusts these values.
Application Test authentication, permissions, critical queries, transactions, integrations, and background jobs.
Operations Verify target monitoring, backups, alerts, and the application's rollback plan.

After the application owner approves the results:

  1. Update application configuration and secrets to use the Azure Database for PostgreSQL Flexible Server endpoint and database.
  2. Start the application against Azure Database for PostgreSQL Flexible Server and enable writes only on the intended system.
  3. Re-enable scheduled jobs and integrations against the target as appropriate.
  4. Monitor errors, query latency, connection usage, and target resource consumption.
  5. Retain the source according to your recovery and retention plan.

Important

Once applications write to Azure Database for PostgreSQL Flexible Server, the Oracle source no longer contains those new changes. Redirecting applications back to Oracle isn't a lossless rollback unless you have a separate reconciliation or reverse-data-movement plan.

Troubleshoot common issues

Symptom Checks and next steps
Source or target connection fails Verify the endpoint, configured port, service or database name, credentials, DNS, routing, firewall rules, and connection-security settings from the DMS network path.
Permission error Verify the approved grants in the correct Oracle service, PDB, or Flexible Server database. Check object access and any required truncation privileges.
Missing or incompatible destination table Verify schema conversion, object names and case, column compatibility, and the configured schema mapping.
Oracle snapshot-read error Have the DBA investigate the specific Oracle error, including undo availability and concurrent database changes where applicable. Plan a fresh attempt after resolving the cause.
Migration slows or target stops responding Check source and target resource utilization, storage capacity and latency, connection pressure, and network throughput. Don't assume a larger target SKU fixes every bottleneck.
Failed or canceled migration leaves destination data Review the error and destination state. Confirm the fresh-load clearing behavior before rerunning; don't assume resume or automatic rollback.

For support, collect the migration identifier, timestamps with time zone, source and target versions, and the reported error. Don't include passwords, access tokens, or unredacted connection strings.

Clean up

After the migration is accepted:

  • Remove temporary firewall access and migration-account privileges that you no longer need.
  • Review whether you still need the DMS resource before deleting it.