HOWTO: A Small 9i Database
This thing is HUUUUUGE...
Yeah, it's a big beast. But if you need an Oracle database for really simple things, you can't or don't want to use 9i Lite, and the only available hardware is an old box or a cramped laptop, then this small guide/howto is for you. I'm assuming you're a database newbie, since I'll be explaining some real basic Oracle stuff here.
Warning: this is not an installation guide! There are enough of those elsewhere. This is more of a configuration guide: I'm assuming you got through the installation and now the Database Configuration Assistant (dbca) pops up and demands that you tell her what to do.
Tested database version: 9.2.0.1.0, on RedHat Linux 8.0 on a Dell Latitude CSx 500Mhz with 256 Mb of memory and a 12 Gb harddisk. If you think this is isn't cramped or old in any way, then think about how you might want to run JDeveloper as well (which eats up about 100 Mb of memory) which runs in KDE - which offers some utilities you just CAN'T live without. You want to browse the web or see the results of some JSP you've just built, so you're also running Mozilla. Together with an untuned database, the laptop just keeps on swapping and swapping, taking 20 seconds to display for instance the first page of SlashDot.
Step 1 to 3 of the Database Configuration Assistant
In step 1, select Create a database and click Next. In step 2, choose New Database and click Next again. In step 3, choose a global database name and a SID. The SID is a unique identifier for the database running on this machine and the global database name is a unique global (duh) identifier for the database. The best way to make it unique is to append the hostname after the sid. If you don't know your hostname, just use the SID.
If you start typing in the first field, the second field is automatically filled as well. I entered orcl in the first field; this fills the second field with the same value. Type a nice, short name and click Next.
Step 4
This screen displays two tabs. Since I unchecked some options when starting the installation, some features are grey. All the better, I'd say! I unselected all the other features, since I only need the database for running some JSPs and stuff like that.
When unselecting, the database assistant could ask whether you want to delete the associated tablespace. Press Yes here, it saves you memory and harddisk space. Don't worry though, nothing is really deleted -- it's just not included when the database is created later on. And should you want to test a feature later on, you can use the dbca again to add it. And if this isn't possible, you can always just add a whole new database.
For the curious: Oracle Spatial is an option for managing geographical information and Oracle Ultra Search offers the capability of indexing other databases and websites. I'm not familiar with the other options (Label Security, Data Mining and OLAP). Clicking help will turn up some marketing talk.
If you're done deselecting things in the first tab, you'll probably also want to click on the Standard database features... button. With my installation, four options were shown here.
I deselected them all, too. Note that some database features are dependent on the JVM, so when you deselect it, other options are automatically deselected as well. If you're done, click OK and then click next to go to step five.
Again, for the curious: JVM stands for Java Virtual Machine. The Oracle database can run its own virtual machine, inside the memory space of the database server. Oracle already had PL/SQL for that, but now Java is available as well. Intermedia gives you an object-oriented API to deal with multimedia stuff like pictures and movies. For instance, you can insert a JPEG into the database and then if you pull it out of the database, you can resize it on-the-fly. The obvious use for this would be an image gallery or something like that. Oracle Text can create an index on binary files, so when you insert files into the database like Word files, PDFs and stuff like that, you can search through them with (almost) plain ole SQL. Click the help button if you want to know about the XML thing.
Step 5
Assuming the database will only be used trivially, it's probably best to select Dedicated Server Mode and clicking Next.
Step 6
This is where it gets interesting. Step 6 shows up and this screen shows five tabs. The first tab is important for the memory usage. Choose the custom option and adjust the values; if you change anything here, it's immediately added up. We want to fill in some small values, but not too small because then later on the creation of the database will fail. The following table shows the values I used and also shows a short explanation.
| Shared Pool | 12 Mb | SQl and PL/SQL needs to be parsed and turned into bytecode (think of it as semi machine language). This parsing is expensive in CPU time so it's cached in the shared pool. |
| Buffer Cache | 4 Mb | Reading from disk is expensive, so if something is read from disk, it's cached in a piece of memory called the buffer cache. |
| Java Pool | 0 Mb | This is the shared pool, but for Java instead of SQL and PL/SQL. When you deselected the JVM, this should be zero of course. |
| Large Pool | 0 Mb | This should be set when you have database servers working in parallel. That't not what I had in mind when. Set to zero. |
| PGA | 10 Mb | The minimum size for 9i is 10 Mb, otherwise the database won't start up. This value is for keeping PL/SQL packages in memory. |
Detail of the Memory tab, showing values for shared pool, buffer cache, java pool, large pool and PGA
Picture of the DB Sizing tab, showing a block size of 8 Kb and a sort area size of 512 Kb
Go to the second tab called Character Sets. The dbca will whine about not meeting the recommended values. Just click No. On the second tab, I chose UTF8 for both values. On the third tab, leave the block size to 8; changing might give you an error later on. I have set the other value to 512 Kb, read the comment if you want to know what it's about.
I didn't touch the fourth tab, but you can if you want. For the interested: here are three parameters here, the first is the init.ora file. This is a textfile in which the settings you are making right now are saved -- which brings us to the second parameter, the spfile.
The spfile (server parameter file) is actually init.ora reborn, because in previous versions you had to open up a shell, edit the init.ora file with your favourite editor and restart the database. Now there is a new syntax with which you can change values while running the database, on-the-fly, just using SQL*Plus. Of course, they have to be saved outside the database in a file and this file is called the spfile. The syntax for changing stuff goes like alter system set name=value scope=both. The name and value-pairs can be found with a select * from v$spparameter.
The fifth tab, Archive, is disabled by default. With this option, no data is lost if the hardware crashes or loses power. Since it requires an extra process which will cost us precious memory, we'll leave it disabled.
We have set everything that's mentioned on the tabs; click on the button All Initialization Parameters... and click on the column header Name to find things easier. Question: do you need to schedule database things? If not, you can set the value of job_queue_processes to 0 (zero). That way, a process called CJQ0 is not necessary when the database is started. About seven megs of memory are free to be used for other things! Whew!
Step 7
This step is all about harddisk space. If you have enough of that (something like 2 or more gigs), just click next. Otherwise, bear with me... This step displays a tree with five items on the first level. Before we continue, I'll give a short explanation of each:
- Controlfiles
- The database needs to know some things to run, like the name of the database, in which files the data is stored, log information et cetera. Those are saved in a file called the control file. There are three controlfiles which are exact copies, so you can spread them among different disks; if one disk fails, the controlfile won't be lost.
- Datafiles
- The name says it all; in datafiles, the data is kept. That data can be table definitions, the table data itself, stored PL/SQL procedures, Java code, views, sequences, you name it. Oracle uses mulitple data files so they can be spread over several disks. That way, I/O is spread.
- Tablespaces
- A tablespace is a logical layer above the level of datafiles. So one tablespace can consist of several data files, but not the other way around. There is a SYSTEM tablespace in which the standard packages, tables, views, etc. are kept, and a tablespace TEMP which is used for temporary data. Note the UNDO tablespace, about which is told more in the paragraph below.
- Rollback Segments
- This one is empty. It's a pre-9i method which is now called manual undo mode and the new method is surprisingly called automatic undo mode. Instead of rollback segments, a special UNDO tablespace is used. One of the methods must be enabled for keeping old values when you change data in the database. Otherwise the ROLLBACK statement wouldn't work.
- Redo Log Groups
- A redo log contains all changes in the data of the database. When a disk fails or something like that, the server can still get the database in the original state by running the statements in the redo logs. There are multiple copies so you can spread them over disks.
With that out of the way, we will resize the datafiles and the redo log files a bit. Since I need a real small database, I have entered the following values (all in megabytes): 1 for indx01.dbf, 200 for system01.dbf, 5 for temp01.dbf, 5 for tools01.dbf, 10 for undotbs01.dbf and 80 for users01.dbf. The sizes can be changed in two places: by clicking on the separate datafiles and then changing the value of the file size, or by clicking on a tablespace and then change the value in the list of datafiles for that tablespace. While the values for the system and undo datafiles look like they could be smaller, it's no use because after the installation they become about this size. Might as well give the dbca more explicit instructions.
Below the Redo Log Groups folder, I clicked on every number and changed the size from 100 to 1 Mb. Nowhere did I touch the directories of the datafiles and redo log files, but you can if you want.
Step 8
We're through! I just checked the option Create Database. If something goes wrong and the message is not quite descriptive, look at the file
$ORACLE_BASE/admin/[sidname]/bdump/alert_[sidname].log
Of course, instead of [sidname] enter the SID you entered in step three.
The creation will take an hour or so, longer if you're on a slow machine or if you don't have enough memory and/or swapspace. If you have any problems, search around using Google, check the Oracle Forums or use Oracle's Metalink if you can.
Checking database memory usage
When the installation is done, the database is started. Login as user sys and type show sga to display memory usage:
[oracle@bkuik-nl orcl]$ sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Nov 29 15:50:47 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> show sga Total System Global Area 26283992 bytes
Fixed Size 450520 bytes
Variable Size 20971520 bytes
Database Buffers 4194304 bytes
Redo Buffers 667648 bytes
SQL>
Checking total memory usage
If you want to see how much memory is being consumed, type something like:
ps -o rss,vsz,cmd --sort=rss -u oracle
The rss column means Resident Set Size i.e. actually occupied virtual memory, the vsz tells us what the total virtual memory size is (including stuff that's swapped away). Check the manpage or go here for more information on ps.
[oracle@bkuik-nl orcl]$ ps -o rss,vsz,cmd --sort=rss -u oracle
RSS VSZ CMD
624 2540 ps -o rss,vsz,cmd --sort=rss -u oracle
1304 4160 -bash
1336 4172 -bash
7000 113148 ora_ckpt_orcl
7412 113700 ora_pmon_orcl
7468 117772 ora_lgwr_orcl
7488 113760 ora_dbw0_orcl
9652 113104 ora_reco_orcl
15404 113124 ora_smon_orcl
19636 113884 oracleorcl
(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
[oracle@bkuik-nl orcl]$And if you quickly want to add up those numbers, do something like:
ps -o rss -u oracle --noheader | awk '{sum=sum+$1;} END {print(sum)}'Congratulations!
Go grab a beer, coffee or whatever you prefer.
Thanks go to Jaco Verheul of Oracle Education for some useful suggestions!