Manually Creating Oracle Database

hello all,

As we have learn how to install oracle on RHEL-5, one might be interested in creating oracle database. There are two procedure through which you can create oracle database if you have oracle software installed.

For creating oracle database on windows, first install oracle binaries (software) from CD to your system. then follow these steps to create database manually. you can create database with the help of 'Database Configuration Assistant' (DBCA) a tool provided by oracle to create database and installed when you install oracle software and it requires no extra license.

Before you start creating database please make sure that following are done:-

1. Make yourself very familiar with oracle initialization parameter.
2. Consider oracle managed file for creating your database.
3. Plan you operating system and partitions.
4. Make sure sufficient disk space exist in your system.
5. Make sure you have sufficient memory to start oracle instance.
6. Decide your table,index structure for your oracle database.

Once these basic needs are fulfilled proceed to oracle database specific requirements.

A. Decide your oracle instance identifier.
B. Authenticate your operating system account with administrator privileges.
C. Create the password file using 'orpwd' utility on command prompt to create password file for your database.

c:\> orapwd file= password= entries=
where,

file =complete path and name of password file
password = your password for sysdba
entries = maximum number of DBA's

example:- orapwd file=d:\oracle\product\database\orclpwd.ora password=password entries=10

D. create initialization parameter file (pfile), if you can't create one then copy from sample. It should be in form init$oracle_sid.ora. It is generally located in $ORACLE_HOME/database. Below is the sample init.ora file

# Cache and I/O
DB_BLOCK_SIZE=4096
DB_CACHE_SIZE=20971520

# Cursors and Library Cache
CURSOR_SHARING=SIMILAR
OPEN_CURSORS=300

# Diagnostics and Statistics
BACKGROUND_DUMP_DEST=D:\oracle\admin\mynewdb\bdump
CORE_DUMP_DEST=D;\oracle\admin\mynewdb\cdump
TIMED_STATISTICS=TRUE
USER_DUMP_DEST=D:\oracle\admin\mynewdb\udump

# Control File Configuration
CONTROL_FILES=("D:\oracle\oradata\mynewdb\control01.ctl",
               "D:\oracle\oradata\mynewdb\control02.ctl",
               "D:\oracle\oradata\mynewdb\control03.ctl")

# Archive
LOG_ARCHIVE_DEST_1='LOCATION=D;\oracle\oradata\mynewdb\archive'
LOG_ARCHIVE_FORMAT=%t_%s.dbf
LOG_ARCHIVE_START=TRUE

# Shared Server
# Uncomment and use first DISPATCHES parameter below when your listener is
# configured for SSL
# (listener.ora and sqlnet.ora)
# DISPATCHERS = "(PROTOCOL=TCPS)(SER=MODOSE)",
#               "(PROTOCOL=TCPS)(PRE=oracle.aurora.server)"
DISPATCHERS="(PROTOCOL=TCP)(SER=MODOSE)",
            "(PROTOCOL=TCP)(PRE=oracle.aurora.server)",
             (PROTOCOL=TCP)

# Miscellaneous
COMPATIBLE=9.2.0
DB_NAME=mynewdb

# Distributed, Replication and Snapshot
DB_DOMAIN=us.oracle.com
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

# Network Registration
INSTANCE_NAME=mynewdb

# Pools
JAVA_POOL_SIZE=31457280
LARGE_POOL_SIZE=1048576
SHARED_POOL_SIZE=52428800

# Processes and Sessions
PROCESSES=150

# Redo Log and Recovery
FAST_START_MTTR_TARGET=300

# Resource Manager
RESOURCE_MANAGER_PLAN=SYSTEM_PLAN

# Sort, Hash Joins, Bitmap Indexes
SORT_AREA_SIZE=524288

# Automatic Undo Management
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=undotbs

E. Check whether all directories mentioned in the init.ora file exists.
F. Create oracle instance if on windows with following command. We assume that our SID is "orcl"

c:\>oradim -new -sid orcl -intpwd password -startmode a

Note:- if you have created password file then this command will overrite that password file. so better create password file with this option.

G. Now connect to the instance we have created in previous step

c:\> set oracle_sid=orcl
c:\>sqlplus/nolog
connect sys as sysdba
password: password
sql>

H. Now you are on sql prompt. start the oracle instance in nomount state.

sql> startup nomount;

I. Now run the create database script as follows;-

create database orcl
user sys identified by password
user system identified by password
logfile group1('d:\oracle\oradata\redo01.log') size 100m,
group2('d:\oracle\oradata\redo02.log') size 100m,
group3('d:\oracle\oradata\redo03.log') size 100m,
maxlogfiles 5
maxlogmembers 6
maxloghistory 1
maxdatafiles 100
maxinstances 1
character set US7ASCII
national character set AL16UTF16
datafile 'd:\oracle\oradata\system01.dbf' size 1024m reuse,
'd:\oracle\oradata\users01.dbf' size 2048m reuse,
'd:\oracle\oradata\tools01.dbf' size 300m reuse,
'd:\oracle\oradata\index01.dbf' size 500m reuse,
extent management local
dafault temporary tablespace temp
datafile 'd:\oracle\oradata\temp01.dbf' size 1024m reuse,
undo tablespace unotbs
datafile 'd:\oracle\oradata\undo01.dbf' size 1024m reuse,
archivelog;

With above script database is created with:-
1.database nam orcl
2.sys and system password is password.
3. maxlog files specify the limit for log group.
4.maxlog members specify the limit for maximum members to be created in one group.
5.maxinstance specify that onle one instance can have this database open and mounted.
6. three log group having one member each.
7. four datafile along with system and users.
8.temporary tablespac temp
9.undo tablespace undotbs.

J.Once you have done with this database creatin task create data dictiobary views by running ctalog and catproc.sql scripts by opening database.

sql>@d:\oracle\rdbms\admin\catalog.sql;
sql>@d:\oracle\rdbms\admin\catproc.sql;

K.Then create server parameter file by command

sql> create spfile from pfile='d:\oracle\admin\orcl\pfile\initorcl.ora';

the spfile will be created in dfault location 'd:\oracle\database\spfileorcl.ora' if you do not specify location for spfile.

sql> shut immediate;
sql> startup;

this time your database will be opened with spfile you have creatd in last step.

This way you database is created manually...

Note:- change paths accordingly.