Writing the CREATE DATABASE Command in Oracle AI Database 26ai

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.

There's one point worth being precise about before anything else: in Oracle AI Database 26ai, multitenant isn't a recommended architecture you're choosing among alternatives — it's the only one. Since Oracle Database 21c, every database is a Container Database (CDB); there is no non-CDB option anymore. Every CREATE DATABASE statement you run creates a CDB, and you then create one or more Pluggable Databases (PDBs) inside it with a separate statement. This lesson covers the correct form of the CREATE DATABASE command for a 26ai CDB, and the steps that surround it.

High-Level Steps to Create a Database

Oracle's own documentation lays this process out in considerably more granular detail than most courses cover — worth knowing if you ever need to troubleshoot a failed creation. Condensed to what matters for understanding the architecture:

1. Choose an instance identifier (SID) and set the required environment variables.
2. Create the initialization parameter file (SPFILE) with DB_NAME and related settings.
3. Connect as SYSDBA and start the instance in NOMOUNT mode.
4. Issue the CREATE DATABASE statement, including ENABLE PLUGGABLE DATABASE.
5. Run catcdb.sql to build the CDB's data dictionary views and PL/SQL packages.
6. Run Datapatch to bring the database to the correct patch level.
7. Create and open one or more PDBs.
8. Take a full backup.

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. This must match the name you supply to CREATE DATABASE.
• DB_CREATE_FILE_DEST – set this when you want Oracle to manage file names and locations itself, using Oracle Managed Files (OMF).
• CONTROL_FILES – required if you're not using OMF for control files; specifies where they'll be created.
• ENABLE_PLUGGABLE_DATABASE – set to TRUE to allow the CDB to be created.
• Memory settings (for example, SGA_TARGET, PGA_AGGREGATE_TARGET, or MEMORY_TARGET for automatic memory management across both).
• Diagnostic destinations (for example, DIAGNOSTIC_DEST).

In modern setups you store these in an SPFILE, not a legacy init.ora text file — PFILE-based configuration is still fully supported, it's just less convenient than SPFILE for making changes that persist across restarts. 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 AI Database 26ai CDB

The following example creates the COIN CDB with explicit files, so you can see exactly what gets created. It's adapted directly from Oracle's own multitenant creation reference, including the ENABLE PLUGGABLE DATABASE SEED FILE_NAME_CONVERT clause — a piece the previous version of this lesson left out entirely, even though it's what actually generates the PDB seed's file names.

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

Key Clauses in the Command

• USER SYS / USER SYSTEM – sets administrative account passwords. These clauses aren't strictly mandatory, but if you specify one, you must specify both.
• LOGFILE – defines redo log groups and sizes. Even with OMF, many DBAs prefer to control the number and size of redo logs explicitly.
• MAXDATAFILES – for a CDB, set this to a number that anticipates the aggregate datafile count across every container you'll eventually create, not just the root.
• CHARACTER SET – use AL32UTF8 for new Unicode databases.
• DATAFILE, SYSAUX, UNDO – define core tablespaces and their datafiles. These are required for a functional CDB.
• ENABLE PLUGGABLE DATABASE ... SEED FILE_NAME_CONVERT – creates the CDB's root and PDB seed together, and generates the seed's file names from the root's. The SEED keyword — and the SYSTEM DATAFILES/SYSAUX DATAFILES sub-clauses shown above — are only required if you want the PDB seed's SYSTEM and SYSAUX files to differ from the root's; omit all three and a plain ENABLE PLUGGABLE DATABASE is enough.
• LOCAL UNDO ON – shared undo is technically the default if this clause is omitted, but local undo — a separate undo tablespace per container — is Oracle's recommended practice for a CDB. It makes PDB unplug operations and point-in-time recovery faster, so include it explicitly.

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 coin
   USER SYS IDENTIFIED BY StrongSysPassword1
   USER SYSTEM IDENTIFIED BY StrongSysPassword2
   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;

Because this example still customizes the PDB seed's SYSTEM and SYSAUX file sizes, the SEED keyword stays required even under OMF. Oracle chooses the actual file names and locations based on your DB_CREATE_FILE_DEST setting. Using OMF is often the best practice for new deployments, since it removes manual file naming as a source of error — though Oracle recommends reviewing the resulting configuration afterward, since the defaults it chooses may not fit every production environment.

Step 4 – Post-Creation Scripts

This is the step where older training material — including the previous version of this lesson — tends to fall out of date fastest. You'll still see references to running catalog.sql, catproc.sql, and pupbld.sql separately; that was accurate for a non-CDB, but a CDB in 26ai is built with a single consolidated script instead:

-- As SYS, connected to the CDB root
@?/rdbms/admin/catcdb.sql

catcdb.sql installs everything a CDB root requires — data dictionary views, synonyms, and PL/SQL packages — in one pass. It prompts interactively for the SYS and SYSTEM passwords and a temporary tablespace name, or you can set the CATCDB_SYS_PASSWD, CATCDB_SYSTEM_PASSWD, and CATCDB_TEMP environment variables beforehand to skip the prompts. It also asks for a log directory and log file name so you can review exactly what it did afterward.

Once catcdb.sql finishes, run Datapatch to bring the database up to the correct patch level — a step that has no equivalent in the older three-script sequence, and is easy to forget:

$ ./datapatch -verbose

Step 5 – Creating a Pluggable Database (PDB)

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

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

CREATE PLUGGABLE DATABASE pdb_custom
   ADMIN USER pdbadmin IDENTIFIED BY StrongPDBAdminPwd1
   FILE_NAME_CONVERT = (
      '/u01/app/oracle/oradata/coin/pdbseed/',
      '/u01/app/oracle/oradata/coin/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 26ai deployments you typically:

• Use an SPFILE rather than a text-based init.ora.
• Rely on OMF and ASM or managed storage where appropriate.
• Run catcdb.sql, not the older three-script sequence, once the CDB is created.
• 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: every database is a CDB, the PDB seed is created alongside it, and local undo is the recommended (not default) undo mode.
• 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.
• After creation, run catcdb.sql and Datapatch — both, in that order — before considering the CDB ready.

Write CREATE DATABASE – Exercise