Tip Of Month - Feb 1999

Creating Database in UNIX

Let's discuss creating a new Oracle7 database on UNIX platform using the scripts supplied by Oracle Corporation. These scripts are normally located under $ORACLE_HOME/rdbms/install/rdbms directory, files named crdb.orc and crdb2.orc. Here I will give you a modified and more readable version of these script files and we will also discuss the minimum required parameters for creating a database.

Environment Setup

Make sure your UNIX environment has the following variable set up correctly.

1. ORACLE_SID : Name of the database instance to be created.
2. ORACLE_HOME : Location of the Oracle software directory installation.

You might also have the following variables set up.

ORACLE_BASE=/ora_home/app/oracle
SH_LIB_PATH=$ORACLE_HOME/lib
ORA_NLS=$ORACLE_HOME/ocommon/nls/admin/data
TNS_ADMIN=$ORACLE_HOME/network/admin
LD_LIBRARY_PATH=$ORACLE_HOME/lib
PATH=$ORACLE_HOME/bin:$PATH

There is a note from Rickard Widlund on setting environment:

For Oracle7 before 7.3: set ORA_NLS
For Oracle 7.3: set ORA_NLS32
for Oracle8: set ORA_NLS33
Its a very common error not to set the correct ORA_NLSxx variable and therefore end up with a 7bit us ascii database.

Thanks Rick for the info!

Initialization Parameters Setup

Many DBAs have the initialization parameters split in two files.

1. configSID.ora : Contains the parameters that do not change after the db creation
2. initSID.ora : Contains parameters that may be adjusted for performance.

These files are generally kept under $ORACLE_BASE/admin/SID/pfile directory. It is good to have a link on initSID.ora made to $ORACLE_HOME/dbs directory, because, by default oracle looks for the initSID.ora file under this directory. If you have such a link, you need not specify the parameter file name while starting up the database. For example, your sid name is TESTDB, create a link using the following command.

$ cd $ORACLE_HOME/dbs
$ ln -s $ORACLE_BASE/admin/TESTDB/pfile/initTESTDB.ora ./initTESTDB.ora

You can also have the configSID.ora and initSID.ora files created under $ORACLE_HOME/dbs directory.

Make sure you have the following parameters defined in the configSID.ora

control_files        = (/ora0/oradata/TESTDB/ctrl1TESTDB.ctl,
                        /ora1/oradata/TESTDB/ctrl2TESTDB.ctl,
                        /ora2/oradata/TESTDB/ctrl3TESTDB.ctl)
background_dump_dest = /oradump/TESTDB/bdump
core_dump_dest       = /oradump/TESTDB/cdump
user_dump_dest       = /oradump/TESTDB/udump
db_block_size        = 4096
db_name              = TESTDB

Define the following parameters in the initSID.ora file.

ifile              = {full_path}/configTESTDB.ora
compatible         = 7.3.4
db_block_buffers   = 1280
log_buffer         = 512000
max_dump_file_size = 20480
processes          = 100
rollback_segments  = (r01,r02,r03,r04)
shared_pool_size   = 10485760
sort_area_size     = 5242880

You may have other parameters in the initSID.ora file depending on your requirement. It is better to create the database with minimal parameters and then to add parameters or to increase the size of SGA, etc. For explanation of these parameters, please refer to Oracle Server Reference Guide.

Create the database

Once the environment variables and initialization variables are set up, you're ready to create the database. Make sure you have enough free space available to create the files. Run the script files after invoking server manager (svrmgrl). You should be logged into the server with an account which belongs to the Oracle dba group.

Script1(crdb1TESTDB.sql) - Step 1: Create database

connect internal
startup nomount pfile=$ORACLE_HOME/dbs/initTESTDB.ora

create database "TESTDB"
   controlfile    reuse
   maxinstances   8
   maxlogfiles    32
   maxlogmembers  4
   maxdatafiles   9000
   character set  "US7ASCII"
datafile
   '/ora0/oradata/TESTDB/system01.dbf' size 100M
logfile
   group 1 ('/ora3/oradata/TESTDB/redo0101.log') size 5M,
   group 2 ('/ora4/oradata/TESTDB/redo0201.log') size 5M,
   group 3 ('/ora3/oradata/TESTDB/redo0301.log') size 5M,
   group 4 ('/ora4/oradata/TESTDB/redo0302.log') size 5M;

Script 2 (crdb2TESTDB.sql) - Step 1: Install data dictionary views

@${ORACLE_HOME}/rdbms/admin/catalog.sql

Script 2 (crdb2TESTDB.sql) - Step 2: Create additional rollback segment

create rollback segment rbs0 tablespace system
       storage (initial 52k next 52k minextents 2 maxextents 40);
alter rollback segment rbs0 online;

Script 2 (crdb2TESTDB.sql) - Step 3: Create tablespace for rollback segments

create tablespace rbs datafile
       '/ora2/oradata/TESTDB/rbs01.dbf' size 500M
       default storage (initial 512k next 512k pctincrease 0
                        minextents 2 maxextents 505 );

Script 2 (crdb2TESTDB.sql) - Step 4: Create tablespace for temporary segments

create tablespace temp datafile
       '/ora1/oradata/TESTDB/temp01.dbf' size 400m
       default storage (initial 10M next 10M
                        pctincrease 0 maxextents 505);
alter tablespace temp temporary;

Script 2 (crdb2TESTDB.sql) - Step 5: Optional - Create tablespace for miscellaneous tools storage

create tablespace tools datafile
       '/ora1/oradata/TESTDB/tools01.dbf' size 150m
       default storage (initial 1M next 1M
                        pctincrease 0 maxextents 500);

Script 2 (crdb2TESTDB.sql) - Step 6: Optional - Create tablespace for miscellaneous user activity

create tablespace users datafile
       '/ora2/oradata/TESTDB/user01.dbf' size 100m
       default storage (initial 512k next 512k
                        pctincrease 0 maxextents 500);

Script 2 (crdb2TESTDB.sql) - Step 7: Create additional rollback segments

create rollback segment r01 tablespace rbs ;
create rollback segment r02 tablespace rbs ;
create rollback segment r03 tablespace rbs ;
create rollback segment r04 tablespace rbs ;

alter rollback segment r01 online;

Script 2 (crdb2TESTDB.sql) - Step 8: Since we have created and made RBS r01 online, we no longer need the second RBS we created at the beginning of this script. So we drop that RBS.

alter rollback segment rbs0 offline;
drop rollback segment rbs0;

Script 2 (crdb2TESTDB.sql) - Step 9: For SYSTEM DBA user, create DBA synonyms

connect system/manager
@${ORACLE_HOME}/rdbms/admin/catdbsyn.sql

Script 2 (crdb2TESTDB.sql) - Step 10: Run scripts for procedural option

@${ORACLE_HOME}/rdbms/admin/catproc.sql

Script 2 (crdb2TESTDB.sql) - Step 11: Create user profile tables

connect system/manager
@${ORACLE_HOME}/sqlplus/admin/pupbld.sql

Create additional tablespaces and user / schema based on your application requirement. Be sure to change the SYS and SYSTEM passwords. The default SYS password is CHANGE_ON_INSTALL and SYSTEM password is MANAGER.

Login to sqlplus, shutdown and startup the database once the creation is complete. Backup the database. To turn archive log on, refer to the documentation Tip of the Month - October 1998

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP,
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. © The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.