Configure Instance Parameters in a CDB (ALTER SYSTEM)
Configuring instance parameters for a CDB is not much different than it was for non-CDB databases. The
ALTER SYSTEM command is used to set initialization parameters, with some database configuration modified using the
ALTER DATABASE command.
When connected as a privileged user and pointing to the root container, any
ALTER SYSTEM command will by default be directed at just the root container. This means the following two commands are functionally equivalent in this context.
ALTER SYSTEM SET parameter_name=value; ALTER SYSTEM SET parameter_name=value CONTAINER=CURRENT;
In addition to the default action, an initialization parameter change from the root container can target all containers using the following syntax.
ALTER SYSTEM SET parameter_name=value CONTAINER=ALL;
CONTAINER=ALL you are instructing the PDBs that they should inherit the specific parameter value from the root container. Unless overridden by a local setting for the same parameter, any subsequent local changes to the root container for this specific parameter will also be inherited by the PDBs.
The PDBs are able to override some parameter settings by issuing a local ALTER SYSTEM call from the container.
Configure Instance Parameters in a PDB (ALTER SYSTEM)
Only a subset of the initialization parameters can be modified locally in the PDB. These can be displayed using the following query.
COLUMN name FORMAT A35 COLUMN value FORMAT A35 SELECT name, value FROM v$system_parameter WHERE ispdb_modifiable = 'TRUE' ORDER BY name;
To make a local PDB change, make sure you are either connected directly to a privileged use in the PDB, or to a privileged common user, who has their container pointing to the PDB in question. As mentioned previously, if the
CONTAINER clause is not mentioned, the current container is assumed, so the following ALTER SYSTEM commands are functionally equivalent.
CONN / AS SYSDBA ALTER SESSION SET CONTAINER = pdb1; ALTER SYSTEM SET parameter_name=value; ALTER SYSTEM SET parameter_name=value CONTAINER=CURRENT;
Instance-level parameter changes in the root container are stored in the SPFILE in the normal way. When you change PDB-specific initialization parameters in the PDB they are not stored in the SPFILE. Instead they are saved in the
Modify a CDB (ALTER DATABASE)
From a CDB perspective, the
ALTER DATABASE command is similar to that of a non-CDB database. You just need to understand the scope of the changes you are making. Some
ALTER DATABASE commands applied to the CDB will by definition affect all PDBs plugged into the CDB.
Modify a PDB (ALTER PLUGGABLE DATABASE)
Remember, to target the PDB you must either connect directly to a privileged user using a service pointing to the PDB, or connect to the root container and switch to the PDB container. Some of the possible PDB modifications are shown below.
CONN / AS SYSDBA ALTER SESSION SET CONTAINER = pdb1; -- Default edition for PDB. ALTER PLUGGABLE DATABASE DEFAULT EDITION = ora$base; -- Default tablespace type for PDB. ALTER PLUGGABLE DATABASE SET DEFAULT BIGFILE TABLESPACE; ALTER PLUGGABLE DATABASE SET DEFAULT SMALLFILE TABLESPACE; -- Default tablespaces for PDB. ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE users; ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE temp; -- Change the global name. This will change the container name and the -- name of the default service registered with the listener. ALTER PLUGGABLE DATABASE OPEN RESTRICTED FORCE; ALTER PLUGGABLE DATABASE RENAME GLOBAL_NAME TO pdb1a.localdomain; ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE; ALTER PLUGGABLE DATABASE OPEN; -- Time zone for PDB. ALTER PLUGGABLE DATABASE SET TIME_ZONE='GMT'; -- Make datafiles in the PDB offline/online and make storage changes. ALTER PLUGGABLE DATABASE DATAFILE '/u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf' OFFLINE; ALTER PLUGGABLE DATABASE DATAFILE '/u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf' ONLINE; ALTER PLUGGABLE DATABASE DATAFILE '/u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf' RESIZE 1G AUTOEXTEND ON NEXT 1M; -- Supplemental logging for PDB. ALTER PLUGGABLE DATABASE ADD SUPPLEMENTAL LOG DATA; ALTER PLUGGABLE DATABASE DROP SUPPLEMENTAL LOG DATA;
In addition there is a mechanism to control the maximum size of the PDB and the amount of the shared temp space it can use.
-- Limit the total storage of the the PDB (datafile and local temp files). ALTER PLUGGABLE DATABASE STORAGE (MAXSIZE 5G); -- Limit the amount of temp space used in the shared temp files. ALTER PLUGGABLE DATABASE STORAGE (MAX_SHARED_TEMP_SIZE 2G); -- Combine the two. ALTER PLUGGABLE DATABASE STORAGE (MAXSIZE 5G MAX_SHARED_TEMP_SIZE 2G); -- Remove the limits. ALTER PLUGGABLE DATABASE STORAGE UNLIMITED;