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/12.1.0.2/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/12.1.0.2/dbhome_1/javavm/install/initjvm.sql; @/oracle/app/oracle/product/12.1.0.2/dbhome_1/xdk/admin/initxml.sql; @/oracle/app/oracle/product/12.1.0.2/dbhome_1/xdk/admin/xmlja.sql; @/oracle/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catjava.sql; @/oracle/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catxdbj.sql;
8. Install CONTEXT component:[optional]
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/ctx/admin/catctx.sql change_on_install SYSAUX TEMP LOCK; @/oracle/app/oracle/product/12.1.0.2/dbhome_1/ctx/admin/defaults/dr0defin.sql "AMERICAN"; @/oracle/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/dbmsxdbt.sql;
9. Install ORADIM component:[optional]
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/ord/admin/ordinst.sql SYSAUX SYSAUX;
10. Install MEDIA component:[optional]
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/ord/im/admin/iminst.sql;
12. Install OLAP component:[optional]
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/olap/admin/olap.sql SYSAUX TEMP;
13. Install SPATIAL component:[optional]
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/md/admin/mdinst.sql;
14. Install LABEL security component:[optional]
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catols.sql;
15. Install APEX component:[optional]
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/apex/catapx.sql change_on_install SYSAUX SYSAUX TEMP /i/ NONE;
16. Install DATABASE VAULT component:[optional]
@/oracle/app/oracle/product/12.1.0.2/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;
17. Create a listener file and start the listener.
vi listener.ora
LISTENER_DBATEAM =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1532))
)
SID_LIST_LISTENER_DBATEAM =
(SID_LIST=
(SID_DESC=
(SID_NAME=DBATEAM)
(ORACLE_HOME= /u01/app/oracle/product/11.2.0.3/db_1)
)
)
lsnrctl start LISTENER_DBATEAM
