Configure and modify CDB and PDB parameters in Oracle12c

Configure and modify CDB and PDB parameters in Oracle12c

 

 

 

1. Configure CDB instance Parameters

 

The instance parameters for configuring CDB are the same as those for normal instances. You can use alter system to set initialization parameters. For some database configurations, you can use the ALTERDATABASE command.

 

After connecting to the rootcontainer, you can use the alter system command to modify it. By default, it is only valid for the root container, that is, the current container. The following two commands serve the same purpose:

 

Alter system set parameter_name = value;

Alter system set parameter_name = valueCONTAINER = CURRENT;

 

 

To make the modification valid for all container, use the following syntax:

Alter system set parameter_name = valueCONTAINER = ALL;

 

If all is used in the root container, all PDB will inherit these parameters unless this parameter is modified separately in PDB.

 

2. Configure PDB instance Parameters

 

If CONTAINER = ALL is set in rootcontainer, the PDB parameter also inherits the previous value, but it can be modified in PDB container through alter system. Note that this parameter can be modified only when it is supported. You can use the following command to view it:

column name format a35column value format a35select name, valuefrom v$system_parameterWHERE ispdb_modifiable = 'TRUE'order by name;

-- Here I have 174 parameters, which can be modified in PDB:

SQL> select count(1) fromv$system_parameter where ispdb_modifiable='TRUE';COUNT(1)----------174

 

The procedure is as follows:

CONN / AS SYSDBAALTER SESSION SET CONTAINER = pdb1;ALTER SYSTEM SET parameter_name=value;ALTER SYSTEM SET parameter_name=valueCONTAINER=CURRENT;

 

3. Modify CDB (alter database)

 

The alter database command is the same as the non-cdb database Command. Some commands can affect CDB and all PDB, and some are only valid for the root container. For more information, see the official website.

 

Http://docs.oracle.com/cd/E16655_01/server.121/e17636/cdb_admin.htm#ADMIN13633

 

 

4. Modify PDB (alter pluggabledatabase)

 

After specifying a specific PDB, you can use the alterpluggable database command to modify the PDB information. Of course, for backward compatibility, the alter database command can also complete most of the modifications.

 

The specific modification operations are as follows:

CONN / AS SYSDBAALTER SESSION SET CONTAINER = pdb1;-- Default edition forPDB.ALTER PLUGGABLE DATABASE DEFAULT EDITION =ora$base;-- Default tablespacetype for PDB.ALTER PLUGGABLE DATABASE SET DEFAULTBIGFILE TABLESPACE;ALTER PLUGGABLE DATABASE SET DEFAULTSMALLFILE TABLESPACE;-- Default tablespacesfor PDB.ALTER PLUGGABLE DATABASE DEFAULT TABLESPACEusers;ALTER PLUGGABLE DATABASE DEFAULT TEMPORARYTABLESPACE temp;-- Change the globalname. This will change the container name and the-- name of the defaultservice registered with the listener.ALTER PLUGGABLE DATABASE OPEN RESTRICTEDFORCE;ALTER PLUGGABLE DATABASE RENAME GLOBAL_NAMETO pdb1a.localdomain;ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;ALTER PLUGGABLE DATABASE OPEN;-- Time zone for PDB.ALTER PLUGGABLE DATABASE SETTIME_ZONE='GMT';-- Make datafiles in thePDB 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 loggingfor PDB.ALTER PLUGGABLE DATABASE ADD SUPPLEMENTALLOG DATA;ALTER PLUGGABLE DATABASE DROP SUPPLEMENTALLOG DATA;In addition there is amechanism to control the maximum size of the PDB and the amount of the sharedtemp space it can use.-- Limit the totalstorage of the the PDB (datafile and local temp files).ALTER PLUGGABLE DATABASE STORAGE (MAXSIZE5G);-- Limit the amount oftemp space used in the shared temp files.ALTER PLUGGABLE DATABASE STORAGE(MAX_SHARED_TEMP_SIZE 2G);-- Combine the two.ALTER PLUGGABLE DATABASE STORAGE (MAXSIZE5G MAX_SHARED_TEMP_SIZE 2G);-- Remove the limits.ALTER PLUGGABLE DATABASE STORAGE UNLIMITED;