CREATE DATABASE MANUALLY IN ORACLE 10g -STEP BY STEP

Step by step create database manually  in oracle 10g

bash-3.2$ echo $ORACLE_HOME

/opt/oracle/db01/app/oracle/product/10.2.0

bash-3.2$

bash-3.2$

bash-3.2$ echo $ORACLE_BASE

/opt/oracle/db01/app/oracle

 //Create following directory

bash-3.2$cd /opt/oracle/db01/app/oracle/admin

bash-3.2$  mkdir adump 

bash-3.2$  mkdir  bdump 

bash-3.2$  mkdir  cdump 

bash-3.2$  mkdir  dpdump 

bash-3.2$  mkdir  pfile  

bash-3.2$  mkdir udump

//Create following directory

bash-3.2$cd /opt/oracle/db01/app/oracle/oradata/

bash-3.2$ mkdir test

//In Pfile change create init.ora file as per requirmnet

##############################################################################

# Copyright (c) 1991, 2001, 2002 by Oracle Corporation

##############################################################################

###########################################

# Cache and I/O

###########################################

db_block_size=8192

db_file_multiblock_read_count=16

###########################################

# Cursors and Library Cache

###########################################

open_cursors=300

###########################################

# Database Identification

###########################################

db_domain=""

db_name=test

###########################################

# Diagnostics and Statistics

###########################################

background_dump_dest=/opt/oracle/db01/app/oracle/admin/test/bdump

core_dump_dest=/opt/oracle/db01/app/oracle/admin/test/cdump

user_dump_dest=/opt/oracle/db01/app/oracle/admin/test/udump

###########################################

# File Configuration

###########################################

control_files=("/opt/oracle/db01/app/oracle/oradata/test/control01.ctl", "/opt/oracle/db01/app/oracle/oradata/test/control02.ctl", "/opt/oracle/db01/app/oracle/oradata/test/control03.ctl")

###########################################

# Job Queues

###########################################

job_queue_processes=10

###########################################

# Miscellaneous

###########################################

compatible=10.2.0.1.0

###########################################

# Processes and Sessions

###########################################

processes=300

sessions=335

###########################################

# SGA Memory

###########################################

sga_target=3812622336

###########################################

# Security and Auditing

###########################################

audit_file_dest=/opt/oracle/db01/app/oracle/admin/test/adump

remote_login_passwordfile=EXCLUSIVE

###########################################

# Shared Server

###########################################

dispatchers="(PROTOCOL=TCP) (SERVICE=orclXDB)"

###########################################

# Sort, Hash Joins, Bitmap Indexes

###########################################

pga_aggregate_target=1270874112

###########################################

# System Managed Undo and Rollback Segments

###########################################

undo_management=AUTO

undo_tablespace=UNDOTBS1

bash-3.2$ export ORACLE_SID=test

bash-3.2$ sqlplus "/as sysdba"

SQL>startup nomount pfile='/opt/oracle/db01/app/oracle/admin/test/pfile/init.ora';

SQL> create spfile from pfile='/opt/oracle/db01/app/oracle/admin/test/pfile/init.ora';

////////////execute database  script

SQL>create database test

logfile group 1 ('/opt/oracle/db01/app/oracle/oradata/test/redo1.log') size 100M,

            group 2 ('/opt/oracle/db01/app/oracle/oradata/test/redo2.log') size 100M,

            group 3 ('/opt/oracle/db01/app/oracle/oradata/test/redo3.log') size 100M

character set WE8ISO8859P1

national character set utf8

datafile '/opt/oracle/db01/app/oracle/oradata/test/system.dbf' size 500M autoextend on next 10M maxsize unlimited extent management local

sysaux datafile '/opt/oracle/db01/app/oracle/oradata/test/sysaux.dbf' size 100M autoextend on next 10M maxsize unlimited

undo tablespace undotbs1 datafile '/opt/oracle/db01/app/oracle/oradata/test/undotbs1.dbf' size 100M

default temporary tablespace temp tempfile '/opt/oracle/db01/app/oracle/oradata/test/temp01.dbf' size 100M;

//for creating users
bash-3.2$ sqlplus "/as sysdba"

SQL>alter user sys identified by test;
SQL>alter user system identified by manager;

bash-3.2$ connect /as sysdba

SQL>@$ORACLE_HOME/rdbms/admin/catalog.sql

SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql

bash-3.2$ connect system/manager

SQL>@/apps/oracledb/product/10.2.0/orahome/sqlplus/admin/pupbld.sql

SQL>@/apps/oracledb/product/10.2.0/orahome/sqlplus/admin/helpus.sql

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.It is located in ORACLE_HOME/rdbms/admin/catalog.sql.

CATPROC.SQL: Runs all scripts required for or used with PL/SQL.It is loacted in ORACLE_HOME/rdbms/admin/catproc.sql.