Run Restore, Switchover/Failover, Corruption, Performance, Security, and Upgrade Drills—and Defend the Design

Learning outcomes

The final production review does not ask “Do we have backups, HA and monitoring?” It asks: when the last restore was performed, how long it took, how much committed work was lost, whether a standby role transition met the service objective, whether corruption/security/performance regressions were detected by the expected evidence, and whether an upgrade rollback remains executable. A design is defended by drills with timestamps and acceptance criteria.

1. Create a timestamped drill ledger

BEGIN EXECUTE IMMEDIATE  'DROP TABLE servicehub_owner.sh29_drill_log PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/CREATE TABLE servicehub_owner.sh29_drill_log (  drill_id VARCHAR2(50) PRIMARY KEY,  drill_type VARCHAR2(40) NOT NULL,  started_at TIMESTAMP WITH TIME ZONE NOT NULL,  ended_at TIMESTAMP WITH TIME ZONE,  target_rpo_seconds NUMBER,  observed_rpo_seconds NUMBER,  target_rto_seconds NUMBER,  observed_rto_seconds NUMBER,  result_code VARCHAR2(12) NOT NULL    CHECK (result_code IN ('RUNNING','PASSED','FAILED','SIMULATED')),  evidence VARCHAR2(2000) NOT NULL,  residual_risk VARCHAR2(2000));

Use wall-clock timestamps with time zone and define the incident start/“service restored” boundary before the drill. Do not subtract database startup time from RTO because it makes the result look better.

2. Restore/PITR drill: validation is step one, isolated restore is the proof

RMAN> VALIDATE CHECK LOGICAL DATABASE;RMAN> RESTORE DATABASE VALIDATE;

INSERT INTO servicehub_owner.sh29_drill_log(  drill_id,drill_type,started_at,ended_at,  result_code,evidence,residual_risk) VALUES(  'DRILL-RMAN-VALIDATE-001',  'RESTORE_VALIDATION',  SYSTIMESTAMP - INTERVAL '5' MINUTE,  SYSTIMESTAMP,  'PASSED',  'RMAN VALIDATE CHECK LOGICAL and RESTORE DATABASE VALIDATE completed; V$DATABASE_BLOCK_CORRUPTION reviewed.',  'Does not prove restored DB open/application RTO; isolated destructive restore still required.');COMMIT;

For a production acceptance drill, restore the backup into an isolated disposable environment, recover to the approved current/PITR target, open it, run application validation, record actual elapsed RTO and compare resulting SCN/data with RPO. Oracle Free allows only one installation per logical environment, so use a separate VM/container/host lifecycle rather than trying to run two Free installations simultaneously in one logical environment.

3. Data Guard switchover drill — real commands only on entitled topology

DGMGRL> SHOW CONFIGURATION;DGMGRL> SHOW DATABASE VERBOSE servicehub_pri;DGMGRL> SHOW DATABASE VERBOSE servicehub_stby;DGMGRL> VALIDATE DATABASE servicehub_stby;DGMGRL> SWITCHOVER TO servicehub_stby;DGMGRL> SHOW CONFIGURATION;

Measure from declared role-transition start until the application service is usable on the new primary and the former primary is healthy as a standby. Verify transport/apply state after the switch; a successful broker message alone is not the application RTO.

UPDATE servicehub_owner.sh29_dr_stateSET role_name =  CASE member_name    WHEN 'SERVICEHUB_PRI' THEN 'PHYSICAL_STANDBY'    WHEN 'SERVICEHUB_STBY' THEN 'PRIMARY'  END,  health_code='OK',  transport_lag_seconds=0,  apply_lag_seconds=0;INSERT INTO servicehub_owner.sh29_drill_log(  drill_id,drill_type,started_at,ended_at,  result_code,evidence,residual_risk) VALUES(  'DRILL-DG-SIM-001','DATA_GUARD_SWITCHOVER',  SYSTIMESTAMP - INTERVAL '2' MINUTE,SYSTIMESTAMP,  'SIMULATED',  'Local state-machine role swap only; no redo transport/apply or service relocation executed.',  'Must be replaced by real DGMGRL drill before claiming Data Guard RTO.');COMMIT;

4. Failover drill is not the same acceptance test

A failover begins with a failed/unreachable primary rather than a cooperative switchover. The drill must record potential data loss against transport state, promotion time, application redirection, and old-primary reinstatement/rebuild. Current broker runbooks verify configuration after failover and can leave the former primary disabled until reinstated.

DGMGRL> SHOW CONFIGURATION;DGMGRL> VALIDATE DATABASE servicehub_stby;DGMGRL> FAILOVER TO servicehub_stby;DGMGRL> SHOW CONFIGURATION;-- Then REINSTATE DATABASE ... if Flashback/conditions permit.

5. Corruption drill: detect safely; do not manufacture file corruption

RMAN> VALIDATE CHECK LOGICAL DATABASE;

SELECT  file#,block#,blocks,corruption_type,corruption_change#FROM v$database_block_corruptionORDER BY file#,block#;

A clean result proves no corruption detectable by that validation was found at that time; it does not prove storage can never corrupt. In a staging restore, use documented block-media recovery only on real detected blocks. Do not hex-edit production datafiles to create a training incident.

INSERT INTO servicehub_owner.sh29_drill_log(  drill_id,drill_type,started_at,ended_at,  result_code,evidence,residual_risk) VALUES(  'DRILL-CORRUPTION-001','CORRUPTION_DETECTION',  SYSTIMESTAMP - INTERVAL '4' MINUTE,SYSTIMESTAMP,  'PASSED',  'RMAN logical/physical validation completed and V$DATABASE_BLOCK_CORRUPTION reviewed.',  'Detection test only; recovery technique depends on actual corruption scope/type.');COMMIT;

6. Performance-regression drill: force a known worse access path, then remove the fault

SELECT /*+ gather_plan_statistics */       COUNT(*)FROM servicehub_owner.sh29_work_ordersWHERE tenant_code='TENANT_A'  AND created_at >= TIMESTAMP '2026-01-03 00:00:00'  AND created_at <  TIMESTAMP '2026-01-03 00:10:00';SELECT *FROM TABLE(  DBMS_XPLAN.DISPLAY_CURSOR(    NULL,NULL,'ALLSTATS LAST +PREDICATE'  ));

SELECT /*+ gather_plan_statistics FULL(w) */       COUNT(*)FROM servicehub_owner.sh29_work_orders wWHERE tenant_code='TENANT_A'  AND created_at >= TIMESTAMP '2026-01-03 00:00:00'  AND created_at <  TIMESTAMP '2026-01-03 00:10:00';SELECT *FROM TABLE(  DBMS_XPLAN.DISPLAY_CURSOR(    NULL,NULL,'ALLSTATS LAST +PREDICATE +NOTE'  ));

The FULL hint is an intentional fault injection for this query only. Compare buffers/elapsed work with the normal optimizer choice, then remove the hint. A production regression drill can instead use a controlled staging statistic/index/application change and verify alerting plus rollback.

INSERT INTO servicehub_owner.sh29_drill_log(  drill_id,drill_type,started_at,ended_at,  result_code,evidence,residual_risk) VALUES(  'DRILL-PERF-001','PERFORMANCE_REGRESSION',  SYSTIMESTAMP - INTERVAL '3' MINUTE,SYSTIMESTAMP,  'PASSED',  'Forced FULL plan captured and compared with normal cursor plan; fault removed by removing hint.',  'Synthetic single-query drill; production needs concurrent load and p95/p99 application telemetry.');COMMIT;

7. Security drill: intentionally create an excessive grant, detect it, revoke it

GRANT UPDATEON servicehub_owner.sh29_work_ordersTO sh29_app;SELECT  grantee,owner,table_name,privilegeFROM dba_tab_privsWHERE grantee='SH29_APP'  AND table_name='SH29_WORK_ORDERS';

The architecture invariant from Lesson 2 says runtime writes go through the package. The direct UPDATE grant is therefore a security incident even if no attacker used it.

REVOKE UPDATEON servicehub_owner.sh29_work_ordersFROM sh29_app;SELECT  grantee,owner,table_name,privilegeFROM dba_tab_privsWHERE grantee='SH29_APP'  AND table_name='SH29_WORK_ORDERS';

INSERT INTO servicehub_owner.sh29_drill_log(  drill_id,drill_type,started_at,ended_at,  result_code,evidence,residual_risk) VALUES(  'DRILL-SEC-001','PRIVILEGE_INCIDENT',  SYSTIMESTAMP - INTERVAL '2' MINUTE,SYSTIMESTAMP,  'PASSED',  'Unexpected direct UPDATE privilege detected via DBA_TAB_PRIVS and revoked; package EXECUTE path retained.',  'Real incident also requires credential/session rotation and audit blast-radius review.');COMMIT;

8. Upgrade/RU rollback drill: test the decision gate without pretending to patch Free

SELECT banner_fullFROM v$versionWHERE banner_full LIKE 'Oracle%';SHOW PARAMETER compatibleSELECT  patch_id,action,status,target_version,action_timeFROM dba_registry_sqlpatchORDER BY action_time DESC;

Free cannot receive supported RUs, so the local drill rehearses the control plane: old/new home inventory, backup/restore validation, application smoke gates, COMPATIBLE unchanged, and the decision to return service to the prior home before incompatible adoption.

CREATE TABLE servicehub_owner.sh29_upgrade_drill_gate (  gate_name VARCHAR2(60) PRIMARY KEY,  pass_flag CHAR(1) CHECK (pass_flag IN ('Y','N')),  rollback_action VARCHAR2(1000) NOT NULL);INSERT INTO servicehub_owner.sh29_upgrade_drill_gate VALUES(  'COMPATIBLE_UNCHANGED','Y',  'Keep COMPATIBLE unchanged until new release acceptance closes.');INSERT INTO servicehub_owner.sh29_upgrade_drill_gate VALUES(  'OLD_HOME_RETAINED','Y',  'Switch service/database startup back to validated previous home if supported rollback is invoked.');INSERT INTO servicehub_owner.sh29_upgrade_drill_gate VALUES(  'APP_SMOKE','N',  'Reject cutover and execute rollback runbook; investigate regression before retry.');COMMIT;SELECT *FROM servicehub_owner.sh29_upgrade_drill_gateWHERE pass_flag='N';

The deliberately failed APP_SMOKE gate proves the organization can stop/roll back a change instead of forcing acceptance because maintenance time is expiring.

9. Architecture defense: convert drills into claims

SELECT  d.drill_type,  d.result_code,  d.target_rpo_seconds,  d.observed_rpo_seconds,  d.target_rto_seconds,  d.observed_rto_seconds,  d.evidence,  d.residual_riskFROM servicehub_owner.sh29_drill_log dORDER BY d.started_at;

A SIMULATED Data Guard drill cannot be cited as measured production switchover RTO. A RESTORE VALIDATE drill cannot be cited as actual restore RTO. The architecture defense must distinguish tested, simulated and still-unproven claims.

10. Residual-risk register

CREATE TABLE servicehub_owner.sh29_residual_risk (  risk_id VARCHAR2(40) PRIMARY KEY,  risk_text VARCHAR2(1000) NOT NULL,  current_control VARCHAR2(1000) NOT NULL,  next_evidence VARCHAR2(1000) NOT NULL,  priority_code VARCHAR2(10) NOT NULL    CHECK (priority_code IN ('HIGH','MEDIUM','LOW')));INSERT INTO servicehub_owner.sh29_residual_risk VALUES(  'RISK-DR-001',  'Site-level role-transition RTO/RPO is not measured in the Free lab.',  'Data Guard architecture/runbook and local state simulation only.',  'Build entitled staging standby and run switchover + failover drills with application service timing.',  'HIGH');INSERT INTO servicehub_owner.sh29_residual_risk VALUES(  'RISK-RMAN-001',  'RMAN restore was validated but not opened as an isolated restored ServiceHub database in this lab.',  'VALIDATE/RESTORE VALIDATE plus backup inventory.',  'Perform isolated full/PDB restore, application validation and timed PITR.',  'HIGH');INSERT INTO servicehub_owner.sh29_residual_risk VALUES(  'RISK-CAP-001',  'Free hardware/resource limits cannot establish production capacity.',  'Repeatable SQL/workload/timing harness.',  'Run representative concurrent workload on target production-class hardware and size failure headroom.',  'HIGH');COMMIT;

11. Final design defense

The defensible ServiceHub design is not “Oracle 26ai with all features.” It is:

• Measurable SLOs and workload classes with explicit evidence owners.
• A constrained relational schema and PL/SQL API with least privilege and focused auditing.
• Indexes/partitioning justified by query/retention evidence.
• Performance baselines using runtime plans, waits, time model and application latency.
• RMAN backup/validation plus a required isolated restore/PITR drill before claiming recovery RTO.
• Data Guard/RAC/sharding only when the corresponding RPO/RTO/availability/locality requirement justifies license/topology complexity.
• Current RU/change runbooks with COMPATIBLE separated from software patch/upgrade.
• Explicit residual risks rather than unsupported HA/performance promises.

12. Optional final cleanup

Keep the capstone objects if you want to continue operating and rerun drills. If this was a disposable lab, clean up in dependency order only after exporting any evidence you want to retain.

NOAUDIT POLICY sh29_api_audit BY sh29_app;DROP AUDIT POLICY sh29_api_audit;DROP USER sh29_app CASCADE;DROP PACKAGE servicehub_owner.sh29_work_order_api;DROP TABLE servicehub_owner.sh29_upgrade_drill_gate PURGE;DROP TABLE servicehub_owner.sh29_residual_risk PURGE;DROP TABLE servicehub_owner.sh29_drill_log PURGE;DROP TABLE servicehub_owner.sh29_runbook PURGE;DROP TABLE servicehub_owner.sh29_change_plan PURGE;DROP TABLE servicehub_owner.sh29_monitor_rule PURGE;DROP TABLE servicehub_owner.sh29_dr_state PURGE;DROP TABLE servicehub_owner.sh29_perf_run PURGE;DROP TABLE servicehub_owner.sh29_schema_release PURGE;DROP TABLE servicehub_owner.sh29_work_order_events PURGE;DROP TABLE servicehub_owner.sh29_work_orders PURGE;DROP TABLE servicehub_owner.sh29_customers PURGE;DROP TABLE sh29_arch_decisions PURGE;DROP TABLE sh29_slo PURGE;

13. Production judgment and course close

A production Oracle design is a set of falsifiable claims: latency under defined load, recoverability from defined failures, acceptable data loss, least privilege, auditable changes and rehearsed role/restore procedures. The final lesson deliberately leaves Data Guard and true restored-open RTO as residual risks when the Free environment cannot prove them. That is stronger engineering than inventing evidence.

Generation baseline is Oracle AI Database 26ai RU 23.26.3, SQL Developer 26.2 and SQLcl 26.2.1.222.1617. No COMPATIBLE increase, hidden parameter, unsupported Free RU patch, real file corruption or paid HA topology was executed. Use the course curriculum to revisit any mechanism whose drill or architecture evidence remains incomplete.

Authoritative references

• RMAN Validation — restore/corruption drill evidence
• Data Guard Broker Switchover — planned role-transition mechanism
• Data Guard Broker Manual Failover — failover/reinstate consequences
• Execution Plan Runtime Evidence — performance regression evidence
• Oracle AI Database Licensing Information — final feature/pack/HA entitlement map