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

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).