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
# 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:
# 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.

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

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

Example: -
[oracle@prod dbs]$ cat initkaran.ora 
karan.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

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;


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;

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

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.


Popular posts from this blog

How to resolve "No protocol specified" error

How to resolve "TNS-12541 : TNS : no listener" error

Oracle "Redo log Groups addition/deletion" | Karan Rajpoot