Executing the CREATE DATABASE Command in Oracle AI Database 26ai

You've written the CREATE DATABASE statement for the COIN database in a previous lesson, and started the instance in NOMOUNT mode in the lesson before that. This lesson closes the loop: assembling the command into a script, running it correctly, and confirming that COIN actually came up. None of the three previous lessons on their own get you a running database — this one does.

Before going further, one correction worth making up front, because it changes how you'll actually type the commands below. Older material on this topic — including the version of this lesson that predates this rewrite — describes running your script "from Server Manager." Server Manager (svrmgrl) was desupported starting with Oracle 8i, back in 1999. Every function it used to provide was absorbed into SQL*Plus, which has been the only tool for this job for over two decades now. The command syntax itself hasn't changed — only the tool name has — so if you've seen "Server Manager" anywhere else, mentally substitute SQL*Plus and everything else still applies.

CREATE DATABASE won't succeed — or worse, will succeed against the wrong files — if any of the following aren't already in place:

• You're connected with the SYSDBA privilege. Use CONNECT / AS SYSDBA (operating system authentication) or CONNECT SYS AS SYSDBA (password file authentication), exactly as covered in the instance-startup lesson. CREATE DATABASE is not something an ordinary user account can run, regardless of what other privileges that account holds.
• The instance is already started in NOMOUNT mode. If you haven't run STARTUP NOMOUNT yet, do that first. CREATE DATABASE is what actually builds the control file, data files, and redo logs — there's nothing for it to act on if the instance isn't running yet, and nothing for it to mount if you've started it any further than NOMOUNT.
• An initialization parameter file exists and contains DB_NAME. Whether you're using a text-based PFILE or an SPFILE, the instance needs to know what database name to expect, and that name needs to match what you're about to type into the CREATE DATABASE statement itself.

One warning, stated plainly rather than assumed: CREATE DATABASE erases all data in any data files you specify that already exist at those paths. If you're reusing a directory from an earlier, failed attempt, or from a previous exercise, double-check what's actually sitting there before you run this. There's no confirmation prompt standing between you and that outcome.

Hopefully you've stored your CREATE DATABASE statement in a SQL file already — create_coin.sql, say. Before you execute it, wrap the actual statement in two more commands:

SET ECHO ON
SPOOL create_coin.lis
your CREATE DATABASE command goes here
SPOOL OFF
SET ECHO OFF

What do these two commands actually buy you? SET ECHO ON causes every command in the script to be echoed to the screen as it executes, rather than running silently. When you're creating a database — an operation with a dozen or more clauses, any one of which can be the source of an error — you want to see exactly which line triggered a problem, not just the error text on its own with no context. SPOOL create_coin.lis redirects all of that output to a file at the same time it's displayed on screen. Always do this. It gives you a permanent record of the database being created, and if something does go wrong partway through, the failure — along with everything that ran successfully before it — is captured in that file rather than lost the moment you scroll past it in the terminal.

Here's the actual statement to drop into the placeholder above — the same COIN example built up in the CREATE DATABASE syntax lesson, now assembled as a complete, runnable script:

SET ECHO ON
SPOOL create_coin.lis

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;

SPOOL OFF
SET ECHO OFF

Notice the ENABLE PLUGGABLE DATABASE clause near the end — every database you create from this point forward is a multitenant Container Database (CDB); there's no non-CDB option to fall back to. That single statement is building the CDB root and its PDB seed together in one pass.

It's worth pausing on what happens physically while this statement runs, rather than treating it as a black box that either succeeds or fails. Working through the clauses in the order Oracle processes them:

• The LOGFILE clause creates the redo log groups first — three groups, two hundred megabytes each, in this example — because Oracle needs somewhere to record changes before it can safely create anything else.
• The bare DATAFILE clause creates the SYSTEM tablespace's data file, followed immediately by SYSAUX DATAFILE. These two are non-negotiable — there's no functioning Oracle database without them.
• DEFAULT TABLESPACE and DEFAULT TEMPORARY TABLESPACE create the tablespaces ordinary user objects and sort operations will land in later — users and temp here — separate from SYSTEM by design, so routine application data never has to compete for space with the data dictionary.
• UNDO TABLESPACE creates the tablespace backing every read-consistent query and rollback in the database from this point forward.
• Finally, ENABLE PLUGGABLE DATABASE ... SEED creates the CDB root alongside a fresh copy of the PDB seed — the template every future CREATE PLUGGABLE DATABASE statement will clone from.

Every one of those files gets written to disk during this single statement — which is also exactly why the erasure warning above matters as much as it does. There's no separate, undoable step where Oracle asks "are you sure?" before writing over whatever was previously at those paths.

Since CREATE DATABASE touches this many files, an error is more the norm than the exception the first time you run this — a typo in a path, a directory that doesn't exist yet, or a permissions problem are all common. Suppose one of the directories referenced above hasn't been created on disk. Your create_coin.lis file would show something like:

CREATE DATABASE coin
*
ERROR at line 1:
ORA-01119: error in creating database file
'/u01/app/oracle/oradata/coin/system01.dbf'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory

This is exactly the value SET ECHO ON and SPOOL provide together: the log shows the failing clause (CREATE DATABASE coin, line 1 — because the whole statement is one logical unit) alongside the specific underlying OS error (Linux Error: 2: No such file or directory), rather than leaving you to guess which of a dozen file paths was the problem. The fix here is simply creating the missing directory structure before re-running the script — but knowing that takes seconds with a log like this, versus a much longer guessing exercise without one.

With the script saved as create_coin.sql, connect to your NOMOUNT instance in SQL*Plus and run it with the @ symbol:

SQL> @create_coin

The @ symbol is the critical element — it tells SQL*Plus that whatever follows is a filename. SQL*Plus looks for a file named create_coin.sql in the current directory (or anywhere on your configured SQLPATH) and executes every command inside it, in order. Depending on your storage configuration and the sizes specified above, this can take anywhere from under a minute to several minutes — Oracle is physically allocating every data file, redo log, and control file you specified, not just registering metadata.

Once the script finishes, open create_coin.lis — the file your SPOOL command created. Because SET ECHO ON was in effect, you'll see every clause of the CREATE DATABASE statement exactly as it ran, interleaved with Oracle's responses. A successful run ends with:

Database created.

If something failed instead, the log shows you precisely which clause was executing when the error occurred — far more useful for troubleshooting than an error message with no surrounding context. Keep this file. It's your record of exactly how and when the COIN database was created, which matters more than it might seem the first time you run this; six months from now, when you're trying to remember what character set you chose, this log has the answer.

A clean "Database created." message is a good sign, but it's worth confirming the database is genuinely open and usable before moving on. From the same SQL*Plus session:

SQL> SELECT name, open_mode FROM v$database;

NAME      OPEN_MODE
--------- --------------------
COIN      READ WRITE

SQL> SELECT instance_name, status FROM v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
coin             OPEN

OPEN_MODE of READ WRITE and instance STATUS of OPEN together confirm the CDB root is fully up. The PDB seed created alongside it exists, but a fresh CDB has no other pluggable databases yet — that's the subject of the CREATE PLUGGABLE DATABASE step covered in the CREATE DATABASE command lesson, and something you'd revisit once COIN itself is confirmed healthy.

Everything in this lesson is worth knowing, but it's not how most real databases get created day to day. Oracle recommends the Database Configuration Assistant (DBCA), or runInstaller -createDatabase, for most installations — particularly CDBs with multiple PDBs, where DBCA's interactive defaults save you from hand-typing dozens of file paths and sizes. The manual, scripted approach you've just walked through suits a different situation: repeatable, scripted, or highly customized deployments where you need to know — and control — exactly what's being created. Both paths produce the same kind of database; this lesson exists so that when DBCA runs, you understand what it's actually doing on your behalf.

Click this Exercise link below to do an ordering exercise on the database creation process.
Create Database Command - Exercise

Click this Exercise link below to perform this task for your own database.
Create Database Command - Exercise