Oracle Dba Common

Oracle DBA - Common Foundation

This skill defines the contracts that every oracle-dba-<domain> skill MUST obey. It ships no end-user task of its own; it is imported by all others.

Target environment (ground truth):

• OS: Linux (.sh) AND Windows (.ps1) — both produced for every OS-level task.
• Oracle: 19c and 23ai (Enterprise Edition). Topology: single_instance and Data Guard. On-prem.
• In-DB work: SQLcl MCP server, saved named connection dba_ai_conn.
• Secrets: Oracle Wallet / external password store. Never plaintext.

1. Connection contract

All in-database actions go through the SQLcl MCP server using the saved named connection dba_ai_conn.

Rules:

1. Connect ONLY by name: sql -name dba_ai_conn (CLI) or the MCP connect/run-sql tools targeting the saved connection dba_ai_conn. The connection's credentials live in the SQLcl/Oracle Wallet store — they are resolved at connect time.
2. Never connect as SYS or SYSTEM unless a specific runbook explicitly requires SYSDBA (e.g. media recovery, STARTUP MOUNT). When a runbook needs SYSDBA it must say so and the operator supplies that privileged connection interactively — it is never the default and never auto-executed.
3. Never hardcode usernames, passwords, connect strings, or wallet paths in SQL, scripts, argv, environment variables, or files.
4. The helper connect_db (in scripts/lib.sh / scripts/lib.ps1) is the only sanctioned way to open a session. It refuses to run if a password-looking value is passed to it.

Privilege model: dba_ai_conn is a least-privilege DBA-role account. SYSDBA is an exception path, gated behind T3 runbooks.

2. Secret resolution (Oracle Wallet — no plaintext ever)

Secrets resolve from the Oracle Wallet / external password store only.

• DB auth: the connect string in dba_ai_conn maps to a wallet credential (mkstore/external password store or auto-login wallet cwallet.sso). SQLcl resolves it; no password is ever typed or stored by us.
• For any other secret (e.g. backup catalog, OS account for rman), use the wallet entry referenced by alias — never the literal secret.
• FORBIDDEN, with no exceptions:
  • passwords on the command line (sql user/pass@db, rman target sys/pass),
  • passwords in environment variables,
  • passwords written to log files, temp files, or config files,
  • echo-ing secrets, or interpolating them into structured log lines.
• connect_db and log_event actively scrub/refuse anything that looks like a credential. If you find yourself needing a plaintext secret, STOP and add a wallet entry instead.

3. Structured logging contract

Every script emits one structured log line per significant event via log_event.

Format — key=value pairs, space-separated, machine-greppable:

ts=<ISO8601-UTC> skill=<skill-name> task=<task-id> tier=<T1|T2|T3> dry_run=<true|false> outcome=<ok|noop|fail|blocked> [key=value ...] msg="<free text>"

• ts is ISO-8601 UTC (e.g. 2026-06-03T14:22:05Z).
• outcome vocabulary: ok (acted, success), noop (already-done / idempotent skip), fail (error), blocked (guard refused: missing --execute or approval token).
• Extra metrics ride along as additional key=value pairs or via emit_metric.
• Secrets are NEVER logged. log_event redacts values that match credential patterns.

Canonical log paths

• Linux: /var/log/oracle-dba/<skill>/
• Windows: C:/ProgramData/oracle-dba/logs/<skill>/

<skill> is the domain skill name (e.g. backup, recovery). The log file is <skill>.log inside that directory (e.g. /var/log/oracle-dba/backup/backup.log). Helpers create the directory if missing (idempotent).

4. Exit-code convention

0 is the ONLY success code. All failure modes use documented non-zero codes so callers and schedulers can branch deterministically. Never swallow an error.

Exit codes 5 and 6 are "blocked" outcomes, not crashes — they are the safety guards firing as designed. The helper library exports these as named constants.

5. Risk-tier banner + autonomy policy

Every .sh/.ps1 MUST begin (after the shebang) with a one-line tier banner comment so a human can see the blast radius at a glance:

# TIER: T2 | REVERSIBLE | requires --dry-run preview (default) ; --execute to act

Banner grammar: # TIER: <T1|T2|T3> | <OBSERVE|REVERSIBLE|DESTRUCTIVE> | <mechanism note>

At runtime the script also prints the banner to stderr and records tier= in logs.

Autonomy policy (hard rule; max self-execute tier = T2)

• T1: monitoring, health/space/AWR reports, backup-status queries.
• T2: scheduled backups, RMAN CONFIGURE, crosscheck+delete WITHIN retention, Data Pump export, create restore point, gather stats.
• T3: any restore/recover (PITR, media, TSPITR, block, DR, cross-platform), Flashback Database, switchover/failover, drops.

When in doubt, runbook. If a task cannot be made safe at or below T2, do not ship an auto-executing script — downgrade it to a printed runbook for a human.

6. How to use this from a domain skill

1. Read references/conventions.md and follow the canonical SKILL.md template verbatim.
2. In every script, source the shared helpers — do not reimplement them:
   • Bash: . "$(dirname "$0")/../../_common/scripts/lib.sh"
   • PowerShell: . "$PSScriptRoot/../../_common/scripts/lib.ps1"
3. Use: connect_db, log_event, require_dry_run, require_approval_token, precheck, postcheck, emit_metric.
4. Record any 19c-vs-23ai behavioural delta in your skill's references/version-notes.md.