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

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.