Create Database   «Prev  Next»

Lesson 2Writing the CREATE DATABASE command
ObjectiveThe correct command to create database in Oracle 23c

Writing the CREATE DATABASE command

The process of creating a new database in Oracle Database involves starting an Oracle instance and executing a `CREATE DATABASE` command. Below is a structured breakdown of the key steps and syntax for this operation:
  1. Prepare the Environment
    • Ensure Oracle software is installed.
    • Set up necessary environment variables (ORACLE_HOME, ORACLE_SID).
    • Start the Oracle instance in NOMOUNT mode (no control files or datafiles are opened yet). 
      STARTUP NOMOUNT;
  2. Write the CREATE DATABASE Command
    • Store the command in a script file (e.g., create_db.sql) for execution.
    • Basic syntax: 
      CREATE DATABASE database_name
  USER SYS IDENTIFIED BY password
  USER SYSTEM IDENTIFIED BY password
  LOGFILE GROUP 1 ('/path/to/redo01.log') SIZE 100M,
                GROUP 2 ('/path/to/redo02.log') SIZE 100M
  MAXLOGFILES 5
  MAXLOGMEMBERS 5
  MAXLOGHISTORY 100
  MAXDATAFILES 100
  CHARACTER SET AL32UTF8
  NATIONAL CHARACTER SET AL16UTF16
  EXTENT MANAGEMENT LOCAL
  DATAFILE '/path/to/system01.dbf' SIZE 500M AUTOEXTEND ON
  SYSAUX DATAFILE '/path/to/sysaux01.dbf' SIZE 500M AUTOEXTEND ON
  DEFAULT TABLESPACE users
    DATAFILE '/path/to/users01.dbf' SIZE 100M AUTOEXTEND ON
  DEFAULT TEMPORARY TABLESPACE temp
    TEMPFILE '/path/to/temp01.dbf' SIZE 100M AUTOEXTEND ON
  UNDO TABLESPACE undotbs
    DATAFILE '/path/to/undotbs01.dbf' SIZE 200M AUTOEXTEND ON;
  3. Key Clauses Explained
    • USER SYS/SYSTEM: Sets passwords for admin accounts.
    • LOGFILE: Defines redo log groups for transaction recovery.
    • CHARACTER SET: Specifies database encoding (e.g., AL32UTF8 for Unicode).
    • DATAFILE: Creates the system tablespace (required) and optional tablespaces (e.g., SYSAUX, UNDO).
    • DEFAULT TABLESPACE: Assigns a default storage location for user data.
  4. Execute the Script
    • Run the script from SQL*Plus or Oracle SQL Developer: 
      @/path/to/create_db.sql
  • Post-Creation Steps
    • Run Oracle’s data dictionary scripts (catalog.sql, catproc.sql) to build views and PL/SQL support: 
      @?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
    • Create additional tablespaces/users as needed.

    • Example Minimal Command
    For a test database named `ORCL`: 
    CREATE DATABASE ORCL
  USER SYS IDENTIFIED BY sys_password
  USER SYSTEM IDENTIFIED BY system_password
  LOGFILE GROUP 1 ('/u01/oradata/ORCL/redo01.log') SIZE 50M
  CHARACTER SET AL32UTF8
  DATAFILE '/u01/oradata/ORCL/system01.dbf' SIZE 300M
  EXTENT MANAGEMENT LOCAL;

    Important Notes
    • Permissions: Requires SYSDBA privileges.
    • File Paths: Replace /path/to/ with actual directories (Oracle must have write access).
    • Backup: Always back up existing databases before creating new ones.

    Oracle 23c Multitenant CREATE DATABASE Script

    Here is a complete `CREATE DATABASE` script for setting up an Oracle 23c multitenant architecture (CDB). This example assumes you're creating a Container Database (CDB) with Oracle Managed Files (OMF) and enabling the use of Pluggable Databases (PDBs): 
    -- Step 1: Start the instance in NOMOUNT state
STARTUP NOMOUNT;

-- Step 2: Create the Container Database (CDB)
CREATE DATABASE cdb23c
   USER SYS IDENTIFIED BY StrongSysPassword1
   USER SYSTEM IDENTIFIED BY StrongSysPassword2
   ENABLE PLUGGABLE DATABASE
   SEED FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/cdb23c', 
                             '/u01/app/oracle/oradata/cdb23c/pdbseed')
   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;

    🔧 Post-Creation Steps (after CREATE DATABASE) 
    -- Step 3: Run necessary scripts as SYSDBA
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql

-- Step 4: Connect as SYSTEM to run data dictionary views
CONNECT SYSTEM/StrongSysPassword2
@?/sqlplus/admin/pupbld.sql

    🧾 Notes
    • Replace /u01/app/oracle/oradata/cdb23c/ with your actual DB_CREATE_FILE_DEST if using Oracle Managed Files.
    • SEED FILE_NAME_CONVERT is required to correctly create the PDB seed in a separate directory.
    • Make sure Oracle environment variables are properly configured (ORACLE_HOME, ORACLE_SID, etc.).
    • You can create additional PDBs using CREATE PLUGGABLE DATABASE once the CDB is operational.

    Follow-up script to create a custom Pluggable Database (PDB)

    Here is a follow-up script to create a custom Pluggable Database (PDB)[1] in an existing Container Database (CDB) in Oracle 23c:
    ✅ CREATE PLUGGABLE DATABASE Script 
    -- Connect as SYSDBA to the root container
CONNECT SYS/StrongSysPassword1@CDB23C AS SYSDBA;

-- Step 1: Create a custom pluggable database named `pdb_custom`
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/');

-- Step 2: Open the newly created pluggable database
ALTER PLUGGABLE DATABASE pdb_custom OPEN;

-- Step 3: Save the state of the PDB so it opens automatically on CDB startup
ALTER PLUGGABLE DATABASE pdb_custom SAVE STATE;

    📝 Explanation of Key Parameters
    • ADMIN USER pdbadmin: Creates the local admin user for the PDB.
    • FILE_NAME_CONVERT: Maps source file locations (from the seed) to the destination directory for the new PDB.
    • SAVE STATE: Ensures the PDB opens automatically when the CDB starts.

    🔒 Optional: Grant Connection Privileges
    To allow the `pdbadmin` user to connect remotely: 
    -- Switch to the PDB
ALTER SESSION SET CONTAINER = pdb_custom;

-- Grant CREATE SESSION
GRANT CREATE SESSION TO pdbadmin;

    🔧 If Using Oracle Managed Files (OMF)
    If `DB_CREATE_FILE_DEST` is set, you can omit `FILE_NAME_CONVERT`: 
    CREATE PLUGGABLE DATABASE pdb_custom
   ADMIN USER pdbadmin IDENTIFIED BY StrongPDBAdminPwd1;

    Parameters for use with the `CREATE DATABASE` statement.

    Below is a listing of the parameters which are for use with the `CREATE DATABASE` statement in Oracle 23c.
    However, several are legacy constructs that are less commonly used today, especially in environments using Oracle Managed Files (OMF)and multitenant architecture (CDB/PDBs). Below is a breakdown of the current status and recommendations for each parameter:
    ✅ Still Supported and Commonly Used
    Parameter Status Notes
    LOGFILE ✅ Active Still used when explicitly managing redo logs. Required if OMF is not used.
    GROUP group_no ✅ Active Required within LOGFILE for defining groups.
    log_file_name ✅ Active Required unless OMF is configured.
    SIZE integer[K M] ✅ Active Recommended to explicitly specify sizes for control, redo, and datafiles.
    MAXDATAFILES ✅ Active Still supported, but can be dynamically extended via DB_FILES parameter.
    ARCHIVELOG ✅ Active Still fully valid.
    NOARCHIVELOG ✅ Active Default, unless overridden.
    CHARACTER SET ✅ Active Use AL32UTF8 for Unicode support.
    NATIONAL CHARACTER SET ✅ Active Use AL16UTF16 typically.
    DATAFILE ✅ Active Needed unless using OMF.
    AUTOEXTEND ✅ Active Still supported and widely used.
    OFF, ON (for AUTOEXTEND) ✅ Active Still required to control behavior.
    NEXT, MAXSIZE ✅ Active Used with AUTOEXTEND.

    Supported but Legacy or Use is Discouraged in Modern Configurations
    Parameter Status Notes
    CONTROLFILE_REUSE ✅ Active, but legacy Supported, but rarely used in practice today. Instead, recreate control files or let Oracle manage them.
    MAXLOGFILES ✅ Active, but less relevant Oracle can handle more redo log groups dynamically. Still valid but typically unnecessary to specify.
    MAXLOGMEMBERS ✅ Active, but rarely changed Default is usually sufficient. Multiplexing is managed separately.
    MAXLOGHISTORY ✅ Active, but specific use case Only relevant for RAC/Data Guard with archivelog mode. Rarely adjusted in modern setups.
    MAXINSTANCES ✅ Active, RAC-specific Only applicable to Real Application Clusters (RAC). Not used in single-instance deployments.

    ✅ Summary
    • None of the listed parameters are deprecated in Oracle 23c.
    • Several parameters (CONTROLFILE_REUSE, MAXLOGHISTORY, MAXINSTANCES) are legacy or niche-use in RAC or manual control file configurations.
    • In modern Oracle 23c deployments, especially those using CDB/PDB with Oracle Managed Files, many of these parameters become optional or are better managed dynamically.

    Specific clauses in command: Best practices in real-world Oracle DBA work

    Here’s a concise breakdown that reinforces your observations for learners in a course setting: ✅ Clause Ordering and Optionality
    • The clauses such as MAXLOGFILES, MAXDATAFILES, MAXLOGMEMBERS, etc., do not have to be written in a specific order in the CREATE DATABASE command.
    • Most are optional because Oracle provides sensible defaults based on the initialization parameters and system configuration.
    🔧 Why Explicitly Include LOGFILE and DATAFILE
    1. LOGFILE Clause:
      • Lets you name redo log groups and their members.
      • Allows you to control file placement and and size, which is crucial for performance and diagnostics.
      • Useful when not using OMF or when deploying databases on specific I/O subsystems.
    2. DATAFILE Clause:
      • Required to define the SYSTEM tablespace location.
      • You should always control the initial size of the SYSTEM tablespace and related datafiles like SYSAUX and USERS.
      • When omitted, Oracle may place the files in a default location, which can lead to unpredictable file organization.
    📌Best Practice for Students in a Course Setting

    > “In this course, unless there's a specific reason to override a default, you can omit most optional clauses. However, you should always include the `LOGFILE` and `DATAFILE` clauses to maintain control over file placement and sizing.” This guidance helps students avoid future headaches with storage management and fosters the habit of thinking like a production DBA.


    • Using the MAX clauses:
      This kind of insight is critical for anyone learning Oracle at a deeper level, especially when dealing with low-level initialization parameters and physical structure management.
      Here’s a clear and structured elaboration on what you're teaching:
      📘 Understanding the `MAX` Clauses in `CREATE DATABASE`
      Oracle provides several `MAXxxx` clauses to preallocate control file space for various structural components. These clauses do not affect the actual creation of datafiles or log files but define the capacity limits recorded in the control file.
      🔍 Key Concepts with Examples

      ✅ `MAXLOGFILES`
      • Specifies the maximum number of redo log *groups* (not individual files).
      • A group may contain 1 or more members (files).
      • You might write:

        LOGFILE GROUP 1 (...), GROUP 2 (...)

      but set: 
        MAXLOGFILES 10

      to allow for future expansion.
      ⚠️ Dependency on `LOG_FILES` Parameter
      • The initialization parameter LOG_FILES sets a lower bound for MAXLOGFILES.
      • For example, in older Oracle versions like 8.1.5 on Windows NT, LOG_FILES = 255 ⇒ MAXLOGFILES must be ≥ 255.
      • If MAXLOGFILES < LOG_FILES, Oracle will raise an error during database creation.
      • To reduce MAXLOGFILES, you'd have to reduce LOG_FILES in your init.ora or SPFILE and restart the instance.
      🧠 Best Practice Don't assume that `MAXLOGFILES` should match the number of groups you're creating now. Plan for future growth.
      🧾 Other MAX Parameters – Summary
      Parameter Applies To Planning Note
      MAXLOGMEMBERS Max members per redo log group Default is often sufficient. Rarely more than 2–3 members needed unless mirroring.
      MAXLOGHISTORY Archived redo logs tracked Relevant for RAC or Data Guard with high archive frequency.
      MAXDATAFILES Preallocates slots for datafiles If you exceed this, you'll need to recreate control file or use OMF.
      MAXINSTANCES RAC: concurrent DB instance limit Set appropriately in RAC clusters. Irrelevant for single-instance DBs.

      💡 Teaching Insight
      You must understand their "upstream dependencies" (i.e., `LOG_FILES`) and "downstream impact" (e.g., limitations on adding more datafiles or redo logs later). It’s often a case of *more going on than meets the eye*.”

    Write Create Database - Exercise

    Now, click the Exercise button to write the CREATE DATABASE statement for your COIN database.
    Write Create Database - Exercise

    [1]pluggable database (PDB) : In Oracle Database, a pluggable database (PDB) is a self-contained unit of data that can be plugged into a container database (CDB). It's like having multiple, independent databases within a single Oracle instance. PDBs are portable, meaning they can be moved between different CDBs, even with different Oracle Database versions. From an application's perspective, a PDB appears as a regular, non-CDB database.
    [2]Oracle Managed Files (OMF): Oracle Managed Files (OMF) in Oracle 23c simplifies database administration by automating the creation, naming, and deletion of various database files (like data files, redo logs, and control files). By setting specific initialization parameters, DBAs can let Oracle handle the physical file management, reducing the risk of errors and streamlining operations, especially in environments utilizing Oracle's multitenant architecture and Automatic Storage Management (ASM).
    [3] multitenant architecture: In Oracle 23c, the multitenant architecture is a fundamental design where a single Oracle database instance, called a Container Database (CDB), can host multiple isolated and portable databases known as Pluggable Databases (PDBs). This allows for efficient consolidation of many databases, sharing a common set of background processes and memory structures while maintaining the appearance of separate databases for applications.

    SEMrush Software 2 SEMrush Banner 2