Manually Creating an Oracle Database: Step-by-Step Guide

Setting up an Oracle database manually provides greater control over database structure, initialization parameters, and storage configuration. While DBCA (Database Configuration Assistant) automates database creation, manual setup helps Database Administrators (DBAs) gain a deeper understanding of Oracle database management.

This guide walks you through the step-by-step process of manually creating an Oracle database, ensuring full customization and hands-on experience.

Why Manually Create an Oracle Database?

Although DBCA simplifies database creation, manual configuration is useful for:

•  Full control over database structure, file locations, and initialization parameters.

• Custom database setups for specific environments.

• Learning and troubleshooting database configurations effectively.

Prerequisites

Before starting, ensure the following:

• Oracle Database software is installed (this guide uses Oracle 19c).

•  Sufficient disk space is available for database files.

•  Oracle environment variables are properly configured.

• You have Oracle user privileges to create and manage the database.

Step 1: Set Up the Oracle Environment

1.1 Define Environment Variables

Log in as the Oracle user and configure environment variables:

export ORACLE_SID=ORCL1

export ORACLE_HOME=/home/oracle/oracle/19.0.0

export PATH=$ORACLE_HOME/bin:$PATH

1.2 Verify Environment Configuration

Run the following commands to confirm the settings:

echo $ORACLE_SID

echo $ORACLE_HOME

Step 2: Create Required Directories

Before creating the database, set up directories for database files:

mkdir -p /home/oracle/oracle/oradata/ORCL1

mkdir -p /home/oracle/oracle/fast_recovery_area/ORCL1

mkdir -p /home/oracle/oracle/admin/ORCL1/adump

2.1 Set Ownership and Permissions

Ensure proper access permissions:

chown -R oracle:oinstall /home/oracle/oracle

chmod -R 775 /home/oracle/oracle

Step 3: Create the Initialization Parameter File (PFILE)

3.1 Navigate to the Database Configuration Directory

cd $ORACLE_HOME/dbs

vi initORCL1.ora

3.2 Add Initialization Parameters

Inside the initORCL1.ora file, define essential parameters:

db_name='ORCL1'

memory_target=1024M

processes=300

db_block_size=8192

audit_file_dest='/home/oracle/oracle/admin/ORCL1/adump'

control_files=('/home/oracle/oracle/oradata/ORCL1/control01.ctl', '/home/oracle/oracle/oradata/ORCL1/control02.ctl')

undo_tablespace='UNDOTBS1'

compatible='19.0.0'

diagnostic_dest='/home/oracle/oracle'

Save and exit the file.

Step 4: Start the Database Instance in NOMOUNT Mode

4.1 Launch SQL*Plus and Start the Database

sqlplus / as sysdba

4.2 Start the Instance in NOMOUNT Mode

STARTUP NOMOUNT PFILE='/home/oracle/oracle/19.0.0/dbs/initORCL1.ora';

If the instance starts successfully, you will see the message:✅ "ORACLE instance started."

Step 5: Create the Database

Run the following SQL command to initialize a new Oracle database:

CREATE DATABASE ORCL1

   USER SYS IDENTIFIED BY oracle123

   USER SYSTEM IDENTIFIED BY oracle123

   LOGFILE GROUP 1 ('/home/oracle/oracle/oradata/ORCL1/redo01.log') SIZE 50M,

           GROUP 2 ('/home/oracle/oracle/oradata/ORCL1/redo02.log') SIZE 50M,

           GROUP 3 ('/home/oracle/oracle/oradata/ORCL1/redo03.log') SIZE 50M

   MAXLOGFILES 5

   MAXLOGMEMBERS 3

   MAXLOGHISTORY 100

   MAXDATAFILES 100

   CHARACTER SET AL32UTF8

   NATIONAL CHARACTER SET AL16UTF16

  DATAFILE '/home/oracle/oracle/oradata/ORCL1/system01.dbf' SIZE 500M AUTOEXTEND ON

   SYSAUX DATAFILE '/home/oracle/oracle/oradata/ORCL1/sysaux01.dbf' SIZE 500M AUTOEXTEND ON

 UNDO TABLESPACE undotbs1 DATAFILE '/home/oracle/oracle/oradata/ORCL1/undotbs01.dbf' SIZE 500M AUTOEXTEND ON

  DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/home/oracle/oracle/oradata/ORCL1/temp01.dbf' SIZE 100M AUTOEXTEND ON;

✅ If successful, you will see: "Database created."

Step 6: Run Post-Creation Scripts

To set up dictionary tables and system views, execute:

@$ORACLE_HOME/rdbms/admin/catalog.sql;

@$ORACLE_HOME/rdbms/admin/catproc.sql;

Step 7: Create and Configure an SPFILE

7.1 Generate an SPFILE from PFILE

CREATE SPFILE FROM PFILE='/home/oracle/oracle/19.0.0/dbs/initORCL1.ora';

7.2 Restart the Database

SHUTDOWN IMMEDIATE;

STARTUP;

Step 8: Configure Listener and Network Settings

8.1 Edit listener.ora

vi $ORACLE_HOME/network/admin/listener.ora

Add the following configuration:

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    )

  )

8.2 Restart the Listener

lsnrctl stop

lsnrctl start

8.3 Edit tnsnames.ora

vi $ORACLE_HOME/network/admin/tnsnames.ora

Add the following entry:

ORCL1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    (CONNECT_DATA =

      (SERVICE_NAME = ORCL1)

    )

  )

8.4 Test the Connection

tnsping ORCL1

Step 9: Verify Database Status

To check if the database is running correctly:

SELECT NAME, OPEN_MODE FROM V$DATABASE;

The output should display "OPEN" if the database is successfully running.

Conclusion

Manually creating an Oracle database allows greater flexibility and deeper knowledge of database operations. Unlike using DBCA, this method provides full control over initialization settings, storage configurations, and network connections.

By following these steps, DBAs can better understand database creation, optimize performance, and ensure a properly configured environment.