Complex Oracle 19c to PostgreSQL 15 Database Migration

Production-oriented migration approach for a complex Oracle 19c to PostgreSQL 15 workload, covering assessment, schema and code conversion, bulk loading, change-data capture, validation, cutover, rollback, and the main technical pain points.

Oracle-to-PostgreSQL is a heterogeneous migration, not a normal upgrade. Oracle RMAN/Data Pump files and PostgreSQL backup files are not directly interchangeable, so schema conversion, data transformation, application remediation, and business validation are mandatory. The internal migration SOP also distinguishes enterprise migrations from small CSV-based table transfers.

---

1. Recommended migration architecture

For a complex production database, use:

Oracle 19c

   |

   |-- Schema assessment and conversion

   |      Ora2Pg / AWS SCT / commercial conversion tool

   |

   |-- Initial full data load

   |      Ora2Pg COPY / AWS DMS / ETL tool

   |

   |-- Continuous change replication

   |      AWS DMS / Oracle GoldenGate / SharePlex / other CDC tool

   |

PostgreSQL 15

   |

   |-- Rewritten PL/pgSQL and application SQL

   |-- Validation, tuning and production cutover

Ora2Pg connects to Oracle, inventories the database, extracts schema or data, and generates PostgreSQL-compatible SQL. It can support assessment, reverse engineering, schema conversion, data export, and partial PL/SQL conversion. 

For a complex database with limited downtime, my recommendation is:

• Ora2Pg or AWS SCT for assessment and schema/code conversion.
• COPY, Ora2Pg, or AWS DMS for initial data loading.
• AWS DMS, GoldenGate, SharePlex, or another CDC product for ongoing synchronization.
• Multiple migration rehearsals followed by a controlled production cutover.
• Do not use CSV/manual exports as the primary enterprise migration method. The internal SOP positions CSV as suitable for small, data-only migrations. 

---

2. Phase 1: Discovery and inventory

Step 1: Establish migration requirements

Record:

• Oracle database size and daily growth.
• Number and size of schemas.
• Largest tables, partitions, indexes, LOB segments.
• Peak transaction rate.
• Redo generation per hour.
• Required downtime.
• RPO and RTO.
• Character set and NLS settings.
• Application connection methods.
• Batch-job windows.
• External interfaces.
• HA and DR requirements.
• Retention, backup, monitoring, audit, encryption, and compliance requirements.

The target PostgreSQL design should explicitly include production HA, storage, backup and monitoring requirements. These items are also part of the internal PostgreSQL implementation checklist. 

Step 2: Inventory Oracle objects

Inventory at least:

• Tables and partitions
• Indexes and index types
• Primary, unique and foreign keys
• Check constraints
• Sequences
• Views and materialized views
• Synonyms
• Database links
• Procedures and functions
• Packages and package bodies
• Triggers
• Scheduler jobs
• User-defined types
• Object-relational types
• XMLType columns
• Spatial objects
• Advanced queues
• External tables
• Virtual columns
• Function-based indexes
• LOBs, SecureFiles and BasicFiles
• Row-level security policies
• Fine-grained auditing
• Roles and grants
• Public and private synonyms
• Java stored procedures
• Oracle-specific options such as Text, Spatial, Partitioning and Advanced Compression

Useful Oracle inventory queries:

SELECT owner, object_type, COUNT(*) object_count

FROM dba_objects

WHERE owner NOT IN (

    'SYS','SYSTEM','XDB','MDSYS','CTXSYS','ORDSYS',

    'OUTLN','DBSNMP','AUDSYS'

)

GROUP BY owner, object_type

ORDER BY owner, object_type;

SELECT owner,

       segment_type,

       ROUND(SUM(bytes)/1024/1024/1024,2) size_gb

FROM dba_segments

WHERE owner NOT IN ('SYS','SYSTEM')

GROUP BY owner, segment_type

ORDER BY size_gb DESC;

SELECT owner,

       table_name,

       column_name,

       data_type,

       data_length,

       data_precision,

       data_scale

FROM dba_tab_columns

WHERE owner NOT IN ('SYS','SYSTEM')

ORDER BY owner, table_name, column_id;

SELECT owner, object_type, object_name, status

FROM dba_objects

WHERE status <> 'VALID'

ORDER BY owner, object_type, object_name;

Step 3: Inventory actual application SQL

DDL inventory alone is insufficient. Capture:

• Top SQL by elapsed time, CPU, executions and I/O.
• SQL containing Oracle-specific functions.
• Dynamic SQL generated by applications.
• ORM-generated SQL.
• PL/SQL package calls.
• SQL issued from reports, ETL and batch jobs.
• Stored procedure output parameter usage.
• Transaction and commit patterns.
• Session-state dependencies.

Use AWR, ASH, V$SQL, SQL trace, application logs and source-code searches.

---

3. Phase 2: Complexity assessment

Step 4: Run an automated assessment

With Ora2Pg, establish a migration project and generate the assessment report before exporting objects.

Illustrative workflow:

ora2pg --project_base /migration </span>

       --init_project oracle_pg_migration

Update the generated ora2pg.conf with values such as:

ORACLE_DSN     dbi:Oracle:host=oracle-host;sid=ORCL;port=1521

ORACLE_USER    migration_user

SCHEMA         APP_SCHEMA

PG_VERSION     15

Then generate the assessment:

ora2pg -t SHOW_REPORT </span>

       -c /migration/oracle_pg_migration/config/ora2pg.conf

Assess objects in four categories:

Category	Typical objects	Action
Low complexity	Basic tables, standard indexes, simple views	Automated conversion
Medium complexity	Triggers, sequences, partitions, materialized views	Convert and manually review
High complexity	Packages, dynamic SQL, bulk operations, autonomous transactions	Manual redesign
Replacement required	AQ, DB links, Oracle Text, Java, Spatial-specific logic	Select PostgreSQL replacement

Create an object-level tracker containing:

• Object owner and name
• Object type
• Automatic conversion status
• Manual remediation required
• Application dependency
• Test case
• Technical owner
• Current status
• Cutover relevance

---

4. Phase 3: Design the PostgreSQL target

Step 5: Build the target platform

Design PostgreSQL 15 for:

• Compute and memory sizing
• Storage layout and IOPS
• WAL capacity
• Connection count
• Connection pooling
• Backup and point-in-time recovery
• Streaming replication
• Automatic failover
• Monitoring and alerting
• TLS
• Authentication
• Secret management
• Audit requirements
• Maintenance and patching
• Tablespace requirements, if justified
• PostgreSQL extensions

The internal installation SOP includes database directories, service startup, authentication configuration, application users, backup scheduling, monitoring and CMDB updates.

Important PostgreSQL 15 security point

PostgreSQL 15 changed the default creation permission for the public schema and introduced pg_database_owner ownership behavior. Role, schema ownership and application deployment scripts must therefore be explicitly tested rather than assuming older PostgreSQL behavior.

Recommended model:

CREATE ROLE app_owner NOLOGIN;

CREATE ROLE app_runtime LOGIN PASSWORD 'managed-by-secret-store';

CREATE ROLE app_readonly LOGIN PASSWORD 'managed-by-secret-store';

CREATE SCHEMA app AUTHORIZATION app_owner;

GRANT USAGE ON SCHEMA app TO app_runtime, app_readonly;

Avoid allowing the application runtime account to own all objects.

---

5. Phase 4: Data-type mapping

Step 6: Approve a data-type mapping matrix

Do not accept conversion-tool defaults blindly.

Oracle	Recommended PostgreSQL mapping	Main concern
NUMBER(p,0)	smallint, integer, bigint or numeric(p,0)	Overflow and performance
NUMBER(p,s)	numeric(p,s)	Precision and rounding
Unconstrained NUMBER	numeric after data profiling	Storage and performance
BINARY_FLOAT	real	Floating-point differences
BINARY_DOUBLE	double precision	Floating-point differences
VARCHAR2	varchar or text	Byte versus character length
NVARCHAR2	varchar or text in UTF-8 database	Unicode validation
Oracle DATE	timestamp(0) without time zone	Oracle DATE includes time
TIMESTAMP WITH TIME ZONE	timestamp with time zone	PostgreSQL normalization
TIMESTAMP WITH LOCAL TIME ZONE	Application-specific redesign	Session time-zone behavior
CLOB	text	Large object and driver behavior
BLOB	bytea or PostgreSQL large object	Size and streaming pattern
RAW	bytea	Binary comparison
LONG	text	Legacy conversion
LONG RAW	bytea	Legacy conversion
XMLTYPE	xml, text, or jsonb	Query semantics
SDO_GEOMETRY	PostGIS geometry	Spatial function conversion
ROWID	Usually no direct equivalent	Application redesign
UROWID	No direct equivalent	Application redesign

The internal migration reference emphasizes mapping exact numeric data to numeric, while floating-point types are more appropriate only where approximation is acceptable.

Mandatory data profiling

Before finalizing mappings, measure:

SELECT

    MIN(number_column),

    MAX(number_column),

    MAX(LENGTH(TO_CHAR(ABS(number_column)))) max_digits

FROM app_table;

Also identify:

• Values exceeding target integer ranges.
• Negative scales.
• NaN or infinity requirements.
• Trailing spaces.
• Empty strings.
• Zero dates represented through application conventions.
• Invalid Unicode or control characters.
• Oversized LOB values.
• Columns declared as dates but used as timestamps.

---

6. Phase 5: Convert schema objects in dependency order

Step 7: Create target objects

Recommended creation order:

1. Roles and schemas
2. Custom types and required extensions
3. Sequences
4. Tables without foreign keys
5. Default expressions
6. Primary and unique constraints
7. Data load
8. Secondary indexes
9. Foreign keys
10. Views
11. Functions and procedures
12. Triggers
13. Materialized views
14. Grants
15. Scheduler jobs
16. Statistics

Do not create all secondary indexes and foreign keys before a very large bulk load. Their maintenance can make the initial load significantly slower.

Example sequence conversion:

CREATE SEQUENCE app.order_seq

    START WITH 1

    INCREMENT BY 1

    CACHE 100;

If the Oracle application uses sequence_name.NEXTVAL, application SQL usually needs conversion to:

nextval('app.order_seq')

After loading data:

SELECT setval(

    'app.order_seq',

    COALESCE((SELECT MAX(order_id) FROM app.orders), 1),

    true

);

---

7. Phase 6: Convert PL/SQL and application logic

Step 8: Rewrite database code

PostgreSQL PL/pgSQL resembles Oracle PL/SQL, but it is not source compatible. PostgreSQL documents important differences involving name ambiguity, function-body quoting, data types, package replacement, package variables, loops and cursor handling.

Critical conversion areas:

Packages

PostgreSQL does not provide Oracle-style packages.

Replace them using:

• PostgreSQL schemas for logical grouping.
• Standalone functions and procedures.
• Composite types for structured parameters.
• Temporary tables or application state for package variables.
• Application services for complex business orchestration.

Empty string versus NULL

Oracle generally treats '' as NULL; PostgreSQL keeps empty string and NULL distinct.

This can break:

• NOT NULL validation
• Unique keys
• Comparisons
• Concatenation
• NVL logic
• Application input validation

Transactions

Oracle procedures may use:

• Autonomous transactions
• Savepoints
• Implicit transaction assumptions
• DDL with implicit commit behavior

These require redesign because PostgreSQL transaction behavior differs.

Exception handling

Convert:

• NO_DATA_FOUND
• TOO_MANY_ROWS
• DUP_VAL_ON_INDEX
• PRAGMA EXCEPTION_INIT
• Application-specific RAISE_APPLICATION_ERROR

to appropriate PostgreSQL exception handling and SQLSTATE logic.

Common SQL conversions

Oracle	PostgreSQL
NVL(a,b)	COALESCE(a,b)
SYSDATE	CURRENT_TIMESTAMP or LOCALTIMESTAMP
SYSTIMESTAMP	CURRENT_TIMESTAMP
DECODE	CASE
DUAL	Usually omitFROM
ROWNUM	LIMIT, window function or explicit ordering
CONNECT BY	Recursive CTE
LISTAGG	string_agg
MINUS	EXCEPT
MERGE	PostgreSQL 15 MERGE, after semantic testing
DBMS_OUTPUT	Logging, notices or application output
UTL_FILE	Application or server-side controlled file method
DBMS_SCHEDULER	pg_cron, OS scheduler or enterprise scheduler
DBMS_LOB	Native text/bytea operations or application streaming
Hints	Query/index/statistics redesign

PostgreSQL 15 includes SQL MERGE, but Oracle MERGE statements must still be individually tested for matching, concurrency, trigger and row-count behavior. PostgreSQL 15’s release documentation confirms MERGE support. 

---

8. Phase 7: Initial data load

Step 9: Prepare the load

Before loading:

• Take a recoverable Oracle backup.
• Record the consistent source point, such as SCN.
• Stop purge jobs.
• Ensure target disk and WAL capacity.
• Disable nonessential target triggers.
• Delay secondary indexes.
• Confirm encoding.
• Split large tables.
• Define reject handling.
• Configure parallelism based on source, network and target capacity.

The internal migration reference explicitly recommends testing in nonproduction first and identifies Ora2Pg as a method for generating schema and data scripts. 

Step 10: Load data

For an offline migration:

1. Stop writes to Oracle.
2. Capture final source SCN.
3. Export table data.
4. Load with PostgreSQL COPY.
5. Create indexes and constraints.
6. Validate.
7. Redirect the application.

For low-downtime migration:

1. Obtain a consistent Oracle SCN.
2. Start CDC from that SCN.
3. Perform the initial full load.
4. Apply ongoing changes.
5. Monitor replication lag.
6. Freeze source writes at cutover.
7. Apply remaining changes.
8. Validate.
9. Switch the application.

Load very large tables independently. Partition by:

• Oracle partition
• Primary-key range
• Date range
• Business unit
• Hash range

---

9. Phase 8: Validation

Step 11: Validate schema and data

Validation should have several layers.

Structural validation

Compare:

• Tables
• Columns
• Data types
• Nullability
• Defaults
• Constraints
• Indexes
• Partitions
• Sequences
• Views
• Executable code
• Grants

Data validation

For every table:

• Row counts
• Minimum and maximum keys
• Null counts
• Duplicate primary keys
• Numeric totals
• Date boundaries
• LOB counts and lengths
• Business totals

Example:

SELECT COUNT(*) row_count,

       MIN(order_id) min_id,

       MAX(order_id) max_id,

       SUM(order_amount) total_amount

FROM orders;

For stronger assurance, calculate checksums in deterministic ranges rather than relying only on total table counts.

Sequence validation

Verify that every target sequence is greater than or equal to the maximum existing key.

Functional validation

Run:

• CRUD tests
• Batch processing
• Reports
• Interfaces
• Stored routine tests
• Failure and retry scenarios
• Month-end or day-end processing
• Security and authorization tests

Performance validation

Compare Oracle and PostgreSQL for:

• Response time
• Throughput
• CPU
• Storage I/O
• Buffer-cache effectiveness
• Lock waits
• Temporary-file generation
• WAL generation
• Checkpoint behavior
• Connection utilization
• Replication lag

After the load:

VACUUM (ANALYZE);

For specific large tables:

ANALYZE app.large_table;

---

10. Phase 9: Cutover

Step 12: Production cutover sequence

A controlled cutover should follow this order:

1. Confirm go/no-go approval.
2. Confirm rollback deadline.
3. Verify Oracle backup and recovery.
4. Verify PostgreSQL backup and recovery.
5. Verify CDC health and lag.
6. Stop batch jobs and schedulers.
7. Put the application in maintenance mode.
8. Block new Oracle writes.
9. Record final SCN and transaction state.
10. Allow CDC to reach zero or accepted lag.
11. Stop replication in a controlled manner.
12. Run final row-count and business validation.
13. Synchronize sequences.
14. Enable final constraints and triggers.
15. Take a PostgreSQL cutover backup.
16. Change application connection configuration.
17. Start application services.
18. Run smoke tests.
19. Open to controlled users.
20. Monitor database and application behavior.
21. Obtain business sign-off.
22. Keep Oracle read-only during the agreed stabilization period.

---

11. Phase 10: Rollback strategy

Rollback is not simply “point the application back” once users have written new transactions to PostgreSQL.

Choose one model:

Model A: Rollback before PostgreSQL writes

• Stop the target application.
• Revert connection strings.
• Restart against Oracle.
• Minimal data reconciliation.

Model B: Short dual-write window

• Requires application-level conflict management.
• High complexity.
• Normally avoid unless already designed into the application.

Model C: Reverse replication

• Replicate PostgreSQL changes back to Oracle.
• Requires advance engineering and testing.
• Data-type and SQL asymmetry make this difficult.

Define a point of no return. After that point, recovery usually becomes a forward-fix or reverse-migration activity rather than a simple rollback.

---

12. Main pain points and major issues

Area	Major issue	Required control
PL/SQL packages	No direct package equivalent	Redesign using schemas, routines and application services
Package variables	No native equivalent	Temporary/session tables or application state
Empty string	Oracle treats it like NULL	Review all conditions and constraints
Oracle DATE	Contains date and time	Usually map to timestamp(0)
Time zones	Different storage/display semantics	Test DST, session zone and UTC conversions
NUMBER	Generic mapping can cause errors or poor performance	Profile values and approve each mapping class
LOBs	Large values, drivers and memory can fail	Stream, chunk, validate hashes and sizes
Character set	Multibyte conversion can expand values	Validate encoding and column length
Byte semantics	VARCHAR2(n BYTE) may not equal target character limit	Profile maximum byte and character length
ROWID	No business-stable equivalent	Replace with primary key
Synonyms	No exact synonym model	Replace with schemas, views or application configuration
DB links	No native direct match	Use FDW, integration service or ETL
Materialized views	Refresh semantics differ	Redesign refresh and concurrency
Scheduler	DBMS_SCHEDULER differs	Use pg_cron or enterprise scheduler
Autonomous transactions	Different transaction model	Redesign logging and commits
Global temporary tables	Different lifecycle semantics	Review ON COMMIT behavior
Partitioning	Syntax and feature behavior differ	Redesign partitions and indexes
Function-based indexes	Expression equivalence matters	Recreate expression indexes and test
Bitmap indexes	No direct equivalent	Use B-tree, BRIN, GIN, GiST or redesign
Hints	Oracle hints do not transfer	Tune statistics, SQL and indexes
Oracle RAC	No direct PostgreSQL RAC equivalent	Design streaming replication and failover
Read consistency	MVCC and vacuum behavior differ	Tune long transactions and autovacuum
Connection model	Process-per-connection can exhaust resources	Use connection pooling
Case sensitivity	Unquoted identifiers fold differently	Standardize lowercase naming
Privileges	Roles, schema ownership and grants differ	Build an explicit security mapping
NLS behavior	Date, numeric and sort defaults can change	Use explicit formats and collations
CDC	Unsupported types or missing keys cause failures	Test every table and ensure stable keys
Foreign keys	Slow initial load	Create or validate after loading
Sequences	Values may lag behind migrated keys	Reset after full load and before cutover
Statistics	Plans may be unstable immediately after loading	Analyze and performance-test
Long transactions	Increase replication lag and vacuum impact	Remove or split before cutover
DDL changes	Source schema drift breaks conversion and CDC	Implement a DDL freeze
Data validation	Row counts alone can hide corruption	Add checksums and business totals
Rollback	Target writes may not exist in Oracle	Define rollback boundary and reconciliation plan

Character-set conversion deserves special attention. Oracle documentation warns that moving from a single-byte to multibyte encoding can expand values and cause truncation unless column widths and schemas are reviewed. 

---

13. Highest-risk objects to identify early

Treat the following as red flags:

• Large PL/SQL packages with shared package state
• Dynamic SQL using Oracle metadata
• Autonomous transactions
• Advanced Queuing
• Oracle Spatial
• Oracle Text
• XML DB
• Java stored procedures
• Object types and nested tables
• Edition-based redefinition
• Fine-grained access control
• Virtual private database policies
• Database links
• Materialized-view fast refresh
• Global indexes on partitioned tables
• Bitmap indexes
• Interval or reference partitioning
• SecureFile LOB features
• Applications relying on ROWID
• Applications depending on Oracle error numbers
• Applications with embedded optimizer hints
• SQL relying on implicit data conversion
• Large tables without primary or unique keys
• Tables receiving frequent DDL
• Very large or frequently modified LOBs

---

Final recommendation

For your Database Architect role, manage this as an application modernization program, not only a DBA migration:

1. Run an automated assessment.
2. Build an object and SQL remediation backlog.
3. Approve data-type mappings.
4. Design PostgreSQL HA, DR, security, backup and monitoring.
5. Convert schema separately from data.
6. Rewrite Oracle-specific logic.
7. Perform at least one full-volume rehearsal.
8. Use CDC where downtime cannot accommodate the full load.
9. Validate with counts, checksums, business totals and application tests.
10. Conduct a timed cutover rehearsal with a fully tested rollback decision point.

The largest effort usually lies in PL/SQL packages, application SQL, transaction behavior, Oracle-specific features, performance retuning, and data validation, not in copying the table rows.

Note :- 

We have to do at least  2-3 iteration of testing

before move to production cutover .