Oracle database migrations are never simple. But they’re increasingly necessary — license costs are punishing, modern alternatives have matured, and the “just renew” option gets more expensive every year.
I’ve led and supported enough Oracle-to-PostgreSQL assessments to know that the assessment itself is where most migrations succeed or fail. Rush it, and you’ll discover the hard problems during cutover. Do it right, and the migration becomes a well-scoped engineering project with predictable timelines.
Here’s the playbook.
Watch the 30-second summary
Auto-generated summary — read the full article below for details.
Why Migrate?
The business drivers I see most often:
- License cost reduction — Oracle licensing is brutal, especially with RAC and partitioning
- End of support / version upgrade pressure — upgrading Oracle is its own project
- Cloud-first strategy — organizations standardizing on cloud-native services
- Vendor lock-in reduction — Oracle’s commercial practices create dependency
- AI/data strategy — unlocking data for modern analytics and ML workloads
The Assessment Framework
1. Current State Discovery
Database Inventory
For each database, document:
- Version and Edition (Enterprise vs. Standard)
- Size (GB)
- Environment (Prod / Non-Prod)
- Business criticality (High / Medium / Low)
Oracle Features in Use
This is where complexity hides. Check every one of these:
Core Features:
- Partitioning (Range / List / Hash / Composite)
- Materialized Views
- Database Links (DB Links)
- Advanced Queuing (AQ)
- Real Application Clusters (RAC)
- Data Guard (Active / Passive)
- Transparent Data Encryption (TDE)
- Flashback (Table / Database / Query)
- Virtual Private Database (VPD) / Row Level Security
PL/SQL Complexity:
- Package count
- Procedure/Function count
- Dynamic SQL (EXECUTE IMMEDIATE, DBMS_SQL)
- Autonomous Transactions
- Pipelined Functions
- Bulk Operations (FORALL, BULK COLLECT)
Connectivity:
- Peak connection count
- Connection pooling mechanism
- Third-party integrations
2. Feature Mapping: Oracle to Aurora PostgreSQL
| Oracle Feature | Aurora PostgreSQL Equivalent | Notes |
|---|---|---|
| RAC (multi-node) | Aurora Multi-AZ + Read Replicas + Custom Endpoints | No shared-nothing clustering |
| Data Guard | Aurora Global Database / Cross-Region Replicas | RPO ~1s for Global DB |
| TDE | AWS KMS encryption at rest (default) | No wallet management |
| Partitioning | PostgreSQL declarative partitioning (PG 10+) | Range, List, Hash supported |
| Materialized Views | PostgreSQL Materialized Views | No automatic refresh — use pg_cron |
| GoldenGate (CDC) | AWS DMS CDC / Logical Replication | |
| PDB multi-tenancy | Database-per-tenant / Schema-per-tenant | Design decision required |
| DBMS_SCHEDULER | pg_cron / Amazon EventBridge + Lambda | |
| Oracle AQ | Amazon SQS / SNS / EventBridge | Application change required |
| DB Links | postgres_fdw / dblink extension | |
| VPD / RLS | PostgreSQL Row-Level Security (native since PG 9.5) | |
| Flashback | PITR / Aurora Backtrack (up to 72h) | |
| PL/SQL | PL/pgSQL | Syntax translation required |
| Oracle Text | PostgreSQL Full-Text Search (tsvector/tsquery) | |
| Autonomous Transactions | Separate DB connection / dblink to self | Pattern change |
3. Complexity Scoring
| Dimension | Low | Medium | High |
|---|---|---|---|
| Schema complexity | <100 objects | 100–500 | >500 |
| PL/SQL volume | <50 procedures | 50–200 | >200 |
| Oracle-specific features | ≤2 features | 3–5 | >5 |
| Application coupling | Loose (ORM) | Mixed | Tight (raw SQL) |
| Data volume | <100 GB | 100 GB–1 TB | >1 TB |
| Downtime tolerance | >8h | 1–8h | <1h |
Score each dimension. Sum them. The total drives your effort estimate and strategy selection.
4. Migration Strategy Selection
| Approach | When |
|---|---|
| Lift & Shift (Oracle on RDS/EC2) | You need to exit the datacenter fast; modernization comes later |
| Re-platform (Oracle → Aurora PG) | Straightforward schema, low PL/SQL complexity |
| Phased Migration | Large system, migrate schema-by-schema |
| Big Bang | Small database, acceptable downtime window |
| Strangler Pattern | Mission-critical, zero-downtime, dual-write during transition |
5. Recommended Tooling
| Phase | Tool | Purpose |
|---|---|---|
| Assess | AWS SCT (Multiserver Assessment) | Automated complexity report |
| Assess | AWS DMS Fleet Advisor | Inventory and right-sizing |
| Convert | AWS DMS Schema Conversion | Schema + code conversion with GenAI assistance |
| Convert | Amazon Q Developer | PL/SQL → PL/pgSQL AI-assisted refactoring |
| Migrate | AWS DMS (CDC) | Data migration with minimal downtime |
| Validate | AWS DMS Data Validation | Row-level comparison |
| Monitor | CloudWatch Database Insights | Post-migration observability |
6. High-Level Roadmap
Phase 1: Assess (2–4 weeks)
├── Run SCT Assessment
├── Application dependency mapping
├── Feature gap analysis
└── Effort estimation & business case
Phase 2: Mobilize (4–6 weeks)
├── Set up Aurora PG target environment
├── Schema conversion (iterative)
├── PoC for high-risk areas
└── Migration runbook development
Phase 3: Migrate & Modernize (6–12 weeks)
├── PL/SQL → PL/pgSQL translation
├── Application refactoring
├── Data migration (full load + CDC)
├── Performance testing & tuning
├── Cutover rehearsals
└── Production cutover
Top Risks
| Risk | Mitigation |
|---|---|
| Complex PL/SQL not auto-convertible | Amazon Q Developer + manual SME review |
| Application hard-coded Oracle SQL | Code scanning + staged refactoring |
| Performance regression post-migration | Benchmark before/after; Aurora Performance Insights |
| Downtime exceeds window | DMS CDC for near-zero downtime; cutover rehearsals |
| Multi-tenancy redesign complexity | PoC early in Mobilize phase |
Key Takeaways
- The assessment is where you win or lose — never skip it
- Run AWS SCT first to get an automated complexity baseline
- PL/SQL translation is usually the biggest effort item — scope it accurately
- Oracle features like RAC and AQ don’t have 1:1 equivalents — they require architectural decisions
- Amazon Q Developer accelerates PL/SQL-to-PL/pgSQL, but complex packages still need human review
- Use DMS with CDC for near-zero-downtime migrations — batch-and-cutover is rarely acceptable for production
- Rehearse the cutover at least twice before the real thing
References
- Oracle to Aurora PG Migration Playbook
- Prescriptive Guidance: On-Prem Oracle to Aurora PG
- AWS DMS + SCT Pattern
Any opinions in this article are my own.