Oracle Dba Multitenant Cdb Pdb

Oracle DBA - Multitenant (CDB / PDB)

Day-to-day operation of Oracle multitenant containers for 19c and 23ai (EE; single-instance and Data Guard; on-prem): PDB status monitoring, open/close, save state, local clone / snapshot copy, unplug-to-manifest, CDB-level Resource Manager, and lockdown profiles. Everything self-executing is T1 (observe) or T2 (reversible, dry-run-default). The structural / cross-CDB operations — PDB relocation, application-container lifecycle, plug-from-manifest, drop, and lockdown-profile drop — are T3 and live only in references/runbooks.md; they print exact commands for a human and never self-execute.

In-DB SQL/PL-SQL runs through the SQLcl MCP saved connection dba_ai_conn (wallet-backed). Host-level steps (none are required by the T1/T2 scripts here beyond invoking SQLcl) and all T3 structural DDL are operator-driven. Never SYS/SYSTEM except inside a T3 runbook that explicitly states AS SYSDBA, supplied interactively.

Scope & risk map

Levels map to oracle_dba_task_level_map.md section 12. Tasks 1-9 there (monitoring, open/close, save state, clone/snapshot, unplug/plug, CDB Resource Manager, lockdown, relocation = L3, application container = L3) are all covered above; relocation and application containers are the two L3 tasks and are given expert-grade Tier-3 runbooks.

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, supplied interactively by the operator. The dba_ai_conn common user needs the relevant privileges granted out of band per site policy: ALTER PLUGGABLE DATABASE / CREATE PLUGGABLE DATABASE (open/close, clone, unplug), ADMINISTER RESOURCE MANAGER (CDB plan), ALTER SYSTEM + CREATE LOCKDOWN PROFILE from CDB$ROOT and ALTER SESSION SET CONTAINER (lockdown). T3 structural DDL needs SYSDBA-class privilege (operator-supplied).
• Container scope matters. CDB-wide tasks (resource plan, lockdown profile create/edit) must run connected to CDB$ROOT — the wrappers verify SYS_CONTEXT('USERENV','CON_NAME')='CDB$ROOT' in their precheck and abort otherwise. Per-PDB tasks act on the named PDB; --pdb ALL loops user PDBs only (skips PDB$SEED and con_id<=2).
• Secrets resolve from the Oracle Wallet / external password store. No plaintext anywhere — not in argv, env, files, or SQL. The helper libraries reject credential-looking arguments with ERR_SECRET (8). Cross-CDB DB links (relocate / plug) use wallet-backed link credentials only.
• 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/multitenant/multitenant.log (Linux) / C:/ProgramData/oracle-dba/logs/multitenant/multitenant.log (Windows); skill segment multitenant throughout (T1/T2 scripts and T3 runbooks).
• T2 dry-run default: every T2 script previews and changes nothing unless given --execute. Architecture floor: 19c is CDB-by-default; 23ai is CDB-only. All work targets the container dba_ai_conn selects; no non-CDB path exists.
• Storage / option prerequisites (operational, not syntax): SNAPSHOT COPY needs copy-on-write storage (ACFS / ASM / supported NFS); hot clone & online relocate need ARCHIVELOG + LOCAL UNDO on the relevant CDBs.

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.

PDB status monitoring (T1)

• Trigger: scheduled (e.g. every 15 min) or investigating a closed / RESTRICTED PDB.
• Precheck: none beyond dba_ai_conn reachable (read-only).
• Action: scripts/checks/check_pdb_status.sh [--max-restricted N --max-violations N] / .ps1 + sql/pdb_status.sql.
• Postcheck: n/a — emits KEYVAL metrics (total_pdbs, pdbs_read_write, pdbs_mounted, pdbs_restricted, unresolved_violations) + non-fatal findings.
• Rollback note: none — read-only.

PDB open / close

• Trigger: bring a PDB online READ WRITE / READ ONLY / UPGRADE, or close it for maintenance.
• Precheck: dba_ai_conn reachable; idempotency probe reads current open_mode.
• Action: scripts/pdb_open_close.sh --pdb NAME|ALL --action open|close [--open-mode "READ WRITE"|"READ ONLY"|UPGRADE] [--force] [--close-mode immediate|normal] --execute / .ps1 -> sql/pdb_open_close.sql. --force only to switch an already-open PDB's mode without closing.
• Postcheck: OPEN -> PDB not MOUNTED; CLOSE -> PDB MOUNTED (verified in v$pdbs).
• Rollback note: OPEN <-> CLOSE are inverses. Does NOT persist across CDB restart — use save state for that.

Save / discard PDB state

• Trigger: make a PDB auto-open to its current mode after a CDB restart (SAVE), or revert to default MOUNTED-on-restart (DISCARD).
• Precheck: dba_ai_conn reachable; reads DBA_PDB_SAVED_STATES.
• Action: scripts/save_pdb_state.sh --pdb NAME|ALL --action save|discard [--ensure-open "READ WRITE"] --execute / .ps1 -> sql/pdb_save_state.sql. --ensure-open opens to the desired mode FIRST (delegates to pdb_open_close) then saves the current mode.
• Postcheck: SAVE -> saved-state row present; DISCARD -> absent.
• Rollback note: SAVE <-> DISCARD are inverses. On Data Guard, run on the PRIMARY.

PDB clone / snapshot copy

• Trigger: spin up a copy of an existing PDB (test/dev refresh, pre-change snapshot).
• Precheck: ARCHIVELOG (hot clone) + dba_ai_conn reachable; idempotency probe checks the target name in CDB_PDBS.
• Action: scripts/pdb_clone.sh --src SRC --tgt NEW [--kind full|snapshot] [--file-convert "from,to,..."] [--storage-max 50G] [--no-open] --execute / .ps1 -> sql/pdb_clone.sql. Creates MOUNTED, then opens READ WRITE (separate logged step) unless --no-open.
• Postcheck: target present in CDB_PDBS (and open unless --no-open).
• Rollback note: a clone is undone by DROP PLUGGABLE DATABASE <tgt> INCLUDING DATAFILES — that is T3 (references/runbooks.md#pdb-drop). This wrapper never drops.

PDB unplug (to manifest)

• Trigger: prepare a PDB to be moved/archived by writing its manifest; first half of an unplug/plug move.
• Precheck: manifest directory exists/writable (where visible) + dba_ai_conn reachable; idempotency probe reads CDB_PDBS.STATUS.
• Action: scripts/pdb_unplug.sh --pdb NAME --manifest /path/pdb.xml [--archive xml|pdb] --execute / .ps1 -> sql/pdb_unplug.sql. Closes the PDB then UNPLUG INTO; leaves it UNPLUGGED (datafiles on disk). Never drops.
• Postcheck: PDB STATUS='UNPLUGGED' in CDB_PDBS.
• Rollback note: re-create from the manifest, or change your mind before dropping. The destructive DROP ... KEEP/INCLUDING DATAFILES + plug elsewhere is T3 (#pdb-plug, #pdb-drop).

CDB-level Resource Manager (configure / activate / deactivate)

• Trigger: govern CPU/parallelism BETWEEN PDBs via per-PDB SHARES; or clear the active plan.
• Precheck: connected to CDB$ROOT (enforced); diff existing plan/directives.
• Action: scripts/cdb_resource_manager.sh --action configure --plan ODB_CDB_PLAN --shares "SALES:3,HR:1" [--activate] --execute / .ps1 -> sql/cdb_resource_manager.sql; deactivate via --action deactivate --execute -> sql/cdb_resource_manager_deactivate.sql.
• Postcheck: plan has directives (DBA_CDB_RSRC_PLAN_DIRECTIVES); deactivate -> no active RESOURCE_MANAGER_PLAN.
• Rollback note: re-run configure with prior SHARES, or deactivate. Definitions are retained on deactivate (re-activation is a one-liner). Deleting a plan entirely is operator-driven.

Lockdown profile (apply / drop-rule / assign)

• Trigger: harden PDBs by DISABLEing features (apply), remove a rule (drop-rule), or bind/unbind a profile to a PDB (assign).
• Precheck: apply/drop-rule require CDB$ROOT (enforced); assign requires dba_ai_conn reachable.
• Action: scripts/lockdown_profile.sh --action apply|drop-rule --profile NAME --rules "ALTER_SYSTEM,OS_ACCESS" --execute / --action assign --profile NAME|NONE --pdb SALES --execute / .ps1 -> sql/lockdown_profile.sql / sql/lockdown_assign.sql.
• Postcheck: apply -> profile present in DBA_LOCKDOWN_PROFILES; assign -> PDB_LOCKDOWN equals target inside the PDB (or empty for NONE).
• Rollback note: drop-rule / re-apply restore rules; assign --profile NONE clears. Dropping the whole profile is T3 (#lockdown-drop).

Tier-3 structural operations (relocation, app containers, plug, drop)

• Trigger: move a PDB across CDBs, manage an application container, plug an unplugged PDB, or drop a PDB / lockdown profile.
• Precheck: the runbook's pre-flight checklist (right env, backup, space, compatibility, LOCAL UNDO + ARCHIVELOG, connectivity, Data Guard awareness).
• Action: none auto-executes. Print references/runbooks.md#<anchor> and stop. Gate with require_approval_token multitenant <task> ...#<anchor> / Require-ApprovalToken.
• Postcheck: per-runbook verification queries (V$PDBS, PDB_PLUG_IN_VIOLATIONS, DBA_APP_PDB_STATUS, etc.).
• Rollback note: per-runbook abort/rollback guidance (abort relocate before OPEN; re-plug KEEP DATAFILES; restore from backup; compensating application UPGRADE).

Tier-3 runbooks

All structural / cross-CDB operations are Tier 3 and live in references/runbooks.md: #pdb-relocate (L3), #app-container (L3), #pdb-plug, #pdb-drop, #lockdown-drop.

These runbooks PRINT exact SQL/DDL 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 rejected). The T2 scripts deliberately refuse the destructive operations (clone/unplug never drop; lockdown wrapper never drops the profile) and point 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/crontab.d + cron/task-scheduler.xml for the schedulable T1/T2 jobs (PDB status monitoring; optional ensure-open + save-state self-healing).