Oracle Dba Storage

Oracle DBA - Space & Storage Management

Domain skill for capacity and storage operations. Loads oracle-dba-common first (_common/scripts/lib.sh / lib.ps1, _common/references/conventions.md). Max self-execute tier = T2. T3 is runbook-only.

Scope & risk map

Every task is listed with its level, tier, mechanism path, and idempotency. 15 tasks: 8 L1, 5 L2, 2 L3.

The 15 in-scope automatable tasks are the L1/L2 rows. The L3 rows are the destructive/structural escalation paths several T2 wrappers defer to (drop, shrink, ILM, undo-switch) — delivered as PRINT-only runbooks.

Preconditions

• SQLcl MCP saved connection dba_ai_conn reachable (wallet-backed; never SYS/SYSTEM unless a runbook explicitly needs SYSDBA). All in-DB SQL runs via connect_db / Connect-Db.
• Secrets resolve from the Oracle Wallet / external password store. No plaintext on argv/env/files anywhere. Storage paths and ASM disk-group names are NOT secrets.
• Helpers sourced from _common/scripts/lib.sh (Linux) / lib.ps1 (Windows). Every OS-level task ships a behaviour-equivalent .sh + .ps1 pair that passes a parse check.
• ASM exception: asm_disk_manage.{sh,ps1} are HOST operations against the ASM instance and cannot use connect_db (that helper targets the DB instance, and the SQLcl MCP server is host-restricted). They shell to sqlplus "/ as sysasm" with OS authentication only (no creds on argv/env; output redacted via _odb_redact). This is an intentional, documented exception — see references/version-notes.md §7.

Procedures

For each task:

• Trigger: a space/capacity symptom or an explicit request (see description).
• Precheck: precheck / Invoke-Precheck read-only guard (connection + object exists + capacity/safety, e.g. archivelog applied-to-standby & backed-up before any delete).
• Action: the scripts/<task>.{sh,ps1} wrapper. T2+ default to --dry-run (preview, change nothing); pass --execute to act. No long inline code lives here.
• Postcheck: postcheck / Invoke-Postcheck verifies the goal (e.g. file >= target, tablespace exists via anchored KEYVAL match, table in target TS with 0 UNUSABLE indexes, FRA reclaimable dropped).
• Rollback note: grow-only ops reverse by RESIZE-up / removing the freshly added file (that removal is the T3 drop-datafile/drop-tempfile runbook); undo_retention prints the previous value/RETENTION setting; reorg moves back to the original tablespace; ASM add reverses via the asm-disk-drop runbook. Destructive reversals go through runbooks.

Space-reclamation safety gate (archivelog / FRA / diag / log_rotate)

These T2 wrappers DELETE files, so they are gated harder than a normal T2:

• Within retention only. Deletion is permitted only for archived logs/backups that are already applied to all required standbys AND backed up, or RMAN-OBSOLETE within the configured retention policy. The read-only archivelog_status.sql / fra_usage.sql KEYVALs (arch_not_applied_to_standby, arch_unbacked_on_disk, fra_reclaimable_gb) are the precheck; if anything recovery-required would be touched, the wrapper refuses.
• Beyond retention / force-delete is T3 — never an unguarded rm/DELETE; escalate to a runbook + require_approval_token.
• diag_cleanup / log_rotate only age/size-prune ADR (trace/incident/cdump), the OS audit_file_dest .aud files, and the listener/alert logs — never datafiles/redo/backups.

Tier-3 runbooks

See references/runbooks.md. Anchored sections: #datafile-segment-shrink, #storage-tiering-ilm, #asm-disk-drop, #drop-datafile, #drop-tempfile, #undo-tablespace-switch. They PRINT exact, copy-pasteable 19c+23ai commands with a pre-flight checklist, diagnostics, verification, and rollback for a human operator, and NEVER self-execute — gated by require_approval_token / Require-ApprovalToken.

Scheduling (cron / Task Scheduler)

See cron/crontab.d (Linux) and cron/task-scheduler.xml (Windows). They schedule ONLY the safe, repeatable jobs: the T1 space health report (read-only) and the T2 reclamation wrappers (archivelog_cleanup, fra_reclaim, diag_cleanup, log_rotate) with --execute. The T2 add/resize/autoextend/reorg/defrag wrappers are operator-initiated (a real storage change is a decision) and are intentionally NOT timer-driven. NO T3 action (shrink, drop, ILM apply, undo switch) is ever scheduled — those stay runbook-only behind require_approval_token.

Version notes

See references/version-notes.md for 19c-vs-23ai deltas (online MOVE LOB/partition/IOT/ domain-index caveats, segment/datafile shrink, Heat Map/ADO availability + Advanced Compression licensing, temp/undo syntax, RMAN/ADRCI reclamation, and the ASM SYSASM OS-auth exception).