Oracle Dba Decommissioning Lifecycle End

Oracle DBA - Decommissioning & Lifecycle End

The orderly, evidence-driven retirement of an Oracle database for Oracle 19c and 23ai (EE; single_instance and Data Guard; on-prem). Everything self-executing is T1 (observe, read-only) or T2 (reversible, dry-run-default): the final KEEP backup, the orderly park (clean shutdown is reversible via STARTUP), the reversible schema quarantine (lock + expire, no drop), the logical archive export, and the read-only lifecycle reports. Every irreversible end-state - DROP DATABASE, PDB unplug+drop, DROP USER ... CASCADE, retention purge, secure destruction, license reclamation - is T3 and lives only in references/runbooks.md: it prints exact commands for a human and never self-executes. When in doubt, runbook.

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 / AS SYSKM (drop, key destruction, SHUTDOWN/ STARTUP), supplied interactively by the operator.
• Secrets resolve from the Oracle Wallet / external password store. No plaintext anywhere. RMAN (final_backup) connects with OS/wallet auth: --target (default /, or @alias) and --catalog are wallet aliases. expdp (archive_export) connects via /@<alias> (default dba_ai_conn). park_database's SHUTDOWN uses local OS-auth SYSDBA only (sqlplus / as sysdba / srvctl). Anything that looks like user/pass@db is rejected with ERR_SECRET (8). TDE key material is never printed or exported.
• 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/decommission/decommission.log (Linux) / C:/ProgramData/oracle-dba/logs/decommission/decommission.log (Windows); skill segment decommission for every task in this domain.
• T2 dry-run default: every T2 script previews and changes nothing unless given --execute. Retirement is irreversible at the T3 boundary, so the T2/T1 steps (final backup, archive, park, quarantine, reports) are the safety net you build before crossing into any runbook.

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 --help. The normal end-of-life order is: inventory -> final backup -> archive export -> park -> schema quarantine -> (T3 runbooks for drop / purge / destruction / reclaim).

Final backup before decommission (T2)

• Trigger: just before retirement; create the legal/restore safety net.
• Precheck: instance reachable + ARCHIVELOG mode (KEEP archival backup needs it).
• Action: scripts/final_backup.sh --execute [--tag DECOMM_<DB>] [--keep-until YYYY-MM-DD | --catalog <alias>] [--compress] / .ps1.
• Postcheck: sql/final_backup_verify.sql confirms a COMPLETED DB backup within the window (fails closed if none).
• Rollback note: a backup is additive - nothing to undo. This backup IS the rollback anchor for the whole decommission.

Pre-decommission inventory (T1)

• Trigger: capture the evidence baseline before any change; weekly while winding down.
• Precheck: none (read-only).
• Action: scripts/predecommission_inventory.sh / .ps1 + sql/predecommission_inventory.sql.
• Postcheck: n/a - emits KEYVAL (db_name, role, sessions, schema count, size, links, last-backup age) + a metric.
• Rollback note: none - read-only.

Database shutdown / park (T2)

• Trigger: stop the instance cleanly for retirement, reversibly (before the T3 drop).
• Precheck: sql/shutdown_readiness.sql - fails closed for a PRIMARY with valid standbys AND many active sessions (coordinate Data Guard / drain first).
• Action: scripts/park_database.sh --execute [--use-srvctl --db-unique-name NAME] [--disable-autostart] / .ps1. SHUTDOWN via local OS-auth SYSDBA.
• Postcheck: instance is no longer reachable via dba_ai_conn (down).
• Rollback note: fully reversible - STARTUP / srvctl start database brings it back. The irreversible drop is #decommission.

Schema / object cleanup - quarantine (T2)

• Trigger: park application schemas before retirement without destroying data.
• Precheck: each schema exists and is application-owned (oracle_maintained='N').
• Action: scripts/schema_cleanup.sh --schemas APPA,APPB --execute / .ps1 -> sql/schema_quarantine.sql (LOCK + EXPIRE).
• Postcheck: sql/schema_quarantine_verify.sql confirms each schema is LOCKED.
• Rollback note: reversible - ALTER USER <u> ACCOUNT UNLOCK; data untouched. The irreversible DROP USER ... CASCADE is #schema-drop.

License / inventory reclamation - evidence (T1)

• Trigger: document licensable option/feature usage before reclaiming entitlements.
• Precheck: none (read-only).
• Action: scripts/license_inventory.sh / .ps1 + sql/license_usage_report.sql.
• Postcheck: n/a - emits feature/option/core/PDB KEYVAL + a metric.
• Rollback note: none - read-only. The actual entitlement return / CMDB / OEM update is #license-reclaim.

Data archival - logical retention export (T2)

• Trigger: produce a consistent long-term archive of governed data that survives the DB.
• Precheck: sql/datapump_validate_directory.sql validates the DIRECTORY object.
• Action: scripts/archive_export.sh --directory ARCHIVE_DIR --mode full|schemas [--schemas A,B] [--parallel N] [--compression METADATA_ONLY] --execute / .ps1 (FLASHBACK_TIME=SYSTIMESTAMP for consistency).
• Postcheck: sql/datapump_job_status.sql confirms the job reached a terminal/clean state.
• Rollback note: export is read-only on the DB; delete the dump to undo on disk. Reload via impdp.

Retention compliance posture (T1) / Secure-destruction readiness (T1)

• Trigger: prove the retention/legal-hold posture and choose a sanitization method before any T3 purge/destruction.
• Precheck: none (read-only).
• Action: scripts/checks/retention_compliance.sh / .ps1 (data footprint, ILM/ADO, Flashback Data Archive); scripts/checks/destruction_readiness.sh / .ps1 (TDE keystore, crypto-erase viability, datafile paths).
• Postcheck: n/a - emit KEYVAL + metric (fda_tracked_tables, crypto_erase_viable).
• Rollback note: none - read-only; never destroys, never prints keys. Enforcement/purge -> #data-archival; destruction -> #secure-destruction.

Tier-3 runbooks

All irreversible end-of-life actions are Tier 3 and live in references/runbooks.md: #decommission (DROP DATABASE / PDB unplug+drop / DBCA delete + Grid/oratab cleanup), #schema-drop (DROP USER ... CASCADE), #data-archival (governed retention purge / legal-hold / archive sign-off), #secure-destruction (TDE crypto-erase / OS secure wipe / media shred), #license-reclaim (entitlement return / CMDB / OEM target removal).

These runbooks PRINT exact 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). The T2 scripts deliberately stop at the reversible boundary (park, quarantine, archive) and point to the runbook for the irreversible step. SYSDBA/SYSKM is a T3-only, interactively-supplied exception. Because decommissioning has no undo once data is gone, the final backup and the verified archive are mandatory pre-flight - take and verify them before any runbook. When in doubt, runbook.

Scheduling

cron/crontab.d (Linux) and cron/task-scheduler.xml (Windows) schedule only the safe, recurring jobs: the T1 read-only lifecycle reports (inventory, license, retention compliance, destruction readiness) and an OPTIONAL T2 periodic retention-archive refresh. The one-time steps (final backup, park) and every T3 action (drop / purge / destruction / reclaim) are deliberately NOT scheduled.

See references/version-notes.md for 19c-vs-23ai behavioural deltas.