Oracle Database Creation Using Manual Method (Step-by-Step)

 

Prerequisites Before Creating Database

a) Oracle software must be installed and ORACLE_HOME should be configured.

b) Set environment variables:

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export ORACLE_SID=ORCL
export PATH=$ORACLE_HOME/bin:$PATH

c) Create required directories:

mkdir -p /u01/app/oracle/oradata/ORCL
mkdir -p /u01/app/oracle/fast_recovery_area/ORCL
mkdir -p /u01/app/oracle/admin/ORCL/adump

Step 1 – Create Password File

Password file stores SYS user password for remote SYSDBA connections.

Command:

orapwd file=$ORACLE_HOME/dbs/orapwORCL password=Oracle123 entries=10

Step 2 – Create PFILE (init.ora)

Create initialization parameter file.

Example:

db_name='ORCL'
memory_target=2G
processes=300
audit_file_dest='/u01/app/oracle/admin/ORCL/adump'
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
db_recovery_file_dest_size=10G
control_files='/u01/app/oracle/oradata/ORCL/control01.ctl'
compatible='19.0.0'

Step 3 – Start Database in NOMOUNT Mode

Start Oracle instance using PFILE.

SQL> startup nomount pfile='$ORACLE_HOME/dbs/initORCL.ora';

Verify:
SQL> show parameter memory_target;

Step 4 – Run CREATE DATABASE Command

This step creates the database files, redo logs, undo tablespace, SYSTEM/SYSAUX tablespaces.

Example:

CREATE DATABASE ORCL
USER SYS IDENTIFIED BY Oracle123
USER SYSTEM IDENTIFIED BY Oracle123
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/ORCL/redo01.log') SIZE 100M,
GROUP 2 ('/u01/app/oracle/oradata/ORCL/redo02.log') SIZE 100M,
GROUP 3 ('/u01/app/oracle/oradata/ORCL/redo03.log') SIZE 100M
MAXLOGFILES 16
MAXDATAFILES 200
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/u01/app/oracle/oradata/ORCL/system01.dbf' SIZE 700M REUSE
SYSAUX DATAFILE '/u01/app/oracle/oradata/ORCL/sysaux01.dbf' SIZE 500M REUSE
DEFAULT TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf' SIZE 100M
UNDO TABLESPACE undotbs1
DATAFILE '/u01/app/oracle/oradata/ORCL/undotbs01.dbf' SIZE 200M;

Step 5 – Run Catalog and Catproc Scripts

Execute Oracle dictionary and PL/SQL package creation scripts.

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

Step 6 – Create SPFILE from PFILE

Convert PFILE to SPFILE.

SQL> create spfile from pfile;

Restart database:

SQL> shutdown immediate;
SQL> startup;

Step 7 – Configure Listener

Edit listener.ora and start listener.

lsnrctl start
lsnrctl status

Step 8 – Configure tnsnames.ora

Example tnsnames entry:

ORCL=
 (DESCRIPTION=
   (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))
   (CONNECT_DATA=
      (SERVICE_NAME=ORCL)
   )
 )

Step 9 – Final Verification

Verify database status:

SQL> SELECT NAME, OPEN_MODE, DB_UNIQUE_NAME FROM V$DATABASE;

Check tablespaces:

SQL> SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;

Check listener:

lsnrctl status

Best Practices & DBA Tips

- Always verify ORACLE_SID before starting database.
- Use AL32UTF8 character set for multilingual support.
- Keep control files multiplexed.
- Take RMAN backup immediately after database creation.
- Configure archive log mode for production databases.
- Monitor alert logs after startup.