How to Create Oracle Database in Windows Manually

Jun 20, 2012, by admin

oracleBelow are the steps engaged in manual database creation on Windows.

Steps in Database creation

Step 1: Decide on Your Instance Identifier (SID)

Step 2: Create the Initialization Parameter File

Step 3: Start the Instance.

Step 4: Issue the CREATE DATABASE Statement

Step 5: Create Additional Tablespaces

Step 6: Run Scripts to Build Data Dictionary Views

 Step 1: Decide on Your Instance Identifier (SID)

Create a new service name using comment prompt

C:/>oradim -new –sid john –intpwd nive

(For help about oradim, type oradim/? In comment prompt)

Set the sid

 Step 2: Create the Initialization Parameter File

Create init.ora file for your database

Or

Copy existing database init.ora parameter file and change the paths in init.ora file

You should change the following parameters

db_name=jooo

instance_name=john (match with you already created sid)

log_archive_dest_1=’LOCATION=D:oraclejoooarchive’

background_dump_dest=D:oraclejooobdump

core_dump_dest=D:oraclejooocdump user_dump_dest=D:oraclejoooudump

control_files= (“D:oraclejooooradatacontrol01.ctl”, “D:oraclejooooradatacontrol02.ctl”, “D:oraclejooooradatacontrol03.ctl”)

oracle database

Step 3: Start the Instance.

In comment prompt

C:>set ora_sid=john

C:>sqlplus /nolog

Start an instance without mounting a database

Sql> startup nomount pfile=’location’

 Step 4: Issue the CREATE DATABASE Statement

When you implement a CREATE DATABASE statement, Oracle performs (at least) the following operations.

Creates the data files for the database

Creates the control files for the database

Creates the redo log files for the database and establishes the ARCHIVELOG mode

Creates the SYSTEM table space and the SYSTEM rollback segment

Creates the data dictionary

Sets the character set that stores data in the database

Sets the database time zone

Mounts and opens the database for use

The general format follows:

CREATE DATABASE [database name]

[CONTROLFILE REUSE]

[LOGFILE [GROUP integer] file specification]

[MAXLOGFILES integer]

[MAXLOGMEMBERS integer]

[MAXLOGHISTORY integer]

[MAXDATAFILES integer]

[MAXINSTANCES integer]

[ARCHIVELOG|NOARCHIVELOG]

[CHARACTER SET charset]

[NATIONAL CHARACTER SET charset]

[DATAFILE filespec [autoextend]]

[DEFAULT TEMPORARY TABLESPACE tablespace filespec]

[UNDO TABLESPACE tablespace DATAFILE filespec]

[SET TIME_ZONE [time_zone_region]];

oracle-databaseExample

CREATE DATABASE jooo

MAXINSTANCES 1

MAXLOGHISTORY 1

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXDATAFILES 100

DATAFILE

‘d:oraclejooooradatasystem01.dbf’ SIZE 325M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED

LOGFILE

GROUP 1 (‘d:oraclejooooradataredo01.log’) SIZE 100M,

GROUP 2 (‘d:oraclejooooradataredo02.log’) SIZE 100M

UNDO TABLESPACE “UNDOTBS1”

DATAFILE ‘d:oraclejooooradataundotbs01.dbf’ SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED

CHARACTER SET WE8MSWIN1252

NATIONAL CHARACTER SET AL16UTF16;

 Step 5:

Create temprory table space and user table space.

Step 6: Run Scripts to Build Data Dictionary Views

Run the scripts necessary to build views, synonyms, and PL/SQL packages

@/vobs/oracle/rdbms/admin/catalog.sql

@/vobs/oracle/rdbms/admin/catproc.sql

 Script Description

CATALOG.SQL

Creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. Grants PUBLIC access to the synonyms.

CATPROC.SQL R

It runs all scripts required for or used with PL/SQL.

 OTHER OPTIONAL WORKS

Specifying the Database Time Zone

Specifying Passwords for Users SYS and SYSTEM

CREATE DATABASE rbdb1

USER SYS IDENTIFIED BY pz6r58

USER SYSTEM IDENTIFIED BY y1tz5p

Specifying FORCE LOGGING Mode

Creating a Server Parameter File

Other important packages

catexp7.sql

Create v7 style export/import views against the v8 RDBMS

catblock.sql

create views of oracle locks.

catoctk.sql

Oracle Cryptographic ToolKit.

catobtk.sql

Contains scripts needed to use the PL/SQL Obfuscation Toolkit.

caths.sql

Create Heterogeneous Services data dictionary objects.

owminst.plb

create user ovm_sys for storing metadata. This will help in database export.