# Oracle Dba Backup And Recovery

> USE THIS SKILL WHENEVER the user mentions RMAN, backups, restore, recover/recovery, Data Pump, expdp/impdp, archivelog, FRA / fast recovery area, retention policy, crosscheck, obsolete/expired backups, recovery catalog, control file or spfile autobackup, restore points, flashback, PITR / point-in-time recovery, TSPITR, block media recovery / block corruption, DR drills, cross-platform CONVERT, or any of the errors ORA-01578 (block corruption), ORA-19xxx (RMAN/backup I/O), or RMAN-xxxxx — EVEN IF they do not name the specific task. Covers scheduled RMAN full/incremental/archivelog backups, RMAN CONFIGURE, backup status monitoring and VALIDATE, crosscheck+delete within retention, recovery-catalog maintenance, Data Pump export/import, guaranteed restore point create/list/drop, and (plan-only, human-driven) recovery runbooks. Follows the oracle-dba-common contracts: SQLcl MCP saved connection dba_ai_conn, Oracle Wallet secrets (never plaintext), --dry-run defaults on T2, and tier gating (max self-execute = T2; al

- Skill: `shreyas70773/oracle-dba-backup-and-recovery` (Agent Skill, multi-file: 20 files)
- Install (CLI): `npx skillmds@latest add shreyas70773/oracle-dba-backup-and-recovery`
- Raw SKILL.md: https://api.skillmd.com/api/skills/shreyas70773/oracle-dba-backup-and-recovery/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: AI & ML
- Author: Shreyas70773 (https://skillmd.com/u/shreyas70773)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/shreyas70773/oracle-dba-backup-and-recovery

---

# Oracle DBA - Backup & Recovery

Physical backups (RMAN), logical backups (Data Pump), backup verification &
lifecycle (validate / crosscheck / catalog), guaranteed restore points, and the
plan-only Tier-3 recovery runbooks for Oracle 19c and 23ai (EE; single-instance
and Data Guard; on-prem). Everything self-executing is **T1 (observe)** or
**T2 (reversible, dry-run-default)**. Every actual **restore/recover is T3** and
lives only in `references/runbooks.md` — it prints commands for a human and never
self-executes.

## Scope & risk map

| Task | Level | Tier | Mechanism (script / sql / runbook path) | Idempotent? |
|------|-------|------|------------------------------------------|-------------|
| RMAN scheduled full (level-0) backup + archivelog + CF autobackup | L1 | T2 | `scripts/rman_full_backup.sh` / `.ps1` -> `rman/full.rman` | yes — noop if a COMPLETED DB FULL/INCR exists within `--window-hours` (default 20h) |
| RMAN incremental backup (level 0/1, differential/cumulative, IUB) | L1 | T2 | `scripts/rman_incremental_backup.sh` / `.ps1` -> `rman/incr.rman` | yes — noop if matching COMPLETED incr/level-0 exists within window |
| RMAN archivelog backup (+ optional input reclaim) | L1 | T2 | `scripts/rman_archivelog_backup.sh` / `.ps1` -> `rman/arch.rman` | yes — noop when no archived logs have `backup_count=0` |
| RMAN persistent CONFIGURE (retention, parallelism, compression, encryption-aware) + CF/spfile autobackup | L2 | T2 | `scripts/configure_rman.sh` / `.ps1` -> `rman/configure.rman` | yes — CONFIGURE is declarative; RMAN no-ops unchanged values; diffs `SHOW ALL` |
| Backup job monitoring & verification | L1 | T1 | `scripts/checks/check_backup_status.sh` / `.ps1` + `sql/backup_status.sql` | yes — read-only (emits metrics + findings, never mutates) |
| Backup validation (RESTORE…VALIDATE / VALIDATE BACKUPSET, optional CHECK LOGICAL) | L2 | T2 | `scripts/validate_backup.sh` / `.ps1` | yes — validation has no side effects; postcheck = empty `V$DATABASE_BLOCK_CORRUPTION` |
| Crosscheck & delete obsolete/expired within retention | L2 | T2 | `scripts/crosscheck_delete.sh` / `.ps1` | yes — previews crosscheck+report first; noop when nothing obsolete/expired; never `DELETE FORCE` |
| Recovery-catalog maintenance (resync / register / purge) | L2 | T2 | `scripts/catalog_maintenance.sh` / `.ps1` (`--action resync\|register\|purge\|all`) + `sql/catalog_status.sql` | yes — REGISTER probes RMAN-20001 then no-ops; RESYNC repeatable |
| Data Pump export (logical backup) | L2 | T2 | `scripts/datapump_export.sh` / `.ps1` + `sql/datapump_validate_directory.sql` (pre) + `sql/datapump_export_status.sql` (post) | yes — noop if today's dump already exists (`REUSE_DUMPFILES=NO`) |
| Data Pump import | L2 | T2 | `scripts/datapump_import.sh` / `.ps1` + `sql/datapump_validate_directory.sql` (pre) + `sql/datapump_export_status.sql` (post) | yes — `TABLE_EXISTS_ACTION=SKIP` default; TRUNCATE/REPLACE need `--execute` + `--confirm-overwrite` |
| Guaranteed restore point — create | L2 | T2 | `scripts/restore_point.sh create` / `.ps1 create` + `sql/restore_point_create.sql` | yes — existing RP name -> noop |
| Guaranteed restore point — list | L2 | T1 | `scripts/restore_point.sh list` / `.ps1 list` + `sql/restore_point_list.sql` | yes — read-only |
| Guaranteed restore point — drop | L2 | T2 | `scripts/restore_point.sh drop` / `.ps1 drop` + `sql/restore_point_drop.sql` | yes — missing RP -> noop; needs `--execute` + `--confirm-drop` |
| Point-in-time recovery (DB-level PITR) | recovery | **T3** | `references/runbooks.md#pitr` | no — rewinds DB; OPEN RESETLOGS makes a new incarnation |
| Table-level recovery (`RECOVER TABLE`) | recovery | **T3** | `references/runbooks.md#table-recover` | no — one-shot (REMAP form is retryable/non-destructive) |
| Complete media recovery (RESTORE/RECOVER DATABASE) | recovery | **T3** | `references/runbooks.md#media-recover` | no — forward-only roll-forward |
| Block media recovery (`RECOVER … BLOCK`) | recovery | **T3** | `references/runbooks.md#block-recover` | partial — re-run after clean repair finds no blocks |
| Tablespace point-in-time recovery (TSPITR) | recovery | **T3** | `references/runbooks.md#tspitr` | no — rewinds tablespace set; invalidates prior backups |
| Disaster recovery restore (alternate host / DG failover) | recovery | **T3** | `references/runbooks.md#dr-restore` | no — failover not trivially reversible |
| Cross-platform backup/restore (`CONVERT`, endianness) | recovery | **T3** | `references/runbooks.md#xplatform` | no — one-shot migration (CONVERT itself is abort-safe) |
| Recovery testing / DR drill | recovery | **T3** | `references/runbooks.md#dr-drill` | partial — VALIDATE/PREVIEW repeatable; scratch-host restore is one-shot |
| `DELETE FORCE` (escape hatch) | recovery | **T3** | `references/runbooks.md#delete-force` | n/a — refused by `crosscheck_delete` |
| Create / drop recovery catalog (escape hatch) | recovery | **T3** | `references/runbooks.md#create-catalog`, `references/runbooks.md#drop-catalog` | n/a — refused by `catalog_maintenance` |

## Preconditions

- **SQLcl MCP connection `dba_ai_conn`** is reachable and wallet-backed. All in-DB
  prechecks, postchecks, idempotency probes, and `sql/*.sql` run through it via
  `connect_db` / `Connect-Db`. Never SYS/SYSTEM — except inside a T3 runbook that
  explicitly states `AS SYSDBA`, supplied interactively by the operator.
- **Secrets resolve from the Oracle Wallet / external password store. No plaintext
  anywhere.** RMAN connects with OS/wallet auth: `$ODB_RMAN_TARGET` (default `/`,
  or `@alias`) and `$ODB_RMAN_CATALOG` are wallet aliases, never `user/pass`.
  `expdp`/`impdp` connect via `/@<alias>` (default `dba_ai_conn`). A target/alias
  that looks like a password is rejected with `ERR_SECRET` (8).
- **Helpers are sourced from `_common`** (`scripts/lib.sh` / `lib.ps1`): `connect_db`,
  `log_event`, `emit_metric`, `require_dry_run`, `guard_blocked_dry_run`,
  `require_approval_token`, `precheck`, `postcheck`, `print_banner`. Not reinvented.
- **Logging**: structured `key=value` lines to the canonical paths
  `/var/log/oracle-dba/backup/backup.log` (Linux) /
  `C:/ProgramData/oracle-dba/logs/backup/backup.log` (Windows); skill segment `backup`
  for backup/verification/Data Pump/restore-point work and `recovery` for T3 runbooks.
- **T2 dry-run default**: every T2 script previews and changes nothing unless given
  `--execute`. Encryption is transparent TDE-wallet only (no key material in files).
  Block Change Tracking, ACO (compression), and a recovery catalog are optional.

## Procedures

Scripts ship as behaviour-equivalent `.sh` (Linux) / `.ps1` (Windows) pairs. T2
scripts default to `--dry-run`; pass `--execute` to act. See each script's `--help`.

### RMAN full (level-0) backup
- **Trigger**: weekly level-0 base for the incremental chain; new-DB baseline.
- **Precheck**: ARCHIVELOG mode, FRA headroom, no concurrent same-type job in window.
- **Action**: `scripts/rman_full_backup.sh --execute` / `.ps1 --execute` -> `rman/full.rman` (add `--standby` to offload on a physical standby).
- **Postcheck**: COMPLETED `DB FULL/INCR` job in `v$rman_backup_job_details`; AUTOBACKUP captured.
- **Rollback note**: a backup adds files only — nothing to undo. Reclaim via `crosscheck_delete`.

### RMAN incremental backup
- **Trigger**: nightly on non-full nights (`--level 1`), or `--iub` for incrementally-updated image copies.
- **Precheck**: a valid level-0 parent exists; ARCHIVELOG + FRA headroom; no duplicate in window.
- **Action**: `scripts/rman_incremental_backup.sh --execute --level 1 [--cumulative] [--iub]` / `.ps1` -> `rman/incr.rman`.
- **Postcheck**: matching COMPLETED incremental in `v$rman_backup_job_details`.
- **Rollback note**: additive; no undo. Chain integrity verified by `validate_backup`.

### RMAN archivelog backup
- **Trigger**: frequent (e.g. every 30 min) to bound RPO and relieve FRA.
- **Precheck**: archived logs with `backup_count=0` exist (else noop).
- **Action**: `scripts/rman_archivelog_backup.sh --execute [--delete-input]` / `.ps1` -> `rman/arch.rman`.
- **Postcheck**: logs now show `backup_count>=1`; `--delete-input` only removes already-backed-up logs per deletion policy.
- **Rollback note**: deleted input logs are recoverable from the just-written backup; restore via `#media-recover`.

### RMAN CONFIGURE (+ control file / spfile autobackup)
- **Trigger**: drift-correct retention/parallelism/compression/encryption/autobackup; just before the weekly full.
- **Precheck**: diff desired (env tunables `ODB_RETENTION/PARALLELISM/DEVTYPE/COMPRESS/ENCRYPTION/TOPOLOGY`) vs current `SHOW ALL`.
- **Action**: `scripts/configure_rman.sh --execute` / `.ps1` -> `rman/configure.rman` (set `ODB_TOPOLOGY=data_guard` for APPLIED-ON-STANDBY deletion policy).
- **Postcheck**: `SHOW ALL` matches desired; `CONTROLFILE AUTOBACKUP` is ON.
- **Rollback note**: re-run with prior env values; CONFIGURE is declarative and reversible.

### Backup status monitoring (T1)
- **Trigger**: daily, shortly after the backup window; investigating a stale/failed backup.
- **Precheck**: none (read-only).
- **Action**: `scripts/checks/check_backup_status.sh [--max-db-age-hours N --max-arch-age-hours N]` / `.ps1` + `sql/backup_status.sql`.
- **Postcheck**: n/a — emits KEYVAL metrics + threshold findings (non-fatal).
- **Rollback note**: none — read-only.

### Backup validation
- **Trigger**: prove restorability without restoring; after suspected corruption.
- **Precheck**: backups exist for the requested scope.
- **Action**: `scripts/validate_backup.sh --execute --mode database|spfile|controlfile|backupset [--check-logical]` / `.ps1`.
- **Postcheck**: `V$DATABASE_BLOCK_CORRUPTION` is empty.
- **Rollback note**: none — non-destructive. Corruption found -> escalate to `#block-recover` / `#media-recover`.

### Crosscheck & delete obsolete/expired
- **Trigger**: weekly off-peak reclaim within the configured retention policy.
- **Precheck**: always runs CROSSCHECK + REPORT OBSOLETE first; noop if nothing obsolete/expired.
- **Action**: `scripts/crosscheck_delete.sh --execute [--scope both --device all]` / `.ps1`. `--force` is refused -> `#delete-force`.
- **Postcheck**: REPORT OBSOLETE empty after delete; only OBSOLETE/EXPIRED removed.
- **Rollback note**: deletions are bounded by retention; only redundant copies are removed. Re-backup if needed.

### Recovery-catalog maintenance
- **Trigger**: daily RESYNC; one-time REGISTER of a new target; periodic PURGE (only when `ODB_RMAN_CATALOG` alias is set).
- **Precheck**: catalog reachable; REGISTER probes RMAN-20001 to detect already-registered.
- **Action**: `scripts/catalog_maintenance.sh --action resync|register|purge|all --execute` / `.ps1`. CREATE/DROP/UNREGISTER -> `#create-catalog`/`#drop-catalog`.
- **Postcheck**: target appears in `RC_DATABASE`; metadata current.
- **Rollback note**: RESYNC/REGISTER are additive; UNREGISTER/DROP are T3 runbook-only.

### Data Pump export (logical backup)
- **Trigger**: nightly schema or weekly full logical copy; pre-change snapshot; cross-version/platform move.
- **Precheck**: `sql/datapump_validate_directory.sql` validates the DIRECTORY object and path.
- **Action**: `scripts/datapump_export.sh --execute --directory DIR --mode schemas|full [--schemas A,B] [--parallel N] [--compression METADATA_ONLY] [--flashback-time now]` / `.ps1`.
- **Postcheck**: `sql/datapump_export_status.sql` confirms the job COMPLETED in `DBA_DATAPUMP_JOBS`.
- **Rollback note**: export is read-only on the DB; delete the dump file to undo on disk.

### Data Pump import
- **Trigger**: object-level recovery from a dump; refresh a target schema.
- **Precheck**: `sql/datapump_validate_directory.sql`; dump file present.
- **Action**: `scripts/datapump_import.sh --execute --directory DIR …` / `.ps1`. Default `TABLE_EXISTS_ACTION=SKIP`; TRUNCATE/REPLACE require `--execute` + `--confirm-overwrite` (`-ConfirmOverwrite`).
- **Postcheck**: `sql/datapump_export_status.sql`; spot-check imported objects/rows.
- **Rollback note**: SKIP/APPEND are non-destructive; before TRUNCATE/REPLACE take a restore point (below) or export the target first. Post-import recovery -> `#table-recover`/`#pitr`.

### Guaranteed restore point — create / list / drop
- **Trigger**: pin a known-good point before a risky change (create); audit FRA pressure (list); release space (drop).
- **Precheck**: create requires ARCHIVELOG + configured FRA (`db_recovery_file_dest`); flashback DB need NOT be ON.
- **Action**: `scripts/restore_point.sh create|list|drop NAME` / `.ps1`. `list` is T1 (immediate). `drop` requires `--execute` + `--confirm-drop` (`-ConfirmDrop`).
- **Postcheck**: `sql/restore_point_list.sql` shows expected presence/absence in `V$RESTORE_POINT`.
- **Rollback note**: a restore point IS the rollback anchor — flashback to it is a T3 recovery (`#pitr`). Dropping a GUARANTEED RP irreversibly frees pinned flashback space.

## Tier-3 runbooks

All **restore and recovery** is Tier 3 and lives in **`references/runbooks.md`**:
`#pitr`, `#table-recover`, `#media-recover`, `#block-recover`, `#tspitr`,
`#dr-restore`, `#xplatform`, `#dr-drill`, plus the escape hatches `#delete-force`,
`#create-catalog`, `#drop-catalog`.

These runbooks **PRINT exact RMAN/SQL commands for a human operator and NEVER
self-execute.** They are gated by `require_approval_token` / `Require-ApprovalToken`:
an automated agent's only sanctioned action is to print the relevant section and
stop with `ERR_APPROVAL` (6) until a human supplies a real change ticket/token
(`--token <TICKET>` or `ODB_APPROVAL_TOKEN`; placeholders are rejected). The T2
scripts deliberately refuse the destructive escape hatches (`DELETE FORCE`,
`CREATE`/`DROP`/`UNREGISTER CATALOG`) and point to the runbook instead. SYSDBA is a
T3-only, interactively-supplied exception. When in doubt, runbook.

See `references/version-notes.md` for 19c-vs-23ai behavioural deltas.