Creating Oracle 11g Database Manually –Step by Step Instruction.

Creating Database Manually –Step by Step Instruction.

Today, I’ll show you how we can create a Database without Database Configuration Creating Database ManuallyAssistant(DBAC).We’re going to create the database manually,useing Create Database statement. Sometime, we are not able to use GUI in order to create a database. For this, we should know syntax of database creation from SQL*Plus.

This is one of the most basic tasks for Oracle DBA,it also helps us to understand how Oracle Database works. Creating Database in 11g is very simple. In 10g you need to create additional directories bdump, cdump, udump instead of diagnostic dump directory.

To Create Database Manually, Follow Below Steps:

Step 1: create Directory Structure for CRD file. 

]$ mkdir –p /disk1/oradata/prod

Step 2: create Parameter File in $ORACLE_HOME/dbs location: 

]$ cd $ORACLE_HOME/dbs

dbs]$ vi initprod.ora

     db_name =prod

     control_files=’/disk1/oradata/prod/control01.ctl’,’/disk1/oradata/prod/control02’

     diagnostic_dest=/disk1/oradata/prod

     undo_tablespace=undotbs1

     undo_retention=9000

     undo_management=auto

     compatible=11.2.0

     :wq

Step 3: Prepare Create Database Script:

dbs]$ cd

   ~]$ vi cr8db.sql

     create database prod datafile ‘/disk1/oradata/prod/system.dbf’ size 200m autoextend on

     sysaux datafile ‘/disk1/oradata/sysaux.dbf’ size 200m autoextend on

     undo tablespace undotbs1 datafile ‘/disk1/oradata/prod/undotbs1.dbf’ size 100m

     default tablespace users datafile ‘/disk1/oradata/prod/users.dbf’ size 200m autoextend on

     default temporary tablespace temp tempfile ‘/disk1/oradata/prod/temp.tmp’ size 100m

     logfile

     group 1(‘/disk1/oradata/prod/redo01.log’ ) size 4m,

     group 2(‘/disk1/oradata/prod/redo02.log’) size 4m;

     :wq

---------------------------(OR)----------------------------------

~]$ vi cr8db.sql

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

Step 4: Set the Oracle Environment and SID of Database in the Operation System:

~]$ . .bash_profile

~]$ export ORACLE_SID=prod

Step 5: Start the Database in nomount state:

~]$ sqlplus / as sysdba

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 3: 

SQL>@cr8db.sql

Step 7: Execute the Catalog.sql ,Catproc.sql, pupbld.sql Scripts:

Catalog.sql= Create dictionary tables and views

Catproc.sql= Create PL/SQL procedures,Functions and Packages necessary.

Pupbld.sql= Create default user and default Profile.

So our database is create.Now just run the catalog.sql, catproc.sql and pupbld.sql scripts.

We will Find catalog.sql,catproc.sql and pupbld.sql in the location of catalog.sql and catproc.sql is  $ORACLE_HOME/rdbms/admin and path of pupbld.sql is $ORACLE_HOME/sqlplus/admin is the path of pupbld.sql.

Know Run those Script individual or make a one file to run at a time. So here we will see both the mathods.

      1)      Individual method:

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

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

SQL> conn system/manager

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

2) Making a Script of 3 file and run at time to save you time: 

~]$ vi run.sql

@$ORACLE_HOME/rdbms/admin/catalog.sql

@$ORACLE_HOME/rdbms/admin/catproc.sql

conn system/manager

@$ORACLE_HOME/sqlplus/admin/pupbld.sql

:wq

SQL> @run.sql

SQL> sho user

SQL> conn / as sysdba

Step 8: Verify the Dictionary views Created.

SQL> select name from v$datbase;

SQL> select instance_name from v$instance;

SQL> select  name from v$tablespace;

SQL> select name from v$datafile;

SQL> select name from v$controlfile;

SQL> select tablespace_name ,status from dba_tablespaces;

SQL> select tablespace_name,file_name from dba_data_files;

SQL> select username,default_tablespace from dba_users;

  

Thanks

comments please!!!