Challenges & Resolutions from oracle to postgresssql

As an Enterprise Architect, when migrating Oracle Exadata/ExaCC to Azure PostgreSQL, the biggest challenges are feature incompatibilities (PL/SQL, data types, sequences), performance tuning, and minimizing downtime. We resolve these using ora2pg + Azure DMS for schema & data migration, refactoring complex PL/SQL into microservices, leveraging PostgreSQL-native features, and ensuring parallel runs with checksum-based reconciliation. We implement a detailed cutover plan with online migration mode to ensure near-zero downtime, while aligning with compliance and enterprise risk frameworks.”

Challenges & Resolutions in Oracle → Azure PostgreSQL Migration

1. Feature Parity / Compatibility

• Challenge: Oracle has advanced features (PL/SQL, hierarchical queries, packages, materialized views, sequences, triggers, Pro*C code) that don’t have direct equivalents in PostgreSQL.

• Resolution:

  • Use Microsoft Database Migration Assistant (DMA) + ora2pg to assess incompatibilities.

  • Rewrite PL/SQL packages into PL/pgSQL or microservices (Spring Boot APIs).

  • Replace Oracle materialized views with PostgreSQL refreshable views or ETL jobs.

  • Use CAST (Common Assessment and Synchronization Tool) for schema conversion.

  • For Pro*C, externalize logic into Java microservices.

2. Data Type Differences

• Challenge: Oracle data types (NUMBER, RAW, BLOB, CLOB, DATE, TIMESTAMP WITH TIMEZONE) don’t always map directly to PostgreSQL types.

• Resolution:

  • Use Azure DMS schema conversion to auto-map where possible.

  • Example mappings:

    • NUMBER(p,s) → NUMERIC(p,s)

    • CLOB → TEXT

    • RAW → BYTEA

    • DATE → TIMESTAMP WITHOUT TIMEZONE

  • Conduct data validation scripts post-migration.

3. Performance & Query Tuning

• Challenge: Oracle has different optimizers, execution plans, and partitioning strategies compared to PostgreSQL.

• Resolution:

  • Perform workload capture in Oracle → replay in PostgreSQL.

  • Redesign indexes (Oracle bitmap indexes ≠ PostgreSQL btree).

  • Implement pg_partman for table partitioning.

  • Use Azure Query Performance Insights to tune slow queries.

4. Stored Procedures & Packages

• Challenge: PL/SQL features like autonomous transactions, exceptions, bulk collect, and cursor loops aren’t 1:1 in PostgreSQL.

• Resolution:

  • Convert procedural logic to PostgreSQL PL/pgSQL or externalize into API layer (Spring Boot microservices).

  • Use ora2pg reports to identify complex PL/SQL.

  • Break large packages into smaller functions.

5. Triggers & Sequences

• Challenge: Oracle uses sequences and triggers heavily for IDs, while PostgreSQL supports serial/identity.

• Resolution:

  • Convert SEQUENCE to GENERATED ALWAYS AS IDENTITY.

  • Rewrite triggers into PostgreSQL triggers or application-level logic.

6. Migration Downtime & Cutover

• Challenge: Banking/financial systems need near zero downtime.

• Resolution:

  • Use Azure DMS online migration mode for continuous replication until cutover.

  • Perform dual-write validation testing (writes go to both Oracle & Postgres temporarily).

  • Schedule cutover during low transaction window.

7. Security & Compliance

• Challenge: Oracle and PostgreSQL differ in user roles, auditing, and encryption methods.

• Resolution:

  • Map Oracle roles to Azure AD + PostgreSQL roles.

  • Enable TDE (Transparent Data Encryption) equivalent via Azure.

  • Implement audit logging via pgAudit extension.

8. Application Dependency

• Challenge: Applications (Java, .NET, Pro*C, batch jobs) are tightly coupled with Oracle DB features.

• Resolution:

  • Use compatibility shims for JDBC drivers.

  • Refactor SQL queries inside applications.

  • Migrate batch jobs to Azure Data Factory / Logic Apps.

9. Testing Complexity

• Challenge: Data integrity & application regression testing is huge.

• Resolution:

  • Build GoldenGate-like reconciliation scripts for row counts & checksum validation.

  • Run end-to-end functional testing for business scenarios (loan approval, KYC, transactions).

10. Enterprise Risks & Mitigation

Some top risks you must mention (align with interview expectations):

• Data Loss Risk → Parallel run, checksum validation.

• Performance Degradation → Load testing in Azure Postgres.

• Compliance Breach → Ensure PCI/ISO controls in Azure.

• High Downtime → Phased cutover, blue-green deployment.

• Skill Gaps → Train Oracle DBAs in PostgreSQL.

✅ Tools Used in Resolution:

• Azure Database Migration Service (DMS) – schema + data migration

• ora2pg – schema & PL/SQL analysis

• Azure Data Factory – ETL for incremental loads

• CAST AIP / VFunction – monolith & DB modernization

• pgAdmin + psql – validation

⚡ Summary (Interview-Safe Answer):