How to create a Database in Oracle 9i manually

Note: Instructions are for Windows XP

1. Create a SID for your new Database

set ORACLE_SID={SID};

Note: Where {SID} is any unique SID created by you.

2. Create suitable database directories within oracle directory.

make folders udump,cdump,bdump within admin/{SID} (optional for logs)
make folder {SID} within oradata

3. Create a initialization parameter file and name it as
init{SID}.ora. Drop this file in

%ORACLE_HOME%/database folder. Contents could look like this.
(Settings and paths will

vary)

# 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=/oracle/admin/{SID}/bdump
CORE_DUMP_DEST=/oracle/admin/{SID}/cdump
TIMED_STATISTICS=TRUE
USER_DUMP_DEST=/oracle/admin/{SID}/udump

# Control File Configuration
CONTROL_FILES=(“/oracle/oradata/{SID}/control01.ctl”,
“/oracle/oradata/{SID}/control02.ctl”,
” /oracle/oradata/{SID}/control03.ctl”)

# Archive
LOG_ARCHIVE_DEST=’/oracle/oradata/{SID}/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.SGiopServer)”
DISPATCHERS =”(PROTOCOL=TCP)(SER=MODOSE)”,
“(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)”,
“(PROTOCOL=TCP)”

# Miscellaneous
COMPATIBLE=9.2.0
DB_NAME={SID}

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

# Network Registration
INSTANCE_NAME={SID}

# 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

4. Create a password file with name PWD{SID}.ora and place in
%ORACLE_HOME%/database folder.

Create the password file using the following command:

orapwd file={path\filename} password=oracle

5. Create an instance of this database

oradim -new – -sid {sid}

6. Start SQLPLUS using the following command

sqlplus /nolog

7. Connect to instance of database

connect / as sysdba

8. Startup the instance without mounting

startup nomount

9. Run create database SQL statement

CREATE DATABASE {sid}
USER SYS IDENTIFIED BY pz6r58
USER SYSTEM IDENTIFIED BY y1tz5p
LOGFILE GROUP 1 (‘/oracle/oradata/{sid}/redo01.log’) SIZE 100M,
GROUP 2 (‘/oracle/oradata/{sid}/redo02.log’) SIZE 100M,
GROUP 3 (‘/oracle/oradata/{sid}/redo03.log’) SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE ‘/oracle/oradata/{sid}/system01.dbf’ SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE ‘/oracle/oradata/{sid}/temp01.dbf’
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE ‘/oracle/oradata/{sid}/undotbs01.dbf’
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;

About these ads