Creating and Configuring a CDB

Creating and configuring a  multitenant container database (CDB) includes tasks such as planning, creating the CDB, and optionally configuring EM Express.

The procedure for creating a multitenant container database (CDB) is similar to the procedure for creating a non-CDB.

The following are typical reasons to create a CDB after installation:

  • You used Oracle Universal Installer (OUI) to install software only, and did not create a CDB.
  • You want to create another CDB on the same host as an existing CDB or an existing non-CDB. In this case, this chapter assumes that the new CDB uses the same Oracle home as the existing database. You can also create the CDB in a new Oracle home by running OUI again.

The specific methods for creating a CDB are:

  • With the Database Configuration Assistant (DBCA), a graphical tool.
  • With the CREATE DATABASE SQL statement.

Creating a CDB

You can create a CDB using DBCA or by manually issuing the CREATE DATABASE SQL statement.

CDB Creation with DBCA

Oracle strongly recommends using the Database Configuration Assistant (DBCA) to create a CDB.

DBCA offers the following advantages over alternative techniques:

  • Creation is largely automated.
  • DBCA enables you to specify the number of PDBs in the CDB when it is created.
  • When DBCA completes, the CDB is ready to use.
  • After a CDB is created, you can use DBCA to do the following:
    • Clone local PDBs
    • Plug in and unplug PDBs
    • Duplicate a CDB (silent mode only)

Depending on the type of install that you select, Oracle Universal Installer (OUI) can launch DBCA. You can also launch DBCA as a standalone tool at any time after Oracle Database installation.

You can use DBCA to create a CDB in either of the following modes:

  • Interactive mode

This mode provides a graphical interface and guided workflow for creating and configuring a CDB.

  • Non-interactive mode (also called silent mode)

This mode enables you to script a preconfigured CDB template deployment with customized PDB seed databases that are suitable for cloning. Run DBCA in silent mode by specifying command-line arguments, a response file, or both.

CDB Creation with SQL Statements

Creating a CDB using the CREATE DATABASE SQL statement is similar to creating a non-CDB.

This section describes additional requirements for creating a CDB. When you create a CDB using CREATE DATABASE, you must do the following:

  • Enable PDBs
  • Specify the names and locations of the CDB root files
  • Specify the names and locations of the PDB$SEED files
Enabling PDBs

To create a CDB, the CREATE DATABASE statement must include the ENABLE PLUGGABLE DATABASE clause.

This clause affects the CREATE DATABASE statement as follows:

  • ENABLE PLUGGABLE DATABASE is included

The statement creates a CDB with the root and PDB$SEED. You can never change the CDB into a non-CDB.

  • ENABLE PLUGGABLE DATABASE is not included

The newly created database is a non-CDB, which means that it does not include the CDB root or PDB$SEED. The non-CDB can never contain PDBs.

Names and Locations of Files for the CDB Root and PDB$SEED

To create the CDB, Oracle Database must know the names and locations of the files for the CDB root and PDB$SEED.

After the CREATE DATABASE statement completes successfully, you can use PDB$SEED and its files to create new PDBs. You cannot modify the PDB seed after it is created.

You must specify the names and locations of the files for PDB$SEED in one of the following ways:

  1. The ENABLE PLUGGABLE DATABASE SEED FILE_NAME_CONVERT clause of CREATE DATABASE
  2. Oracle Managed Files
  3. The PDB_FILE_NAME_CONVERT initialization parameter

If you use more than one technique, then the CREATE DATABASE statement uses one technique in the order of precedence of the list. For example, if you use all techniques, then the CREATE DATABASE statement only uses the specifications in the ENABLE PLUGGABLE DATABASE SEED FILE_NAME_CONVERT clause because it is first in the list.

The SEED FILE_NAME_CONVERT Clause

The SEED FILE_NAME_CONVERT clause of the CREATE DATABASE statement specifies how to generate the names of the PDB$SEED files using the names of the CDB root files.

You can use this clause to specify one of the following options:

  • One or more file name patterns and replacement file name patterns, in the following form:
'string1' , 'string2' , 'string3' , 'string4' , ...

The string2 file name pattern replaces the string1 file name pattern, and the string4 file name pattern replaces the string3 file name pattern. You can use as many pairs of file name pattern and replacement file name pattern strings as required.

If you specify an odd number of strings (the last string has no corresponding replacement string), then an error is returned. Do not specify more than one pattern/replace string that matches a single file name or directory.

File name patterns cannot match files or directories managed by Oracle Managed Files.

  • NONE when no file names should be converted. Omitting the SEED FILE_NAME_CONVERT clause is the same as specifying NONE.

SEED FILE_NAME_CONVERT Clause

This SEED FILE_NAME_CONVERT clause generates file names for the PDB$SEED files in the /oracle/pdbseed/ directory using file names in the /oracle/dbs directory.

SEED FILE_NAME_CONVERT = ('/oracle/dbs/', '/oracle/pdbseed/')
Oracle Managed Files

When Oracle Managed Files is enabled, it can determine the names and locations of the PDB$SEED files.

The PDB_FILE_NAME_CONVERT Initialization Parameter

The PDB_FILE_NAME_CONVERT initialization parameter can specify the names and locations of the seed’s files.

To use this technique, ensure that the PDB_FILE_NAME_CONVERT initialization parameter is included in the initialization parameter file when you create the CDB.

File name patterns specified in this initialization parameter cannot match files or directories managed by Oracle Managed Files.

Attributes of the Data Files for PDB$SEED

You can use the PDB seed (PDB$SEED) as a template to create new containers.

The attributes of the data files for the CDB root SYSTEM and SYSAUX tablespaces might not be suitable for the PDB seed. In this case, you can specify different attributes for the PDB seed data files by using the tablespace_datafile clauses. Use these clauses to specify attributes for all data files comprising the SYSTEM and SYSAUX tablespaces in the PDB seed. The values inherited from the root are used for any attributes whose values have not been provided.

The syntax of the tablespace_datafile clauses is the same as the syntax for a data file specification, excluding the name and location of the data file and the REUSE attribute. You can use the tablespace_datafile clauses with any of the methods for specifying the names and locations of the PDB seed’s data files.

The tablespace_datafile clauses do not specify the names and locations of the PDB seed’s data files. Instead, they specify the attributes of SYSTEM and SYSAUX data files in the PDB seed that differ from those in the root. If SIZE is not specified in the tablespace_datafile clause for a tablespace, then data file size for the tablespace is set to a predetermined fraction of the size of a corresponding root data file.

Using the tablespace_datafile Clauses

Assume the following CREATE DATABASE clauses specify the names, locations, and attributes of the data files that comprise the SYSTEM and SYSAUX tablespaces in the root.

DATAFILE '/u01/app/oracle/oradata/newcdb/system01.dbf' 
  SIZE 325M REUSE
SYSAUX DATAFILE '/u01/app/oracle/oradata/newcdb/sysaux01.dbf' 
  SIZE 325M REUSE

You can use the following tablespace_datafile clauses to specify different attributes for these data files:

SEED 
  SYSTEM DATAFILES 
    SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
  SYSAUX DATAFILES 
    SIZE 100M

In this example, the data files for the PDB seed’s SYSTEM and SYSAUX tablespaces inherit the REUSE attribute from the root’s data files. However, the following attributes of the PDB seed’s data files differ from the root’s:

  • The data file for the SYSTEM tablespace is 125 MB for the PDB seed and 325 MB for the root.
  • AUTOEXTEND is enabled for the PDB seed’s SYSTEM data file, and it is disabled by default for the root’s SYSTEM data file.
  • The data file for the SYSAUX tablespace is 100 MB for the PDB seed and 325 MB for the root.
CDB Undo Mode

Shared undo is the default. You can use the undo_mode_clause to an ENABLE PLUGGABLE DATABASE clause to specify the undo mode of the CDB.

The undo_mode_clause specifies whether the CDB undo mode is local or shared. Local undo mode means that every container in the CDB uses local undo. To configure local undo mode for the CDB, specify LOCAL UNDO ON.

Shared undo mode means that there is one active undo tablespace for a single-instance CDB, or for an Oracle RAC CDB, there is one active undo tablespace for each instance. To configure shared undo mode for the CDB, either do not specify undo_mode_clause, or specify LOCAL UNDO OFF.

Creating a CDB with the CREATE DATABASE Statement

When you use the CREATE DATABASE statement to create a CDB, you must complete additional actions before you have an operational CDB.

These actions include building views on the data dictionary tables and installing standard PL/SQL packages in the root. Perform these actions by running the supplied catcdb.sql script, which installs all components required by a CDB.

To create a CDB with the CREATE DATABASE statement:

In a CDB, the DB_NAME initialization parameter specifies the name of the root. Also, it is common practice to set the SID to the name of the root. The maximum number of characters for this name is 30.

  1. Use the CREATE DATABASE statement to create a new CDB.
  2. Run the catcdb.sql SQL script.

Enter the following in SQL*Plus to run the script:

@?/rdbms/admin/catcdb.sql
  • When prompted by the script, enter the log file directory for parameter 1 and the log file name for parameter 2.

For following example enters /tmp for the first prompt and create_cdb.log for the second prompt:

SQL> host perl -I &&rdbms_admin &&rdbms_admin_catcdb --logDirectory &&1 --logFilename &&2
Enter value for 1: /tmp
Enter value for 2: create_cdb.log
  • When prompted by the script, enter any other required information.

For example, the scripts prompts for administrator passwords and the temporary tablespace name:

Enter new password for SYS: ********
Enter new password for SYSTEM: ********
Enter temporary tablespace name: TEMP

Creating a CDB with the CREATE DATABASE Statement: Examples

These examples create a CDB named newcdb.

Creating a CDB Without Using Oracle Managed Files

The following statement creates a CDB named newcdb. This name must agree with the DB_NAME parameter in the initialization parameter file.

Assumptions

This example assumes the following:

  • The initialization parameter file specifies the number and location of control files with the CONTROL_FILES parameter.
  • The directory /u01/app/oracle/oradata/newcdb exists.
  • The directory /u01/app/oracle/oradata/pdbseed exists.
  • The directories /u01/logs/my and /u02/logs/my exist.

This example includes the ENABLE PLUGGABLE DATABASE clause to create a CDB with the root and the PDB seed. This example also includes the SEED FILE_NAME_CONVERT clause to specify the names and locations of the PDB seed’s files. This example also includes tablespace_datafile clauses that specify attributes of the PDB seed data files for the SYSTEM and SYSAUX tablespaces that differ from the root data files. This example includes the undo_mode_clause to specify that the CDB undo mode is local.

CREATE DATABASE newcdb
  USER SYS IDENTIFIED BY sys_password
  USER SYSTEM IDENTIFIED BY system_password
  LOGFILE GROUP 1 ('/u01/logs/my/redo01a.log','/u02/logs/my/redo01b.log') 
             SIZE 100M BLOCKSIZE 512,
          GROUP 2 ('/u01/logs/my/redo02a.log','/u02/logs/my/redo02b.log') 
             SIZE 100M BLOCKSIZE 512,
          GROUP 3 ('/u01/logs/my/redo03a.log','/u02/logs/my/redo03b.log') 
             SIZE 100M BLOCKSIZE 512
  MAXLOGHISTORY 1
  MAXLOGFILES 16
  MAXLOGMEMBERS 3
  MAXDATAFILES 1024
  CHARACTER SET AL32UTF8
  NATIONAL CHARACTER SET AL16UTF16
  EXTENT MANAGEMENT LOCAL
  DATAFILE '/u01/app/oracle/oradata/newcdb/system01.dbf'
    SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
  SYSAUX DATAFILE '/u01/app/oracle/oradata/newcdb/sysaux01.dbf'
    SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
  DEFAULT TABLESPACE deftbs
     DATAFILE '/u01/app/oracle/oradata/newcdb/deftbs01.dbf'
     SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
  DEFAULT TEMPORARY TABLESPACE tempts1
     TEMPFILE '/u01/app/oracle/oradata/newcdb/temp01.dbf'
     SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
  UNDO TABLESPACE undotbs1
     DATAFILE '/u01/app/oracle/oradata/newcdb/undotbs01.dbf'
     SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
  ENABLE PLUGGABLE DATABASE
    SEED
    FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/newcdb/', 
                         '/u01/app/oracle/oradata/pdbseed/')
    SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
    SYSAUX DATAFILES SIZE 100M
  USER_DATA TABLESPACE usertbs
    DATAFILE '/u01/app/oracle/oradata/pdbseed/usertbs01.dbf'
    SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
  LOCAL UNDO ON;

A CDB is created with the following characteristics:

  • The CDB is named newcdb. Its global database name is newcdb.us.example.com, where the domain portion (us.example.com) is taken from the initialization parameter file.
  • Three control files are created as specified by the CONTROL_FILES initialization parameter, which was set before CDB creation in the initialization parameter file.
  • The passwords for user accounts SYS and SYSTEM are set to the values that you specified. The passwords are case-sensitive. The two clauses that specify the passwords for SYS and SYSTEM are not mandatory in this release of Oracle Database. However, if you specify either clause, then you must specify both clauses.
  • The new CDB has three online redo log file groups, each with two members, as specified in the LOGFILE clause. MAXLOGFILESMAXLOGMEMBERS, and MAXLOGHISTORY define limits for the redo log. The block size for the redo logs is set to 512 bytes, the same size as physical sectors on disk. The BLOCKSIZE clause is optional if block size is to be the same as physical sector size (the default). Typical sector size and thus typical block size is 512. Permissible values for BLOCKSIZE are 512, 1024, and 4096. For newer disks with a 4K sector size, optionally specify BLOCKSIZE as 4096.
  • MAXDATAFILES specifies the maximum number of data files that can be open in the CDB. This number affects the initial sizing of the control file. For a CDB, set MAXDATAFILES to a high number that anticipates the aggregate number of data files for all containers, in addition to the CDB root files.
  • The AL32UTF8 character set is used to store data in this CDB.
  • The AL16UTF16 character set is specified as the NATIONAL CHARACTER SET used to store data in columns specifically defined as NCHARNCLOB, or NVARCHAR2.
  • The SYSTEM tablespace, consisting of the operating system file /u01/app/oracle/oradata/newcdb/system01.dbf, is created as specified by the DATAFILE clause. If a file with that name already exists, then it is overwritten.
  • The SYSTEM tablespace is created as a locally managed tablespace.
  • SYSAUX tablespace is created, consisting of the operating system file /u01/app/oracle/oradata/newcdb/sysaux01.dbf as specified in the SYSAUX DATAFILE clause.
  • The DEFAULT TABLESPACE clause creates and names a default permanent tablespace for this CDB.
  • The DEFAULT TEMPORARY TABLESPACE clause creates and names a default temporary tablespace for the root of this CDB.
  • The UNDO TABLESPACE clause creates and names an undo tablespace that is used to store undo data for this CDB. In a CDB, an undo tablespace is required to manage the undo data, and the UNDO_MANAGEMENT initialization parameter must be set to AUTO. If you omit this parameter, then it defaults to AUTO.
  • Redo log files will not initially be archived, because the ARCHIVELOG clause is not specified in this CREATE DATABASE statement. This is customary during CDB creation. You can later use an ALTER DATABASE statement to switch to ARCHIVELOG mode. The initialization parameters in the initialization parameter file for newcdb relating to archiving are LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_FORMAT.
  • The ENABLE PLUGGABLE DATABASE clause creates a CDB with the root and the PDB seed.
  • SEED is required for the FILE_NAME_CONVERT clause and the tablespace_datafile clauses.
  • The FILE_NAME_CONVERT clause generates file names for the PDB seed’s files in the /u01/app/oracle/oradata/pdbseed directory using file names in the /u01/app/oracle/oradata/newcdb directory.
  • The SYSTEM DATAFILES clause specifies attributes of the PDB seed SYSTEM tablespace data file(s) that differ from the root’s.
  • The SYSAUX DATAFILES clause specifies attributes of the PDB seed SYSAUX tablespace data file(s) that differ from the root’s.
  • The USER_DATA TABLESPACE clause creates and names the PDB seed’s tablespace for storing user data and database options such as Oracle XML DB. PDBs created using the PDB seed include this tablespace and its data file. The tablespace and data file specified in this clause are not used by the root.
  • The LOCAL UNDO ON clause sets the CDB undo mode to local, which means that each container in the CDB uses local undo.

When the CDB is created in local undo mode, the PDB seed includes an undo tablespace so that any new PDB created from the PDB seed has an undo tablespace. When a PDB is created by plugging it in or cloning a remote PDB, and the source PDB was in shared undo mode, an undo tablespace is created for the PDB automatically the first time the PDB is opened.

Creating a CDB Using Oracle Managed Files: Example

This example illustrates creating a CDB with Oracle Managed Files, which enables you to use a much simpler CREATE DATABASE statement.

To use Oracle Managed Files, the initialization parameter DB_CREATE_FILE_DEST must be set. This parameter defines the base directory for the various CDB files that the CDB creates and automatically names.

The following statement is an example of setting this parameter in the initialization parameter file:

DB_CREATE_FILE_DEST='/u01/app/oracle/oradata'

This example sets the parameter Oracle ASM storage:

DB_CREATE_FILE_DEST = +data

This example includes the ENABLE PLUGGABLE DATABASE clause to create a CDB with the root and the PDB seed. This example does not include the SEED FILE_NAME_CONVERT clause because Oracle Managed Files determines the names and locations of the PDB seed’s files. However, this example does include tablespace_datafile clauses that specify attributes of the PDB seed data files for the SYSTEM and SYSAUX tablespaces that differ from the root data files.

With Oracle Managed Files and the following CREATE DATABASE statement, the CDB creates the SYSTEM and SYSAUX tablespaces, creates the additional tablespaces specified in the statement, and chooses default sizes and properties for all data files, control files, and redo log files. Note that these properties and the other default CDB properties set by this method might not be suitable for your production environment, so it is recommended that you examine the resulting configuration and modify it if necessary.

CREATE DATABASE newcdb
USER SYS IDENTIFIED BY sys_password
USER SYSTEM IDENTIFIED BY system_password
EXTENT MANAGEMENT LOCAL
DEFAULT TABLESPACE users
DEFAULT TEMPORARY TABLESPACE temp
UNDO TABLESPACE undotbs1
ENABLE PLUGGABLE DATABASE
   SEED
   SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
   SYSAUX DATAFILES SIZE 100M;

A CDB is created with the following characteristics:

  • The CDB is named newcdb. Its global database name is newcdb.us.example.com, where the domain portion (us.example.com) is taken from the initialization parameter file.
  • The passwords for user accounts SYS and SYSTEM are set to the values that you specified. The passwords are case-sensitive. The two clauses that specify the passwords for SYS and SYSTEM are not mandatory in this release of Oracle Database. However, if you specify either clause, then you must specify both clauses.
  • The DEFAULT TABLESPACE clause creates and names a default permanent tablespace for this CDB.
  • The DEFAULT TEMPORARY TABLESPACE clause creates and names a default temporary tablespace for the root of this CDB.
  • The UNDO TABLESPACE clause creates and names an undo tablespace that is used to store undo data for this CDB. In a CDB, an undo tablespace is required to manage the undo data, and the UNDO_MANAGEMENT initialization parameter must be set to AUTO. If you omit this parameter, then it defaults to AUTO.
  • Redo log files will not initially be archived, because the ARCHIVELOG clause is not specified in this CREATE DATABASE statement. This is customary during CDB creation. You can later use an ALTER DATABASE statement to switch to ARCHIVELOG mode. The initialization parameters in the initialization parameter file for newcdb relating to archiving are LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_FORMAT.
  • The ENABLE PLUGGABLE DATABASE clause creates a CDB with the root and the PDB seed.
  • SEED is required for the tablespace_datafile clauses.
  • The SYSTEM DATAFILES clause specifies attributes of the PDB seed’s SYSTEM tablespace data file(s) that differ from the root’s.
  • The SYSAUX DATAFILES clause specifies attributes of the PDB seed’s SYSAUX tablespace data file(s) that differ from the root’s.

Configuring EM Express for a CDB

For a CDB, you can configure Oracle Enterprise Manager Database Express (EM Express) for the root and for each PDB by setting a global HTTPS port, or you can set a different port for every container in the CDB.

To configure EM Express for a CDB:

You can set a global port, which enables you to use EM Express to connect to all PDBs in the CDB using the HTTPS port for the CDB. Alternatively, you can set a different HTTPS port for every container in a CDB.

  1. In SQL*Plus, access a container in a CDB.

The user must have common SYSDBA administrative privilege, and you must exercise this privilege using AS SYSDBA at connect time. The container can be the root or a PDB.

  • Set the port in one of the following ways:
    • To set the global port, connect to the CDB$ROOT, and issue the following SQL statement to configure the global port for the CDB:
EXEC DBMS_XDB_CONFIG.SETGLOBALPORTENABLED(TRUE);
  • To set the HTTPS port for the current container, run the following procedure:
exec DBMS_XDB_CONFIG.SETHTTPSPORT(https_port_number);

Replace https_port_number with the appropriate HTTPS port number.

  • Access EM Express in one of the following ways:
    • To use the global port, enter the EM Express URL provided by Database Configuration Assistant (DBCA) when it configured the CDB that includes the PDB. When the EM Express login screen appears, specify your administrator credentials, and enter the name of the PDB that you want to connect to in the Container Name field.
    • The URL for the HTTPS port for a container:
https://database_hostname:https_port_number/em/

Replace database_hostname with the host name of the computer on which the database instance is running, and replace https_port_number with the appropriate HTTPS port number.

  • When connected to the root, EM Express displays data and enables actions that apply to the entire CDB. When connected to a PDB, EM Express displays data and enables actions that apply to the PDB only.
  • Note:
  • If the listener is not configured on port 1521, then you must manually configure the port for EM Express.