DBA Hub

📋Steps in this guide1/5

Multitenant : Configure Instance Parameters and Modify Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 1 (12.1)

This article shows how to configure instance parameters and modify the database for container databases (CDB) and pluggable databases (PDB) in Oracle Database 12c Release 1 (12.1).

oracle 12cconfigurationintermediate
by OracleDba
33 views
1

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 command is used to set initialization parameters, with some database configuration modified using the command. When connected as a privileged user and pointing to the root container, any command will by default be directed at just the root container. This means the following two commands are functionally equivalent in this context. In addition to the default action, an initialization parameter change from the root container can target all containers using the following syntax. By using 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. See documentation here .

Code/Command (click line numbers to comment):

1
2
3
4
alter system set parameter_name=value;
alter system set parameter_name=value container=current;

alter system set parameter_name=value container=all;
2

Configure Instance Parameters in a PDB (ALTER SYSTEM)

In the previous section we mentioned that instance parameters can be set for all PDBs belonging to the CDB by using the clause of the command from the root container. Even when this inheritance is set, the local PDB can override the setting using a local call. Only a subset of the initialization parameters can be modified locally in the PDB. These can be displayed using the following query. 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 clause is not mentioned, the current container is assumed, so the following ALTER SYSTEM commands are functionally equivalent. 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 system table, which is discussed below. See documentation here .

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
column name format a35
column value format a35
 
select name, value
from   v$system_parameter
where  ispdb_modifiable = 'TRUE'
order by name;

conn / as sysdba
alter session set container = pdb1;

alter system set parameter_name=value;
alter system set parameter_name=value container=current;
3

Modify a CDB (ALTER DATABASE)

From a CDB perspective, the command is similar to that of a non-CDB database. You just need to understand the scope of the changes you are making. Some commands applied to the CDB will by definition affect all PDBs plugged into the CDB. Others target just the root container itself. The scoping of the the command is shown in a table in the documentation here .
4

Modify a PDB (ALTER PLUGGABLE DATABASE)

Modifying a PDB is done by pointing to the relevant container and using the command, but for backward compatibility reasons the command will work for most of the possible modifications. Not surprisingly, the possible modifications available to PDB are a subset of those possible for a CDB or non-CDB 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. 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. Thanks to Pavel Rabel for pointing out the problem with this shared temporary tablespace, as described in this MOS note. PDB to Use Global CDB (ROOT) Temporary Tablespace Functionality is Missing (Doc ID 2004595.1) More configuration options are discussed in the ALTER PLUGGABLE DATABASE documentation and in the multitenant option articles on this site .

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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;

-- 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;
5

PDB_SPFILE$

As mentioned previously, initialization parameters for PDBs are stored in a system table called . There isn't much information about it in the main documentation, but it is discussed in the following MOS note. - Initialization parameters in a Multitenant database - Facts and additional information (Doc ID 2101596.1) The documentation encourages us to use the , and views, which all include a column, rather than querying the table. It is not 100% clear how the table is managed. For example, this MOS note says reset parameters remain in the table, but are marked as deleted, but there doesn't seem to be anything in the row that indicates a value is marked as deleted. - ALTER SYSTEM RESET DOESN'T UPDATE PARAM VALUE IN PDB_SPFILE$ (Doc ID 2287601.1) Clearly there are situations where the contents of the table can cause issues, and need manual intervention. In the following MOS note, the recommendation is to manually delete a specific parameter from the table and restart the PDB. - PDB Is Not Picking Up local_listener Parameter From Container Database (Doc ID 2699062.1) What I'm about to discuss is not a recommendation. It is something I have done to solve a specific situation. Do this under advice from Oracle Support, or at your own risk! One scenario that has caused issues for me relates to creating a PDB from a non-CDB instance. At the end of the process I've been left with PDB-level parameters in the table that I don't want, and resetting them at the PDB level doesn't seem to have any impact. To solve this I did the following. Get the list of parameters, including the . Delete the PDB-level parameters from the table, using the value. Restart the container database. It's rather drastic, and as I warned above, this is not a recommendation! For more information see: - Introduction to the Multitenant Architecture - Overview of the Multitenant Architecture - Managing a Multitenant Environment - ALTER SYSTEM - ALTER DATABASE - ALTER PLUGGABLE DATABASE - Multitenant : All Articles Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
set linesize 120
column pdb_name format a10
column name format a30
column value$ format a30

select ps.db_uniq_name,
       ps.pdb_uid,
       p.name as pdb_name,
       ps.name,
       ps.value$
from   pdb_spfile$ ps
       join v$pdbs p on ps.pdb_uid = p.con_uid
order by 1, 2, 3;

delete from pdb_spfile$ where pdb_uid = {your PDB_UID value};
commit;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!