How to Create Oracle Database Manually through the file system

In this article, you will get to know the step by step process of 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”
 
[[email protected] ~]$ 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: –
[[email protected] ~]$ . 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: –
[[email protected] ~]$ cd $ORACLE_HOME/dbs
/u01/app/oracle/product/11.2.0/db_1/dbs
 
==> edit  parameter file (initkaran.ora) file 
 
Example: –
[[email protected] 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
 
[[email protected] dbs]$ mkdir -p /u01/app/oracle/oradata/karan
 
[[email protected] dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area/karan
 
[[email protected] dbs]$ mkdir -p /u01/app/oracle/oradata/karan/datafile
 
[[email protected] 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: –
 
[[email protected] 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.
 

Hope this blog will help you!!

If this article is useful for you please comment, share with your friends.

In case of any issues please send your query to below comment section.

For more articles please do visit below links: –

How to create database through Graphical tool “DBCA” Database Configuration Assistant

Oracle 12c Grid binary installation | Karan Rajpoot

Oracle 12c Grid ASM installation Part-1 | Karan Rajpoot

Oracle 12c Grid ASM installation Part-2 | Karan Rajpoot

Advertisement

This Post Has 2 Comments

Leave a Reply