Implement RMAN/PITR, Data Guard, Monitoring, Auditing, Patching, and Incident Runbooks

Learning outcomes

A database can meet today's latency target and still be operationally unsafe if backups are never restored, audit alerts have no owner, and the “DR plan” is a diagram that no one has switched over. This lesson converts the capstone SLOs into concrete recovery, monitoring, audit, patch and incident controls.

1. Backup policy is a statement of recoverability

“Nightly backup” is not a recovery policy. A defensible policy states what is backed up, where copies live, encryption/key handling, retention, archive-log frequency, off-host/off-site failure independence, and which restore/PITR drills prove the RPO/RTO.

SELECT  log_mode,  flashback_on,  force_logging,  db_unique_nameFROM v$database;SELECT name,valueFROM v$parameterWHERE name IN (  'control_file_record_keep_time',  'db_recovery_file_dest',  'db_recovery_file_dest_size')ORDER BY name;

Database point-in-time recovery requires ARCHIVELOG and all required backup/redo history to the target point. Flashback Database can be faster for some logical errors but is not a replacement for independent backups.

2. RMAN backup/validation runbook

RMAN> SHOW ALL;RMAN> LIST BACKUP SUMMARY;RMAN> REPORT OBSOLETE;

RMAN> BACKUP DATABASE PLUS ARCHIVELOG      TAG 'SH29_BASELINE';RMAN> BACKUP CURRENT CONTROLFILE      TAG 'SH29_CONTROLFILE';RMAN> BACKUP SPFILE      TAG 'SH29_SPFILE';

If the Free lab database is not in ARCHIVELOG mode, do not run the PLUS ARCHIVELOG line blindly; either configure ARCHIVELOG in a disposable learning environment with a documented restart/change window or use a consistent offline backup procedure appropriate to the lab. Production RPO normally requires archived redo.

3. Validate database files and backup restorability

RMAN> VALIDATE CHECK LOGICAL DATABASE;

RMAN> RESTORE DATABASE VALIDATE;

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

VALIDATE checks database files; RESTORE ... VALIDATE reads the backups RMAN would use without writing restored datafiles. Neither proves that an isolated restored database can open and serve the application within the RTO. Lesson 5's restore drill record distinguishes validation from a real destructive restore exercise.

4. PITR runbook must name the recovery target and consequence

Database Point-in-Time Recovery (DBPITR) restores backups older than a target time/System Change Number (SCN) and applies redo only up to that target, then opens RESETLOGS. Changes after the target are intentionally abandoned for that recovered database incarnation. PDB PITR can limit the scope to one PDB, but still requires the correct backups/redo/auxiliary space and incarnation handling.

RMAN> RUN {  SET UNTIL SCN <approved_target_scn>;  RESTORE DATABASE;  RECOVER DATABASE;}SQL> ALTER DATABASE OPEN RESETLOGS;

5. Data Guard turns redo into a separate database failure domain

A physical Data Guard standby receives redo from the primary and applies it to another database. Transport lag contributes potential RPO exposure; apply lag affects how current the standby is. Protection mode (Maximum Performance/Availability/Protection) changes transport/durability behavior. A switchover is a planned role reversal; a failover promotes a standby after a primary failure and may require reinstating/recreating the old primary.

6. Source-side Data Guard readiness evidence

SELECT  database_role,  protection_mode,  protection_level,  switchover_status,  force_loggingFROM v$database;SELECT  dest_id,  status,  target,  destination,  errorFROM v$archive_dest_statusWHERE status <> 'INACTIVE'ORDER BY dest_id;

On the Free single-instance lab, DATABASE_ROLE remains PRIMARY and there is no real remote standby apply. That fact is useful evidence, not a reason to simulate V$DATAGUARD_STATS output as though it were real.

7. Entitled Data Guard broker runbook

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;

Before switchover, transport/apply should be healthy and lag within the SLO; services must be configured for the new role. After switchover, verify the new primary is read/write, former primary is applying redo, and application services/pools land on the correct role.

8. Simulate DR state and lag acceptance on Free

BEGIN EXECUTE IMMEDIATE  'DROP TABLE servicehub_owner.sh29_dr_state PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/CREATE TABLE servicehub_owner.sh29_dr_state (  member_name VARCHAR2(40) PRIMARY KEY,  role_name VARCHAR2(20) NOT NULL,  health_code VARCHAR2(12) NOT NULL,  last_primary_scn NUMBER,  last_applied_scn NUMBER,  transport_lag_seconds NUMBER,  apply_lag_seconds NUMBER,  CONSTRAINT sh29_dr_role_ck    CHECK (role_name IN ('PRIMARY','PHYSICAL_STANDBY')),  CONSTRAINT sh29_dr_health_ck    CHECK (health_code IN ('OK','WARNING','ERROR')));INSERT INTO servicehub_owner.sh29_dr_stateVALUES('SERVICEHUB_PRI','PRIMARY','OK',100000,100000,0,0);INSERT INTO servicehub_owner.sh29_dr_stateVALUES('SERVICEHUB_STBY','PHYSICAL_STANDBY','WARNING',       100000,99850,25,40);COMMIT;SELECT *FROM servicehub_owner.sh29_dr_stateWHERE health_code <> 'OK'   OR transport_lag_seconds > 0   OR apply_lag_seconds > 0;

This table is explicitly a runbook simulation. Production thresholds come from the approved RPO/RTO and measured redo/apply characteristics—not from the numbers above.

9. Monitoring rules derive from SLO and failure mechanics

CREATE TABLE servicehub_owner.sh29_monitor_rule (  rule_name VARCHAR2(60) PRIMARY KEY,  source_name VARCHAR2(80) NOT NULL,  condition_text VARCHAR2(1000) NOT NULL,  severity VARCHAR2(12) NOT NULL,  owner_team VARCHAR2(60) NOT NULL,  runbook_ref VARCHAR2(100) NOT NULL,  CONSTRAINT sh29_rule_sev_ck    CHECK (severity IN ('INFO','WARN','CRITICAL')));INSERT INTO servicehub_owner.sh29_monitor_rule VALUES(  'BLOCKING_SESSION_SLO',  'V$SESSION',  'Alert when a blocking wait exceeds the transaction latency budget; threshold must come from OLTP SLO.',  'CRITICAL','DB_PLATFORM','RB-LOCK-001');INSERT INTO servicehub_owner.sh29_monitor_rule VALUES(  'BACKUP_AGE_RPO',  'V$RMAN_BACKUP_JOB_DETAILS',  'Alert when recoverable backup/archivelog coverage would violate approved RPO.',  'CRITICAL','DB_PLATFORM','RB-RMAN-001');INSERT INTO servicehub_owner.sh29_monitor_rule VALUES(  'DG_LAG_RPO',  'V$DATAGUARD_STATS',  'On entitled standby, alert when transport/apply lag approaches the approved RPO/RTO budget.',  'CRITICAL','DB_PLATFORM','RB-DG-001');COMMIT;

The rule contains the mechanism and ownership; the numeric threshold belongs in deployment configuration after SLO approval.

10. Unified audit review is an operational process

SELECT  event_timestamp,  dbusername,  action_name,  object_schema,  object_name,  return_code,  client_program_nameFROM unified_audit_trailWHERE event_timestamp >= SYSTIMESTAMP - INTERVAL '1' DAYORDER BY event_timestamp DESCFETCH FIRST 100 ROWS ONLY;

Review failed privileged actions, unusual administrative sessions and high-value API access according to the security SLO. Archive/purge audit data through supported audit-management procedures; do not let audit retention exhaust the database.

11. RU maintenance plan continues Chapter 28

The current RU reference remains 23.26.3. Free itself is not supported for RU application, so a production patch calendar must target an entitled patchable edition/service. Use out-of-place Gold Image guidance, inventory every home/PDB, run datapatch where applicable, test application/backup/HA behavior, and retain the rollback home until acceptance gates close.

CREATE TABLE servicehub_owner.sh29_change_plan (  change_id VARCHAR2(40) PRIMARY KEY,  change_type VARCHAR2(20) NOT NULL,  target_state VARCHAR2(100) NOT NULL,  precheck_ref VARCHAR2(100) NOT NULL,  rollback_ref VARCHAR2(100) NOT NULL,  acceptance_ref VARCHAR2(100) NOT NULL);INSERT INTO servicehub_owner.sh29_change_plan VALUES(  'RU-23.26.3',  'RELEASE_UPDATE',  'All target homes/PDB SQL registries consistent at approved RU',  'RB-PATCH-PRECHECK',  'RB-PATCH-ROLLBACK',  'RB-PATCH-ACCEPT');COMMIT;

12. Incident runbook registry

CREATE TABLE servicehub_owner.sh29_runbook (  runbook_id VARCHAR2(40) PRIMARY KEY,  incident_type VARCHAR2(50) NOT NULL,  first_evidence VARCHAR2(1000) NOT NULL,  containment VARCHAR2(1000) NOT NULL,  recovery VARCHAR2(1000) NOT NULL,  escalation_owner VARCHAR2(80) NOT NULL);INSERT INTO servicehub_owner.sh29_runbook VALUES(  'RB-LOCK-001','BLOCKING_TRANSACTION',  'V$SESSION blocker/waiter, SQL_ID, module/action, transaction age',  'Stop new conflicting work if SLO at risk; do not kill blindly',  'Resolve owner/transaction, rollback/commit safely, then verify queue recovery',  'DB_PLATFORM');INSERT INTO servicehub_owner.sh29_runbook VALUES(  'RB-RMAN-001','MEDIA_OR_LOGICAL_RECOVERY',  'alert/ADR + RMAN repository + corruption view + incident target SCN/time',  'Protect surviving files/backups; stop destructive actions',  'Choose complete recovery, PITR, flashback or object-level method and execute tested procedure',  'DB_PLATFORM');INSERT INTO servicehub_owner.sh29_runbook VALUES(  'RB-PRIV-001','PRIVILEGE_INCIDENT',  'UNIFIED_AUDIT_TRAIL + DBA_TAB_PRIVS + credential/session inventory',  'Revoke/disable compromised privilege/credential and preserve audit evidence',  'Rotate/reissue minimum privilege, validate API/access and investigate blast radius',  'SECURITY');COMMIT;

13. Production judgment

Backup policy is accepted only after restore evidence; DR is accepted only after role-transition drills; monitoring is useful only when thresholds map to SLOs and every alert has an owner/runbook; auditing is useful only when reviewed; patching is complete only after binary/SQL/application/HA acceptance.

RMAN core validation is Free-compatible. Data Guard redo apply is not. No mandatory lesson uses AWR/ASH/ADDM or a paid HA feature. Lesson 5 now runs the drills and records whether the architecture actually meets the SLOs.

Authoritative references

• Backup and Recovery User's Guide — Validation — RMAN VALIDATE/RESTORE VALIDATE/corruption
• Database Point-in-Time Recovery — DB/PDB PITR prerequisites/workflow
• Data Guard Broker Switchover — planned role transitions
• Security Guide — Unified Auditing — audit review/policy management
• Licensing Information — RMAN/Data Guard/pack offering boundaries