Table Recovery, Immutable Backups and BYOL on Base Database Service
A financial-services firm runs a two-node RAC VM DB system on OCI Base Database Service, Oracle 19c, BYOL, 4 OCPUs per node and 1.5 TB of storage. Automatic backups are enabled with 30-day retention, and a Data Guard standby sits in a second region. Five days ago a release script ran a DELETE without a WHERE clause against a 200-million-row transaction table; the commit was at 14:07 and the mistake was noticed at 14:40. The application kept writing to other tables until the release was halted at 15:10. The recycle bin was disabled two years ago for space reasons, and undo retention is four hours. Compliance requires backups to be immutable for seven years and wants evidence that a restore was tested in the last 12 months. A full database restore takes about four hours and would lose 63 minutes of unrelated transactions.
1. What is the fastest way to recover the table with minimal impact, and what are the prerequisites?
Read the worked answer
Flashback options are out: FLASHBACK DROP needs a populated recycle bin, which was disabled, and FLASHBACK TABLE TO TIMESTAMP needs undo covering five days, while undo retention is four hours.
The right tool is RMAN RECOVER TABLE ... TO TIMESTAMP (or TO SCN) for 14:07. It reads the backups and archive logs, creates an auxiliary instance in a destination you specify, and imports the table or its partitions back into the live database, leaving the rest of the database online. Prerequisites: backups and archive logs covering 14:07 (the 30-day retention covers it), an auxiliary destination with space for 200 million rows plus indexes, and the table's dependent objects - indexes, constraints, triggers and grants - which must be re-created or re-enabled afterwards.
If RECOVER TABLE is not feasible because of space, the fallback is a point-in-time clone of the DB system to a new DB system at 14:07, a Data Pump export of the table, and an import. That takes longer but touches nothing in production.
One reconciliation step is easy to miss: rows inserted after 14:07 still exist in the current table because they were never deleted, so after restoring the 14:07 image, re-insert the rows newer than 14:07 from the current table and re-validate foreign keys.
2. How would you satisfy the seven-year immutability requirement and produce evidence of a tested restore?
Read the worked answer
The 30-day automatic backup retention is a recovery window, not an archive, and it will not satisfy the auditor.
Immutable archive: copy a periodic full backup plus the archive logs to an Object Storage bucket governed by a retention rule or Object Lock that prevents deletion or overwrite until seven years have passed, and place that bucket in a separate compartment with its own IAM policy so the DBA group cannot delete it. Keep a cross-region copy for durability and add a lifecycle rule to move objects to an archive tier after the initial period to control cost. Verify that immutability is actually enforced by attempting a delete as a DBA and recording that it failed.
Tested restore: a restore into the same DB system is not evidence. Schedule a quarterly restore into an isolated compartment, record the target SCN or timestamp, the elapsed time, and the validation queries run against the recovered data, and keep the runbook and results as the artefact. The 4-hour full restore time is itself the finding - document it against the recovery objectives so the business understands the real RTO.
3. The CIO asks whether the standby can be shut down between tests to reduce BYOL licensing. How do you answer?
Read the worked answer
No, and the reasoning matters more than the answer.
A Data Guard standby has to be running and applying redo to be a standby. Shut it down and redo accumulates at the primary; when you restart it, apply has to catch up on everything that accumulated, which for a busy OLTP system can take hours, and if the archive log gap on the primary is overwritten you are rebuilding the standby from scratch. During that window your RPO and RTO are both effectively undefined.
On licensing: BYOL requires that you hold current Oracle licenses for every OCPU you enable, including the standby, and for the options in use such as RAC, Data Guard and partitioning. You can reduce the standby's shape to reduce both cost and license consumption, but that also reduces failover capacity, and resizing a DB system is not instantaneous - the recovery objective has to absorb that.
What you can legitimately do is size the standby to a documented minimum that still meets the RTO, keep entitlements current, and make sure the same edition and options are licensed on both sides. Recommend documenting this before an Oracle audit rather than after.