Create Database Manually – Step by Step Instruction Posted by MOHAMMED ABDUL HAMEED on 5:18 AM

Step 1: Create Necessary Directory Structure.

oradata , udump , bdump , cdump

Step 2 : Export Environment Variables

$ export ORACLE_SID=PROD
$ export ORACLE_HOME=/u01/home/app/product/11.2.0/dbhome_1
$ export PATH=$ORACLE_HOME/bin:$PATH:

Step 3 : Create Parameter file

Vi Initprod.ora  (Parameter File)

db_name=prod         --- defines database name 
Instance_name=prod   --- defines instance name
Control_files=’/u01/prod/control01.ctl’         --- name and location of controlfile
Db_block_size = 8192                 --- to set db block size in bytes (8k)
Undo_management=’auto’                         ---undo segment management values ‘manual’ or ‘auto’
Undo_tablespace=undotbs1   ---undo tablespace name
Undo_retention=900   --- undo database retention periold
Compatible=11.2.0         --- oracle software version
memory_max_target=400m   --- to set upper limit of sga
memory_target=300m                                 ---to set actual size of sga component   
                                                                      + PGA Aggregate (except log buffers)
Log_buffer = 100                                         --- to set log buffer size
Workarea_size_policy = auto                     --- auto sizing of the pga within aggregate pga
diagnostic_dest=’/u01/prod/diag’                    --- location of  alert log and trace files

Step 4 : Export database & Startup in NOMOUNT STAGE

$ export ORACLE_SID=PROD
$ sqlplus / as sysdba
SQL> startup nomount pfile='/u01/app/oracle/admin/pfile/initprod.ora';

Step 5 : Create the Control File 

Vi dbcreate_script.sql

CREATE DATABASE prod
   USER SYS IDENTIFIED BY manager
   USER SYSTEM IDENTIFIED BY manager
   LOGFILE GROUP 1 ('/u01/hameed/prod/redo01.log') SIZE 50M,
                 GROUP 2 ('/u01/hameed/prod/redo02.log') SIZE 50M,
               GROUP 3 ('/u01/hameed/prod/redo03.log’) SIZE 50M
   MAXLOGFILES 5
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 50
   MAXDATAFILES 100
   MAXINSTANCES 1
   DATAFILE '/u01/hameed/prod/system01.dbf'  SIZE 100M autoextend on
   SYSAUX DATAFILE '/u01/hameed/prod/sysaux01.dbf'  SIZE 100M  autoextend on
   DEFAULT TABLESPACE users datafile ‘/u01/hameed/prod/users01.dbf’ size 100m   autoextend on
 DEFAULT TEMPORARY TABLESPACE temp TEMPFILE /u01/hameed/prod/temp01.dbf'  SIZE 50m
UNDO TABLESPACE undotbs1 DATAFILE '/u01/hameed/prod/undotbs01.dbf'   SIZE 200M;

Step 6 : Run the control script at database level

SQL>@/u01/app/oracle/admin/scripts/dbcreate_script.sql

** Check the creation of Control , Redo , Data files.

Step 7 : Run this minimal scripts for creating database

@$ORACLE_HOME/rdbms/admin/catalog.sql -->  script to create data Dictionary tables.

@$ORACLE_HOME/rdbms/admin/catproc.sql  --> script to create procedure and packages

Connect system/manager
@$ORACLE_HOME/sqlplus/admin/pupbld.sql  --> script to create users Profile

Step 8 :Shutdown the instance and startup the database. Your database is ready for use!

SQL>shutdown immediate;
SQL>startup;