How to create a Database manually?

In this post, I’m going to show you how to create a manually Database step by step and how to create a password file and how to configure Enterprise Manager (EM). For manual Database creation, we have to need a directory structure which is mandatory for every Database. In Oracle every Database has a separate Directory structure, this directory structure is used to store the Oracle database component files. Follow the below steps.

Steps to Manually Database Creation

Follow the below steps to creating a manual Database in oracle.

Directory structure

Firstly we need a directory structure so we are creating a directory structure.

$ mkdir  /u01/ora11g
$ cd  /u01/ora11g
$ mkdir  adump  cdump  pfile  create  flash_recovery_area 

create a parameter file

To start this database we need a pfile, using below parameter we are going to create a pfile.

$ vim  /u01/ora11g/pfile/initora11g.ora

db_name=ora11g
instance_name=ora11g
control_files='/u01/ora11g/create/control01.ctl','/u01/ora11g/create/control02.ctl'
db_block_size=8192
db_cache_size=70m
shared_pool_size=100m
audit_file_dest=/u01/ora11g/adump
core_dump_dest=/u01/ora11g/cdump
db_recovery_file_dest=/u01/ora11g/flash_recovery_area
db_recovery_file_dest_size=4g
undo_tablespace=undotbs1
undo_management=auto
remote_login_passwordfile=exclusive
compatible=11.2.0.3

After creating the parameter file we have to need a creation file which is required for creating Datafiles, controlfiles, redo log files, and so on. Using the following commands.

$ vim /u01/ora11g/cre_ora11g.sql

create database ora11g
logfile
group 1 '/u01/ora11g/create/redo01.log' size 100m,
group 2 '/u01/ora11g/create/redo02.log' size 100m
maxinstances 1
maxlogfiles 5
maxlogmembers 5
maxdatafiles 100
maxloghistory 1
datafile  '/u01/ora11g/create/system01.dbf' size 500m
sysaux datafile '/u01/ora11g/create/sysaux01.dbf' size 500m
undo tablespace undotbs1
datafile '/u01/ora11g/create/undotbs01.dbf' size 50m
default temporary tablespace temp
tempfile '/u01/ora11g/create/temp01.dbf' size 50m

Save above file using press Esc, shift+:wq

Now export oracle sid using the below commands, then login into your new database and start is up to nomount stage.

$ export ORACLE_SID=ora11g
$ sqlplus sys/sys as sysdba 
SQL> startup nomount pfile=/u01/ora11g/pfile/initora11g.ora 

Now calling your creation script which is created in step 3 using following command.

SQL>@/u01/ora11g/cre_ora11g.sql 

Once the above file run successfully, now we are going to create a spfile from pfile.

create spfile from pfile='/u01/ora11g/pfile/initora11g.ora'; 

Run below two scripts for creating pl/sql objects and permissions, using following commands.

SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql 

Now create password file for remotely connection. Using following command.

$orapwd file=$ORACLE_HOME/dbs/orapwora11g  password=ora11g  entries=10  force=y

After that, if you want to configure Enterprise Manager (EM) for this Database then following the below command.

$ emca  -config  dbcontrol  db  -repos  create 

Read - How to Create Database using OMF parameter

Connect with me on:

Instagram: https://www.instagram.com/shripaldba
Linkedin: https://www.linkedin.com/in/shripal-singh
Twitter: https://twitter.com/ocptechnology
Facebook: https://www.facebook.com/ocptechnology
YouTube:https://www.youtube.com/ocptechnology