Choosing Between Autonomous and a VM DB System for a Vendor-Constrained Estate
Metro Claims runs a 2 TB claims-processing application that is transactional during the day and drives a heavy reporting extract between 01:00 and 03:00. The vendor contract requires a named DBA to patch the database host on the vendor's schedule and to install a host-level monitoring agent; the current estate runs Oracle Database 19c with a standby in a second data centre. A separate development team needs an 8 OCPU environment only on weekdays from 09:00 to 18:00, and its data is disposable. The team proposes Autonomous Transaction Processing Serverless for everything because "it scales itself", but the DBA is worried about the vendor requirement and about the reporting spike driving OCPU autoscaling charges.
1. Recommend a database service for each of the three workloads (production OLTP, dev/test, reporting extract) and justify each choice against the constraints.
Read the worked answer
- Production claims database: VM DB System, not Autonomous. The vendor requirement to let a named DBA patch the host and install a host-level agent needs OS-level access. Autonomous Database (Serverless or Dedicated) does not expose the database host or operating system, so it fails the contract regardless of its scaling advantages. Run a VM DB System in the primary region with Data Guard to a standby in the second region, preserving today's behaviour.
- Development: Autonomous Transaction Processing Serverless. Data is disposable, the load is small and intermittent. Set 8 OCPUs, turn auto-scaling off so the ceiling is 8, and stop the instance outside 09:00-18:00 (compute billing stops; storage continues). Schedule start/stop (Resource Scheduler or a Functions-driven job) rather than relying on people remembering.
- Reporting extract: move it off the production database if the 01:00-03:00 job is analytic rather than transactional. Replicate or Data Pump into Autonomous Data Warehouse and let a columnar, auto-scaling service absorb the spike. This is the decision with the biggest cost effect, because it lets the production DB system be sized for OLTP alone.
- Record the exception in the design document: the vendor constraint is why production is not autonomous, so a future reviewer does not "modernise" it into a contract breach.
2. The DBA worries that the reporting spike will make Autonomous OCPU autoscaling expensive. Analyse the cost behaviour of both options.
Read the worked answer
- ADB Serverless bills per OCPU-second plus storage. With a base of 8 OCPUs and auto-scaling on, a two-hour nightly ramp to 24 OCPUs costs roughly the extra 16 OCPU-hours for those two hours, not 24 OCPUs continuously. That matches a predictable nightly spike almost perfectly.
- The risk is not the spike, it is an unbounded ceiling and an instance left running all weekend. Controls: disable auto-scaling where a hard ceiling matters, set the base to the true steady state, stop the instance when idle, and alert on daily OCPU-hours through a budget or a query on usage.
- A VM DB System bills for the provisioned shape 24/7, whether or not the extract runs. Sizing the production system for a two-hour job means paying for that headroom for roughly 720 hours a month.
- So compare on total monthly cost at real durations, not on list price per OCPU-hour. The conclusion: do not size the production DB system around the extract. Either offload the extract to ADW, or accept a longer extract window against the OLTP shape, and reserve elasticity for workloads that are genuinely bursty and disposable - like dev/test.
3. How would you design and validate the DR capability for the production system, given a second data centre already exists?
Read the worked answer
- Agree RPO and RTO with the business first (for example RPO 15 minutes, RTO 2 hours). The protection mode follows from that: Maximum Availability uses SYNC transport and supports near-zero data loss, but the primary can stall if the standby becomes unreachable; Maximum Performance uses ASYNC and keeps the primary available at the cost of a small RPO. Maximum Availability is defensible only once the inter-site latency and bandwidth are known to support it.
- Use Data Guard with Fast-Start Failover and place the observer outside both regions, so a site loss does not take the observer with it.
- Exercise it on a schedule: a switchover (planned, no data loss) quarterly and a failover (simulated primary loss) at least annually.
- Test without risking production by cloning from backup into a separate compartment for application-level drills, and validate the parts that always get forgotten: connection strings, DNS/TNS entries, the vendor agent on the standby host, and application failover behaviour.
- Record the measured RTO from the drill. An unmeasured RTO is a guess, and a standby that has never been failed over to is a configuration, not a recovery plan.