Oracle Manual Database creation steps

Once the Oracle 11gR2 or lower is installed, Follow the below steps to manually create a database.

Ps : Its not an industry standard, Its only for educational purpose.

Database Name: demo_db

1.   Create the necessary directories.

[oracle@asmoracle database]$ cd

[oracle@asmoracle ~]$ mkdir -p /u01/app/oracle/oradata/demo_db/control

[oracle@asmoracle ~]$ mkdir -p /u01/app/oracle/oradata/demo_db/datafile

[oracle@asmoracle ~]$ mkdir -p /u01/app/oracle/oradata/demo_db/redolog

[oracle@asmoracle ~]$ ls -ld /u01/app/oracle/oradata/demo_db

drwxr-xr-x. 5 oracle oinstall 4096 Jul 26 21:06 /u01/app/oracle/oradata/demo_db

[oracle@asmoracle ~]$ ls -ld /u01/app/oracle/oradata/demo_db/control/

drwxr-xr-x. 2 oracle oinstall 4096 Jul 26 21:06 /u01/app/oracle/oradata/demo_db/control/

2.   Update the oratab -- $ORACLE_SID:$ORACLE_HOME:<N|Y>:

[oracle@asmoracle ~]$ vi /etc/oratab

# This file is used by ORACLE utilities.  It is created by root.sh

# and updated by either Database Configuration Assistant while creating

# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator.  A new line terminates

# the entry.  Lines beginning with a pound sign, '#', are comments.

#

# Entries are of the form:

#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:

#

# The first and second fields are the system identifier and home

# directory of the database respectively.  The third filed indicates

# to the dbstart utility that the database should , "Y", or should not,

# "N", be brought up at system boot time.

#

# Multiple entries with the same $ORACLE_SID are not allowed.

demo_db:/u01/app/oracle/product/11.2.0.4/db_1:N:

save the file.

3.   Create the init<SID>.ora file ( pfile )

[oracle@asmoracle ~]$ cd /u01/app/oracle/product/11.2.0.4/db_1/dbs

[oracle@asmoracle dbs]$ vi initdemo_db.ora

db_name=demo_db

instance_name=demo_db

service_names=demo_db,test

control_files='/u01/app/oracle/oradata/demo_db/control/control01.ctl','/u01/app/oracle/oradata/demo_db/control/control02.ctl','/u01/app/oracle/oradata/demo_db/control/control03.ctl'

memory_target=500m

undo_tablespace=undotbs1

undo_management=AUTO

diagnostic_dest=/u01/app/oracle

compatible=11.2.0

4.   Create database creation script

[oracle@asmoracle dbs]$ vi /home/oracle/createdb.sql

create database demo_db

user sys identified by sys

user system identified by system

logfile group 1 ('/u01/app/oracle/oradata/demo_db/redolog/redo01.log') size 100m,

       group 2 ('/u01/app/oracle/oradata/demo_db/redolog/redo02.log') size 100m

maxlogfiles 5

maxlogmembers 5

maxloghistory 100

maxdatafiles 100

character set US7ASCII

national character set AL16UTF16

extent management local

datafile '/u01/app/oracle/oradata/demo_db/datafile/system01.dbf' size 1024m

sysaux datafile '/u01/app/oracle/oradata/demo_db/datafile/sysaux01.dbf' size 325m

default tablespace users

datafile '/u01/app/oracle/oradata/demo_db/datafile/users01.dbf' size 500m

reuse autoextend on maxsize unlimited

default temporary tablespace temp

tempfile '/u01/app/oracle/oradata/demo_db/datafile/temp01.dbf' size 50m reuse

undo tablespace undotbs1

datafile '/u01/app/oracle/oradata/demo_db/datafile/undotbs1.dbf' size 50m

;

5.   Update your environment variables

[oracle@asmoracle ~]$ . /home/oracle/.bash_profile

[oracle@asmoracle ~]$ which sqlplus

/u01/app/oracle/product/11.2.0.4/db_1/bin/sqlplus

[oracle@asmoracle ~]$ . oraenv

ORACLE_SID = [oracle] ? demo_db

The Oracle base has been set to /u01/app/oracle

6.   Create Database

[oracle@asmoracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jul 26 21:17:25 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL>

SQL> set echo on term on

SQL> startup nomount

ORACLE instance started.

Total System Global Area  521936896 bytes

Fixed Size          2254824 bytes

Variable Size           343935000 bytes

Database Buffers  167772160 bytes

Redo Buffers              7974912 bytes

SQL> @/home/oracle/createdb.sql;

SQL> create database demo_db

  2  user sys identified by sys

  3  user system identified by system

  4  logfile group 1 ('/u01/app/oracle/oradata/demo_db/redolog/redo01.log') size 100m,

  5      group 2 ('/u01/app/oracle/oradata/demo_db/redolog/redo02.log') size 100m

  6  maxlogfiles 5

  7  maxlogmembers 5

  8  maxloghistory 100

  9  maxdatafiles 100

 10  character set US7ASCII

 11  national character set AL16UTF16

 12  extent management local

 13  datafile '/u01/app/oracle/oradata/demo_db/datafile/system01.dbf' size 1024m

 14  sysaux datafile '/u01/app/oracle/oradata/demo_db/datafile/sysaux01.dbf' size 325m

 15  default tablespace users

 16  datafile '/u01/app/oracle/oradata/demo_db/datafile/users01.dbf' size 500m

 17  reuse autoextend on maxsize unlimited

 18  default temporary tablespace temp

 19  tempfile '/u01/app/oracle/oradata/demo_db/datafile/temp01.dbf' size 50m reuse

 20  undo tablespace undotbs1

 21  datafile '/u01/app/oracle/oradata/demo_db/datafile/undotbs1.dbf' size 50m

 22  ;

Database created.

SQL>

SQL> !ps -ef | grep pmon

oracle    7655     1  0 21:18 ?        00:00:00 ora_pmon_demo_db

oracle    9326  7649  0 21:21 pts/1    00:00:00 /bin/bash -c ps -ef | grep pmon

oracle    9328  9326  0 21:21 pts/1    00:00:00 grep pmon

SQL>

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

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

SQL> @?/sqlplus/admin/pupbld.sql;

SQL> create spfile from pfile;

File created.

SQL> !ls -lrt /u01/app/oracle/product/11.2.0.4/db_1/dbs                                                           

total 20

-rw-r--r--. 1 oracle oinstall 2851 May 15  2009 init.ora

-rw-r--r--. 1 oracle oinstall  369 Jul 26 21:13 initdemo_db.ora

-rw-r-----. 1 oracle oinstall   24 Jul 26 21:18 lkDEMO_DB

-rw-rw----. 1 oracle oinstall 1544 Jul 26 21:19 hc_demo_db.dat

-rw-r-----. 1 oracle oinstall 1536 Jul 26 21:48 spfiledemo_db.ora

Database and its dependent files are created!!!

Please let me know your feedback in the comment section.