Create database Manually without DBCA

Create a new database manually without help of DBCA utility

1) Login with Oracle user or user which oracle software is installed on Operating System.

su - oracle

2) create a oradata directory structure & for trace directories

mkdir -p /u01/oradata/orcl
mkdir -p /u01/app/oracle/admin/orcl
cd /u01/app/oracle/admin/orcl
mkdir bdump cdump udump pfile adump

3) Create parameter file manually with mandatory parameters

vi initorcl.ora

*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'
*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'
*.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'
*.diagnostic_dest=/u01/app/oracle/admin/diag
*.compatible='11.2.0.0.0'
*.control_files=('/u01/oradata/orcl/control01.ctl')
*.db_block_size=8192
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.fast_start_mttr_target=600
*.instance_name='orcl'
*.job_queue_processes=10
*.workarea_size_policy='AUTO'
*.pga_aggregate_target=25M
*.remote_login_passwordfile='EXCLUSIVE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.undo_retention=3600
*.sga_max_size=250m
*.sga_target=250m
*.db_file_recovery_dest='/u01/oradata/backups/'
*.db_file_recovery_dest_size=2g
*.processes=500
*.sessions=300

Note: the parameter file name format must be init.ora, here i am going to create a database name as orcl and used initorcl.ora
Remember, this parameter file should be placed in $ORACLE_HOME/dbs
The order of the pfile read by oracle is
it will read spfile if its there in $ORACLE_HOME/dbs
if not read pfile if its there in $ORACLE_HOME/dbs
if not read the pfile given in prompt like startup pfile=”

4) Set oracle sid

export ORACLE_SID=orcl

Note the oracle_sid is case sensitive

5) set oracle home

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

6) set path variable

export PATH=/u01/app/oracle/product/11.2.0/db_1:$PATH

5) Create a password file

$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/pwdorcl.ora password=oracle entries=5

6) start the instance

sqlplus / as sysdba
startup nomount

7) In SQLPLUS run the following statement for creating the database:

create database orcl
MAXINSTANCES 8  
MAXLOGHISTORY 100  
MAXLOGFILES 64  
MAXLOGMEMBERS 3  
MAXDATAFILES 150
logfile group 1 ('/u01/oradata/orcl/redo1.log') size 100M,
            group 2 ('/u01/oradata/orcl/redo2.log') size 100M,
            group 3 ('/u01/oradata/orcl/redo3.log') size 100M
character set WE8ISO8859P1
national character set utf8
datafile '/u01/oradata/orcl/system.dbf' size 500M autoextend on next 10M maxsize unlimited extent management local
sysaux datafile '/u01/oradata/orcl/sysaux.dbf' size 100M autoextend on next 10M maxsize unlimited
undo tablespace undotbs1 datafile '/u01/oradata/orcl/undotbs1.dbf' size 100M
default temporary tablespace temp tempfile '/u01/oradata/orcl/temp01.dbf' size 100M;

8) Create dictionary views

Sqlplus / as sysdba
@?/sqlplus/rdbms/admin/catalog.sql
@?/sqlplus/rdbms/admin/cataproc.sql
@?/sqlplus/rdbms/lib/pupbld.sql

9) Shutdown the database and restart

sqlplus / as sysdba
shut immediate
exit

sqlplus / as sysdba
startup
exit

10) compile invalids objects

@?/rdbms/admin/utlrp.sql

11) create a users tablespace

create tablespace users datafile '/u01/oradata/orcl/users.dbf' size 100m maxsize unlimited extent management local segment space management auto;

12) Create network files
cd /u01/app/oracle/product/11.2.0/db_1/network/admin
vi tnsnames.ora

DEMO =  
(DESCRIPTION =  
(ADDRESS = (PROTOCOL = TCP)(HOST = node1.localdomain)(PORT = 1521))  
(CONNECT_DATA =  
(SERVER = DEDICATED)  
(SERVICE_NAME = orcl)  
)  
)

in Listener.ora in sid_list

      (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
       )

13) Start the listener

14) add entry in oratab

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.