Lesson 1

This module walks through creating a working Oracle AI Database 26ai database — the COIN database this course has been planning — by writing and executing a CREATE DATABASE statement. If you're not running Oracle 26ai yourself, follow along anyway: the process and the reasoning behind each clause apply regardless of which current release you're on.

The single fact that shapes everything else in this lesson: since Oracle Database 21c, a multitenant container database (CDB) is the only supported architecture. There is no non-CDB option anymore. When you run CREATE DATABASE, you are always creating a CDB — a root container with its own data files, its own control file, and its own online redo log. Any pluggable databases (PDBs) you want inside it come from a separate CREATE PLUGGABLE DATABASE statement, run afterward. PDBs don't get their own control file or redo log; they share the CDB's.

On older Oracle releases, "creating a database" also meant a checklist of manual follow-up work: building the data dictionary views by hand, installing the built-in PL/SQL packages, setting up the product user profile, laying out initial tablespaces, and creating rollback segments. Most of that list no longer describes real work. Oracle automatic undo management replaced manually created rollback segments years ago — you specify an UNDO TABLESPACE and Oracle manages the rest. The product user profile is SQL*Plus-era terminology with no place in current architecture. And whether you create the database through the Database Configuration Assistant (DBCA) or your own script, the dictionary views, PL/SQL packages, and default tablespaces — SYSTEM, SYSAUX, UNDO, and TEMP — come into existence as part of that one CREATE DATABASE step, not as separate housekeeping afterward. By the time the statement finishes, you have a fully functional, empty CDB ready for PDBs and real work.

The CREATE DATABASE clause itself is mandatory:

CREATE DATABASE database_name;

If you omit database_name, Oracle falls back to the DB_NAME parameter from whichever initialization parameter file the instance is using — either a traditional text-based PFILE (commonly named init.ora) or a server parameter file (SPFILE). Both are fully supported in 26ai; PFILE isn't deprecated, it's just less convenient day to day, since SPFILE lets ALTER SYSTEM changes persist automatically instead of requiring manual file edits. See Module 6's lesson on database parameters for the full PFILE-versus-SPFILE comparison. Whichever file you use, the name you supply on the command line should match DB_NAME exactly.

CONTROLFILE REUSE is optional, and matters only when the control files named by the CONTROL_FILES parameter already exist on disk. Without it, Oracle refuses to overwrite existing control files and halts with an error — a deliberate safeguard against accidental data loss. With it, Oracle reuses (overwrites) those files instead of erroring out, which is the normal case when you're recreating a database, restoring from backup, or reinitializing an instance:

CREATE DATABASE mydb
CONTROLFILE REUSE;

MAXLOGFILES sets the ceiling on how many redo log groups the database can ever have — a platform-specific default, often 16 or 32. MAXLOGMEMBERS sets the ceiling on how many members (mirrored copies) each redo log group can hold, which is what gives you redundancy against losing a single redo log file. Both limits get baked into the control file's preallocated space at creation time, and neither can be changed afterward without recreating the control file:

MAXLOGFILES 32
MAXLOGMEMBERS 3

MAXDATAFILES sets the upper limit on how many datafiles the database can ever have. Like the log-file limits above, it affects how much space Oracle preallocates in the control file, so it's worth setting generously enough to cover the database's expected growth rather than just its starting size:

MAXDATAFILES 100

CHARACTER SET specifies how the database stores text data, and it's not a setting to treat casually — changing it after creation is disruptive and complex, so get it right up front. AL32UTF8 is the recommended choice for full Unicode support and is what most new 26ai databases should use; WE8MSWIN1252 exists for legacy Western European Windows environments but isn't a starting point for new work. NATIONAL CHARACTER SET is a separate, narrower setting that governs only the NCHAR, NVARCHAR2, and NCLOB datatypes, and is typically set to AL16UTF16:

CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16

UNDO TABLESPACE names the tablespace Oracle uses for undo data — the information behind read consistency and transaction rollback. This is the modern replacement for manually managed rollback segments described earlier: you name an undo tablespace, and Oracle's automatic undo management handles the rest. The tablespace itself is created separately, or automatically if the CREATE DATABASE statement includes a DEFAULT UNDO TABLESPACE clause:

UNDO TABLESPACE undotbs1

One detail worth knowing before you run this yourself: Oracle sets newly created user tablespaces to be locally managed by default. If you create the database through DBCA, the SYSTEM tablespace itself also ends up locally managed by default. If you run CREATE DATABASE manually and accept the defaults, SYSTEM instead comes out dictionary managed — a legacy, deprecated mode you don't want. This is precisely why Oracle recommends using DBCA for new databases: it's the path that reliably gets SYSTEM onto the locally managed format.

A complete example, incorporating everything above:

CREATE DATABASE coin
   USER SYS IDENTIFIED BY "your_sys_password"
   USER SYSTEM IDENTIFIED BY "your_system_password"
   CONTROLFILE REUSE
   LOGFILE GROUP 1 (
     '/oradata02/coin/redo0101.log',
     '/oradata03/coin/redo0102.log'
   ) SIZE 200M,
   GROUP 2 (
     '/oradata02/coin/redo0201.log',
     '/oradata03/coin/redo0202.log'
   ) SIZE 200M
   MAXLOGFILES 32
   MAXLOGMEMBERS 3
   MAXDATAFILES 100
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   UNDO TABLESPACE undotbs1
   DATAFILE '/oradata01/coin/system01.dbf' SIZE 800M
     AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;

-- Additional files typically added alongside the CREATE DATABASE statement:
DATAFILE '/oradata01/coin/sysaux01.dbf' SIZE 500M
   AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;
DATAFILE '/oradata01/coin/undotbs01.dbf' SIZE 200M
   AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;
TEMPFILE '/oradata01/coin/temp01.dbf' SIZE 100M
   AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;

A few things to note about this example. Oracle 26ai requires you to supply passwords for the SYS and SYSTEM users at creation time — there's no way around that anymore. SYSAUX, UNDO, and TEMP are effectively mandatory alongside SYSTEM; a real database doesn't function without them. And CONTROLFILE REUSE only matters if control files already exist at those paths — for a genuinely new database, on new storage, you can safely leave it out.

Each clause in the CREATE DATABASE statement ultimately controls one of three physical structures: the control file, the redo log groups, or the datafiles. CONTROLFILE REUSE governs the control file directly. MAXLOGFILES and MAXLOGMEMBERS govern the redo log groups and their mirrored members. MAXDATAFILES, CHARACTER SET, and UNDO TABLESPACE all bear on the datafiles — how many can exist, what they can store, and where undo data lives among them. Every one of those limits gets locked into the control file's preallocated space the moment the database is created, which is exactly why none of them can be changed later without recreating the control file from scratch.

The LOGFILE clause specifies where the online redo log files live. Leave out the GROUP clause and Oracle creates the files you list as separate groups, one member each; a database needs at least two redo log groups to function. In the worked example above, Oracle creates two groups with two members apiece — a sensible minimum for redundancy. Keep all redo log groups the same size; mixed sizes complicate log switching for no real benefit. The REUSE keyword, when applied to individual redo log files, overwrites an existing file of the same name provided the size matches.

The five limit clauses that follow — MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES — all drive how much space Oracle preallocates in the control file, which is why every one of them is fixed at creation time. MAXLOGFILES caps how many redo log groups the database can ever have. MAXLOGMEMBERS caps how many members (mirrored copies) each group can hold. MAXLOGHISTORY is relevant to Oracle Real Application Clusters (Oracle RAC) configurations specifically — in a single-instance database it's largely inert — and specifies the maximum number of archived redo log files retained for automatic media recovery. MAXDATAFILES caps how many datafiles the database can have in total; new datafiles get created whenever you create a tablespace or extend an existing one. MAXINSTANCES caps how many instances — relevant again for Oracle RAC — can simultaneously mount and open the database. Change your mind about any of these five after the database exists, and the only way forward is recreating the control file.

Nothing in this lesson requires you to type a CREATE DATABASE statement by hand. Oracle documents two supported paths to a new database: the Database Configuration Assistant (DBCA), or supplying your own script. Both are legitimate; they just trade off differently.

DBCA is the path Oracle recommends, and for a specific reason beyond convenience: it's the reliable way to get a locally managed SYSTEM tablespace. Run CREATE DATABASE manually and accept the defaults, and SYSTEM comes out dictionary managed instead — a deprecated, legacy mode. DBCA also walks you through memory sizing, character set selection, and initial tablespace layout with sensible defaults, and generates the underlying script for you, so you can inspect exactly what it's about to run before committing to it. For most new databases, especially your first few, this is the path worth taking.

A hand-written script earns its place when you need something DBCA's interactive flow doesn't easily give you: identical, repeatable database creation across many environments, integration into a larger provisioning pipeline, or fine-grained control over every clause discussed above. The tradeoff is that you're now responsible for getting the SYSTEM tablespace onto a locally managed footing yourself, along with every other default DBCA would otherwise have handled. Scripted creation is common in production automation; it's simply a path that assumes you already know what DBCA would have chosen for you and are choosing deliberately not to rely on it.

Running CREATE DATABASE gives you a CDB — a root container, complete and functional, but empty of any pluggable databases. That's a deliberate two-step design, not an oversight: the CDB provides the shared infrastructure (control file, redo log, background processes), and each PDB you create afterward gets its own dedicated set of datafiles within that shared infrastructure, isolated from any other PDBs in the same CDB.

Adding a PDB is a separate statement, run against the CDB once it's open:

CREATE PLUGGABLE DATABASE coin_pdb1
   ADMIN USER pdb_admin IDENTIFIED BY "your_admin_password"
   FILE_NAME_CONVERT = ('/oradata01/coin/pdbseed/', '/oradata01/coin/coin_pdb1/');

The FILE_NAME_CONVERT clause tells Oracle where to copy the new PDB's datafiles from the PDB$SEED template that ships as part of every CDB. Every PDB you create is, in effect, a copy of that seed — which is also why a PDB never gets its own control file or online redo log of its own: those stay at the CDB level, shared by every PDB inside it. This is the architecture point worth carrying forward from this lesson into everything that follows in this course: a database, in Oracle AI Database 26ai, is a CDB plus however many PDBs you've chosen to plug into it, and the CREATE DATABASE statement covered above is only ever building the first half of that picture.