Introduction
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.
