Oracle is a complicated beast to administer; why make our lives more difficult?  A lot of people ask that question when they look at a command line interface.  My response is always the same.  If you're trying to use the database, interfacing with it through an application, you're an end user.  So the more transparent it is the better.  For these folks the command line interface is obviously not ideal.  For those of us who are supposed to be the mechanics, the troubleshooters, the ones whom everyone else can go to when they have a problem, you want to get down and dirty, so you know what's going on.  The more layers of interface you have between you and your jet engine, the less you're going to understand how it works.  You do want to understand things don't you?

Why Create A Database Manually?

Creating a database follows along in this same way.  Sure there is a great Oracle supplied GUI tool which does it, and does a pretty good job at it too.  But what if it fails?  What if you get some warning or error, then what?  Do you want to know what it's doing behind the scenes?  Do you want to have further insight into the magic Oracle performs during database creation?  Manual database creation is an excellent way to illustrate all of this.

When you create a database manually, you issue commands at the sqlplus prompt.  Normally you add these commands to a sql script, which you then call from sqlplus.  Assuming all of the paths and options are correct, and the volumes specified exist, and do not reach capacity during database creation, it should run through smoothly.  Your script also serves as documentation of what options you used to create the database.  Furthermore, if you develop an application which you deploy on Oracle, your application may provide a database creation script, which does things your way.  This is an excellent way to provide a push-button or script with prompts for the administrator to use to install with.

When Should I Use The Manual Method?

Obviously when you're first learning Oracle, you should certainly be using the manual method.  Once you understand it inside and out, you might consider using the GUI to simplify your work.  If you want to control the naming conventions of files, however, I'd still tend to stick to the manual method.  Also when new versions of Oracle come out, be sure to check out what new options have been added to the create database syntax.  

What about if you're creating a whole bunch of databases which have similar characteristics.  Here again, the manual method appeals to us because it can help simplify our workload.

How Do I Do It?

Let's call our database DBJ.  At minimum, you would set your ORACLE_SID environment variable to DBJ, edit initDBJ.ora and add:

db_name=DBJ

Then fire up sqlplus and issue:

SQL> startup nomount;
SQL> create database;

Oracle will pick some defaults for all of the parameters it needs such as where to put datafiles, controlfiles, how much memory to allocate, and then startup without mounting any files.  The create database statement will ask Oracle to create a database with a minimal system and sysaux tablespace and so on.

In the real world, however we'd want to specify many more options.  Here's a real world example of what a create database script would look like in Oracle:

connect / as sysdba;
startup nomount;
CREATE DATABASE "dbj"
DATAFILE 
  '/u01/oradata/sysdbj01.dbf' size 500M
SYSAUX DATAFILE
  '/u01/oradata/auxdbj01.dbf' size 500M
UNDO TABLESPACE dbjundo DATAFILE
  '/u01/oradata/unddbj01.dbf' size 250M
LOGFILE
  '/u02/oradata/rdodbj01.dbf' size 10M,
  '/u02/oradata/rdodbj02.dbf' size 10M
CHARACTER SET "WE8ISO8859P1"
NATIONAL CHARACTER SET "UTF8"
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE mytemp TEMPFILE 
  '/u01/oradata/tmpdbj01.dbf' SIZE 500M
NOARCHIVELOG
MAXDATAFILES 1000
MAXLOGFILES 10;
# create the rest of the data dictionary
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql

What's Happening At Each Stage?

When we issue "startup nomount" we're telling Oracle to do something different than when we normally startup.  What's different?  Well we tell Oracle to startup all of it's processes, and allocate memory for the system global area.  In essence, we tell Oracle to start an instance, but not a database.  Why not a database too?  Because we don't have one at this stage.  So, we have no database, which is effectively a collection of datafiles, to mount yet.

When we issue the create database command, Oracle creates a system, sysaux, undo, and temp tablespaces, and sets the character set for the database.  It's allocating datafiles with header information that specifies that we want extent management local, and archive or noarchivelog mode as well.  Once it's done all of this, it executes a magical file called sql.bsq.  I recommend you viewing this file; it should prove interesting, and educational.  It's Oracle bootstrapping code, all there for us to see and understand.  The sql.bsq file can be found in the $ORACLE_HOME/rdbms/admin directory.  Note that this file changes from release to release, so it might be illustrative to check for changes periodically.

After this is run, the Oracle data dictionary is basically built.  catalog.sql and catproc.sql merely build useful views on top of the real data dictionary, and various stored procedures, packages, and functions that every Oracle database requires.

Conclusions

Far from being the drudgery that some may associate with the command line, I find it fun and exciting.  Peering into the Oracle database creation process really pulls back the curtain on the inner workings of the database, allowing us to understand all of the levers, and pieces that make up a running Oracle database.  In the process, we learn more about init.ora, the bootstrapping scripts Oracle relies on, how the data dictionary is created, and the important differences between an instance and a database.

» See All Articles by Columnist Sean Hull