30 May 2016

Creating Oracle 11g Database manually on Linux

4:21 PM MongoDB 0 Comment

Here we are going to create the Oracle 11g database manually. Before you create the database make sure you have done the planning about the size of the database, number of tablespaces and redo log files you want in the database.

Let us create a database my_db with the following specification.

Step 1: Create the directory structure

[oracle@localhost ~]$ mkdir /prod

[oracle@localhost ~]$ cd /prod

[oracle@localhost prod]$ mkdir my_db

[oracle@localhost prod]$ cd my_db

[oracle@localhost my_db]$ mkdir admin oradata scripts backup diag flash_recovery_area

[oracle@localhost my_db]$ chown -R oracle:oinstall /prod/my_db /u01

[oracle@localhost my_db]$ chmod -R 777 /prod/my_db/* /u01

Step 2: Create environment file

[oracle@localhost my_db]$ vi my_db.env
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/my_db
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID=orcl
export TNS_ADMIN=/u01/app/oracle/product/11.2.0.4/my_db/network/admin
:wq

Step 3: Create Parameter file in $ORACLE_HOME/dbs

[oracle@localhost my_db]$ cd $ORACLE_HOME/dbs

Now open the parameter file and set the following parameters.

[oracle@localhost dbs]$ vi initorcl.ora
db_name=’my_db’
compatible =’11.2.0′
memory_target=2048
db_block_size=8192
db_recovery_file_dest=’/prod/my_db/flash_recovery_area’
db_recovery_file_dest_size=2G
diagnostic_dest=’/prod/my_db/diag’
undo_tablespace=’UNDOTBS1′
control_files = ‘/prod/my_db/oradata/control1.ctl’
:wq!

Step 4: Write Create Database script

[oracle@localhost ~]$ cd /prod/my_db/scripts

[oracle@localhost scripts]$ vi create_db.sql

CREATE DATABASE my_db
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
LOGFILE
GROUP 1 ‘/prod/my_db/oradata/redo1.log’ SIZE 30M,
GROUP 2 ‘/prod/my_db/oradata/redo2.log’ SIZE 30M,
GROUP 3 ‘/prod/my_db/oradata/redo3.log’ SIZE 30M
DATAFILE
‘/prod/my_db/oradata/system.dbf’ size 200M
sysaux datafile ‘/prod/my_db/oradata/sysaux.dbf’ size 100m
undo tablespace UNDOTBS1 datafile ‘/prod/my_db/oradata/undo1.dbf’ size 100m
DEFAULT TEMPORARY TABLESPACE temp1 TEMPFILE ‘/prod/my_db/oradata/temp01.dbf’ SIZE 100M
CHARACTER SET UTF8;
:wq!

Step 5: Start the Database in nomount State

[oracle@localhost ~]$ cd /prod/my_db

[oracle@localhost my_db]$ ./my_db.env

[oracle@localhost my_db]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Mon May 30 15:46:15 2016
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Release 11.2.0.4.0 – 64bit Production
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1071333376 bytes
Fixed Size 1341312 bytes
Variable Size 620759168 bytes
Database Buffers 444596224 bytes
Redo Buffers 4636672 bytes
SQL> select status from v$instance;

STATUS
————
STARTED

Step 6: Execute Create Database script created in Step 4

SQL>@/prod/my_db/scripts/create_db.sql

Database created

After the command finishes you will get the above message

Step 7:Post creation scripts

So your database is created. Now just run the catalog.sql and catproc.sql scripts.
You will find the in $ cd $ORACLE_HOME/rdbms/admin

catalog.sql =>Creates dictionary tables and views
catproc.sql =>Creates PL/SQL procedures,functions and packages necessary.

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

Now run pupbld.sql script it creates user profiles

SQL>conn system/manager

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

SQL> select name from v$database;

NAME
———
MY_DB

Leave a Reply