Step 01
Decide on the location of

Three control files (minimum) on different disks/ drives/volumes

Three groups of redo log files, with each group having two members (minimum) on different disks/ drives/volumes Create a file structure for the data files

Step 02
Creating database manually without auto undo management:

1. a. Decide on a unique instance name on NT/2000 machines up to 8.0.x cannot have a name with more than 4 characters and 8.1.x cannot have more than 8 characters

b. Database character set. Modifying the character set at a later date lands in many issues.

2. Set the operating system variables

(UNIX)

ORACLE_HOME

ORACLE_SID

ORACLE_BASE

ORA_NLS33

PATH

(NT)

ORADIM80 -NEW -SID u16

-INTPWD password -STARTMODE auto

-PFILE ORACLE_HOMEDATABASEinitu16.ora

You must decide the SID, Password, and

Create init<sid_name>.ora file

SET ORACLE_SID=<sid_name>

Make <sid_name> the current SID

3. Prepare the parameter file

use init.ora as a parameter file template

db_name =  Eight characters or fewer that identify the database

control_files = the location of three control files

DB_BLOCK_SIZE = Determines the database block size (can not    change after the database has been created)

4. Create a password file

5. Start the instance

STARTUP NOMOUNT pfile=init.ora

6. Create the database

MANUALLY FROM Server Manager

CREATE DATABASE “<sid_name>”

MAXLOGFILES 6

MAXLOGMEMBERS 6

MAXDATAFILES 30

MAXLOGHISTORY 100

ARCHIVELOG

LOGFILE

GROUP 1

‘E:DATA sid GROUP1log1a.rdo’ SIZE 200K,

‘E:DATA sid GROUP1log1b.rdo’ SIZE 200K

GROUP 2

‘E:DATA sid GROUP2log2a.rdo’ SIZE 200K,

‘E:DATA sid GROUP2log2b.rdo’ SIZE 200K

GROUP 3

‘E:DATA sid GROUP3log3a.rdo’ SIZE 200K,

‘E:DATA sid GROUP3log3b.rdo’ SIZE 200K

DATAFILE

‘E:DATA sid DATAFILESsystem01.dbf’

size 30M

CHARACTER SET WE8ISO8859P1;

Database

select name,created,log_mode from v$database;

Thread

select status, instance from v$thread;

Datafiles

select name from v$datafile;

Logfiles

select member from v$logfile;

Controlfiles

select name from v$controlfile;

Verify System Creation

select file_name from dba_data_files

where tablespace_name=’SYSTEM’;

Look at the database users

select username, created from dba_users;

7. Run scripts to generate the data dictionary and        accomplish postcreation steps.

Create the data dictionary

CATALOG.SQL

prostcreation objects

CATPROC.SQL

  1. to know/determine the archive mode of the database 

            svrmgrl> archive log list   

                  or  issue the following statement 

                        select log_mode from v$database;                     

also issue the following statements to know whether the archive process is enabled or not 

select archiver from v$instance;

The output determines whether the process is started or not.

Creating a Database using Instance Manager

Step 1.

Start Instance Manager

Press the New Button

Input a SID (4 characters) ->abcd

Step 2.

Enter the DBA Authorization password and authenication

Step 3.

Press the Advanced Button

* The Database Name must be the same as the Database Name

in the initabcd.ora file

* Enter the max logfile value and the max group member value

* Design the location of your datafiles, logfiles,

archive files, and control files

* Change the logfile location and name to meet your design

* Set the location of the Parameter file

Defining Parameters in the Parameter File

db_name = {myDBName}#database name using

to identify the database

db_files = 30

#maximum allowable

number of database files

#control file list

(Created by the Instance Manager)

control_files = (D:orantDATABASEctl1{SID}orcl.ora,

D:orantDATABASEctl2{SID}orcl.ora,

D:orantDATABASEctl3{SID}orcl.ora)

#database will be compatible with

software of this version

compatible = 7.3.0.0.0

#db_file_multiblock_read_count= number of database

blocks to read with each I/O.

#db_file_multiblock_read_count =  8 # INITIAL

# db_file_multiblock_read_count = 8 # SMALL

db_file_multiblock_read_count = 16 # MEDIUM

# db_file_multiblock_read_count = 32 # LARGE

# db_block_buffers = number of database blocks

cached in memory

db_block_buffers tells the oracle kernel the

size of the area that stores database read from the disk

#db_block_buffers =  200  # INITIAL

# db_block_buffers = 200  # SMALL

db_block_buffers = 550   # MEDIUM

# db_block_buffers = 3200  # LARGE

Size in bytes of the shared pool

#shared_pool_size =  6500000  # INITIAL

# shared_pool_size = 3500000  # SMALL

shared_pool_size = 6000000   # MEDIUM

# shared_pool_size = 9000000  # LARGE

Number of redo blocks for checkpoint

threshold

log_checkpoint_interval = 10000

Maximum number of user processes

#processes =  50  # INITIAL

# processes = 50  # SMALL

processes = 100  # MEDIUM

# processes = 200  # LARGE

DML locks – one for each table

modified in a transaction

#dml_locks =  100  # INITIAL

# dml_locks = 100  # SMALL

dml_locks = 200   # MEDIUM

# dml_locks = 500  # LARGE

Redo circular buffer size

#log_buffer =  8192      # INITIAL

# log_buffer = 8192      # SMALL

log_buffer = 32768     # MEDIUM

# log_buffer = 163840    # LARGE

Number of sequence cache entries

#sequence_cache_entries =  10   # INITIAL

# sequence_cache_entries = 10   # SMALL

sequence_cache_entries = 30    # MEDIUM

# sequence_cache_entries = 100  # LARGE

#sequence_cache_hash_buckets =  10     # INITIAL

# sequence_cache_hash_buckets = 10     # SMALL

sequence_cache_hash_buckets = 23      # MEDIUM

# sequence_cache_hash_buckets = 89     # LARGE

# audit_trail = true

# if you want auditing

# timed_statistics = true

# if you want timed statistics

max_dump_file_size = 10240

# limit trace file size to 5 Meg each

Start the Archiver Process

log_archive_start = true      # if you want automatic archiving

LOG_ARCHIVE_DEST = E:{db_name}ARCHIVE

#location of the archive directory

# define directories to store trace and alert files

background_dump_dest=%RDBMS73%trace

user_dump_dest=%RDBMS73%trace

Size of database block in bytes. Db_block_size can not

be changed after database creation

db_block_size must be a multiple of 512K

db_block_size = 8192

Number of job queue processes to start

snapshot_refresh_processes = 1

Password file usage

remote_login_passwordfile = shared

Enable text searching

text_enable = true

Step 4. Create your database

Once the instance has been started, it is an idle instance.

Steps to starting the database

From a DOS Prompt

set ORACLE_SID=abcd

set LOCAL =2:abcd

svrmgrl (NT)

connect internal/password

startup pfile=initabcd.ora

Creating a Database with AUTO-UNDO management 

Create database mydb

Controlfile reuse

Logfile group 1( ‘d:oradatamydbredomydb_redo_01a.log’,

              ‘e:oradatamydbredomydb_redo_01b.log’) size 10m,

         group 2( ‘e:oradatamydbredomydb_redo_02a.log’,

              ‘f:oradatamydbredomydb_redo_02b.log’) size 10m,

group 1( ‘f:oradatamydbredomydb_redo_03a.log’,

              ‘d:oradatamydbredomydb_redo_03b.log’) size 10m

maxinstances  1

maxlogfiles   5

maxloghistory 100

maxdatafiles 100

archivelog

datafile ‘d:oradata mydbsystemmydb_system_01.dbf’ size 100M autoextend on next 20M maxsize unlimited,

default temporary tablespace temp

tempfile ‘f:oradata mydbtempmydb_temp_01.dbf’ size 100M

undo tablespace undo_tbsp datafile

‘e:oradata mydbrbsmydb_undo_01.dbf’ size 100M autoextend off;

Creating a Database with OMF (Oracle Managed File system) 

Step 01 

The following parameters are to be included in the init.ora parameter file of the database.

— Defining the destination for the redo log files

db_create_online_dest_1 = d:oradatamydbredo

db_create_online_dest_2 = e:oradatamydbredo

db_create_online_dest_3 = f:oradatamydbredo

— Defining the destination for the data files

db_create_file_dest= d:oradatamydbdata

— defining the undo auto management

undo_management=auto

undo_tablespace=undo_tbsp
Step 02
 Create database mydb

Datafile size 200M

Logfile group 1 size 20M, group 2 size 20M, group 3 20M

Default temporary tablespace temp tempfile size 100M

Undo tablespace undo_tbsp datafile size 100M

Maxlogfiles=5

Maxlogmembers=5

Maxdatafiles=200

Noarchivelog;

This is not advised to be used for a production environment. Multiple datafile destinations are not supported and your ability to balance the IO is crippled.

This is recommended for the development environment to enable to developers to play with database objects easily.