Managing Initialization Parameters in Oracle 23c (CDB/PDB)

In Oracle 23c the database is multitenant by default: a Container Database (CDB) hosts one or more Pluggable Databases (PDBs). Initialization parameters therefore have scope (CDB or PDB) and persistence (memory and/or server parameter file). This lesson shows how to view, change, and govern parameters safely in modern environments.

Key Concepts

• SPFILE vs PFILE: Use an SPFILE for persistent, centralized configuration. A PFILE is static text, typically used only to bootstrap an SPFILE.
• Dynamic vs Static: Dynamic parameters allow ALTER SYSTEM/ALTER SESSION at runtime. Static parameters require a restart of the affected container.
• Scope: In a CDB, parameters can be set at the CDB root (inheritance to PDBs) or overridden in an individual PDB if the parameter is PDB-modifiable.
• Persistence: SCOPE=MEMORY, SCOPE=SPFILE, or SCOPE=BOTH control whether a change is immediate, persistent on next restart, or both.

Viewing Parameters

Use data dictionary views to see the effective and inherited values.

-- From CDB$ROOT to see container-aware values
SELECT name, value, isses_modifiable, issys_modifiable, con_id
FROM   v$system_parameter
ORDER  BY name;

-- From within a PDB to see its effective values
SHOW parameter pga_aggregate_target;

-- List parameters that can be set at PDB level
SELECT name FROM v$parameter WHERE pdb_modifiable = 'TRUE' ORDER BY name;

Changing Parameters at the CDB Level

CDB-level settings are inherited by PDBs unless overridden. Use CONTAINER=ALL in the root to apply to all open PDBs (for dynamic parameters that support it).

-- In CDB$ROOT (dynamic param, immediate and persistent)
ALTER SYSTEM SET pga_aggregate_target = 8G SCOPE=BOTH;

-- Apply a dynamic parameter to all currently open PDBs
ALTER SYSTEM SET optimizer_adaptive_features = TRUE
  CONTAINER=ALL SCOPE=BOTH;

-- Persist only (effective after restart)
ALTER SYSTEM SET processes = 2000 SCOPE=SPFILE;

Overriding a Parameter in a Specific PDB

For parameters marked PDB_MODIFIABLE=TRUE, connect to the target PDB and set the value there. The PDB value overrides the root value for that PDB only.

-- Connect to the PDB and override a PDB-modifiable parameter
ALTER SESSION SET container = salespdb;

ALTER SYSTEM SET pga_aggregate_target = 2G SCOPE=BOTH;

-- Revert to inheritance by resetting in the PDB
ALTER SYSTEM RESET pga_aggregate_target SCOPE=BOTH;

Session-Level Settings (Per Connection)

Use ALTER SESSION for temporary tuning or controlled testing without changing system-wide configuration.

ALTER SESSION SET sql_trace = TRUE;
ALTER SESSION SET optimizer_mode = FIRST_ROWS_10;

Common 23c Parameter Patterns

• Memory: use aggregate targets (e.g., pga_aggregate_target, sga_target) instead of many discrete memory knobs. Consider Auto Memory when appropriate.
• Multitenant: keep CDB root values conservative and override per PDB for workload-specific needs (e.g., reporting vs OLTP).
• Optimizer: avoid legacy RULE hints; rely on up-to-date stats (DBMS_STATS) and adaptive features unless you have a baseline/SQL Plan Management requirement.
• NLS & Time Zone: set at the PDB level if tenants differ; document client-side expectations.
• Logging/Tracing: use per-session settings for diagnostics; centralize persistent tracing carefully to avoid overhead.

Safe Change Process (Sanity Check)

1. Assess: Confirm whether the parameter is dynamic and PDB-modifiable. Identify blast radius (CDB vs PDB).
2. Test: Trial via ALTER SESSION or in a non-prod PDB. Validate with AWR/ASH/SQL Monitor where applicable.
3. Apply: Use SCOPE=BOTH for immediate + persistent where safe; otherwise SCOPE=SPFILE and schedule a restart.
4. Document: Record in IaC (Terraform/Ansible), track in Git, and note ticket/change ID.
5. Observe: Verify impact via OCI Database Management metrics and alerts. Roll back if KPIs regress.

Migrating Legacy Init Practices

• Replace static PFILE edits with SPFILE and ALTER SYSTEM operations.
• Move “server-wide” assumptions to the CDB; override per PDB only when justified by SLOs.
• Parameterize via IaC (e.g., Terraform variables) for consistent provisioning and drift control.

Troubleshooting Tips

-- See pending (SPFILE) vs in-memory differences
SELECT name, value, isdefault, isspecified, con_id
FROM   v$system_parameter
WHERE  name IN ('pga_aggregate_target','sga_target')
ORDER  BY con_id, name;

-- Identify who changed what (if auditing enabled)
SELECT * FROM dba_audit_trail WHERE obj_name = 'SYSTEM_PARAMETER' ORDER BY timestamp DESC;

-- Show effective optimizer features (example)
SELECT name, value FROM v$parameter WHERE name LIKE 'optimizer%';

Modern parameter management is about scope, safety, and repeatability. Use CDB inheritance for baseline behavior, apply PDB overrides sparingly, and capture every change in versioned automation to keep fleets consistent.

Next lesson: OCI Database Configuration Tools - how the Console, CLI, and APIs streamline common post-provision steps compared to legacy DBCA-only workflows.