How to Create Oracle Database Manually through the file system

                               STEPS TO CREATE a "MANUALLY DATABASE" IN ORACLE

Step 1) Create a manual entry in /etc/oratab file for a database which needs to be built, in my case I am using "Karan"

[oracle@prod ~]$ cat /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 an 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.

#

#

#prod:/u01/app/oracle/product/11.2.0/db_1:N

karan:/u01/app/oracle/product/11.2.0/db_1:N

Step 2) Create a parameter file to start the database in a nomount stage, but first, you need to set the environment through. oraenv command.

Example: -

[oracle@prod ~]$ . oraenv

ORACLE_SID = [oracle] ? karan

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

==> Now go to below path to create parameter file (init<SID>.ora)

Location of parameter file : -  ORACLE_HOME/dbs

Example: -

[oracle@prod ~]$ cd $ORACLE_HOME/dbs

[oracle@prod dbs]$ pwd

/u01/app/oracle/product/11.2.0/db_1/dbs

==> edit  parameter file (initkaran.ora) file 

Example: -

[oracle@prod dbs]$ cat initkaran.ora 

karan.__db_cache_size=343932928

karan.__java_pool_size=4194304

karan.__large_pool_size=8388608

karan.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

karan.__pga_aggregate_target=339738624

karan.__sga_target=503316480

karan.__shared_io_pool_size=0

karan.__shared_pool_size=134217728

karan.__streams_pool_size=4194304

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/u01/app/oracle/oradata/karan/control01.ctl','/u01/app/oracle/fast_recovery_area/karan/control02.ctl'

*.db_block_size=8192

*.db_name='karan'

*.diagnostic_dest='/u01/app/oracle'

*.memory_target=840957952

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

Step 3) Make necessary directories for control files, data files and redo log files

[oracle@prod dbs]$ mkdir -p /u01/app/oracle/oradata/karan

[oracle@prod dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area/karan

[oracle@prod dbs]$ mkdir -p /u01/app/oracle/oradata/karan/datafile

[oracle@prod dbs]$ mkdir -p /u01/app/oracle/oradata/karan/redolog

Step 4) Make your database in nomount stage through below command and also check whether your instance is started or not.

SQL> startup nomount

ORACLE instance started.

Total System Global Area  839282688 bytes

Fixed Size     2257880 bytes

Variable Size   494930984 bytes

Database Buffers   339738624 bytes

Redo Buffers     2355200 bytes

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

karan

Step 5) create a SQL script and make entries inside it to create a database.

Note: - you can create SQL script with any name whatever you want.

Example: -

[oracle@prod dbs]$ cat create.sql 

create database karan

user sys identified by sys

user system identified by system

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

group 2 ('/u01/app/oracle/oradata/karan/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/karan/datafile/system01.dbf' size 1024m

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

default tablespace users

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

reuse autoextend on maxsize unlimited

default temporary tablespace temp

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

undo tablespace undotbs1

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

==> Now run the create.sql script using below command using SQL prompt

Command: - @create.sql or specify the whole path in which you have created your SQL script

Example: -

SQL> @create.sql

Database created.

SQL> select name,open_mode from v$database;

NAME   OPEN_MODE

--------- --------------------

KARAN   READ WRITE

Step 6) Execute script catalog.sql and catproc.sql placed in ORACLE_HOME/RDBMS/admin

CATALOG.SQL: - Creates the views of the data dictionary tables, the dynamic performance views, and public synonyms.

CATPROC.SQL: - Runs all scripts required for or used with PL/SQL.