# Oracle Dba Patching Upgrades

> USE THIS SKILL WHENEVER the user mentions patching or upgrading Oracle: Release Updates (RU/RUR), one-off / interim patches, OPatch / opatchauto / datapatch, OJVM patches, patch conflicts / merge / napply / superset patches, patch rollback, Grid Infrastructure (GI) patching, rolling RAC patching, Data Guard standby-first patching, OS / host patch and reboot coordination, DST / time-zone file upgrades, database version upgrades (19c -> 23ai), AutoUpgrade (autoupgrade.jar analyze/fixups/deploy), DBUA / manual catupgrd, Fleet Patching & Provisioning (FPP / rhpctl), or cross-version / out-of-place migration — EVEN IF they do not name the specific tool. Covers read-only patch/upgrade observability and readiness reports, OPatch prereq/conflict analysis, datapatch SQL reconcile (+ rollback note), DST PREPARE analysis, AutoUpgrade analyze/fixups, OS-patch quiesce/resume coordination, and the plan-only Tier-3 runbooks for every destructive apply/upgrade/migration. Follows the oracle-dba-common contracts: SQLcl MCP sav

- Skill: `shreyas70773/oracle-dba-patching-upgrades` (Agent Skill, multi-file: 28 files)
- Install (CLI): `npx skillmds@latest add shreyas70773/oracle-dba-patching-upgrades`
- Raw SKILL.md: https://api.skillmd.com/api/skills/shreyas70773/oracle-dba-patching-upgrades/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: DevOps & Infra
- Author: Shreyas70773 (https://skillmd.com/u/shreyas70773)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/shreyas70773/oracle-dba-patching-upgrades

---

# Oracle DBA - Patching & Upgrades

Patch level observability, pre-patch readiness & conflict analysis, the reversible SQL /
DST / OS-coordination steps, and the plan-only Tier-3 runbooks for every destructive apply,
upgrade, rollback, and migration on 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 **binary apply, GI/RAC patch, DST upgrade, OS reboot, version upgrade,
AutoUpgrade deploy, patch rollback, FPP move, conflict resolution, and cross-version
migration is T3** and lives only in `references/runbooks.md` — it PRINTS commands for a
human and never self-executes. See `references/version-notes.md` for 19c-vs-23ai deltas.

## Scope & risk map

| Task | Level | Tier | Mechanism (script / sql / runbook path) | Idempotent? |
|------|-------|------|------------------------------------------|-------------|
| Patch level / datapatch status report | L1 | T1 | `scripts/checks/patch_status.sh` / `.ps1` + `sql/patch_inventory.sql` + `sql/registry_patch_status.sql` | yes — read-only (opatch lsinventory + in-DB registry; flags home-vs-DB mismatch) |
| Patch conflict / prereq DETECTION | L1 | T1 | `scripts/checks/patch_conflict_check.sh` / `.ps1` | yes — read-only opatch prereq analyzers; same verdict each run |
| FPP inventory query | L1 | T1 | `scripts/checks/fpp_query.sh` / `.ps1` | yes — read-only `rhpctl query` only |
| Pre-patch readiness snapshot | L1 | T1 | `sql/patch_readiness.sql` (RU/GI/OS window) | yes — read-only; KEYVAL summary |
| DG patch readiness snapshot | L1 | T1 | `sql/dg_patch_readiness.sql` (run on primary + standby) | yes — read-only |
| Upgrade readiness snapshot (19c->23ai) | L1 | T1 | `sql/upgrade_readiness.sql` | yes — read-only |
| DST version / readiness report | L1 | T1 | `sql/dst_status.sql` | yes — read-only |
| Post-change verification | L1 | T1 | `sql/post_change_verify.sql` (drives every T3 verify step) | yes — read-only; KEYVAL pass/fail |
| OPatch prereq / conflict analysis (RU/GI/OJVM) | L2 | T2 | `scripts/opatch_prereq_check.sh` / `.ps1` (GO/NO-GO) | yes — analysis only; touches inventory lock + logs, no patch state |
| datapatch SQL reconcile (RU / one-off / OJVM) | L2 | T2 | `scripts/datapatch_apply.sh` / `.ps1` + `sql/datapatch_verify.sql` | yes — datapatch reports "Nothing to apply/rollback" => noop; warns if no GRP anchor |
| DST PREPARE analysis | L2 | T2 | `scripts/dst_prepare.sh` / `.ps1` + `sql/dst_prepare.sql` | yes — ends a stale PREPARE first; changes DST window state + populates sys.dst$ tables; no app TSTZ data |
| AutoUpgrade analyze / fixups (safe modes) | L2 | T2 | `scripts/autoupgrade_analyze.sh` / `.ps1` (`--mode analyze\|fixups`) | yes — analyze repeatable; fixups no-op already-satisfied checks; config regenerated deterministically |
| OS-patch quiesce / resume coordination | L2 | T2 | `scripts/os_patch_coordination.sh quiesce\|resume\|status` / `.ps1` + `sql/patch_readiness.sql` | yes — stop-when-down / start-when-up / existing GRP => noop; SYSDBA fallback gated to `#os-patch` |
| **RU binary apply** | L2 | **T3** | `references/runbooks.md#ru-apply` | no — modifies ORACLE_HOME binaries (opatch apply); reverse via opatch rollback + datapatch -rollback |
| **Grid Infrastructure patching** | L2 | **T3** | `references/runbooks.md#gi-patch` | no — root-run opatchauto/rootcrs; cluster-wide |
| **Rolling RU on RAC** | L3 | **T3** | `references/runbooks.md#rolling-patch-rac` | no — node-by-node HA; mixed-binary window |
| **Standby-first / DG patching** | L3 | **T3** | `references/runbooks.md#standby-first-patch` + `sql/dg_patch_readiness.sql` | no — includes switchover |
| **DST time-zone UPGRADE** | L2 | **T3** | `references/runbooks.md#dst-apply` | no — rewrites TSTZ data; backout = FLASHBACK |
| **OS patch / host reboot** (SYSDBA stop/start) | L2 | **T3** | `references/runbooks.md#os-patch` | partial — stop/start reversible; the reboot itself is the sysadmin's |
| **AutoUpgrade deploy** | L3 | **T3** | `references/runbooks.md#autoupgrade` + `sql/upgrade_readiness.sql` | no — irreversible in-place upgrade; restore via AutoUpgrade -mode restore |
| **Database version upgrade 19c->23ai** (manual) | L3 | **T3** | `references/runbooks.md#upgrade-19c-23ai` | no — catupgrd/dbupgrade; backout = FLASHBACK + source home |
| **Fleet Patching & Provisioning ops** (rhpctl move/add) | L3 | **T3** | `references/runbooks.md#fpp` | no — out-of-place move; the old home is the backout |
| **Patch conflict resolution** (merge/napply/rollback) | L3 | **T3** | `references/runbooks.md#patch-conflict` | no — binary rollback/merge/napply |
| **Patch rollback** (datapatch -rollback / opatch rollback) | L2 | **T3** | `references/runbooks.md#patch-rollback` | partial — SQL rollback reversible; FLASHBACK is last resort |
| **Cross-version / out-of-place migration** | L3 | **T3** | `references/runbooks.md#xversion-migration` | no — one-shot; source left intact as the backout |

## 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` (e.g. `STARTUP UPGRADE`, `DBMS_DST.*_UPGRADE`,
  `SHUTDOWN`/`STARTUP`), which the operator supplies **interactively** — never the default,
  never auto-executed.
- **Secrets resolve from the Oracle Wallet / external password store. No plaintext anywhere.**
  OPatch / opatchauto / datapatch / AutoUpgrade / srvctl / rhpctl authenticate via OS / local
  bequeath / wallet — no `user/pass` is ever passed. Args that look like a credential are
  rejected with `ERR_SECRET` (8). `ODB_*` env inputs (homes, SID, patch ids) are paths/ids,
  never secrets.
- **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/patching/patching.log` (Linux) /
  `C:/ProgramData/oracle-dba/logs/patching/patching.log` (Windows); skill segment `patching`.
- **T2 dry-run default**: every T2 script previews and changes nothing unless given
  `--execute`. Anchor every patch/upgrade window with a **guaranteed restore point** first
  (`backup-and-recovery/scripts/restore_point.sh create` or `os_patch_coordination quiesce`)
  and confirm a recent RMAN backup — that restore point is the universal FLASHBACK backout.

## 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 header / `--help`.

### Patch level + datapatch status (T1)
- **Trigger**: routine drift check; investigating a home-vs-DB patch mismatch.
- **Precheck**: none (read-only).
- **Action**: `scripts/checks/patch_status.sh` / `.ps1` (+ `sql/patch_inventory.sql`, `sql/registry_patch_status.sql`).
- **Postcheck**: n/a — emits the OS home patch level + in-DB registry; flags mismatch.
- **Rollback note**: none — read-only.

### Patch conflict / prereq detection + OPatch GO/NO-GO (T1 detect, T2 analyze)
- **Trigger**: before any RU/one-off apply.
- **Precheck**: patch unzipped to a directory the analyzers can read.
- **Action**: `scripts/checks/patch_conflict_check.sh` / `.ps1` (T1 detect) and
  `scripts/opatch_prereq_check.sh --execute` / `.ps1` (T2 GO/NO-GO verdict).
- **Postcheck**: verdict = GO and no conflicts/space failures before proceeding to `#ru-apply`.
- **Rollback note**: none — analysis only; touches the inventory lock + logs, no patch state.

### datapatch SQL reconcile (T2)
- **Trigger**: after an RU/one-off/OJVM **binary** apply (`#ru-apply`/`#gi-patch`) to reconcile the SQL registry.
- **Precheck**: datapatch binary present; DB OPEN READ WRITE (PDBs open); **advisory** warning if no guaranteed restore point exists.
- **Action**: `scripts/datapatch_apply.sh --execute` / `.ps1` (dry-run runs `datapatch -prereq` + registry report).
- **Postcheck**: `sql/datapatch_verify.sql` returns `datapatch_verdict=PASS`.
- **Rollback note**: `datapatch -rollback <patch_id> -verbose` (in-window, reversible) then
  `sql/post_change_verify.sql`; FLASHBACK to the restore point if the registry cannot be
  recovered. Full steps + token gate: `references/runbooks.md#patch-rollback`.

### DST PREPARE analysis (T2)
- **Trigger**: after an OS DST patch stages newer TZ files; before the T3 DST upgrade.
- **Precheck**: `sql/dst_status.sql` (current vs available DSTv; no in-flight UPGRADE window).
- **Action**: `scripts/dst_prepare.sh --execute` / `.ps1` -> `sql/dst_prepare.sql` (PREPARE
  analysis; changes the DST window state + populates `sys.dst$` analysis tables; no app data).
- **Postcheck**: `dst_prepare_verdict=READY` (zero error rows) for the target version.
- **Rollback note**: PREPARE is reversible (END_PREPARE; analysis tables only). The actual
  TSTZ UPGRADE is T3 — `references/runbooks.md#dst-apply` (backout = FLASHBACK).

### AutoUpgrade analyze / fixups (T2)
- **Trigger**: pre-upgrade validation before the T3 deploy.
- **Precheck**: `autoupgrade.jar` (from the TARGET home), java, and required `ODB_AU_*` env present.
- **Action**: `scripts/autoupgrade_analyze.sh --execute --mode analyze|fixups` / `.ps1`
  (deploy/upgrade are refused -> `#autoupgrade`).
- **Postcheck**: AutoUpgrade status shows no ERROR/FATAL; `sql/upgrade_readiness.sql` clean.
- **Rollback note**: analyze is read-only; fixups are reversible pre-fixes. The deploy and its
  restore are T3 — `references/runbooks.md#autoupgrade` (`-mode restore` / FLASHBACK).

### OS-patch quiesce / resume / status (T2)
- **Trigger**: bracket an OS patch + host reboot window owned by the sysadmin.
- **Precheck**: `status` (T1) report; ARCHIVELOG mode for the restore-point anchor.
- **Action**: `scripts/os_patch_coordination.sh quiesce|resume --execute` / `.ps1`
  (Grid `srvctl` stop/start self-executes; the **SYSDBA `sqlplus` stop/start fallback is NOT
  auto-executed** — it is gated by `require_approval_token` and routed to `#os-patch`).
- **Postcheck**: quiesce asserts the instance is DOWN (not reachable as OPEN) before host
  hand-off; resume asserts OPEN READ WRITE + runs `sql/patch_readiness.sql`.
- **Rollback note**: `resume` restores service; FLASHBACK to the anchored restore point is the
  DB-side backout if the host does not return clean. Drop the restore point only after sign-off.

## Tier-3 runbooks

All destructive apply / upgrade / rollback / HA / migration work is **Tier 3** and lives in
**`references/runbooks.md`**: `#ru-apply`, `#gi-patch`, `#rolling-patch-rac`,
`#standby-first-patch`, `#dst-apply`, `#os-patch`, `#autoupgrade`, `#upgrade-19c-23ai`,
`#fpp`, `#patch-conflict`, `#patch-rollback`, `#xversion-migration`.

These runbooks **PRINT exact, copy-pasteable OPatch/opatchauto/datapatch/AutoUpgrade/
dgmgrl/rhpctl/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). Each runbook carries a pre-flight checklist (linking `sql/patch_readiness.sql`,
`sql/dg_patch_readiness.sql`, `sql/upgrade_readiness.sql`), exact 19c **and** 23ai commands,
diagnostics, verification (`sql/post_change_verify.sql`), and an explicit rollback
(restore point FLASHBACK / `datapatch -rollback` / `opatch`/`opatchauto rollback` /
AutoUpgrade restore). SYSDBA is a T3-only, interactively-supplied exception. When in doubt,
runbook. See `references/version-notes.md` for 19c-vs-23ai behavioural deltas.