Best Practices
The 40-Point Oracle Migration Checklist (Before You Start)
Rakesh Mamidala·Founder & Lead Engineer··10 min read
Why a Checklist?
Oracle migrations fail at predictable points: missing supplemental logging breaks CDC, forgotten XMLTYPE columns cause bulk load failures, and unchecked circular FKs stall the entire data load. This 40-point checklist is distilled from real migrations — each point maps to a failure mode we have seen in production.
Source Database Preparation (10 points)
- Confirm Oracle is in ARCHIVELOG mode
- Enable supplemental logging at database level
- Enable ALL COLUMNS supplemental logging per table
- Create a read-only migration user with required grants
- Check available redo log space (for CDC window)
- Identify and document XMLTYPE columns
- Identify columns using Oracle-specific types (INTERVAL, RAW, LONG)
- Document all DB links (cross-database references)
- List all Oracle Jobs / DBMS_SCHEDULER jobs
- Note Oracle version and any proprietary features in use
Schema Assessment (10 points)
- Run DBMigrateAIPro assessment report — save the HTML output
- Review HIGH and CRITICAL risk items
- Identify all Oracle packages and review complexity scores
- Check for CONNECT BY / hierarchical queries
- Document circular foreign key dependencies
- Review all triggers — especially AUTONOMOUS_TRANSACTION
- Check for ROWID-based application logic
- Identify sequences with NOCACHE (impact on CDC)
- Review partitioned tables — verify partition strategy
- Document synonym-heavy schemas (synonym flattening needed)
Target PostgreSQL Preparation (8 points)
- Provision PostgreSQL 14+ with sufficient storage (1.5× source size)
- Set max_connections appropriate for parallel workers
- Increase work_mem for bulk load sessions
- Disable autovacuum on target during bulk load
- Create target schema(s) with correct names
- Install required extensions (uuid-ossp, pgcrypto)
- Verify network throughput between source and target
- Test target connection from the migration machine
Migration Execution (7 points)
- Run a dry-run migration on a non-production copy first
- Record start SCN before bulk migration
- Monitor parallel worker CPU and memory during bulk load
- Check for type cast errors in migration logs
- Verify FK constraint counts match source after load
- Run the comparison report — confirm 100% row match
- Verify sequence values are synced to MAX(id) + 1
Post-Migration Validation (5 points)
- Run application smoke tests against PostgreSQL target
- Check query performance — run EXPLAIN ANALYZE on critical queries
- Run VACUUM ANALYZE to build statistics
- Re-enable autovacuum on target
- Confirm CDC lag < 5 seconds before cutover
Using This Checklist
Print this page or copy it into a migration runbook. Go through each item with your team before migration day. The most common cause of failed migrations is skipping items 1–3 in the Source Preparation section. Supplemental logging must be enabled before you start — you cannot retroactively capture changes without it.