Oracle Database Creation

You can create a database in two ways:

1) Use the Oracle installer to generate a default database

Steps: create the Oracle user, directories, and run "orainst" install

program.

2) Use a script to manually create the database for better control

Steps: create the Oracle user, directories, create database scripts,

and initialization parameter files.



For the create database script, use the CREATE DATABASE command and

then issue CREATE TABLESPACE, CREATE ROLLBACK, CREATE USER, and so on.



A sample script is below:

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

connect internal

startup nomount pfile=/home/oracle/admin/PROD/pfile/initPROD_0.ora

create database "PROD"

maxinstances 8

maxlogfiles 32

character set "WE8ISO8859P9"

datafile

'/export/home6/oradata/PROD/system01.dbf' size 40M

logfile

'/export/home6/oradata/PROD/redoPROD01.log' size 8M,

'/export/home6/oradata/PROD/redoPROD02.log' size 8M,

'/export/home6/oradata/PROD/redoPROD03.log' size 8M;



REM # install data dictionary views:

@/home/oracle/product/7.3.4/rdbms/admin/catalog.sql



REM * Create additional rollback segment in SYSTEM before creating tablespace.

REM *

create rollback segment r0 tablespace system

storage (initial 16k next 16k minextents 2 maxextents 20);



REM * Use ALTER ROLLBACK SEGMENT ONLINE to put r0 online without shutting

REM * down and restarting the database.

REM *

alter rollback segment r0 online;

REM * 1 rollback segments for every 4 concurrent xactions.

REM * No more than 50 rollback segments.

REM * All rollback segments the same size.

REM * Between 2 and 4 homogeneously-sized extents per rollback segment.

REM * Attempt to keep rollback segments to 4 extents.

REM *

create tablespace rbs datafile

'/export/home6/oradata/PROD/rbs01.dbf' size 100M

default storage (

initial 2M

next 2M

pctincrease 0

minextents 2

);



REM * Create a tablespace for temporary segments.

REM * Temporary tablespace configuration guidelines:

REM * Initial and next extent sizes = k * SORT_AREA_SIZE, k in {1,2,3,...}.

REM *

create tablespace temp datafile

'/export/home6/oradata/PROD/temp01.dbf' size 50M



default storage (

initial 256k

next 256k

pctincrease 0

);



REM * Create a tablespace for database tools.

REM *

create tablespace tools datafile

'/export/home6/oradata/PROD/tools01.dbf' size 15M;



REM * Create a tablespace for miscellaneous database user activity.

REM *

create tablespace users datafile

'/export/home6/oradata/PROD/users01.dbf' size 10M;





REM * Create rollback segments.

REM *

create rollback segment r01 tablespace rbs;

create rollback segment r02 tablespace rbs;

create rollback segment r03 tablespace rbs;

create rollback segment r04 tablespace rbs;



REM * Use ALTER ROLLBACK SEGMENT ONLINE to put rollback segments online

REM * without shutting down and restarting the database. Only put one

REM * of the rollback segments online at this time so that it will always

REM * be the one used. When the user shuts down the database and starts

REM * it up with initSID.ora, all four will be brought online.

REM *

alter rollback segment r01 online;

alter rollback segment r02 online;

alter rollback segment r03 online;





REM * Since we've created and brought online 4 more rollback segments,

REM * we no longer need the second rollback segment in the SYSTEM tablespace.

alter rollback segment r0 offline;

drop rollback segment r0;



REM * Alter SYS and SYSTEM users.

REM *

alter user sys temporary tablespace temp;

alter user system default tablespace tools temporary tablespace temp;



REM * Run catproc as internal...

@$ORACLE_HOME/rdbms/admin/catproc.sql



REM * Run dbmsutil as internal...

@$ORACLE_HOME/rdbms/admin/dbmsutil.sql



REM * For each DBA user, run DBA synonyms SQL script. Don't forget that EACH

REM * DBA USER created in the future needs dba_syn.sql run from its account.

REM *

connect system/manager

@$ORACLE_HOME/rdbms/admin/catdbsyn.sql

@$ORACLE_HOME/sqlplus/admin/pupbld.sql