7 steps to create a new Oracle database from the command line

Preamble

​​

If you are a Linux sysadmin or developer, there comes a time when you need to manage an Oracle database that can work in your environment.

In this case, it is important to understand some basic Oracle DBA activities. In this tutorial, we will show you how to create an Oracle database from the command line.

If you have decided not to create a new database, but to install only Oracle software, you can then create the database separately.

In order to create an Oracle database, you have two options:

1. Use the Configuration Assistant Database (DBCA) and create a new database using the graphical interface. This is quite straightforward.
2. Use the “Create Database” command to create a new Oracle database from the command line. This method is useful when you do not have access to the server console to run DBCA. Or, if your server does not have the correct Xterm settings, use this method.

1. Configure the corresponding Oracle environment variables

First, you must set the corresponding environment variable on the server correctly.

In this example, it is assumed that the oracle is set in /u01/app/oracle/product. 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 parameter is ORACLE_SID, which will have the name of the new Oracle database you want to create. In this example, the new database name is set to “dev” as shown below.

export ORACLE_SID=dev

2. Creation of ini-initdev.ora file

Then create an ora.ini file for the new database. This is the initialization file for the new database.

Depending on your Oracle version, you can see a sample file init.ora in $ ORACLE_HOME. If you have one, use it as a base one and edit the corresponding values.

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

As shown above, the initialization file for the new database should be in the following format: INIT {ORACLE_SID} .ora – So, in this case the file name will be: initdev.ora.

If you don’t see the default template init.ora 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'.

Several items should be considered in the specified file:

• In the specified file, make sure that you set db_name to the name ORACLE_SID set in the previous step.
• Whatever the name undo_tablespace is, we point out that we must use the exact name in the CREATE DATABASE command.
• Change the location of the directories properly, based on your system. Don’t forget to change the “dev” in the above directory to your name ORACLE_SID.

3. Create a Serve Parameter file (spfile)

SP file means a server parameter file. Unlike the sp file, the file is initialized in a binary file and you cannot edit SPFILE manually.

The sp file is created from an ini file. The advantage of the sp file is that you can change the value of the initialization parameters after the database starts using the ALTER SYSTEM command.

In other words, when you use the ALTER SYSTEM command to change any parameter value, it saves it to the sp file.

Then, when starting an Oracle database, it shall first look for the sp file for the parameter value. If he cannot find the sp file, he shall use the text based on the ini file.

To create an sp file for our new database, use the following command.

First, use the command sqlplus and get the SYSDBA Oracle prompt from where we will create the new database.

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

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

Thus, the first step is to create a new sp file based on an ini file. spfile means an ini file. The next command will create a new spfile.

SQL> CREATE SPFILE FROM PFILE;
File created.

As you can see below, the command automatically creates spfiledev.ora.

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

4. Run Idle Instance

Before creating the database, we must run the instance for the “dev” database using the STARTUP NOMOUNT command. As you may have already guessed, this command will not connect to the database. This is just to start a new empty simple instance of ORACLE_SID named “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

In the above command, it will read the default spfile with the name spfile{ORACLE_SID}.ora from the default spfile location of $ORACLE_HOME/dbs. If spfile does not exist, it will use the default initialization of the init{ORACLE_SID}.ora file.

For some reason, if you want to specify your spfile location, you can do so bypassing the spfile parameter as shown below.

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

In addition, you can get the following ORA-01078 and LRM-00109 if the spfile, or the initialization file is not in the default location.

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

5. Creating a 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:

• The Create database command will create an Oracle database named “dev”.
• The password specified on the 2nd line will be assigned to the SYS user.
• The password specified on the 3rd line will be assigned to the SYSTEM user.
• We create two duplicate logs with 100MB each.
• MAXLOGFILES – Maximum number of log files is set to 5.
• MAXDATAFILES – This indicates that the maximum number of Oracle data files that can be created for this database.
• DATAFILE – Indicates the data file to be used in the tabular system.
• SYSAUX DATAFILE – This indicates that the data file will be used in SYSAUX tablespace.
• The default tablespace for this database is set to USERS.
• The default time tablespace is set in TEMPTS.
• Cancel tablespace is set in UNDOTS.

If you specify DB_CREATE_FILE_DEST in the initialization file for the directory location, you do not need to specify the exact location and file names for all data files, Oracle will take care of it for you.

For example, if you specified this in the file initdev.ora.

# 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 commands above will create the corresponding data files required for all tablespaces (undo, temporary, etc.) according to the location specified in the DB_CREATE_FILE_DEST directory.

6. Build a dictionary of Views data

As a final step, run catalog.sql and catproc.sql. The script directory will create all dictionary tables, performance-related views, and necessary public synonyms. It will also provide appropriate access to all synonyms that have been created. The catproc script runs all the scripts that are necessary for the PL/SQL functionality.

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

Partial output from 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’re interested, you can take a look at the directory and the catproc script to see exactly what it does.

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

7. Verification – Completion and Commissioning

Finally, perform regular shutdowns and launches to ensure that everything works as expected on this new database.

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

SQL> STARTUP;
ORACLE instance started.
Total System Global Area 1234563200 bytes.
Fixed Size 1262454 bytes
Variable Size 522935700 bytes
Database Buffers 720583588 bytes
Redo Buffers 12946358 bytes
Database mounted.

 Oracle : Silent Installation and Database Creation

Enteros

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of RDBMS, NoSQL, and machine learning database platforms.