# Oracle Dba Routine Maintenance

> USE THIS SKILL WHENEVER the user mentions routine / housekeeping DBA maintenance: recompiling INVALID objects (utlrp / UTL_RECOMP), purging old trace / alert / incident files (ADR / adrci), the standard daily/weekly health-check report, sequence cache tuning (row-cache / SQ enqueue / "sequence" waits), index rebuild / coalesce, partition add / drop / split / merge maintenance, materialized-view refresh, PURGE RECYCLEBIN, AWR snapshot / retention management, optimizer stats-history purge, or Unified/standard audit-trail purge (DBMS_AUDIT_MGMT) — EVEN IF they do not name the specific task. Covers Oracle 19c and 23ai, EE, single-instance and Data Guard, on-prem. Follows the oracle-dba-common contracts: in-DB work via the SQLcl MCP saved connection dba_ai_conn, Oracle Wallet secrets (never plaintext, never SYS/SYSTEM), --dry-run default on every T2 script (explicit --execute to act), structured logging, and tier gating (max self-execute = T2; structural DROP/SPLIT/MERGE/EXCHANGE and big segment moves are T3 runbo

- Skill: `shreyas70773/oracle-dba-routine-maintenance` (Agent Skill, multi-file: 37 files)
- Install (CLI): `npx skillmds@latest add shreyas70773/oracle-dba-routine-maintenance`
- Raw SKILL.md: https://api.skillmd.com/api/skills/shreyas70773/oracle-dba-routine-maintenance/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-routine-maintenance

---

# Oracle DBA - Routine Maintenance

Housekeeping that keeps a database healthy: recompiling invalid objects, purging
diagnostic files and history (trace/alert, recyclebin, AWR, stats history, audit
trail), the standard health-check report, sequence-cache tuning, and the reversible
side of index / partition / materialized-view maintenance — for Oracle 19c and 23ai
(EE; single-instance and Data Guard; on-prem). Everything self-executing here is
**T1 (observe, read-only)** or **T2 (reversible, dry-run-default)**. **Structural**
changes — partition `SPLIT`/`MERGE`/`DROP`/`EXCHANGE`, index `DROP`/`UNUSABLE`/
partition-level rebuild, and large segment/table moves (online redefinition / shrink)
— are **T3** and live only in `references/runbooks.md`; they print commands for a human
and never self-execute.

In-DB tasks run their SQL through the SQLcl MCP saved connection `dba_ai_conn`
("sqlcl-mcp" routing) via the `connect_db` / `Connect-Db` helper. The single host/OS
task (ADR file purge) cannot go through SQLcl MCP (host commands are out of scope for
that server, restrict level 4) so it runs as a generated `.sh`/`.ps1` ("script"
routing, a candidate to later front the guarded `oracle-dba-ops` MCP).

## Scope & risk map

| Task | Level | Tier | Mechanism (script / sql / runbook path) | Idempotent? |
|------|-------|------|------------------------------------------|-------------|
| Recompile invalid objects (utlrp / UTL_RECOMP) | L1 | T2 | `scripts/recompile_invalid.sh` / `.ps1` -> `sql/recompile_invalid.sql` | yes — 0 INVALID -> RECOMP_PARALLEL no-ops; re-run never double-applies |
| Old trace / alert / incident purge (ADR, host op) | L1 | T2 | `scripts/purge_trace_logs.sh` / `.ps1` (adrci, no SQL) | yes — nothing older than retention age -> adrci PURGE removes nothing (noop) |
| Standard health-check report | L1 | T1 | `scripts/checks/health_check.sh` / `.ps1` + `sql/health_check.sql` | yes — read-only (emits KEYVAL metrics + threshold findings, never mutates) |
| Sequence cache / order maintenance | L1 | T2 | `scripts/sequence_maintenance.sh` / `.ps1` + `sql/sequence_maintenance.sql` | yes — sequence already at desired CACHE/ORDER -> no-op (ALTER skipped) |
| Index rebuild / coalesce (single, non-partitioned, ONLINE) | L2 | T2 | `scripts/index_maintenance.sh` / `.ps1` + `sql/index_maintenance.sql` | yes — COALESCE/REBUILD ONLINE re-runnable; partitioned/DROP refused -> `#index-structural` |
| Partition maintenance — ADD (range) only | L2 | T2 | `scripts/partition_maintenance.sh` / `.ps1` + `sql/partition_maintenance.sql` | yes — existing/covering partition or INTERVAL table -> no-op |
| Partition maintenance — DROP / SPLIT / MERGE / EXCHANGE | L2 | **T3** | `references/runbooks.md#partition-structural` | no — data-moving / data-loss structural change; refused by the T2 script |
| Materialized view refresh (single MV, atomic) | L2 | T2 | `scripts/mview_refresh.sh` / `.ps1` + `sql/mview_refresh.sql` | yes — refresh re-derives from base tables; safe to re-run |
| Purge recyclebin (user / dba scope) | L2 | T2 | `scripts/purge_recyclebin.sh` / `.ps1` + `sql/purge_recyclebin.sql` | yes — empty recyclebin -> purged=0 no-op |
| AWR snapshot / retention management | L2 | T2 | `scripts/awr_retention.sh` / `.ps1` + `sql/awr_retention.sql` | yes — retention/interval already at target -> MODIFY skipped; snapshot is additive |
| Optimizer stats-history purge | L2 | T2 | `scripts/stats_history_purge.sh` / `.ps1` + `sql/stats_history_purge.sql` | yes — nothing older than retention floor -> purge no-op; never purges inside window |
| Audit trail purge (Unified / AUD$ / FGA, bounded) | L2 | T2 | `scripts/audit_trail_purge.sh` / `.ps1` + `sql/audit_trail_purge.sql` | yes — nothing older than cutoff -> 0 rows removed (noop); bounded by last-archive timestamp |
| Index DROP / make UNUSABLE / partition-level rebuild | structural | **T3** | `references/runbooks.md#index-structural` | n/a — refused by `index_maintenance` |
| Segment / table reorg / shrink / MOVE (space reclamation) | structural | **T3** | `references/runbooks.md#segment-reorg` | n/a — not exposed as a T2 script (DBMS_REDEFINITION / MOVE / SHRINK) |

## Preconditions

- **SQLcl MCP connection `dba_ai_conn`** is reachable and wallet-backed. Every in-DB
  precheck, postcheck, idempotency probe, and `sql/*.sql` runs 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. The connection
  user must hold the privileges each task needs (e.g. `PURGE DBA_RECYCLEBIN`,
  `DBMS_AUDIT_MGMT`, `DBMS_WORKLOAD_REPOSITORY`), granted out of band per site policy.
- **Secrets resolve from the Oracle Wallet / external password store. No plaintext
  anywhere** — not in argv, env, files, or SQL. An argument that looks like
  `user/pass@db` or `password=` is rejected with `ERR_SECRET` (8). `adrci`/`--adr-home`
  is a path, never a credential.
- **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/routine-maintenance/routine-maintenance.log` (Linux) /
  `C:/ProgramData/oracle-dba/logs/routine-maintenance/routine-maintenance.log` (Windows).
- **T2 dry-run default**: every T2 script previews and changes nothing unless given
  `--execute`. The deletion-class tasks (recyclebin / stats-history / audit purge)
  additionally print exactly what they *would* remove in dry-run so a human can object.
- **Data Guard**: AWR settings/snapshots and audit/stats purges run on the **PRIMARY**
  only (a physical standby is read-only / read-mostly). The health check is safe on a
  standby (read-only). Recompile/MV-refresh/sequence/index/partition changes are DML/DDL
  -> primary only.
- **Licensing**: AWR (`awr_retention`) requires the Diagnostic Pack; the health check's
  AWR-adjacent reporting is metadata-only and license-safe.

## Procedures

Scripts ship as behaviour-equivalent `.sh` (Linux) / `.ps1` (Windows) pairs. Every T2
script defaults to `--dry-run`; pass `--execute` to act. See each script's `--help`.
The trigger->precheck->action->postcheck->rollback shape is uniform; details below.

### Recompile invalid objects (utlrp)
- **Trigger**: after a patch/datapatch, DDL wave, import, or when the health check
  reports `invalid_objects > 0`.
- **Precheck**: `dba_ai_conn` reachable; counts INVALID objects (noop if 0).
- **Action**: `scripts/recompile_invalid.sh --execute [--degree N]` / `.ps1 -Degree N` -> `sql/recompile_invalid.sql` (UTL_RECOMP.RECOMP_PARALLEL — the utlrp engine; no SYSDBA/`@?` needed).
- **Postcheck**: `invalid_after <= invalid_before`; lists any still-invalid objects to investigate.
- **Rollback note**: none needed — recompilation only re-derives code from stored source; it changes no data. A still-invalid object is simply recompiled again.

### Old trace / alert / incident purge (ADR)
- **Trigger**: ADR / `diag` filesystem filling; scheduled weekly housekeeping.
- **Precheck**: at least one ADR home is visible to `adrci`.
- **Action**: `scripts/purge_trace_logs.sh --execute [--trace-age-days 30] [--alert-age-days 90] [--adr-home PATH]` / `.ps1`. Host op (adrci), not SQLcl.
- **Postcheck**: adrci still reports a valid home after purge (ADR not corrupted).
- **Rollback note**: purged files are diagnostics only; if needed they are regenerated by future incidents. adrci PURGE is age-bounded and never touches the database or current incidents.

### Standard health-check report (T1)
- **Trigger**: daily/weekly status; first response to any "is the DB OK?" question.
- **Precheck**: none (read-only).
- **Action**: `scripts/checks/health_check.sh` / `.ps1` + `sql/health_check.sql`.
- **Postcheck**: n/a — emits KEYVAL metrics (invalid objects, tablespace pressure, recyclebin, alerts, DG lag) + threshold findings.
- **Rollback note**: none — read-only.

### Sequence cache / maintenance
- **Trigger**: row-cache / `SQ` enqueue / "sequence" contention; standard RAC-friendly cache sizing.
- **Precheck**: sequence exists; compares current CACHE/ORDER to desired.
- **Action**: `scripts/sequence_maintenance.sh --owner APP --name ORDERS_SEQ --cache 1000 [--order NOORDER] --execute` / `.ps1` -> `sql/sequence_maintenance.sql`.
- **Postcheck**: `DBA_SEQUENCES` shows the requested CACHE/ORDER.
- **Rollback note**: `ALTER SEQUENCE ... CACHE` does not reset NEXTVAL and loses no data; ALTER back to the prior cache to reverse.

### Index rebuild / coalesce
- **Trigger**: leaf-block bloat after heavy DML; reclaim/compact a single non-partitioned index.
- **Precheck**: index exists and is **not partitioned** (partitioned -> `#index-structural`).
- **Action**: `scripts/index_maintenance.sh --owner APP --name ORDERS_PK --action coalesce|rebuild [--parallel N] --execute` / `.ps1` -> `sql/index_maintenance.sql` (REBUILD is always `ONLINE`).
- **Postcheck**: index `STATUS = VALID/USABLE`.
- **Rollback note**: COALESCE is in-place online; ONLINE REBUILD swaps atomically and the old index stays usable throughout — structurally unchanged. DROP/UNUSABLE are T3.

### Partition maintenance (ADD only)
- **Trigger**: pre-create the next range partition ahead of incoming data.
- **Precheck**: table is RANGE-partitioned and **not** INTERVAL (INTERVAL auto-creates -> noop); target partition absent.
- **Action**: `scripts/partition_maintenance.sh --owner APP --table SALES --name P_2026_07 --high-value "TO_DATE('2026-08-01','YYYY-MM-DD')" --execute` / `.ps1` -> `sql/partition_maintenance.sql`.
- **Postcheck**: new partition present in `DBA_TAB_PARTITIONS`.
- **Rollback note**: a freshly-ADDed partition is empty — DROP the still-empty partition to reverse. SPLIT/MERGE/DROP/EXCHANGE of populated partitions are T3 -> `#partition-structural`.

### Materialized view refresh
- **Trigger**: stale MV after base-table changes; scheduled refresh outside an automatic refresh group.
- **Precheck**: MV exists; reports current staleness/last-refresh.
- **Action**: `scripts/mview_refresh.sh --owner APP --name SALES_MV [--method ?|F|C|P] --execute` / `.ps1` -> `sql/mview_refresh.sql` (`atomic_refresh => TRUE`).
- **Postcheck**: `DBA_MVIEWS.STALENESS = FRESH`.
- **Rollback note**: a refresh re-derives the MV from base data and never alters base tables; a wrong/partial refresh is corrected by refreshing again.

### Purge recyclebin
- **Trigger**: reclaim space pinned by already-dropped objects; FRA/tablespace pressure from recyclebin segments.
- **Precheck**: counts recyclebin objects (empty -> noop); dry-run reports what would be purged.
- **Action**: `scripts/purge_recyclebin.sh --scope user|dba --execute` / `.ps1` -> `sql/purge_recyclebin.sql`.
- **Postcheck**: recyclebin object count drops as expected.
- **Rollback note**: objects in the recyclebin are ALREADY dropped; purging only frees their space. The only loss is the ability to `FLASHBACK ... TO BEFORE DROP` those objects — so dry-run + human review precede `--execute`.

### AWR snapshot / retention management
- **Trigger**: tune AWR retention/interval to policy; capture a manual baseline snapshot.
- **Precheck**: Diagnostic Pack licensed; on PRIMARY; reads current `DBA_HIST_WR_CONTROL`.
- **Action**: `scripts/awr_retention.sh --action settings --retention-days 14 --interval-min 30 --execute` (or `--action snapshot`) / `.ps1` -> `sql/awr_retention.sql`.
- **Postcheck**: `DBA_HIST_WR_CONTROL` reflects requested retention/interval.
- **Rollback note**: re-run with the prior retention/interval to reverse a settings change; a manual snapshot is additive. Dropping a snapshot range is out of scope (runbook).

### Optimizer stats-history purge
- **Trigger**: SYSAUX growth from `WRI$_OPTSTAT_*`; automatic SMON purge falling behind.
- **Precheck**: reads current retention + oldest-available; refuses to purge inside the retention window.
- **Action**: `scripts/stats_history_purge.sh --action purge|set-retention --retention-days 31 --execute` / `.ps1` -> `sql/stats_history_purge.sql`.
- **Postcheck**: oldest-available stats history moves to the boundary.
- **Rollback note**: history is the rollback buffer behind `DBMS_STATS.RESTORE_TABLE_STATS`. Purging only removes the ability to restore stats older than retention; CURRENT stats are untouched. Never purges newer than the window.

### Audit trail purge
- **Trigger**: Unified/AUD$/FGA audit trail growing in SYSAUX/AUDSYS; compliance-bounded retention.
- **Precheck**: reports total rows and rows older than the cutoff; dry-run shows the exact removable count.
- **Action**: `scripts/audit_trail_purge.sh --retention-days 90 [--trail-type AUDIT_TRAIL_UNIFIED] [--no-archive-ts] --execute` / `.ps1` -> `sql/audit_trail_purge.sql` (`DBMS_AUDIT_MGMT`, bounded by last-archive timestamp).
- **Postcheck**: total audit rows drop; nothing newer than the cutoff is removed.
- **Rollback note**: audit deletion can be irreversible, so dry-run + human review precede `--execute`. The purge is bounded by the last-archive timestamp so it can never delete audit newer than the requested window.

## Tier-3 runbooks

**Structural maintenance** is Tier 3 and lives in **`references/runbooks.md`**:
`#partition-structural` (DROP / SPLIT / MERGE / EXCHANGE partition),
`#index-structural` (index DROP / make UNUSABLE / partition-level rebuild), and
`#segment-reorg` (online redefinition / `ALTER TABLE ... MOVE` / `SHRINK SPACE` for
space reclamation). These runbooks **PRINT exact SQL 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 SQL
deliberately refuses these structural operations (partitioned-index rebuild, non-ADD
partition ops, DROP) and points 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, and `cron/`
for schedulable T1/T2 jobs (Linux `crontab.d`, Windows `task-scheduler.xml`).