# Oracle Dba Multitenant Cdb Pdb

> USE THIS SKILL WHENEVER the user mentions multitenant, container database / CDB, pluggable database / PDB, CDB$ROOT, PDB$SEED, open/close a PDB, PDB open mode (READ WRITE / READ ONLY / MOUNTED / RESTRICTED), SAVE STATE / DISCARD STATE, PDB clone or snapshot copy, PDB unplug / plug / manifest (.xml/.pdb), PDB relocation, CDB resource plan / inter-PDB shares, lockdown profile / PDB_LOCKDOWN, application container / application root / application PDB, PDB_PLUG_IN_VIOLATIONS, or the related views V$PDBS / CDB_PDBS / DBA_PDB_SAVED_STATES — EVEN IF they do not name the specific task. Covers PDB status monitoring, PDB open/close, save/discard state, local clone & snapshot copy, unplug-to-manifest, CDB-level Resource Manager configuration, lockdown profile rules + assignment, and the plan-only Tier-3 runbooks for PDB relocation, application-container management, plug, drop, and lockdown-profile drop. Follows the oracle-dba-common contracts: SQLcl MCP saved connection dba_ai_conn, Oracle Wallet secrets (never plaintex

- Skill: `shreyas70773/oracle-dba-multitenant-cdb-pdb` (Agent Skill, multi-file: 28 files)
- Install (CLI): `npx skillmds@latest add shreyas70773/oracle-dba-multitenant-cdb-pdb`
- Raw SKILL.md: https://api.skillmd.com/api/skills/shreyas70773/oracle-dba-multitenant-cdb-pdb/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: AI & ML
- Author: Shreyas70773 (https://skillmd.com/u/shreyas70773)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/shreyas70773/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

| Task | Level | Tier | Mechanism (script / sql / runbook path) | Idempotent? |
|------|-------|------|------------------------------------------|-------------|
| PDB status monitoring | L1 | T1 | `scripts/checks/check_pdb_status.sh` / `.ps1` + `sql/pdb_status.sql` | yes — read-only (emits KEYVAL metrics + RESTRICTED / violation findings, never mutates) |
| PDB open / close | L2 | T2 | `scripts/pdb_open_close.sh` / `.ps1` + `sql/pdb_open_close.sql` | yes — already in target mode -> noop; OPEN <-> CLOSE inverses; `--pdb ALL` skips seed/root |
| Save / discard PDB state | L2 | T2 | `scripts/save_pdb_state.sh` / `.ps1` + `sql/pdb_save_state.sql` | yes — SAVE when already saved / DISCARD when none -> noop; `--ensure-open` opens first then saves |
| PDB clone / snapshot copy | L2 | T2 | `scripts/pdb_clone.sh` / `.ps1` + `sql/pdb_clone.sql` | yes — existing target PDB -> noop; CREATE only (drop is T3) |
| PDB unplug (to manifest) | L2 | T2 | `scripts/pdb_unplug.sh` / `.ps1` + `sql/pdb_unplug.sql` | yes — already UNPLUGGED -> noop; closes + writes manifest, never drops |
| CDB-level Resource Manager — configure / activate | L2 | T2 | `scripts/cdb_resource_manager.sh --action configure` / `.ps1` + `sql/cdb_resource_manager.sql` | yes — plan + per-PDB SHARES upserted; unchanged -> noop; must run from CDB$ROOT |
| CDB-level Resource Manager — deactivate | L2 | T2 | `scripts/cdb_resource_manager.sh --action deactivate` / `.ps1` + `sql/cdb_resource_manager_deactivate.sql` | yes — no active plan -> noop; definitions retained |
| Lockdown profile — apply / drop-rule | L2 | T2 | `scripts/lockdown_profile.sh --action apply\|drop-rule` / `.ps1` + `sql/lockdown_profile.sql` | yes — present rule on apply / absent on drop-rule -> noop; from CDB$ROOT |
| Lockdown profile — assign to PDB | L2 | T2 | `scripts/lockdown_profile.sh --action assign` / `.ps1` + `sql/lockdown_assign.sql` | yes — PDB_LOCKDOWN already = target -> noop; `--profile NONE` clears |
| PDB plug (from manifest) | L2-struct | **T3** | `references/runbooks.md#pdb-plug` | partial — `CREATE ... USING` is one-shot; retry after `DROP ... KEEP DATAFILES` |
| PDB relocation (online, across CDBs) | **L3** | **T3** | `references/runbooks.md#pdb-relocate` | no — source PDB dropped at cut-over; abort only before OPEN |
| Application container management | **L3** | **T3** | `references/runbooks.md#app-container` | partial — versioned INSTALL/UPGRADE/PATCH + SYNC; re-runnable per block, not a free rollback |
| Drop a PDB | struct | **T3** | `references/runbooks.md#pdb-drop` | no — `INCLUDING DATAFILES` irreversible; `KEEP DATAFILES` re-pluggable |
| Drop a lockdown profile (escape hatch) | struct | **T3** | `references/runbooks.md#lockdown-drop` | n/a — refused by the T2 lockdown wrapper |

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).