Appendix C
Appendix C: Operational Checklists
Six operational checklists for migration phases. Each item includes the responsible role and a verification method. Print or copy to your project wiki before each phase.
Checklist 1: Pre-Assessment
Purpose: Confirm that the Oracle source system is accessible and ready for assessment before running any assessment queries.
| # | Item | Owner | Verification |
|---|---|---|---|
| 1 | Oracle JDBC connectivity confirmed from pipeline host | Migration Eng | SELECT 1 FROM DUAL via JDBC |
| 2 | Oracle user with read access to DBA_* views granted | Oracle DBA | SELECT COUNT(*) FROM DBA_TABLES succeeds |
| 3 | V$SQL and V$SESSION accessible (for workload analysis) | Oracle DBA | SELECT COUNT(*) FROM V$SESSION succeeds |
| 4 | DBA_SOURCE accessible (for PL/SQL inventory) | Oracle DBA | SELECT COUNT(*) FROM DBA_SOURCE succeeds |
| 5 | Oracle version confirmed (19c / 21c / 23c) | Oracle DBA | SELECT VERSION FROM V$INSTANCE |
| 6 | Schema owner(s) confirmed and documented | Migration Eng | List of schemas in scope agreed with App team |
| 7 | Table exclusion list agreed (staging tables, temp tables, etc.) | App Eng | Written exclusion list reviewed by both teams |
| 8 | Assessment output destination confirmed (path/bucket) | Migration Eng | Write test to output location succeeds |
| 9 | Estimated schema size reviewed (table count, row counts) | Migration Eng | SELECT COUNT(*) FROM DBA_TABLES WHERE OWNER = :schema |
| 10 | Assessment timeline communicated to Oracle DBA (will run queries) | PM | Calendar entry / Slack confirmation |
Checklist 2: Pre-Full-Load
Purpose: Confirm that all prerequisites are in place before starting the full data load.
| # | Item | Owner | Verification |
|---|---|---|---|
| 1 | Pre-load DDL applied to PostgreSQL target | PG Eng | \dt in psql shows all tables (no constraints yet) |
| 2 | Sequences created in PostgreSQL (not advanced yet) | PG Eng | \ds shows all sequences starting at 1 |
| 3 | PostgreSQL WAL settings tuned for bulk load | PG Eng | SHOW synchronous_commit; returns off |
| 4 | Oracle supplemental logging enabled for all in-scope tables | Oracle DBA | SELECT * FROM DBA_SUPPLEMENTAL_LOGGING all rows show Y |
| 5 | Anchor SCN recorded and stored in staging ledger | Migration Eng | SELECT anchor_scn FROM _pulsaride.full_load_config |
| 6 | Oracle UNDO_RETENTION sufficient for load duration | Oracle DBA | SHOW PARAMETER UNDO_RETENTION > estimated load hours × 3600 |
| 7 | Pipeline worker count configured and tested (benchmark run) | Migration Eng | Benchmark command completed, throughput documented |
| 8 | LOB-heavy tables configured with reduced chunk size | Migration Eng | Config YAML reviewed, LOB tables have chunkSize ≤ 5000 |
| 9 | Staging ledger tables created | Migration Eng | SELECT COUNT(*) FROM _pulsaride.full_load_status succeeds |
| 10 | Monitoring dashboard accessible | Migration Eng | /pulsaride/dashboard loads in browser |
| 11 | Estimated full load duration communicated to Oracle DBA | PM | Slack/email with estimated duration |
| 12 | Archive log retention verified for load duration + 24h | Oracle DBA | SHOW PARAMETER DB_RECOVERY_FILE_DEST_SIZE and retention policy |
Checklist 3: Pre-CDC
Purpose: Confirm that the CDC pipeline is ready to start consuming Oracle redo logs from the anchor SCN.
| # | Item | Owner | Verification |
|---|---|---|---|
| 1 | Full load confirmed complete (all tables, all chunks COMPLETE) | Migration Eng | SELECT status FROM _pulsaride.full_load_status WHERE status != 'COMPLETE' returns 0 rows |
| 2 | Partial write detection run (no chunks with rows_loaded ≠ rows_expected) | Migration Eng | SELECT * FROM _pulsaride.full_load_status WHERE rows_loaded != rows_expected returns 0 rows |
| 3 | Post-load DDL applied (constraints and indexes) | PG Eng | \d account shows PRIMARY KEY, FOREIGN KEY, indexes |
| 4 | Sequence advancement applied | PG Eng | SELECT last_value FROM account_seq > max(account_id) in account |
| 5 | CDC connector configured (LogMiner or Debezium) | Migration Eng | CDC config YAML reviewed, connection test passed |
| 6 | Anchor SCN confirmed in CDC configuration | Migration Eng | startScn in CDC config matches staging ledger anchor_scn |
| 7 | Oracle archived log availability for anchor SCN | Oracle DBA | SELECT * FROM V$LOG_HISTORY WHERE FIRST_CHANGE# <= :anchor_scn shows log exists |
| 8 | CDC consumer Kafka topics created (if Debezium) | Migration Eng | kafka-topics.sh --list shows topics for all in-scope tables |
| 9 | PostgreSQL WAL settings restored to durable mode | PG Eng | SHOW synchronous_commit; returns on |
| 10 | CDC lag monitoring alert configured | Migration Eng | Alert fires on test event (set lag threshold to 0, confirm alert, reset) |
Checklist 4: Pre-Reconciliation
Purpose: Confirm that reconciliation can start and will produce meaningful results.
| # | Item | Owner | Verification |
|---|---|---|---|
| 1 | CDC lag < 5 minutes (target: < 30 seconds for reconciliation to be meaningful) | Migration Eng | pulsaride_cdc_lag_seconds < 300 |
| 2 | Reconciliation configuration YAML reviewed | Migration Eng | All tables in scope have reconciliation mode configured |
| 3 | Normalization rules configured for known column patterns | Migration Eng | email: lowercase, phone: E164, CHAR columns: rtrim |
| 4 | Blocking vs advisory thresholds set for each table tier | Migration Eng | financial tier: blockingThresholds.countDeltaAbsolute=0 |
| 5 | Reconciliation queries tested against Oracle (no performance issues) | Oracle DBA | Sample reconciliation query runs in < 60s on largest tables |
| 6 | False positive baseline established (first run expected discrepancies documented) | Migration Eng | First run output reviewed, expected false positives noted |
| 7 | Compensating write approval workflow confirmed | Migration Lead | Who approves? What is the SLA for approval? Documented. |
| 8 | Reconciliation alert routing confirmed | Migration Eng | Test alert reaches correct Slack channel and PagerDuty |
Checklist 5: Pre-Cutover (Go/No-Go)
Purpose: The final gate before the cutover maintenance window opens. All items must be confirmed.
| # | Item | Owner | Verification |
|---|---|---|---|
| 1 | CDC lag < 30 seconds for all tables | Migration Eng | Dashboard: all tables < 30s lag for past 4 hours |
| 2 | Zero RED tables in reconciliation dashboard | Migration Eng | Dashboard: 0 RED, 0 unexpected YELLOW |
| 3 | SLA gate: financial tables clean for 24 hours | Migration Eng | _pulsaride.sla_gates all PASSING |
| 4 | Schema parity: zero open SCHEMA_DRIFT findings | Migration Eng | _pulsaride.schema_changes all applied=TRUE |
| 5 | Sequence advancement documented (values confirmed) | PG Eng | Written record of pre-advancement and post-advancement values |
| 6 | Zero ORPHANED_EVENT findings | Migration Eng | _pulsaride.orphaned_events count = 0 |
| 7 | Application functional test suite PASSED on PostgreSQL | App Eng | Test run output attached to go/no-go document |
| 8 | Application performance test PASSED (p99 latency acceptable) | App Eng | Load test report attached |
| 9 | Rollback pipeline configured and dry-run completed | Migration Eng | Reverse replication dry run log attached |
| 10 | Maintenance window notifications sent | PM | Email/Slack confirmation, all stakeholders ack'd |
| 11 | Oracle DBA available for cutover window | Oracle DBA | Calendar hold confirmed |
| 12 | Business sponsor sign-off obtained | PM | Written (email) approval from CTO/Director |
| 13 | Cutover runbook reviewed by all core team members | All | Each person confirms they read the runbook |
| 14 | Rollback decision authority confirmed | PM | "If X happens, [person] has authority to call rollback" documented |
Checklist 6: Post-Cutover (First 48 Hours)
Purpose: Confirm that the migrated system is stable and Oracle is safely archived.
| # | Item | Owner | Time |
|---|---|---|---|
| 1 | Application error rate within normal bounds | App Eng | T+30 min |
| 2 | PostgreSQL write throughput matches expected (no bottleneck) | PG Eng | T+30 min |
| 3 | Zero application-reported data issues | App Eng | T+4 hours |
| 4 | PostgreSQL slow query log reviewed | PG Eng | T+24 hours |
| 5 | Rollback window closed (documented time) | Migration Eng | T+4 hours |
| 6 | Reverse replication pipeline stopped | Migration Eng | After rollback window closes |
| 7 | Oracle put into read-only mode | Oracle DBA | After rollback window closes |
| 8 | Staging ledger tables archived (pg_dump to cold storage) | PG Eng | T+48 hours |
| 9 | pulsaride-monitoring dashboard bookmarked for operations team | Migration Eng | T+2 hours |
| 10 | Alert thresholds adjusted for production operation (vs migration) | Migration Eng | T+24 hours |
| 11 | Oracle decommission date set | PM | T+48 hours |
| 12 | Migration retrospective scheduled | PM | T+48 hours (meeting at day 30) |