Architecture
Reference architecture and the demo boundary
The layered design below is what I would build on a real Oracle estate. This application demonstrates the design, the metrics it would emit and the operating practice around it — it does not connect to Oracle, ODI, Informatica or GoldenGate.
End-to-end flow
Concept diagram ┌──────────────┐ ┌───────────────┐ ┌────────────────┐ ┌──────────────┐
│ Source feeds │──▶│ Landing (STG) │──▶│ Conform (PL/SQL)│──▶│ Serve (facts)│
└──────────────┘ └───────────────┘ └────────────────┘ └──────┬───────┘
watermark DQ gate SCD2 + MERGE │
▼
┌─────────────┐ ┌──────────────┐
│ Audit sink │◀──│ Replication │
└─────────────┘ └──────────────┘
write-once CDC / standbyLayer by layer
Demonstrated here
Simulated feed metadata, arrival-window checks and checkpoint semantics.
Production shape
External tables or an ODI/Informatica load plan writing to a landing schema with idempotent watermarks.
Demonstrated here
Annotated PL/SQL and MERGE patterns rendered as documentation, not executed.
Production shape
Versioned PL/SQL packages deployed through CI, orchestrated by ODI mappings or an equivalent scheduler.
Demonstrated here
Partitioning strategy, aggregate design and replica lag reporting.
Production shape
Range-partitioned facts with local indexes, refreshed aggregates and a reporting standby.
Demonstrated here
Lag budgets, drill outcomes and RPO/RTO targets against synthetic runs.
Production shape
GoldenGate-style CDC plus Data Guard-style standby, with scheduled restore validation.
Demonstrated here
Control catalogue, classification labels and a synthetic audit evidence trail.
Production shape
Unified auditing to a write-once sink, quarterly recertification and automated retention proofs.
How this application itself is built
Implementation status: all pages are complete and driven by typed synthetic fixtures. Deterministic quality, reconciliation, replication and backup helpers are unit tested. Live database connectivity, a real audit sink and scheduled orchestration are documented as architecture concepts and are deliberately out of scope for this portfolio lab.