Oracle database migration: Refactor

Database for PostgreSQL
Database Migration service
Virtual Network

Do you have legacy Oracle code and prefer using a managed service on Azure? If so, you can use the Azure Database Migration Service to migrate your Oracle database to Azure Database for PostgreSQL. It's the option for you because it:

Architecture

An architecture diagram that shows a private endpoint connection reaching out to an Azure Database for PostgreSQL.

Workflow

  1. Use Azure Database Migration Service to automate your Oracle database migration to Azure.

  2. Migrate the database to Azure Database for PostgreSQL.

Components

Deploy this scenario

Prerequisites

Microsoft Assessment and Planning (MAP) Toolkit

Use the Microsoft Assessment and Planning (MAP) Toolkit to evaluate the existing Oracle database and schemas. For more information, refer to the Oracle to SQL Server: Migration guide.

Assess the migration complexity

Download Ora2Pg. Run the following command to get the migration complexity assessment:

ora2pg -t SHOW_REPORT --estimate_cost

Here's an example of the output of a schema assessment:

Migration levels:

    A - Migration that might be run automatically

    B - Migration with code rewrite and a human-days cost up to 5 days

    C - Migration with code rewrite and a human-days cost above 5 days

Technical levels:

    1 = trivial: no stored functions and no triggers

    2 = easy: no stored functions but with triggers, no manual rewriting

    3 = simple: stored unctions and/or triggers no manual rewriting

    4 = manual: no stored functions but with triggers or views with code rewriting

    5 = difficulty: stored functions and/or triggers with code rewriting

Oracle Objects conversion and Data migration

Use ora2pg to convert Oracle tables, stored procedures, packages, and other database objects. After the conversion, they'll be PostgreSQL-compatible. Next, start a migration pipeline in Azure Database Migration Service.

Convert Oracle objects

Install Ora2Pg on an Azure Virtual Machine (VM). Refer to the Step-by-Step Guide to install ora2pg on Linux & Windows.

Connect to Ora2pg to convert the schemas. Refer to the Oracle to Azure Database for PostgreSQL Migration Cookbook.

Migrate data online

You can migrate your data online to reduce downtime. Refer to Create a DMS instance for more info.

Workaround list

Below you'll find a workaround list. It's useful when you're migrating Oracle database to PostgreSQL. Refer to the Oracle migrate to PostgreSQL workaround list to get detailed scripts.

Oracle PostgreSGL
Database Link Foreign Data Wrapper
External Table Foreign Table
Synonym View / Set search_path
Global Temporary Table Unlogged Table / Temp Table
Virtual column View / Function / Trigger
Connected by With Recursive
Reverse Index Functional Index
Index Organized Table (IOT) Cluster the table according to an Index

Contributors

This article is maintained by Microsoft. It was originally written by the following contributors.

Principal author:

To see non-public LinkedIn profiles, sign in to LinkedIn.

Next steps

To begin migrating your Oracle database to Azure Database for PostgreSQL, see Tutorial: Migrate Oracle to Azure Database for PostgreSQL online using DMS.

Note

If this migration path doesn't seem like the right one for your business needs, refer back to the Migration decision tree.