Tuesday, March 11, 2014

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.





No comments:

Post a Comment

About the Author : Anurag Pandey | Database cloud Architect | Oracle | SQL Server | PostgreSQL | AI and Data Platform Enthusiast

Anurag Pandey is a Database Architect with expertise in Oracle, PostgreSQL, SQL Server, Linux, and enterprise database platforms and specializes in database architecture, performance tuning, high availability, disaster recovery, cloud modernization, and operational excellence. With extensive experience in enterprise-scale environments, Anurag is passionate about leveraging AI, automation, and modern data platforms to improve reliability, scalability, and business outcomes. He actively contributes to technical communities through knowledge sharing, architecture guidance, and thought leadership in database and data engineering domains.

Follow more articles at

Visit Blog

PostgreSQL Process Architecture Diagram and Details

PostgreSQL Process Architecture Diagram                            +------------------+                            |    POSTMASTER    |     ...