Configure instance parameters and modify container database (CDB) and pluggable Database (PDB) in oracle12c

The multi-homed option in ORACLE12C allows a container database (CDB) to accommodate multiple separate pluggable databases (PDB). This article shows you how to configure instance parameters and modify the container database (CDB) and the pluggable database (PDB).
1. Configuring instance parameters in CDB (ALTER SYSTEM)
There is nothing different about configuring CDB instance parameters and configuring non-CDB instance parameters. The alter system command is used to set initialization parameters, while some database configuration modifications require the ALTER DATABASE command.
When logged on as a privileged user and connected to the root container, any alter system command will be used by default for that root container. This means that the following two commands are the same in this case.
ALTER SYSTEM SET Parameter_name=value;
ALTER SYSTEM SET parameter_name=value container=current;
In addition to the default action, changing the initialization parameters in the root container can use the following syntax to act on all containers.
ALTER SYSTEM SET parameter_name=value container=all;
By using Container=all, you indicate that PDBs inherits the determination parameters of the root container. Unless the same parameter settings are overridden by local parameters, any modifications to the root container that determine parameters are inherited by PDBs.
You can override some of the parameter settings by publishing the local alter system command in PDBs.
2. Configuring instance parameters in the PDB (ALTER SYSTEM)
Earlier, we mentioned that you can use the ALTER system command with the Container=all clause in the root container to set the instance parameters of all PDBs in the root container. These settings can be overridden by the local alter system command in the PDB, even when these parameter settings are inherited. Only a subset of the initialization parameters can be modified in the PDB, and these parameters can be obtained through 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;
In order to modify the local PDB settings, you need to be sure that a privileged user is connected to the PDB, or connect to the PDB through a generic user. As mentioned earlier, if you do not use the container clause, modify the settings of the current container. Therefore, the following alter SYSTEM command functions as equivalent.
Conn/as SYSDBA
ALTER SESSION SET CONTAINER = pdb1;


ALTER SYSTEM SET Parameter_name=value;
ALTER SYSTEM SET parameter_name=value container=current;
3. Modify CDB (Alter DATABASE)
From the CDB perspective, the ALTER Database command is similar to non-CDB databases. All you need to know is the scope of the modifications being made. Some ALTER DATABASE commands that are applied to the CDB will affect all PDBs in the CDB. Others only act on CDB's own.
4. Modify the PDB (alter pluggable DATABASE)
You can modify the PBD by connecting to the relevant container and the ALTER pluggable database command. But for backwards compatibility, the ALTER database command can make most of the changes. Undoubtedly, these modifications are only part of CDB or non-CDB.
Remember, in order to modify the PDB, you must connect directly to the privileged user through the service name that points to the PDB, or connect to the root container first, and then switch to the PDB container. Some of the modifications available for the PDB are shown below.
Conn/as SYSDBA
ALTER SESSION SET CONTAINER = pdb1;


--The default version of the 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 table space for PDB
ALTER pluggable DATABASE DEFAULT tablespace users;
ALTER pluggable DATABASE DEFAULT temporary tablespace temp;


--Change the global name. This will also change the container name and the default service name registered to 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;


--The time zone of the PDB
ALTER pluggable DATABASE SET time_zone= ' GMT ';


--Make PDB data files offline/online and make storage modifications
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 log for PDB
ALTER pluggable DATABASE ADD supplemental LOG DATA;
ALTER pluggable DATABASE DROP supplemental LOG DATA;
In addition, there are mechanisms to control the maximum size of the PDB and the size of the available shared temporary space.
--Limit the total amount of PDB storage (data files and local temporary files).
ALTER pluggable DATABASE STORAGE (MAXSIZE 5G);


--Limit the size of available shared temporary files
ALTER pluggable DATABASE STORAGE (max_shared_temp_size 2G);


--Combine the two above
ALTER pluggable DATABASE STORAGE (MAXSIZE 5G max_shared_temp_size 2G);


--Removal restrictions
ALTER pluggable DATABASE STORAGE UNLIMITED;

Configure instance parameters and modify container database (CDB) and pluggable Database (PDB) in oracle12c