4. Creating a database

4.1 Overview

Hopefully you followed the advice from the previous section and didn't create a database.

For most people, I can probably outline the process in a couple of words: "Run 'dbassist'." Unless this is the first time you've ever run Oracle, none of the questions should really phase you.

For completeness, I'll document what I did but I'd best say what I was aiming for first. Bottom line: this is neither a production system nor a 'serious' (i.e., several people, full time) development box. I installed 8i to play around and see what was new or different from 8 and older versions.

This means that when 'dbassist' offered an easy option I took it. And when it suggested using a different disk, or at least a different partition, I declined. My $ORACLE_HOME is /home/oracle. All the data files and software are in there, all on one partition.

4.2 Step-by-step guide

1. Bring up a command prompt and type:

   dbassist
   

2. My machine tells me that "JNLS Exception: oracle.ntpg.jnls.JNLSException. Unable to find any National Character Sets." According to Oracles 8i Patch FAQ, this is a known problem (884001) and can safely be ignored.
3. Select the "Create a database" radio button and press "Next"
4. There are two options: Typical and Custom. If you knew exactly what you were doing you probably wouldn't be reading this and could comfortably select Custom. I'm not going to cover that. Instead I'll assume you select "Typical" and press "Next"
5. Next it asks whether you want to copy the database from your CD or to create the data files. Whenever I tried the first option, Oracle couldn't find my CD player (you just installed from it!). So I recommend choosing the second option. It's not difficult, it probably just takes longer
6. It's probably safe to select 'Hybrid' when it asks you what environment the database will operate in
7. Now it asks you how many users will be using your database at any given time. I put five.
8. Next it asks you what products you want to install in your new database. Again, you know what you want better than me!
9. Oracle needs a "Global Database Name" and a "SID" now. The database name is like a fully qualified domain name (but different). If you're the Oracle guru you'll know what to put, if not your organisation might have some conventions. I called mine 'dev1' (both the SID and database name).
10. Now, do you want to create the database 'now' or should you let it save the information to a shell script? With 128Mb of RAM I found the former option painful. I created the shell script, quit out of X and anything else using a lot of memory and then ran the script. Much more snappy.
11. I didn't notice this in any of the documentation, but your database won't work properly without it! The database that 'dbassist' creates is fine, but by default the user rollback segments are left off-line. (Read: non-system users can't perform any operation that requires transactions.) Type:

    cd $ORACLE_HOME/dbs
    

    You now need to edit a file called "init<SID>.ora" ("initdev1.ora" in my case). About half-way down the file is a commented out line looking something like this:

    # rollback_segments = (r01, r02, r03, r04)
    

    Uncomment this line (remove the hash), save the file and you're done.
12. This is a kind of meta-step. You have a database and you should be able to start it up, but you probably don't know what any of the system passwords are! There are two that you need to know. The first is the SYSTEM password. This defaults to 'MANAGER'. (It seems to be conventional to put Oracle passwords in uppercase. In fact passwords are not case sensitive.) I recommend you change it straight away by typing password at the SQL*Plus prompt. (For people expecting an ALTER USER command, this is new to the version of SQL*Plus supplied with 8i.) The other password that you need to know is the one for SYS. It defaults to 'CHANGE_ON_INSTALL' and you should do exactly what it says!
13. Final step. This one gets rid of the annoying 'no profile' warnings you get when you log into SQL*Plus. Log into SQL*Plus as user 'system' (sqlplus system/<password>). Then type:

    @?/sqlplus/admin/pupbld.sql
    

    The question-mark is an alias for the $ORACLE_HOME directory.
14. This is an optional step used to define the default editor for SQL*Plus (it defaults to ed so you do!). Open $ORACLE_HOME/sqlplus/admin/glogin.sql in your favourite editor and add define_editor=<editor name> to the end.

And that's it. You should now have an operational database that you can log into using SQL*Plus.

4.3 Questions and answers

Is it really that easy?

Yes and no. If you're just playing around, building a database for yourself to learn the new features of 8i, then 'yes.' The database the above instructions will build is complete and will work fine.

However, if you know anything about Oracle, you will quickly realise that the default configuration is appallingly bad. If you're making a serious, production system I recommend you use the "Custom" option.

Even for my toy system I did some tweaking. I increased the sizes of most of the table-spaces and changed them so that they didn't grow automatically (I hate software when it tries to be too clever).

Is it really necessary to put all the files on different disks?

No and it will work fine if you don't, but I don't recommend putting all your files on the same disk nevertheless.

Spreading the files over a number of disks, even it's just the data files on one and the rollback segments on another, will have a significant performance advantage. Read an Oracle DBA book if you need further information.

I can't start dbassist

Caused by several zero-length files in the initial installation. Following the patch procedure will fix this problem.

I get "ORA-01034: ORACLE not available"

To cut a long story short, your $ORACLE_SID is probably set incorrectly or not at all. Make sure it's set to the same value you gave 'dbassist' and that it's value is exported (i.e., export ORACLE_SID in any Bourne compatible shell).

I get "ORA-01012: Not logged in"

This is a very common error, and there are a number of different things that cause it.

Firstly you'll want to make sure that you're not creating a Shared Server configuration (sometimes known as MTS). Create a database using Dedicated Server and convert it later.

If that's not it, check your NLS_LANG environment variable. The easiest option is to unset it. If you really want to use it, make sure that you have it exactly right. Make sure you don't transpose any '1's (one's) for 'l's (the twelfth letter of the alphabet)!

Can data-files only be 1Gb in size?

'dbassist' won't let you create a datafile bigger than 1Gb. I believe this to be a bug as Linux has no problem with files up to 2Gb.

Note that does not limit the size of your database to 1Gb or less. A database is made up of many table-spaces which can be made up of many data-files. Talk to your friendly DBA for more information.

Can I use raw files?

Recent versions of the Linux kernel allow applications to directly access the disks. Oracle is able to use this facility and can (sometimes) increase its performance.

Technically the answer is 'yes,' you can use raw files. But realistically the answer is 'no.' The performance improvement you'll get probably isn't worth the administrative overhead.