Mamun's DBA Blog: How to create an oracle database manually

An oracle database can be created either using Manually or using DBCA( GUI Method).  But few organizations recommend to use manual method for creating database. Here I have provided steps for creating database Manually with installation of differnt optional components.
Before creating an oracle database, Make sure oracle database software is installed.
REFER –  Steps to install oracle 12c software
Lets say we will create a database named DBATEAM

1. First, create a profile with a correct environment variable.

ORACLE_SID = SYSTEM IDENTIFIER , SAME AS db instance name
ORACLE_HOME = Oracle database binary installation location.

vi .profile_DBATEAM

#!/bin/sh

umask 022

ORACLE_SID=DBATEAM; export ORACLE_SID

ORACLE_HOME=/oracle/app/oracle/product/11.2.0.4/dbhome_1; export ORACLE_HOME

export PATH=$ORACLE_HOM/bin:$PATH

2. Now create the init.ora file( in $ORACLE_HOME/dbs location)

When we start the instance, it will read this file and set values accordingly.
mkdir /u01/app/oracle
mkdir -p /u01/oradata/DBATEAM/data/DBATEAM

cd $ORACLE_HOME/dbs

$ vi initDBATEAM.ora

*.compatible='12.1.0.2.0'

*.control_files='/u01/oradata/DBATEAM/data/DBATEAM/control01.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='DBATEAM'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=DBATEAMXDB)'

*.open_cursors=300

*.pga_aggregate_target=536870912

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=1610612736

*.undo_tablespace='UNDOTBS1'

COMPATIBLE –             Version of the oracle database
CONTROL_FILE –        When we create the database, the control_files will be created in the specified location.
DB_BLOCK_SIZE–       This is the default block_size of the database. Any tablespace we create in the database will take this block size value.
DB_NAME  –                   Name of the database
DIAGNOSTIC_DEST–  ADR location, where the log and trace files will be written
SGA_TARGET –             Shared global area, memory allocated.
UNDO_TABLESPACE–  The default undo tablespace, that will be created in the database

3.  Start the database in nomount stage:

. ./.profile_DBATEAM

SQL > sqlplus / as sysdba

SQL*Plus: Release 11.1.0.2.0 Production on Mon Jul 20 11:19:05 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL > startup nomount

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size                  2228784 bytes

Variable Size             503320016 bytes

Database Buffers         1073741824 bytes

Redo Buffers               24121344 bytes

SQL >

4.  Below the create database script.

SQL > CREATE DATABASE DBATEAM

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXDATAFILES 100

MAXINSTANCES 1

LOGFILE

GROUP 1 '/u04/oradata/DBATEAM/data/redo1.log' SIZE 10M,

GROUP 2 '/u04/oradata/DBATEAM/data/redo2.log' SIZE 10M,

GROUP 3 '/u04/oradata/DBATEAM/data/redo3.log' SIZE 10M

DATAFILE

'/u04/oradata/DBATEAM/data/system.dbf' size 200M REUSE

sysaux datafile '/u04/oradata/DBATEAM/data/sysaux.dbf' size 100m

undo tablespace UNDOTBS1 datafile '/u04/oradata/DBATEAM/data/undo1.dbf' size 100m

DEFAULT TEMPORARY TABLESPACE temp1

TEMPFILE '/u04/oradata/DBATEAM/data/temp01.dbf' SIZE 100M REUSE

CHARACTER SET AL32UTF8;

database created

MAXLOGFILES – Maximum number of redo log files that can be created in the database.
MAXLOGMEMBERS – maximum number of redo log file members that any given log file group can have in the database.
MAXLOGHISTORY – Maximum number of archive redo logs for automatic media recovery.
MAXDATAFILES – This defines the maximum number of datafile, But upon reaching max value, it will extend automatically to value upto DB_FILES parameter.
The MAXINSTANCES – No. of instances for the database, for standalone it will be 1 and for RAC database, it will be that of no. of rac nodes.
CHARACTER SET – Characterset of the database.

5. Now create the data dictionary views(CATALOG and CATPROC component)

These two are mandatory scripts we need to run after creating a database. Below scripts will create the dictionary views and tables.

SQL > @$ORACLE_HOME/rdbms/admin/catalog.sql

SQL > @$ORACLE_HOME/rdbms/admin/catproc.sql

6. Create the product user profile table ( by connecting to system user)

SQL > conn system/manager

SQL > @$ORACLE_HOME/sqlplus/admin/pupbld.sql

You can install the additional components depending upon the requirement. These are the optional components.

7. Install JVM(JAVA VIRTUAL MACHINE) component:[optional]

conn / as sysdba

@/oracle/app/oracle/product/11.2.0.4/dbhome_1/javavm/install/initjvm.sql;

@/oracle/app/oracle/product/11.2.0.4/dbhome_1/xdk/admin/initxml.sql;

@/oracle/app/oracle/product/11.2.0.4/dbhome_1/xdk/admin/xmlja.sql;

@/oracle/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/catjava.sql;

@/oracle/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/catxdbj.sql;

8. Install CONTEXT component:[optional]

@/oracle/app/oracle/product/11.2.0.4/dbhome_1/ctx/admin/catctx.sql change_on_install SYSAUX TEMP LOCK;

@/oracle/app/oracle/product/11.2.0.4/dbhome_1/ctx/admin/defaults/dr0defin.sql "AMERICAN";

@/oracle/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/dbmsxdbt.sql;

9. Install ORADIM component:[optional]

@/oracle/app/oracle/product/11.2.0.4/dbhome_1/ord/admin/ordinst.sql SYSAUX SYSAUX;

10. Install MEDIA component:[optional]

@/oracle/app/oracle/product/11.2.0.4/dbhome_1/ord/im/admin/iminst.sql;

11. Install OLAP component:[optional]

@/oracle/app/oracle/product/11.2.0.4/dbhome_1/olap/admin/olap.sql SYSAUX TEMP;

12. Install SPATIAL component:[optional]

@/oracle/app/oracle/product/11.2.0.4/dbhome_1/md/admin/mdinst.sql;

13. Install LABEL security component:[optional]

@/oracle/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/catols.sql;

14. Install APEX component:[optional]

@/oracle/app/oracle/product/11.2.0.4/dbhome_1/apex/catapx.sql change_on_install SYSAUX SYSAUX TEMP /i/ NONE;

15. Install DATABASE VAULT component:[optional]

@/oracle/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/catmac.sql SYSAUX TEMP &&sysPassword;

You can check the installed components by running the below query.

select comp_id,status,comp_name from dba_registry;

16. Create a listener file and start the listener.

vi listener.ora

LISTENER_DBATEAM =

    (DESCRIPTION=

      (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))

    )

SID_LIST_LISTENER_DBATEAM =

  (SID_LIST=

    (SID_DESC=

      (SID_NAME=DBATEAM)

      (ORACLE_HOME= /u01/app/oracle/product/11.2.0.4/dbhome_1)

    )

  )

lsnrctl start LISTENER_DBATEAM