How to Create Oracle Database in Windows Manually
Jun 20, 2012, by admin
Below 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”)
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]];
Example
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.
