Wednesday, January 16, 2019

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.

No comments:

Post a Comment