We?ve already looked at the responsibilities of the DBA
or administrative user; now we need to answer what are the normal
responsibilities of the other user types? According to the Oracle8 Server
Administrator's Guide, they are:
Design and develop database applications.
Design the database structure for an application.
Estimate storage requirements for an application.
Specify modifications of the database structures for an application.
Keep the database administrator informed of required changes.
Tune the application (not the database!) during development.
Establish an application's security requirements during development.
Application User's Responsibilities
Enter, modify, and delete data, where permitted.
Generate reports of data.
All Oracle databases have three types of users:
administrative, developmental, and application. As their names imply,
administrative users administer and maintain the database itself; developmental
users develop applications and systems; and application users use the developed
applications and systems.
These three types of users have different needs. The
space and disk needs of a developer are usually greater than those of an
application user. A developer system may get by with a smaller SGA because,
generally speaking, developers will work with a subset of all the data expected,
while a production database user will need a larger SGA because the data set is
much larger. Administrative users usually have the same quotas as a
developmental user, but their privileges are greater.
The number of each of these types of users will tell you
about required SGA sizing, disk resource requirements, and required system
What Is the Number and Placement of Control Files?
Control files are probably the smallest and most
important files in the Oracle system. They contain the following data:
* Names of the database and redo log files
* Timestamp of database creation
* Begin/end of rollback segments
* Checkpoint information
* Current redo log file sequence number
This data is critical for database operation, and at
least one control file is required for database startup. There are methods for
rebuilding a control file, but it is much easier to maintain--or rather have
Oracle maintain--multiple copies of the control file.
Oracle recommends two copies on separate disk resources.
For OFA compliance, three are required. Obviously, if they are on the same
platter or are placed in the same stripe set, the same disaster can waste all of
them; therefore, they should be placed on different physical disk arrays if
possible. More than three copies is a bit paranoid, but if it makes you feel
safer, have as many as you wish; only one usable, current file is required for
What Is the Number and Placement of Redo Logs?
Oracle requires at least two groups of one redo log. If
you are archiving, three are suggested. In a number of installations, up to six
or more have been defined. If you do a lot of update activity and have numerous
users, more than six may be required. When a log fills, the next one in the
queue is opened, and the previously active log is marked for archive (if you
have archiving enabled). The logs are archived on a first-in, first-out basis,
so, depending on the speed that the log groups can be written to disk or tape,
more than one log group may be waiting to be archived. One redo log group is
used at a time, with multiple users writing into it at the same time. The size
of the redo logs in a group depends on one critical piece of data: How much data
can you afford to lose on a system crash?
You see, the smaller the log group size, the more often
it is written to disk and the less data (time-wise) is lost. The larger the log
group size, the less often it is written to disk and the more data (time-wise)
is lost. For instance, if your log groups are filling every 10 minutes, then you
may lose 10 minutes? worth of data should the disk(s) crash that holds that redo
log group's files. It has been demonstrated on an active system that a 100-MB
redo log group may only last a few seconds. In an inactive or read-only-type
situation, a 100-MB redo log may last for hours. It is all dependent on how the
database is being used and the size of the redo log group. Remember, a group of
three 100-MB redo logs is actually treated as only a single 100-MB redo log (the
other two files are mirrors). If you mirror redo logs by placing the group
members on separate disks (not just on separate file systems; be sure it is
separate physical disks), then your ability to recover from a disk array or
controller crash increases manyfold.
You have to balance the needs for restoration and
minimal data loss against time to recover data. Obviously, if you have archiving
happening every minute and your normal workday is eight hours, you will have 480
logs written to disk daily. Over a five-day workweek, this turns into 2,400
files. If you have to restore from a crash or other disaster, you may have to
apply all of these to your last backup to bring the database current to the time
of the crash. In one case, a DBA had to apply 9,000-plus files to recover his
system because he hadn't looked at how often his redo logs were archiving.
Needless to say, he pays more attention now. The minimum size for redo log
groups is 50 KB. By default the Oracle9i DBCA creates 100 megabyte redo log
In Chapter 12 on database tuning, we will discuss how to
determine if you have a sufficient number of redo log groups and how to optimize
your archive process.