Recent Posts

Tuesday, March 18, 2008

Creating Oracle 9i Database Manually

Here is the sample to create the oracle 9i database manually.

The notations

%ORACL_BASE%= the drive and directory where oracle is installed in local machine such as D:\Oracle\


%ORACL_BASE% is the folder associated with the version of oracle such as
D:\Oracle\ora92

SID id the identifier for database system say it MYDB

SpfileSID.ora is the notation for spfile associated with the database system MYDB and is actually denotes the file SPFILEMYDB.ORA in this example.


Steps:
1) run oradim utilily to create oracle agent service from command prompt.
eg oradim - new -sid sidname -intpwd password -maxusers no_of _users -startmode a
2) run orapwd utility to create password file in the default %ORACLE_HOME%\DATABASE folder
3) add folder named oradata and SID in %ORACLE_BASE% directory.
4) add folder named bdump, udump,cdump,pfile,create folder inside %ORACLE_HOME%\admin\SID directory
5) prepare initialisation file initSID.ora OR SPFILESID.ora IN %ORACLE_HOME%\DATABASE direcotry
6) Using Oracle Managed Files set
db_create_file_dest='%ORACLE_BASE%\oradata\SID'
db_create_online_log_dest_n='%ORACLE_BASE%\oradata\SID'
replace n with integer starting 1 upto 5 destinations
7) startup database in nomount stage from prompt
startup nomount
8) run the script below:


#########DATABASE CREATION USING DIRECTORY PATH NAMES
CREATE DATABASE database_name
logfile
group 1 ('%ORACLE_BASE%\oradata\SID\redo01.dbf') size 10m,
group 2 ('%ORACLE_BASE%\oradata\SID\redo02.dbf') size 10m,
group 3 ('%ORACLE_BASE%\oradata\SID\redo03.dbf') size 10m
maxlogfiles 10
maxlogmembers 3
maxloghistory 1
maxdatafiles 100
maxinstances 1
datafile '%ORACLE_BASE%\oradata\SID\system01.dbf' size 500m
undo tablespace UNDOTBS1
datafile '%ORACLE_BASE%\oradata\SID\undotbs.dbf' size 200m
autoextend on next 5120k maxsize unlimited
default temporary tablespace temp
tempfile %ORACLE_BASE%\oradata\SID\temp01.dbf' size 200m
autoextend on next 5120k maxsize 500m
character set US7ASCII
national character set AL16UTF16
set TIME_ZONE='America/New_York';

#########OMF MANAGED DATABASE CREATION

CREATE DATABASE database_name
logfile
group 1 size 10m,
group 2 size 10m,
group 3 size 10m
maxlogfiles 10
maxlogmembers 3
maxloghistory 1
maxdatafiles 100
maxinstances 1
datafile size 500m
undo tablespace UNDOTBS1
datafile size 200m
autoextend on next 5120k maxsize unlimited
default temporary tablespace temp
tempfile size 200m
autoextend on next 5120k maxsize 500m
character set US7ASCII
national character set AL16UTF16
set TIME_ZONE='America/New_York';

9) run script catalog.sql and catproc.sql as a sys user from %ORACLE_HOME\rdms\admin directory
10) run script pupbld.sql as a system user from %ORACLE_HOME\sqlplus\admin directory

This completes the database manual database creation.

Related Posts by Categories




No comments:

Post a Comment