• 14 May, 2026
• 0 Comments
• 3 Mins Read

Oracle 19c/21c PDB Schema Migration to PostgreSQL 15 Using Ora2Pg

Introduction

Ora2Pg is one of the most popular open-source tools used for migrating Oracle databases to PostgreSQL. This document explains step-by-step migration of Oracle 19c/21c PDB schema to PostgreSQL 15 using Ora2Pg.

Environment Details

Oracle Database: 19c/21c XE
PostgreSQL: 15
Migration Tool: Ora2Pg
Schema Name: U2

Ora2Pg Oracle Configuration

# ORACLE CONNECTION CONFIGURATION

ORACLE_HOME     /opt/oracle/product/21c/dbhomeXE
ORACLE_DSN      dbi:Oracle:host=node1.learnomate.org;port=1521;service_name=pdbprim
ORACLE_USER     system
ORACLE_PWD      oracle

EXPORT_SCHEMA   1
SCHEMA          U2

Ora2Pg PostgreSQL Configuration

PG_DSN          dbi:Pg:dbname=db1;host=node1.learnomate.org;port=5432
PG_USER         postgres
PG_PWD          postgres

Create Oracle Schema User

CREATE USER u2 IDENTIFIED BY oracle;

GRANT CONNECT, RESOURCE TO u2;

ALTER USER u2 QUOTA UNLIMITED ON users;

Create Oracle Tables

CREATE TABLE u2.students
(
    student_id     NUMBER,
    student_name   VARCHAR2(100),
    city           VARCHAR2(50),
    course_name    VARCHAR2(50),
    fees           NUMBER
);

CREATE TABLE u2.trainers
(
    trainer_id      NUMBER,
    trainer_name    VARCHAR2(100),
    technology      VARCHAR2(50),
    salary          NUMBER
);

CREATE TABLE u2.courses
(
    course_id       NUMBER,
    course_name     VARCHAR2(100),
    duration_months NUMBER,
    fees            NUMBER
);

Insert Sample Data

INSERT INTO u2.students VALUES (101,'Amit','Pune','Oracle DBA',35000);
INSERT INTO u2.students VALUES (102,'Rahul','Mumbai','PostgreSQL DBA',30000);
INSERT INTO u2.students VALUES (103,'Sneha','Nagpur','AWS',25000);
INSERT INTO u2.students VALUES (104,'Priya','Hyderabad','Azure',28000);
INSERT INTO u2.students VALUES (105,'Kiran','Delhi','Data Engineer',40000);

COMMIT;

Create Sequence, View, Index, Procedure, Function and Trigger

CREATE SEQUENCE u2.student_seq
START WITH 1000
INCREMENT BY 1;

CREATE VIEW u2.student_fees_view AS
SELECT student_name, course_name, fees
FROM u2.students;

CREATE INDEX u2.idx_students_city
ON u2.students(city);

CREATE OR REPLACE PROCEDURE u2.get_student_count
AS
    v_count NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO v_count
    FROM u2.students;

    DBMS_OUTPUT.PUT_LINE('TOTAL STUDENTS : ' || v_count);
END;
/

CREATE OR REPLACE FUNCTION u2.total_fees
RETURN NUMBER
AS
    v_total NUMBER;
BEGIN
    SELECT SUM(fees)
    INTO v_total
    FROM u2.students;

    RETURN v_total;
END;
/

CREATE OR REPLACE TRIGGER u2.trg_students
BEFORE INSERT
ON u2.students
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('NEW RECORD INSERTED');
END;
/

Verify Oracle Objects

SET LINESIZE 200
SET PAGESIZE 200

COLUMN object_type FORMAT A25

SELECT
       object_type,
       COUNT(*) AS object_count
FROM dba_objects
WHERE owner='U2'
GROUP BY object_type
ORDER BY object_type;

Install Ora2Pg

yum install -y perl perl-DBI perl-DBD-Pg perl-CPAN

cpan install Ora2Pg

ora2pg --version

Initialize Ora2Pg Project

ora2pg --project_base /u01/app/oracle/Ora2PGExp/ --init_project my_project

Generate Migration Report

ora2pg -t SHOW_REPORT -c ./config/ora2pg.conf --estimate_cost > report.html

Export Oracle Schema and Data

ora2pg -c config/ora2pg.conf -t COPY

Create PostgreSQL Database

CREATE DATABASE db1;

CREATE SCHEMA u2;

Import into PostgreSQL

./import_all.sh -d db1 -o postgres

Verify PostgreSQL Objects

SELECT 'TABLE' AS object_type, count(*) AS object_count
FROM pg_tables
WHERE schemaname='u2';

Conclusion

Ora2Pg simplifies Oracle to PostgreSQL migration by automating schema conversion, data migration, PL/SQL conversion, and PostgreSQL object creation.

Master Oracle to PostgreSQL Migration with Ora2Pg!

Learn how to migrate Oracle 19c/21c PDB schemas to PostgreSQL 15 using Ora2Pg with real-time practical sessions at Learnomate Technologies.

1. Oracle to PostgreSQL Migration
2. Ora2Pg Configuration & Setup
3. Schema & Data Migration
4. Performance Tuning Tips
5. Hands-on DBA Projects
6. Real-Time Oracle DBA Concepts

This course is perfect for:
✔ Oracle DBAs
✔ PostgreSQL Administrators
✔ Database Engineers
✔ Cloud & Migration Professionals

Get industry-ready with expert guidance and practical labs.

Enroll Now at Learnomate Technologies

Contact us for Demo Sessions & Course Details

#OracleDBA #PostgreSQL #Ora2Pg #DatabaseMigration #Oracle19c #PostgreSQL15 #LearnomateTechnologies #DBATraining #OracleToPostgreSQL #OracleDBAInstitute