Sunday, June 28, 2009

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=

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

# Cursors and Library Cache

# Diagnostics and Statistics

# Control File Configuration

# Archive

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

# Miscellaneous

# Distributed, Replication and Snapshot

# Network Registration

# Pools

# Processes and Sessions

# Redo Log and Recovery

# Resource Manager

# Sort, Hash Joins, Bitmap Indexes

# Automatic Undo Management
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
connect sys as sysdba
password: password

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,

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.


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.