7 Steps to Create New Oracle Database from Command Line

Even if you are a Linux sysadmin or developer, sometimes you might end-up managing Oracle database that is running in your environment.

In that case, it is essential to understand certain basic Oracle DBA activities. In this tutorial, we’ll explain how to create an Oracle database from command line.

When you install Oracle software, it will give you an option to create a new database from the UI.

At that time, if you decide not to create a new database, and install only the Oracle software, later you can create the database separately.

To create an Oracle database, you have two options:

1. Use Database Configuration Assistant (DBCA) and create new database by using the GUI. This is fairly straight forward.
2. Use the “Create Database” command to create a brand new oracle database from the command line. This method is helpful when you don’t have console access to the server to launch the DBCA. Or, when your server doesn’t have Xterm setup properly, use this method.

1. Setup Appropriate Oracle Environment Variables

First, you should setup appropriate oracle environment variable on your server.

In this example, it assumes that oracle is installed under /u01/app/oracle/product directory. Change this value according to your environment.

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0

The most important variable is ORACLE_SID, which will have the name of the new oracle database that you like to create. In this example, the name of the new database is set to “dev” as shown below.

export ORACLE_SID=dev

2. Create an Ini File – initdev.ora

Next, create a ora.ini file for your new database. This is the initialization file for the new database.

Depending on the version of your Oracle, you might see a sample init.ora file under $ORACLE_HOME. If you have one, use that as a baseline and edit the values accordingly.

cd $ORACLE_HOME/dbs
cp init.ora initdev.ora

Note: As shown above, the init file for this new database should be of this format: init{ORACLE_SID}.ora — So, in this case, the filename will be: initdev.ora

If you don’t see a default init.ora template in your $ORACLE_HOME/dbs, use the following sample.

*.db_name='dev'
*.db_domain=''
*.audit_file_dest='/u01/app/oracle/admin/dev/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.memory_target=1G
*.control_files='/u01/app/oracle/oradata/dev/control01.ctl','/home/oracle/u02/oradata/dev/control02.ctl'
*.db_block_size=8192
*.diagnostic_dest='/u01/app/oracle/admin/dev'
*.open_cursors=250
*.processes=100
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTS'

Few points to consider in the above file:

• In the above file, make sure you set the db_name to the ORACLE_SID name that you set in the previous step
• Whatever the name of the undo_tablespace that we specify here, we have to use the exact name during the CREATE DATABASE command.
• Change the directory location appropriately based on your system. Don’t forget to change “dev” in the above directory location to your ORACLE_SID name.

3. Create Serve Parameter file (spfile)

SP file stands for Server Parameter file. Unlike the init file sp file is a binary file, and you can’t edit the spfile manually.

SP file is created from the text based ini file. The advantage of SP file is that, you can change the initialization parameter values after you start the database using the ALTER SYSTEM command.

In other words, when you use “ALTER SYSTEM” command to change any of the parameter’s value, it stores them in the SP file.

Then, when the oracle database is started, it first looks for the SP file for the parameter value. If it can’t find any SP file, then it will use the text based Ini file.

To create a SP file for our new database, use the following command.

First, use sqlplus command and get the oracle sysdba prompt, from where we’ll create a new database.

$ sqlplus / as sysdba
Connected to an idle instance.
SQL>

If you notice in the above output, it says “Connected to an idle instance.”. This is because our current ORACLE_SID is set to dev, which is a new database, which we have not yet created.

So, the first step is to create a new SP file based on our Ini file. PFILE stands for Ini file. The following command will create a new SPFILE.

SQL> CREATE SPFILE FROM PFILE;
File created.

As you see below, the above command has created the spfiledev.ora automatically.

$ ls -1 $ORACLE_HOME/dbs/
initdev.ora
spfiledev.ora

4. Start the Idle Instance

Before we create the database, we should start the instance for “dev” database using STARTUP NOMOUNT command. As you might’ve guessed, this command will not MOUNT any database. This is simply starting the empty new idle instance with the ORACLE_SID name “dev”.

SQL> STARTUP NOMOUNT;
ORACLE instance started.

Total System Global Area 1258291200 bytes
Fixed Size                  1261564 bytes
Variable Size             520093700 bytes
Database Buffers          721420288 bytes
Redo Buffers               15515648 bytes

During the above command, it will read the default spfile with the name spfile{ORACLE_SID}.ora from the default spfile location $ORACLE_HOME/dbs. If the spfile is not there, it will use the default init file init{ORACLE_SID}.ora

For some reason, if you want to specify the location of the pfile yourself, you can do that by passing PFILE parameter as shown below.

SQL> STARTUP NOMOUNT PFILE=/tmp/initdev.ora

Also, you might get the following ORA-01078 and LRM-00109, if the spfile, or the init file is missing in the default location.

SQL> STARTUP NOMOUNT    
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbs/initdev.ora'

5. Create New Oracle Database

Use the following CREATE DATABASE command to create an empty database.

SQL> CREATE DATABASE dev
     USER SYS IDENTIFIED BY DevSysPass
     USER SYSTEM IDENTIFIED BY DevSystemPass
     LOGFILE GROUP 1 ('/home/oracle/u02/oradata/dev/redomed_01.log') SIZE 50M,
             GROUP 2 ('/home/oracle/u02/oradata/dev/redomed_02.log') SIZE 50M,
     MAXLOGFILES 5
     MAXLOGHISTORY 10
     MAXDATAFILES 50
     CHARACTER SET US7ASCII
     NATIONAL CHARACTER SET AL16UTF16
     DATAFILE '/home/oracle/u02/oradata/dev/system01.dbf' SIZE 100M REUSE
     SYSAUX DATAFILE '/home/oracle/u02/oradata/dev/sysaux01.dbf' SIZE 100M REUSE
     DEFAULT TABLESPACE USERS
       DATAFILE '/home/oracle/u02/oradata/dev/users01.dbf'
       SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
     DEFAULT TEMPORARY TABLESPACE TEMPTS
       TEMPFILE '/home/oracle/u02/oradata/dev/tempts01.dbf'
       SIZE 30M REUSE
     UNDO TABLESPACE UNDOTS
       DATAFILE '/home/oracle/u02/oradata/dev/undots01.dbf'
       SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

In the above command:

• Create database command will create an oracle database with name “dev”
• The password specified in the 2nd line will be assigned to SYS user
• The password specified in the 3rd line will be assigned to SYSTEM user
• We are creating two redo logfiles with size 100MB each.
• MAXLOGFILES – The maximum number of redo log files is set to 5
• MAXDATAFILES – This indicates that maximum number of oracle data files that can be created for this database.
• DATAFILE – This specify the datafile that will be used by the SYSTEM tablespace
• SYSAUX DATAFILE – This indicates the datafile that will be used by the SYSAUX tablespace
• The default tablespace for this database is set to USERS
• The default temporary tablespace is set to TEMPTS
• The undo tablespace is set to UNDOTS

If you want to create additional tablespace after the DB is created, use this: 15 Oracle Tablespace and Datafile Command Examples

Note: If you specify DB_CREATE_FILE_DEST in your init file to a directory location, then you don’t need to specify the exact location and filenames for all the datafiles, oracle will take care of those for you.

For example, if you’ve specified this in your initdev.ora file.

# vi initdev.ora
DB_CREATE_FILE_DEST='/home/oracle/u02/oradata/dev'

In this case, you can simplify your CREATE DATBASE command as shown below.

SQL> CREATE DATABASE dev
     USER SYS IDENTIFIED BY DevSysPass
     USER SYSTEM IDENTIFIED BY DevSystemPass
     MAXLOGFILES 5
     MAXLOGHISTORY 10
     MAXDATAFILES 50
     CHARACTER SET US7ASCII
     NATIONAL CHARACTER SET AL16UTF16
     DEFAULT TABLESPACE USERS
     DEFAULT TEMPORARY TABLESPACE TEMPTS
     UNDO TABLESPACE UNDOTS

The above will create the appropriate datafiles required for all the tablespaces (undo, temporary, etc) under the location specified in the DB_CREATE_FILE_DEST directory.

To understand more about UNDO tablespace, this should help: 10 Oracle UNDO Tablespace Management Examples

6. Build Data Dictionary Views

As a last step, execute the catalog.sql and catproc.sql. catalog script will create all the dictionary tables, performance related views, required public synonyms. This will also grant appropriate access to all the synonyms that was created. catproc script executes all the scripts that are required for PL/SQL functionalities.

SQL> @?/rdbms/admin/catalog.sql;
SQL> @?/rdbms/admin/catproc.sql

Partial output of the above commands.

SQL> @?/rdbms/admin/catalog.sql;
DOC>######################################################################
DOC>######################################################################
DOC>    The following statement will cause an "ORA-01722: invalid number"
DOC>    error and terminate the SQLPLUS session if the user is not SYS.
DOC>    Disconnect and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#
no rows selected
Package created.
Package body created.
..
..
Synonym created.
Grant succeeded.
PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/catproc.sql
..
..
Comment created.
Synonym created.
Grant succeeded.
PL/SQL procedure successfully completed.

If you are curious, you can look at the catalog and catproc script to understand what exactly it does.

vi $ORACLE_HOME/rdbms/admin/catalog.sql;
vi $ORACLE_HOME/rdbms/admin/catproc.sql

7. Verify – Shutdown and Startup

Finally, perform a regular shutdown and startup to make sure everything works as expected on this new database.

$ sqlplus / as sysdba
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP;
ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size                  1261564 bytes
Variable Size             520093700 bytes
Database Buffers          721420288 bytes
Redo Buffers               15515648 bytes
Database mounted.