Parameter Files, SPFILE/PFILE, Dynamic Parameters, ALTER SYSTEM, and Scope

Learning outcomes

ServiceHub restarts after maintenance and a parameter “fix” is gone. On another system, a change that was meant to be temporary survives a restart. These are not random Oracle behaviors: parameter values can come from a text PFILE, a binary server parameter file SPFILE, memory, container-level overrides, and explicit system/session changes. Safe administration requires knowing both the effective value and its persistence/provenance.

1. PFILE and SPFILE answer different operational needs

A PFILE is a client/server-readable text initialization parameter file, traditionally named like initSID.ora. A SPFILE is an Oracle-managed server parameter file designed for persistent parameter administration through SQL. If an instance started from a PFILE, a memory change cannot automatically persist back into that text file. If it started from an SPFILE, ALTER SYSTEM ... SCOPE=SPFILE or BOTH can persist a supported setting.

SHOW PARAMETER spfileSELECT name, value, display_value, isdefault,       isses_modifiable, issys_modifiable, ispdb_modifiableFROM   v$parameterWHERE  name IN ('spfile','compatible','processes','open_cursors','session_cached_cursors')ORDER  BY name;

A nonempty SPFILE value is direct evidence that the current instance was started with a server parameter file. Do not infer the startup file from a path you found on disk: unused PFILEs and old SPFILE copies can coexist in an Oracle Home.

2. Effective value and persistent value are not the same

V$PARAMETER describes parameter values visible to the current session/context. V$SYSTEM_PARAMETER represents system-level effective settings and includes modifiability/container metadata. V$SPPARAMETER represents values stored in the SPFILE; a row can exist with ISSPECIFIED='FALSE' or a null value when no explicit SPFILE setting was supplied for that item.

SELECT name, value, display_value, isdefault,       issys_modifiable, ispdb_modifiableFROM   v$system_parameterWHERE  name IN ('processes','open_cursors','session_cached_cursors')ORDER  BY name;SELECT name, value, isspecified, sid, con_idFROM   v$spparameterWHERE  name IN ('processes','open_cursors','session_cached_cursors')ORDER  BY name, sid, con_id;

If the runtime value differs from the SPFILE value, that can be legitimate—for example, a memory-only change is active but will disappear on restart. Your change record should always contain both “effective now” and “persistent next startup” columns.

3. SCOPE tells Oracle when the change should exist

In the CDB root, SCOPE follows instance/SPFILE semantics. In a PDB, only parameters with ISPDB_MODIFIABLE='TRUE' can be changed for that PDB. Persistent PDB parameter values are stored so that they survive PDB reopen/CDB restart and can travel in unplug metadata where documented. Always capture SHOW CON_NAME before a CDB/PDB-sensitive change.

SHOW CON_NAMESELECT name, value, issys_modifiable, ispdb_modifiableFROM   v$system_parameterWHERE  ispdb_modifiable = 'TRUE'ORDER  BY nameFETCH FIRST 25 ROWS ONLY;

4. Deliberate failure: try to make a static parameter memory-only

PROCESSES is a classic startup-sizing parameter. Its current metadata should report that it cannot be modified dynamically. In a disposable SYSDBA session, the following command is intentionally wrong:

SELECT name, value, issys_modifiableFROM   v$parameterWHERE  name = 'processes';ALTER SYSTEM SET processes = 500 SCOPE=MEMORY;

Oracle should reject the command because a static parameter cannot be applied to the running instance. The important lesson is not the exact ORA number; it is the evidence chain: check ISSYS_MODIFIABLE, determine whether an SPFILE is in use, assess the restart requirement, then prepare rollback before persisting anything. Do not “solve” the rejection by editing an SPFILE manually or by changing an underscore parameter you found online.

5. Back up parameter state before a persistent experiment

Oracle supports creating a readable PFILE from the current SPFILE. In the Free container/Linux lab, this creates a rollback/audit snapshot without modifying the SPFILE. Use a path writable by the Oracle software owner. On Windows, choose a secured local path appropriate to that installation rather than copying the Unix path literally.

CREATE PFILE='/tmp/servicehub_ch02_before.ora' FROM SPFILE;SELECT name, value, display_value,       issys_modifiable, ispdb_modifiableFROM   v$system_parameterWHERE  name IN ('processes','open_cursors','session_cached_cursors')ORDER  BY name;

Store the output with the file and the exact RU/host/instance/container. A PFILE export is not a substitute for an RMAN/control-file/database backup; it is configuration evidence and a recovery aid for parameter-file mistakes.

6. Optional disposable change: prove MEMORY versus SPFILE

This branch is optional because it changes instance-wide behavior. Perform it only when no unrelated workload uses the Free instance. Choose SESSION_CACHED_CURSORS because the lesson is about scope, not about claiming a tuning benefit. First record the exact original value. Then set a deliberately modest memory-only value, verify the runtime/SPFILE mismatch, and restore the recorded original value before leaving the lab.

COLUMN old_scc  NEW_VALUE old_scc  NOPRINTCOLUMN test_scc NEW_VALUE test_scc NOPRINTSELECT TO_NUMBER(value) AS old_scc,       TO_NUMBER(value) + 10 AS test_sccFROM   v$system_parameterWHERE  name = 'session_cached_cursors';ALTER SYSTEM SET session_cached_cursors = &test_scc SCOPE=MEMORY;SELECT name, valueFROM   v$system_parameterWHERE  name = 'session_cached_cursors';SELECT name, value, isspecifiedFROM   v$spparameterWHERE  name = 'session_cached_cursors';ALTER SYSTEM SET session_cached_cursors = &old_scc SCOPE=MEMORY;UNDEFINE old_sccUNDEFINE test_scc

The script derives a test value ten higher than the current setting, proves the memory-only value differs from persistent SPFILE state, then restores the exact original runtime value captured at the beginning. The acceptance criterion is not “more cached cursors is better.” It is that you can predict which state changes, which state survives restart, and how to reverse the experiment.

7. Hidden parameters and copied tuning recipes are a change-control smell

Parameters whose names begin with an underscore are internal/hidden. They can be exposed in diagnostic contexts, but self-directed changes can invalidate support assumptions and create hard-to-explain behavior. The course does not recommend hidden parameters as tuning levers. If Oracle Support directs a hidden-parameter change, record the service request, exact scope, RU, rationale, expiry/review condition, and rollback.

The same discipline applies to ordinary parameters: a value that helped a different version/workload may be harmful here. Configuration is evidence-driven state, not a collection of “best settings.”

8. Hands-on lab: configuration provenance report

SELECT SYS_CONTEXT('USERENV','CON_NAME') AS con_name FROM dual;SHOW PARAMETER spfileSELECT p.name,       p.value AS runtime_value,       p.isdefault,       p.issys_modifiable,       p.ispdb_modifiable,       sp.value AS spfile_value,       sp.isspecifiedFROM   v$system_parameter pLEFT JOIN v$spparameter sp       ON sp.name = p.name      AND sp.sid IN ('*', SYS_CONTEXT('USERENV','INSTANCE_NAME'))WHERE  p.name IN ('compatible','processes','open_cursors',                  'session_cached_cursors','sga_target','pga_aggregate_target')ORDER  BY p.name;

Verification checklist:

• You know whether the instance started with an SPFILE.
• You can distinguish runtime from SPFILE values.
• You can explain MEMORY, SPFILE, and BOTH without treating them as synonyms.
• You checked CDB/PDB scope and ISPDB_MODIFIABLE before considering a PDB override.
• You observed a static-parameter rejection without forcing a dangerous workaround.
• You created a readable PFILE snapshot before any optional persistent experiment.

9. Production judgment

Parameter changes are deployment changes. Require a measured problem, current Oracle documentation, exact RU and offering, current/effective/persistent values, scope, restart/reopen requirement, test evidence, change window, rollback, and post-change verification. For RAC, instance-specific versus wildcard SID settings add another dimension; for managed cloud services, the provider may restrict direct parameter control.

Do not raise COMPATIBLE casually. It is not merely a feature switch and can affect downgrade/recovery paths. Chapter 28 handles upgrades and compatibility deliberately. Lesson 4 next uses startup states to show exactly when parameter files, control files, and datafiles become required.

10. Summary and next step

Oracle configuration has provenance. PFILE is text startup configuration; SPFILE is Oracle-managed persistent configuration. Runtime values can differ from persistent values. Parameter metadata tells you whether an item is modifiable and whether a PDB override is supported. Safe operators inspect first, snapshot configuration, change the smallest supported scope, verify both runtime and persistence, and restore cleanly.

Authoritative references

• Oracle AI Database SQL Language Reference — ALTER SYSTEM — SCOPE, SID, CONTAINER, and parameter-change semantics
• Oracle AI Database Reference — Parameter Files — PFILE/SPFILE and parameter-file behavior
• Oracle AI Database Reference — Changing Parameter Values — persistent parameter administration
• Oracle Multitenant Administrator’s Guide — Administering PDBs — PDB parameter scope and persistence
• Oracle AI Database Administrator’s Guide — Managing Memory — supported memory parameter administration