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

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.