Writing the CREATE DATABASE Command in Oracle 23c

In real-world environments you will usually use Database Configuration Assistant (DBCA) or cloud automation to create Oracle databases. However, every architect and DBA should understand the CREATE DATABASE command itself, because it defines the physical and logical structure of a new database and explains what DBCA is doing behind the scenes.

In Oracle 23c, the recommended architecture is multitenant: you create a Container Database (CDB) and then create one or more Pluggable Databases (PDBs) inside it. This lesson focuses on the correct form of the CREATE DATABASE command for a 23c CDB and highlights best practices that apply both on-premises and in Oracle Cloud.

High-Level Steps to Create a Database

At a high level, creating a database manually involves:

1. Preparing the initialization parameters (SPFILE or PFILE).
2. Starting the instance in NOMOUNT mode.
3. Issuing the CREATE DATABASE command.
4. Running catalog and PL/SQL support scripts.
5. Creating and opening one or more PDBs.

Step 1 – Prepare Initialization Parameters

Before you can issue CREATE DATABASE, you must define basic initialization parameters such as:

• DB_NAME – name of the database.
• DB_CREATE_FILE_DEST – recommended when using Oracle Managed Files (OMF).
• CONTROL_FILES (if not using OMF for control files).
• Memory settings (for example, MEMORY_TARGET or separate SGA/PGA parameters).
• Diagnostic destinations (for example, DIAGNOSTIC_DEST).

In modern setups you typically store these in an SPFILE, not a legacy init.ora text file. You then start the instance using those parameters.

Step 2 – Start the Instance in NOMOUNT Mode

Connect as SYSDBA and start the instance:

SQL> STARTUP NOMOUNT;

In NOMOUNT mode, Oracle has allocated memory and started background processes, but control files and data files do not yet exist. They are created when you run CREATE DATABASE.

Step 3 – Correct CREATE DATABASE Command for an Oracle 23c CDB

The following example shows a correct and modern CREATE DATABASE command for Oracle 23c using multitenant architecture. It explicitly manages files for teaching purposes; in production you will often prefer OMF to avoid hard-coded paths.

CREATE DATABASE cdb23c
   USER SYS IDENTIFIED BY StrongSysPassword1
   USER SYSTEM IDENTIFIED BY StrongSysPassword2
   ENABLE PLUGGABLE DATABASE
   LOGFILE
      GROUP 1 ('/u01/app/oracle/oradata/cdb23c/redo01.log') SIZE 200M,
      GROUP 2 ('/u01/app/oracle/oradata/cdb23c/redo02.log') SIZE 200M,
      GROUP 3 ('/u01/app/oracle/oradata/cdb23c/redo03.log') SIZE 200M
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
   DATAFILE
      '/u01/app/oracle/oradata/cdb23c/system01.dbf'
        SIZE 700M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
   SYSAUX DATAFILE
      '/u01/app/oracle/oradata/cdb23c/sysaux01.dbf'
        SIZE 500M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
   DEFAULT TABLESPACE users
      DATAFILE '/u01/app/oracle/oradata/cdb23c/users01.dbf'
        SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE temp
      TEMPFILE '/u01/app/oracle/oradata/cdb23c/temp01.dbf'
        SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
   UNDO TABLESPACE undotbs1
      DATAFILE '/u01/app/oracle/oradata/cdb23c/undotbs01.dbf'
        SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

Key Clauses in the Command

• ENABLE PLUGGABLE DATABASE – required for a CDB so that you can create PDBs.
• USER SYS / USER SYSTEM – sets administrative account passwords.
• LOGFILE – defines redo log groups and sizes. Even with OMF, many DBAs prefer to control the number and size of redo logs explicitly.
• CHARACTER SET – use AL32UTF8 for new Unicode databases.
• DATAFILE, SYSAUX, UNDO – define core tablespaces and their datafiles. These are required for a functional database.
• DEFAULT TABLESPACE – defines where user objects are stored by default.
• DEFAULT TEMPORARY TABLESPACE – defines where sorts and temp segments go.

Many other parameters such as MAXDATAFILES, MAXLOGFILES, and MAXLOGMEMBERS are still supported, but often unnecessary in modern environments that use sensible defaults and OMF.

Optional: Using Oracle Managed Files (OMF)

When DB_CREATE_FILE_DEST is set, Oracle can automatically create and manage file names and locations. This greatly simplifies the command and is common in Oracle Cloud and ASM-based deployments.

-- Example with OMF (files placed under DB_CREATE_FILE_DEST)
CREATE DATABASE cdb23c
   USER SYS IDENTIFIED BY StrongSysPassword1
   USER SYSTEM IDENTIFIED BY StrongSysPassword2
   ENABLE PLUGGABLE DATABASE
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
   DEFAULT TABLESPACE users
   DEFAULT TEMPORARY TABLESPACE temp
   UNDO TABLESPACE undotbs1;

In this case, Oracle chooses file names and locations based on your OMF configuration. This is often the best practice for new deployments because it reduces manual file management and naming errors.

Step 4 – Post-Creation Scripts

After the CREATE DATABASE completes successfully, you must run catalog and PL/SQL support scripts as SYSDBA:

-- As SYS in the root container
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql

-- Build SQL*Plus product user profile
CONNECT SYSTEM/StrongSysPassword2
@?/sqlplus/admin/pupbld.sql

These scripts create data dictionary views, PL/SQL packages, and other components needed by tools and applications.

Step 5 – Creating a Pluggable Database (PDB)

Once the CDB is open, you can create a PDB that applications will connect to. The following is a typical 23c example using explicit file conversion:

-- Connect as SYSDBA to the root container
CONNECT SYS/StrongSysPassword1@cdb23c AS SYSDBA;

CREATE PLUGGABLE DATABASE pdb_custom
   ADMIN USER pdbadmin IDENTIFIED BY StrongPDBAdminPwd1
   FILE_NAME_CONVERT = (
      '/u01/app/oracle/oradata/cdb23c/pdbseed/',
      '/u01/app/oracle/oradata/cdb23c/pdb_custom/'
   );

ALTER PLUGGABLE DATABASE pdb_custom OPEN;
ALTER PLUGGABLE DATABASE pdb_custom SAVE STATE;

If you use OMF and DB_CREATE_FILE_DEST, you can omit FILE_NAME_CONVERT and let Oracle generate the file layout automatically.

Parameters Commonly Used with CREATE DATABASE

The full CREATE DATABASE syntax supports many clauses. For an introductory architecture lesson, you should be able to name and classify the most important groups of parameters rather than memorize every option:

In earlier releases you might see more emphasis on low-level initialization parameters stored in init.ora. In modern 23c deployments you typically:

• Use an SPFILE rather than a text-based init.ora.
• Rely on OMF and ASM or managed storage where appropriate.
• Use DBCA or cloud provisioning wizards, and refer to the underlying CREATE DATABASE script for education and troubleshooting.

Best Practices for Students and New DBAs

• Learn the core structure of the CREATE DATABASE command rather than memorizing every clause.
• Focus on the architecture: CDB vs. PDB, redo layout, and core tablespaces.
• In lab exercises, use explicit LOGFILE and DATAFILE clauses to see exactly what is created.
• In production, prefer DBCA or automated provisioning with OMF and ASM for manageability and safety.

Write CREATE DATABASE – Exercise