A useful parameter change starts with its container, supported modification scope, and intended lifetime. The same parameter name can have an inherited PDB value, a persistent local override, or, for a parameter that supports it, a session-specific value. A PDB rename also changes connection identity, so the service and client consequences belong in the plan.
Oracle DBA Lesson 54 Part 2 — Change the Intended PDB Setting
Check the current container
On Oracle Database 19c, a CDB uses one shared startup parameter file. ALTER SYSTEM in a PDB does not create a private SPFILE for that PDB. In SQL*Plus, check each container you intend to change:
SHOW CON_NAME
SELECT name, value, ispdb_modifiable, issys_modifiable
FROM v$system_parameter
WHERE name = 'open_cursors';
Oracle 19c documents the OPEN_CURSORS default as 50. An installation can already use a different configured value. The figures 300, 400, 500, and 600 below are example limits, not defaults or a tuning recommendation. An opening comparison can look like this:
| Connected container | NAME | VALUE |
|---|---|---|
CDB$ROOT | open_cursors | 300 |
APP_PDB | open_cursors | 300 |
OPEN_CURSORS limits how many open cursors one session can hold. A cursor is a handle to a private SQL area. The limit is a count per session, not a memory allocation. Investigate the application's cursor use, and any cursor errors, before you choose a value.
The same number in the root and the PDB does not prove inheritance. A local setting can equal the root value. Establish the configuration from the recorded local changes and the persistent-setting evidence for that environment.
A wider check of related parameters is:
SELECT name, value, ispdb_modifiable,
issys_modifiable, isdefault
FROM v$system_parameter
WHERE name IN ('sessions', 'cursor_sharing', 'local_listener')
ORDER BY name;
Read each parameter on its own. Whether it allows a local change, and when that change takes effect, are separate properties. Changing CURSOR_SHARING can affect SQL matching and execution. It is not a general tuning remedy. LOCAL_LISTENER concerns listener registration. It does not create a private listener process for a PDB.
| Metadata | Interpretation |
|---|---|
ISPDB_MODIFIABLE = TRUE | The parameter supports modification inside a PDB, subject to its documented constraints. |
ISPDB_MODIFIABLE = FALSE | Reject a proposed independent local PDB modification. |
ISSYS_MODIFIABLE = IMMEDIATE | A supported ALTER SYSTEM change can take effect immediately. Scope still controls whether the running setting is changed. |
ISSYS_MODIFIABLE = DEFERRED | The change applies to subsequent sessions. Use the required DEFERRED form. Existing sessions retain the earlier value. |
ISSYS_MODIFIABLE = FALSE | A running system change is unsupported. A supported static change needs persistent configuration and the later restart or reopening boundary that applies. |
ISSES_MODIFIABLE = TRUE | ALTER SESSION is supported for that parameter. |
ISDEFAULT | Describes default versus explicitly specified configuration under the view's documented semantics. It is not an inheritance flag. |
A flag is one part of the decision. Also check the permitted range, dependencies, root and PDB limits, and the other restrictions for that parameter.
See V$SYSTEM_PARAMETER, OPEN_CURSORS, and CURSOR_SHARING.
Compare the session value with the system value
V$PARAMETER describes the value in effect for the current session. V$SYSTEM_PARAMETER describes system-level values. In a PDB, read the system value in the connected container. A new session starts from the system values. A supported session change can then make that session differ.
For a parameter that supports session changes:
SELECT name, value, isses_modifiable, ismodified
FROM v$parameter
WHERE name = 'cursor_sharing';
SELECT name, value, ispdb_modifiable,
issys_modifiable, isdefault
FROM v$system_parameter
WHERE name = 'cursor_sharing';
When one connection behaves differently, compare its session evidence with the system evidence. A session override applies only to that session. It is separate from a persistent change for the whole PDB. The OPEN_CURSORS statements below are system changes. They do not set CURSOR_SHARING, and OPEN_CURSORS is not the session-modifiable case.
See V$PARAMETER.
A root change reaches inheriting PDBs
Start from this configuration. REPORT_PDB inherits OPEN_CURSORS. APP_PDB has a persistent local override.
| Container | OPEN_CURSORS | Configuration |
|---|---|---|
CDB$ROOT | 300 | Root value |
REPORT_PDB | 300 | Inherits the root value |
APP_PDB | 400 | Persistent local override |
Changing the root to 500 with CONTAINER=CURRENT updates the root and the PDB that inherits the parameter. The PDB with a persistent override keeps its local value.
| Container | Resulting value | Reason |
|---|---|---|
CDB$ROOT | 500 | Root setting changed |
REPORT_PDB | 500 | Inherits the changed root setting |
APP_PDB | 400 | Persistent local override retained |
What changes is the inherited value. The root keeps its new value. The statement does not move a database, a cursor, or a file.
Before the change, record the original running value, the persistent setting, whether the PDB inherits or overrides, the affected services and sessions, the intended result, and the restoration path. Schedule the interruption and account for unfinished work. This form applies to a supported dynamic parameter in an SPFILE-backed CDB:
-- Example only: connected to CDB$ROOT
ALTER SYSTEM SET open_cursors = 500 CONTAINER = CURRENT;
This is the root-impact form. It is not the way to change only one application PDB. CURRENT in the root includes PDBs that inherit the parameter. CONTAINER=ALL, issued from the appropriate root with the required privilege, applies the setting across containers and sets their inheritance property to true. Use ALL only when that broader result is intended. The statement above uses the documented SPFILE-backed default scope. State SCOPE explicitly in the plan.
See administering a CDB.
Set a local PDB value
After you confirm APP_PDB, parameter support, the required privilege, and the value:
SHOW CON_NAME
-- Expected target: APP_PDB
ALTER SYSTEM SET open_cursors = 600
SCOPE = BOTH
CONTAINER = CURRENT;
The earlier persistent local value was 400. This statement changes it to 600. In the connected PDB, CONTAINER=CURRENT targets that PDB. SCOPE=BOTH changes the running value and stores a persistent PDB-specific setting. Justify 600 from the workload. It is not a recommendation.
Check the system value in the target container and test the application behavior that depends on it. When the parameter is deferred, use a new session. A query of the current value does not prove restart behavior. Verify persistence when the PDB is closed and reopened, or when the CDB restarts.
Choose the lifetime of a PDB setting
| SCOPE | Running PDB | After close/reopen or CDB restart |
|---|---|---|
MEMORY | Supported dynamic running value changes. | The temporary local value is lost. Root inheritance resumes under the documented conditions. |
SPFILE | The current running value is unchanged. | The stored PDB setting takes effect at the later reopening boundary. |
BOTH | Supported dynamic running value changes. | The local setting persists across reopening and restart. |
A traditional text PFILE cannot contain PDB-specific parameter values. Check the startup mode and the supported scope before you plan persistence. For a root-level static parameter, the required boundary can be a full instance restart. Reopening a PDB does not apply every root or static change.
See administering PDBs and ALTER SYSTEM.
Remove a local override
If the parameter was inherited, setting the same number locally does not restore inheritance. That statement creates a local override. Reset the PDB-specific persistent setting:
-- Run in the intended PDB:
ALTER SYSTEM RESET open_cursors SCOPE = SPFILE;
Close and reopen the PDB before you rely on the restored inherited running value. Verify the value and the inheritance against the root configuration. Restoring a stored local value and removing a local override are different tasks. Use the reset scope that parameter supports.
Rename the PDB and its default service
Record the old and proposed global names. Inventory default-service users, user-defined application services, connection strings, connection pools, jobs, and other name references. The proposed global name must differ from the other container global names and meet the documented naming rules.
Application containers, proxy PDBs, and managed services have additional conditions. For an ordinary PDB on a single instance, the rename workflow is:
- Open the target PDB READ WRITE RESTRICTED. Arrange the interruption, and confirm sessions and unfinished work, before that open.
- Connect to that PDB with
ALTER DATABASE, granted commonly or locally in the PDB, and with restricted-session access. - Rename the global name.
- Close the PDB and reopen it READ WRITE so the new service name is integrated.
Issue the rename in the target PDB:
ALTER PLUGGABLE DATABASE
RENAME GLOBAL_NAME TO app_new.example.com;
The PDB name becomes APP_NEW, the first component of the new global name. Oracle changes the default service name and sets the PDB property of every service in that PDB to the new global name. Inspect the network, service, and domain configuration in the environment.
User-defined application service names and external client references need their own review. When clients used the old default service name, update their route. After reopening, check service registration, the ordinary application account, SHOW CON_NAME or SYS_CONTEXT('USERENV','CON_NAME'), and the required application work.
On RAC, the PDB must be open on the current instance only and closed on the other instances.
To return to the previous global name, repeat that restricted rename, close and reopen, and restore the affected service and client references. Restore changed parameter values, inheritance, and the opening policy as well. A rename back to the old global name leaves external client configuration and application behavior to be checked on their own.
Read a proxy HOST and PORT as an endpoint
A proxy PDB connects to a referenced PDB through listener endpoint information. The documented HOST and PORT values say how the proxy reaches that listener. Updating them changes the reference destination. It does not start a listener process for every ordinary PDB. Configure and verify listener processes, endpoints, and service registration through network administration. Several PDB services can share one listener.
See creating a proxy PDB.
Recap
Start with the container, the modification flags, and the lifetime. V$PARAMETER is the current session. V$SYSTEM_PARAMETER is the system value in the connected container. ISPDB_MODIFIABLE = FALSE means an independent local PDB change is unsupported. CONTAINER=CURRENT in the root also updates PDBs that inherit the parameter, and a persistent local override stays. SCOPE=BOTH changes the running value and stores a PDB-specific setting. A text PFILE cannot hold that PDB setting. Setting the inherited number locally does not restore inheritance: reset the PDB-specific setting, then close and reopen. RENAME GLOBAL_NAME changes the PDB name and the default service. Close and reopen read/write to integrate the new service name, then check services and clients. A proxy HOST and PORT update the referenced endpoint. They do not create a listener for each PDB.
No comments:
Post a Comment