This website use cookies to help you have a superior and more admissible browsing experience on the website.
Loading...
By:
Creating a new Oracle database is very different from creating a database in MySQL or PostgreSQL. In Oracle, the database includes the physical storage structure, background processes, control files, redo logs, system tablespaces, and internal metadata that power the entire instance.
That is why Oracle database creation involves more planning and configuration than a simple CREATE DATABASE command in other platforms.
Before diving in: if you only need to create tables in an existing Oracle instance, you don’t need this command. In Oracle, a schema is tied to a user. Running CREATE USER gives you a named workspace equivalent to what MySQL calls a database. The steps below are for DBAs provisioning a brand-new Oracle instance from scratch.
The CREATE DATABASE statement initializes the physical file structure on disk: datafiles, control files, and redo log files. It also builds the data dictionary, the internal catalog Oracle uses to track every user, object, and piece of metadata in the system.
CREATE DATABASE is a one-time operation per instance. Once executed, the database exists permanently on disk. Re-running it on the same instance requires deleting all existing files and rebuilding from scratch.
To create an Oracle database, you need to plan the storage layout, instance parameters, and architecture before anything runs, and include additional configuration steps after creation is complete. The following are four ways to approach it, from manual scripts to fully managed cloud services.
The method you choose depends on your environment and how much control you need. Before running any of them, make sure you have completed the following planning steps.
Some of these decisions cannot be changed after creation, so plan before you run any commands.
The manual method gives you full control over every aspect of the database structure. It is rarely used for standard production setups today, but understanding it helps you know exactly what tools like DBCA are doing under the hood.
Step 1: Set ORACLE_SID and prepare directories
Set the ORACLE_SID environment variable to identify your instance, then create the necessary directories on your server.
export ORACLE_SID=testdb
mkdir -p $ORACLE_BASE/admin/testdb/adump
mkdir -p $ORACLE_BASE/oradata/testdb
Step 2: Create and edit the parameter file (init<SID>.ora)
The initialization file defines how the instance behaves. Configure these key parameters before starting:
CREATE DATABASE statement exactly.Step 3: Start the instance in NOMOUNT mode
In NOMOUNT mode, Oracle reads the parameter file and initializes memory structures but does not look for an existing database on disk. This is the required state before running CREATE DATABASE.
sqlplus / as sysdba
STARTUP NOMOUNT;
Step 4: Execute the CREATE DATABASE statement
Before running the statement, check your Oracle version. Starting with Oracle 21c, the parameter ENABLE_PLUGGABLE_DATABASE defaults to TRUE, and setting it to FALSE will cause CREATE DATABASE to fail.
If you are on 19c or later, Oracle recommends creating a CDB by including the ENABLE PLUGGABLE DATABASE clause in your statement. Once the CDB exists, you can add pluggable databases to it using CREATE PLUGGABLE DATABASE.
The following statement builds the physical file structure for an Oracle 19c database.
CREATE DATABASE testdb
USER SYS IDENTIFIED BY sys_password
USER SYSTEM IDENTIFIED BY system_password
-- Define the redo log groups for transaction history
LOGFILE GROUP 1 ('/u01/oradata/testdb/redo01.log') SIZE 50M BLOCKSIZE 512,
GROUP 2 ('/u01/oradata/testdb/redo02.log') SIZE 50M BLOCKSIZE 512,
GROUP 3 ('/u01/oradata/testdb/redo03.log') SIZE 50M BLOCKSIZE 512
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
-- Set the permanent character sets
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
-- Define mandatory system tablespaces
DATAFILE '/u01/oradata/testdb/system01.dbf' SIZE 325M REUSE
SYSAUX DATAFILE '/u01/oradata/testdb/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE users
DATAFILE '/u01/oradata/testdb/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/u01/oradata/testdb/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs1
DATAFILE '/u01/oradata/testdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Step 5: Run post-creation scripts
After the statement completes, the database files exist on disk but the data dictionary views and built-in packages are not yet available. Run these two scripts as SYSDBA:
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
Step 6: Back up the database immediately
Take a full backup using Oracle Recovery Manager (RMAN) before loading any application data. This establishes a clean baseline recovery point.
The Oracle Database Configuration Assistant (DBCA) is Oracle’s official recommended tool for creating databases. It automates parameter file creation, directory setup, and post-creation scripts, producing a fully configured database when it completes. DBCA can be launched as a graphical wizard or run in silent mode from the command line.
dbca from the command line with your Oracle environment variables set. On systems with a display, this opens the graphical wizard.For automated or scripted environments, use silent mode:
dbca -silent -createDatabase \
-templateName General_Purpose.dbc \
-gdbName testdb \
-sid testdb \
-characterSet AL32UTF8 \
-sysPassword sys_password \
-systemPassword system_password \
-datafileDestination /u01/oradata \
-storageType FS
This method is useful when you need a staging or development environment that matches your production database’s structure and configuration.
In Oracle Cloud Infrastructure (OCI), you do not need to run creation scripts manually. Both options below provision the database through the OCI console.
DB Systems give you a virtual machine or bare metal server with Oracle software pre-installed. Navigate to Oracle Database → DB Systems → Create DB System, select your shape, storage, and Oracle Database version, and configure your network settings. You retain full DBA access to both the operating system and the database.
Autonomous Database is fully managed. Oracle handles patching, tuning, and backups automatically. Navigate to Oracle Database → Autonomous Database → Create Autonomous Database, choose a workload type such as Transaction Processing, and set your admin credentials. The database is ready within minutes.
With Autonomous Database, you do not have access to the OS layer or DBCA. You connect directly as the ADMIN user and begin creating your application objects immediately.
Creating an Oracle database has many steps, and a single typo in a parameter file or a missing directory can stop the process entirely. Here are the most frequent errors and how to resolve them.
This is a generic error that is always accompanied by a second, more specific error code. Check the alert log to find the root cause.
$ORACLE_BASE/diag/rdbms///trace/alert_.log The name in your CREATE DATABASE statement does not match the DB_NAME value in your init.ora file. Make both names identical, or omit the name from the SQL statement to inherit the value from the parameter file.
No database name was provided in the SQL statement, and DB_NAME is not set in the initialization file. Add DB_NAME=yourname to your init.ora file.
A command was issued before the instance reached the required state. Use NOMOUNT for a fresh creation or MOUNT for maintenance tasks.
Choosing a restricted character set such as WE8ISO8859P1 means your database may be unable to store multi-language data or special characters later. This cannot be changed without rebuilding the entire database. Always use AL32UTF8.
If CREATE DATABASE completes but queries against USER_TABLES or DBA_OBJECTS return errors, you have not yet run the post-creation scripts. Run catalog.sql and catproc.sql as SYSDBA to create the data dictionary views and built-in PL/SQL packages.
If you see a “Protocol Adapter Error” or “ORA-12560” when connecting, the ORACLE_SID environment variable is not set correctly. Set it before launching SQL*Plus:
# Linux
export ORACLE_SID=
# Windows
set ORACLE_SID=
Complete these steps before the database goes into production.
The first thing to do after creating a database is to establish a backup strategy. Oracle’s native Recovery Manager (RMAN) is a solid tool, but it requires scripting knowledge, manual scheduling, and offers no centralized management interface across multiple databases or servers. For teams managing several Oracle instances, this operational overhead adds up quickly.
i2Backup is an enterprise backup solution that supports Oracle database protection with a centralized, web-based console. It reduces the operational complexity of database backup without replacing the protection RMAN provides.
For teams looking beyond backup, Info2Soft also offers i2Availability, a solution for high availability and disaster recovery, and i2Stream, a database replication tool that supports Oracle and over 40 other database environments. Together, these tools cover the full spectrum of data protection from backup to continuous availability.
Creating an Oracle database is not a single command. It requires planning your storage layout, character set, and instance parameters before anything runs, and post-creation steps like running catalog.sql and catproc.sql before the database is fully usable.
The right method depends on your situation. Manual CREATE DATABASE gives you full control and is useful for understanding how Oracle initializes an instance. DBCA is Oracle’s recommended approach for most production deployments and handles the heavy lifting automatically. Template-based cloning works well when you need to replicate a known configuration across environments. Oracle Cloud removes infrastructure management entirely for teams that do not need OS-level access.
Whichever method you use, take a backup with solutions like Info2soft’s i2Backup before loading any application data. A clean baseline recovery point is the simplest safeguard against configuration mistakes and early data loss.
