Create Oracle database from command line

Day 2 activity of the DBA’s, after setting up the database server and configured all the required privileges and applying the patch, we will create a new database

Developers and SysAdmins also should know to create database.

Creating database from DBCA by using the GUI is very straight forward and simple. But if you don’t have console access to the server to launch DBCA or the Xterm setup is not in your OS, you will relay on command like tool to create database

Setup the appropirate envionment variables

Mainly you need to setup the ORACLE_BASE and ORACLE_HOME, refer my recent blog post to set the environment variable in your operating system

In addition to that, one main variable is ORACLE_SID, which will have the name of the oracle database that you like to create

In this post, i use vignesh as database name

export ORACLE_SID=vignesh

Create the init file ( INI file)

Next, create an ora file for your new database, this is the initialisation file for your new database

Depending on the version of the installed oracle, you might see a sample init.ora file in your $ORACLE_HOME/dbs location, you can use that as a baseline for your ora file

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

Note: Make sure the file name must be init<ORACLESID>.ora, so in my case initvignesh.ora

Copy and edit the file

• Make sure you set the db_name same as ORACLE_SID, in my case it is vignesh
• The name you provide for the undo_tablespace should be used exactly same in create command
• Change the directory location according to your server

Here is my sample ora file

db_name='vignesh'
memory_target=1G
processes = 150
audit_file_dest='/u01/app/oracle/db/admin/vignesh/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/app/oracle/db/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle/db'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='vigneshundo'
You may want to ensure that control files are created on separate physical
devices
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'

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. You can change the initialization parameter values after you start the database using the ALTER SYSTEM command.

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

[oracle@nimesavvol dbs]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Mon Feb 3 12:55:37 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL>

SQL> CREATE SPFILE FROM PFILE;
File created.

Start the Idle Instance using the initvignesh.ora file

SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 738198840 bytes
Database Buffers 318767104 bytes
Redo Buffers 8146944 bytes
SQL>

At this point, there is no database. Only an SGA and background processes are started in preparation for the creation of a new database.

During the above command, it will read the default spfile with the name spfile<ORACLE_SID>.ora ( in my case initvignesh.ora ) from the default spfile location $ORACLE_HOME/dbs. If the spfile is not there, it will use the default init file init.ora

Some common error and fix

If you are getting ORA-09925: Unable to create audit trail file

Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925

Make sure the folder audit_file_dest value exist and writable

If you are getting error ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated

Make sure the folder exist and writable, still facing the same error Comment the db_recovery_file_dest line or delete the line and try again

Create a new database

When you execute a CREATE DATABASE statement, Oracle performs the following operations:

• Creates the data files for the database
• Creates the control files for the database
• Creates the redo log files for the database
• Creates the SYSTEM tablespace and the SYSTEM rollback segment
• Creates the data dictionary
• Creates the users SYS and SYSTEM
• Specifies the character set that stores data in the database
• Mount and Open the database for use

CREATE DATABASE vignesh
UNDO TABLESPACE vigneshundo
DATAFILE '/u02/oradata/vignesh/vigneshundo01.dbf'
SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

You can specify the log file and data file location and size as well, make sure that the datafile and redo log file names that you specify do not conflict with files of another database. example

CREATE DATABASE vignesh
DATAFILE 'vignesh_system' SIZE 10M
LOGFILE GROUP 1 ('vignesh_log1a', 'vignesh_log1b') SIZE 500K,
GROUP 2 ('vignesh_log2a', 'vignesh_log2b') SIZE 500K;

Verification

Triggered the SHUTDOWN command to dismount and shutdown and startup

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

SQL> STARTUP
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 738198840 bytes
Database Buffers 318767104 bytes
Redo Buffers 8146944 bytes
Database mounted.
Database opened.

---

Also published on Medium.