A successful parameter change has two separate questions: which value operates now, and which setting will initialize the next instance? ALTER SYSTEM uses SCOPE to select the live configuration, the saved startup configuration, or both. Eligibility and the startup source determine which choices are available.
Oracle DBA Lesson 28 Part 2 — Match Live and Persistent Parameter Changes
Live value and saved configuration
The live value is the initialization parameter value in effect for the running instance. V$SYSTEM_PARAMETER exposes these instance-effective values. V$PARAMETER instead reports parameters effective for the querying session. That distinction matters for session overrides. See V$SYSTEM_PARAMETER.
The SPFILE is a binary server parameter file on physical storage. Oracle reads its saved settings when starting an instance. An administrator changes it through supported Oracle statements rather than a text editor. V$SPPARAMETER exposes its entries. These are logical parameter destinations. SCOPE=MEMORY does not mean that the parameter is a database buffer cache or another SGA or PGA component. See managing initialization parameters using a server parameter file and V$SPPARAMETER.
In the CDB root, confirm the current startup source:
SHOW USER
SHOW CON_NAME
SHOW PARAMETER SPFILE
These are SQL*Plus commands. The SPFILE parameter identifies the server parameter file currently in use. Confirm the actual startup arrangement, including a text PFILE that may point to an SPFILE. An instance started directly from a text PFILE supports eligible live changes. ALTER SYSTEM does not automatically edit that text file.
Eligibility comes before scope
Inspect the individual parameter documentation and its ISSYS_MODIFIABLE metadata:
| Value | Meaning for a system change |
|---|---|
IMMEDIATE | An eligible live change takes effect immediately. |
DEFERRED | The change applies to subsequent sessions. Include DEFERRED in the statement. Existing sessions retain their values. |
FALSE | The setting is static for the current instance. A permitted SPFILE change takes effect in a subsequent instance. |
ISSES_MODIFIABLE concerns session-level eligibility, while ISPDB_MODIFIABLE concerns changes inside a PDB. Neither grants the required privileges. Parameter-specific restrictions still apply.
For root or non-CDB changes:
| Scope | Live configuration | Saved configuration | Activation / persistence |
|---|---|---|---|
MEMORY | Changes the eligible live setting | Remains as recorded | Immediate parameters apply now and are temporary until shutdown. Deferred parameters affect subsequent sessions. |
SPFILE | Continues with its current value | Saves the replacement in the active SPFILE | Takes effect at later startup using that file. This is the permitted scope for static parameters. |
BOTH | Changes the eligible live setting | Saves the replacement in the active SPFILE | Live timing follows modifiability and persists across restart using the applicable saved entry. |
SPFILE and BOTH require an active SPFILE. With PFILE startup, MEMORY is the only scope available through ALTER SYSTEM. Persistence needs a separately reviewed text-file update. Oracle's root default is BOTH with SPFILE startup and MEMORY with PFILE startup. Specify scope explicitly, particularly for static settings, which require SPFILE. See ALTER SYSTEM.
Three alternatives from the same baseline
Assume an immediately modifiable parameter has live value 300 and explicit saved value 300. The relevant saved entry has no overriding instance-specific value, and the next startup uses this same SPFILE. Each row is a separate alternative beginning at that baseline:
| Choice of replacement 400 | Live after the change | Saved after the change | Live after restart using that file |
|---|---|---|---|
SCOPE=MEMORY | 400 | 300 | 300 |
SCOPE=SPFILE | 300 | 400 | 400 |
SCOPE=BOTH | 400 | 400 | 400 |
The restart outcome follows the saved startup source. Instance-specific SPFILE entries can take precedence over a global SID='*'. Some parameters may be internally adjusted by Oracle, so verify the actual live value after startup rather than treating a saved entry as proof of activation.
A static parameter keeps its current instance value after a SCOPE=SPFILE change. Its replacement becomes eligible for activation at restart. MEMORY and BOTH are unavailable for static parameters because they require a live change.
Inspect and temporarily change OPEN_CURSORS
OPEN_CURSORS limits how many cursors a session can have open at once. A cursor is a handle used for SQL work. The parameter is documented as ALTER SYSTEM modifiable and PDB modifiable. See OPEN_CURSORS.
From the intended root, capture the live value and metadata:
SELECT con_id, name, value, issys_modifiable, ispdb_modifiable
FROM v$system_parameter
WHERE name = 'open_cursors';
Interpret VALUE as the effective system value for the relevant container row. Confirm ISSYS_MODIFIABLE=IMMEDIATE. Keep the current container and CON_ID in the evidence.
Capture the saved entries independently:
SELECT con_id, sid, name, value, isspecified
FROM v$spparameter
WHERE name = 'open_cursors';
SID identifies an instance-specific or global setting. ISSPECIFIED=TRUE indicates an explicit SPFILE entry. A row with FALSE is not an explicit saved setting and can leave the applicable default in effect. When no SPFILE was used to start the instance, this view reports FALSE and null values. That behavior must be interpreted together with the startup-source check.
For the baseline of live 300 and explicit saved 300, the temporary change is:
ALTER SYSTEM SET open_cursors = 400
SCOPE = MEMORY;
Run both inspection queries again. The expected case is live 400, with the recorded saved entry still 300. Capture the real results and compare all applicable saved entries with the baseline. A success message alone does not answer both verification questions.
Restore the original live value:
ALTER SYSTEM SET open_cursors = 300
SCOPE = MEMORY;
Run both queries again to verify the original live value and unchanged saved configuration. Do not use RESET as a substitute for restoring a captured value. Resetting has its own startup and default semantics.
Container scope matters
The video demonstrates root behavior. With the default CONTAINER=CURRENT, a root setting applies to the root and to PDBs inheriting that parameter. A PDB with its own override has a different inheritance state. A root change can therefore affect application containers that inherit it.
Inside a PDB, only parameters with ISPDB_MODIFIABLE=TRUE are eligible for a PDB-level change. Saved PDB-specific settings have container persistence rules. A SCOPE=SPFILE change can activate when the PDB closes and reopens or when the CDB restarts. A BOTH change applies live and persistently for that PDB. A temporary PDB MEMORY value can revert to the root's value on reopen. Confirm the particular parameter, container, privileges, and inheritance behavior before using a root procedure in a PDB.
No comments:
Post a Comment