Note:

About Creating a CDB with the CREATE DATABASE Statement

Creating a CDB using the CREATE DATABASE SQL statement is very similar to creating a non-CDB. This section describes additional requirements for creating a CDB.

Using the CREATE DATABASE SQL statement is a more manual approach to creating a CDB than using DBCA. One advantage of using this statement over using DBCA is that you can create CDBs from within scripts.

When you create a CDB using the CREATE DATABASE SQL statement, you must enable PDBs and specify the names and locations of the root‘s files and the seed‘s files.

This section contains the following topics:

• About Enabling PDBs

• About the Names and Locations of the Root‘s Files and the Seed‘s Files

• About the Attributes of the Seed‘s Data Files

SEEDFILE_NAME_CONVERT = (‘/oracle/dbs/‘, ‘/oracle/pdbseed/‘)

DATAFILE ‘/u01/app/oracle/oradata/newcdb/system01.dbf‘ SIZE 325M REUSESYSAUX DATAFILE ‘/u01/app/oracle/oradata/newcdb/sysaux01.dbf‘ SIZE 325M REUSE

SEEDSYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITEDSYSAUX DATAFILES SIZE 100M

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. You perform these actions by running the supplied catcdb.sql script.

The instructions in this section apply to single-instance installations only. See the Oracle Real Application Clusters (Oracle RAC) installation guide for your platform for instructions for creating an Oracle RAC CDB.

The examples in the following steps create a CDB named newcdb.

To create a CDB with the CREATE DATABASE statement: 

1. Complete steps 1 - 8 in "Creating a Database with the CREATE DATABASE Statement".

   To create a CDB, the ENABLE_PLUGGABLE_DATABASE initialization parameter must be set to TRUE.

   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. For more information, see the discussion of the DB_NAME initialization parameter in Oracle Database Reference.

2. Use the CREATE DATABASE statement to create a new CDB.

   The following examples illustrate using the CREATE DATABASE statement to create a new CDB:

   • Example 1: Creating a CDB Without Using Oracle Managed Files

   • Example 2: Creating a CDB Using Oracle Managed Files

   Example 1: 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. 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 seed. This example also includes the SEEDFILE_NAME_CONVERT clause to specify the names and locations of the seed‘s files. This example also includes tablespace_datafile clauses that specify attributes of the seed‘s data files for the SYSTEM and SYSAUX tablespaces that differ from the root‘s.

   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;

   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. See "Determining the Global Database Name".

   • Three control files are created as specified by the CONTROL_FILES initialization parameter, which was set before CDB creation in the initialization parameter file. See "Sample Initialization Parameter File" and "Specifying Control Files".

   • 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. For further information about the use of these clauses, see "Protecting Your Database: Specifying Passwords for Users SYS and SYSTEM".

   • The new CDB has three online redo log file groups, each with two members, as specified in the LOGFILE clause. MAXLOGFILES, MAXLOGMEMBERS, and MAXLOGHISTORY define limits for the redo log. See "Choosing the Number of Redo Log Files". 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. See "Planning the Block Size of Redo Log Files" for more information.

   • 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.

     Note:

     You can set several limits during CDB creation. Some of these limits are limited by and affected by operating system limits. For example, if you set MAXDATAFILES, then Oracle Database allocates enough space in the control file to store MAXDATAFILES filenames, even if the CDB has only one data file initially. However, because the maximum control file size is limited and operating system dependent, you might not be able to set all CREATE DATABASE parameters at their theoretical maximums.

     For more information about setting limits during CDB creation, see the Oracle Database SQL Language Referenceand your operating system–specific Oracle documentation.

   • 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 NCHAR, NCLOB, 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. See "Creating a Locally Managed SYSTEM Tablespace".

   • A SYSAUX tablespace is created, consisting of the operating system file /u01/app/oracle/oradata/newcdb/sysaux01.dbf as specified in theSYSAUX DATAFILE clause. See "About the SYSAUX Tablespace".

   • 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 this CDB. See "Creating a Default Temporary Tablespace".

   • 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. See "Using Automatic Undo Management: Creating an Undo Tablespace".

   • 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. See Chapter 12, "Managing Archived Redo Log Files".

   • The ENABLE PLUGGABLE DATABASE clause creates a CDB with the root and the 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 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 seed‘s SYSTEM tablespace data file(s) that differ from the root‘s.

   • The SYSAUX DATAFILES clause specifies attributes of the seed‘s SYSAUX tablespace data file(s) that differ from the root‘s.

   • The USER_DATA TABLESPACE clause creates and names the seed‘s tablespace for storing user data and database options such as Oracle XML DB. PDBs created using the seed include this tablespace and its data file. The tablespace and data file specified in this clause are not used by the root.

   Note:

   • Ensure that all directories used in the CREATE DATABASE statement exist. The CREATE DATABASE statement does not create directories.

   • If you are not using Oracle Managed Files, then every tablespace clause must include a DATAFILE or TEMPFILEclause.

   • If CDB creation fails, then you can look at the alert log to determine the reason for the failure and to determine corrective actions. See "Viewing the Alert Log". If you receive an error message that contains a process number, then examine the trace file for that process. Look for the trace file that contains the process number in the trace file name. See "Finding Trace Files" for more information.

   • To resubmit the CREATE DATABASE statement after a failure, you must first shut down the instance and delete any files created by the previous CREATE DATABASE statement.