Oracle database migration - Refactor

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.

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

  2. Migrate the database to Azure Database for PostgreSQL.

Components

Deployment

Prerequisites

Evaluate your Oracle database

To evaluate your Oracle database, run the Oracle PL\SQL and Oracle PL\SQL 2 tools in your existing Oracle database. See the Assessment guide for instructions on how to run both of the tools.

Download the Assessment Calculator Template spreadsheet so you can record the results.

Assess 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

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.