Script to Create a New Oracle Database

DB Scripts

If you need to create a database in Oracle 10g but doesn¥t want to use the DBCA feature or are unable to use it, you could simply follow the following steps to get it created using some scripts.

Before you run the script you must have an Oracle Database Installed and an instance already set up.

To set up the instance just have its PFILE created on $ORACLE_HOME/dbs/initSID.ora

1- Set you $ORACLE_SID

export ORACLE_SID=oracle_sid_name

2-Start the Instance in NOMOUNT mode

In order to create the database the instance should be started in NOMOUNT mode as follows:

sqlplus / as sysdba

startup nomount

3-Run Create Database Script

With the instance started run the script below

create database orcl
logfile group 1 (‘/path/orcl/redolog/log1/redo1.log’) size 50M,
group 2 (‘/path/orcl/redolog/log2/redo2.log’) size 50M,
group 3 (‘/path/orcl/redolog/log3/redo3.log’) size 100M
character set WE8ISO8859P1
national character set utf8
datafile ‘/path/orcl/data/orcl_sys0.dbf’ size 300M autoextend off extent management local
sysaux datafile ‘/path/orcl/data/sysaux.dbf’ size 100M autoextend off
undo tablespace undotbs0 datafile ‘/pathorcl/data/orcl_und0.dbf’ size 50M
default temporary tablespace temp tempfile ‘/path/orcl/data/orcl_tmp0.dbf’ size 60M;

LOGFILE ñ defines the paths and how many log groups will be created and its size;

CHARACTER SET ñ defines the type of character set according to the region the database is and the characters used. Is used for CHAR, VARCHAR2, CLOB columns;

NATIONAL CHARACTER SET ñ also defines the type of character and is used for NCHAR, NVARCHAR2, NCLOB columns;

Fist datafile ñ defines the path whete tablespace SYSTEM will be created, autoextend was disabled for better DBA management;

SYSAUX DATAFILE ñ defines the path and size for tablespace SYSAUX;

UNDO TABLESPACE ñ defines the undo tablespace name and DATAFILE its path;

DEFAULT TEMPORARY TABLESPACE ñ defines the name for the temporary tablespace and TEMPFILE its path.

Obs.: The script above aims the creation of a simple database. There are several others options and parameters to set up.

4-Run Catalog and Catproc

Since database is created you should run the scripts catalog and catproc (both provided by oracle). Their path is $ORACLE_HOME/rdbms/admin. Just run as follows: