Oracle Database Creation Step by Step Guide

Hi Friends,

Please follow the steps as shown for Oracle Database Creation (11G).

Step 1)Create a directory structure where u want to keep the database files.Not to choose /tmp for this.
Considering mount point name as /database

mkdir /database/oradata  
mkdir /database/orarch
mkdir /database/orabase
mkdir /database/orabase/adump
mkdir /database/orabase/bdump
mkdir /database/orabase/cdump
mkdir /database/orabase/udump

oradata------------For Datafiles
oraarch------------For Archive Log
orabase------------It contains the dump file like core dump.

Step 2)Create the following if does not exist.

groupadd dba
groupadd oinstall
useradd -g oinstall -G dba oracle
passwd oracle


Step 3)Login to Oracle and change permission of the directories created before to Oracle as owner and Oinstall as secondary owner.

cd  /database
chown -R oracle:oinstall oradata
chown -R oracle:oinstall orabase
chown -R oracle:oinstall oraarch
chmod 777 -R ora*

Step 4) Set the following variables:

export ORACLE_SID=test
export ORACLE_HOME=/oracle11g/product/admin
export PATH=$ORACLE_HOME/bin:PATH
export ORACLE_BASE=/oracle11g/product

Step 5) Create a pfile in the dbs directory
cd $ORACLE_HOME/dbs
vi inittest.ora
(copy and paste the contect bellow in the file and then save and exit (:wq))


test.__db_cache_size=2G
test.__oracle_base='/oracle11g/product/'
test.__pga_aggregate_target=2G
test.__sga_target=5G
test.__shared_io_pool_size=500M
test.__shared_pool_size=500M
test.audit_file_dest='/database/orabase/admup/'
test.control_files='/database/oradata/control01.dbf'
test.db_block_size=8192
test.db_name='test'
test.diagnostic_dest='/database/oradata/cdump'
test.log_archive_format='%t_%s_%r.dbf'
test.log_archive_max_processes=4
test.memory_target=5G
test.open_cursors=300
test.processes=1500
test.remote_login_passwordfile='EXCLUSIVE'
test.sessions=1105
test.standby_file_management='AUTO'
test.undo_tablespace='UNDOTBS'

Step 6)NOMOUNT the instance
sqlplus / as sysdba
startup nomount pfile='$ORACLE_HOME/dbs/inittest.ora';

CREATE DATABASE test

USER SYS IDENTIFIED BY password

USER SYSTEM IDENTIFIED BY password

LOGFILE GROUP 1 ('/database/oradata/redo01.log') SIZE 100M,

GROUP 2 ('/database/oradata/redo02.log') SIZE 100M,

GROUP 3 ('/database/oradata/redo03.log') SIZE 100M

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXLOGHISTORY 1

MAXDATAFILES 100

CHARACTER SET US7ASCII

NATIONAL CHARACTER SET AL16UTF16

EXTENT MANAGEMENT LOCAL

DATAFILE '/data2/testdb/oradata/system01.dbf' SIZE 325M REUSE

SYSAUX DATAFILE '/database/oradata/sysaux01.dbf' SIZE 325M REUSE

DEFAULT TABLESPACE users

DATAFILE '/database/oradata/users01.dbf'

SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

DEFAULT TEMPORARY TABLESPACE tempts1

TEMPFILE '/database/oradata/temp01.dbf'

SIZE 20M REUSE

UNDO TABLESPACE UNDOTBS

DATAFILE '/database/oradata/undotbs01.dbf'

SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

After successful completion of the above run the following 3 script which will take around 15-20 min.

@$ORACLE_HOME/rdbms/admin/catalog.sql

@$ORACLE_HOME/rdbms/admin/catproc.sql

@$ORACLE_HOME/sqlplus/admin/pupbld.sql 

 Database creation is successful in case of any issues please connect with me on the Gmail ID.

Now you can create Tablespace and add datafile as required.

Free Overused Cache(Linux OS)

In Linux operating system we can see that a lot of memory get allocated to cache.Which result in CPU IO.

This Issue can be resolved in few steps:

CMD to Check Cache usage:

free -g

You can use different flag as per your requirement this will provide you the memory details.

where u can see the memory associated with Cache.

If 50% of the total memory seems to be allocated to Cache,It can be cleared.

And trust me it provide a great help in reducing the system CPU IO.

echo 1 > /proc/sys/vm/drop_caches

This can be used in Production Environment in business hours without any harm and prior notification.