Sort Areas

Finally, sort areas can greatly improve the performance of one of the most common database operations—sorting. Very few people want to review data records in the order that they were input (or in Oracle’s case, the order that Oracle chooses to store the data). The data for most applications has some form of ordering to it (you sort a list of names alphabetically, sort dates, and so forth). Using the logic that memory access is much faster than disk access, Oracle likes to store a list of values to be sorted in memory and perform the sort there. That is the purpose of the sort area.

Disk Storage

After that glowing discussion about the wonders of memory, it is time to address the somewhat slower, but inexpensive and nonvolatile storage mechanism found in most Oracle instances—the magnetic (hard) disk drive. These little jewels enable us to build those multigigabyte (or in some cases terabyte) store houses of corporate wisdom. This section provides a brief introduction to the types of files that Oracle employs in its battles with information overload. Now, you need to look at the seven types of files used by the Oracle RDBMS (see Figure 2.3):

•  Data files

•  Online redo log files

•  Archive log files

•  Initialization files

•  Control files

•  SGA definition files

•  Oracle processing log and error (trace) files

Figure 2.3.  The basic types of Oracle data files.

Data Files

The easiest place to start is with the data files. These contain exactly what you would expect to find in a relational database management system—a series of records of data arranged in tables. There are a number of other types of database objects (indexes, views, and so forth) that you will find in these files, but for now, it is enough to understand that this is where you will find your data. One interesting point to note is that Oracle enables you to store software within these database files (in the form of packages and procedures).

Log Files

The next set of files is the online redo log files. The concept of these files is simple—record every transaction made to the database in a file separate from the main data files. These can be used to recover all changes made to the database in the event that a data file is damaged. Oracle uses several of these files so that when it gets done writing to the last file in the series, it begins overwriting to the first online redo log file. An obvious question is, how can you recover from a damaged data file if you overwrite one of your online redo log files?

The answer lies in the archive log files. Their function is simple. When you finish writing to an online redo log file, a background process makes a copy of that redo log file to a separate file that is given a unique sequential number. This file can be placed on a magnetic disk drive or a magnetic tape. Then, if you want to recover a data file that has been destroyed, you get a copy from the archive log tapes that you have been religiously making and apply all transactions in the redo and archive log files that have occurred since that backup was made.

Supporting Files

Now that data storage has been taken care of, it is time to investigate some of the supporting files that Oracle uses. The initialization files are the equivalent of Microsoft DOS’s autoexec.bat and config.sys. There are two files in the Oracle initialization file set, although you could get by with one or even more than two. These files are called init.ora and config.ora. The exact name of a file for a particular instance has the ID (SID) of your particular Oracle instance in it. For example, if your Oracle instance has an ID of test, your files are called inittest.ora and configtest.ora. With that out of the way, the purpose of these control files is to specify the following startup parameters to Oracle:

•  Values for all those tuneable parameters that Oracle uses to improve performance

•  Locations of the control files and archive log files

•  Locations for some of the log and error files

Control Files

The next set of files that helps Oracle keep track of what is going on is the control files set. These files keep track of where the data files are and also record the number of the latest transaction applied to the database. This latter feature helps Oracle during recovery to know how many transactions need to be applied to come up to date. These files are stored in a binary format that you cannot read using standard operating system text file commands (such as more on UNIX or type on VMS).

SGA Definition File

Another binary supporting file is the SGA definition file. You will have one of these for each of the Oracle instances that you create. This file tells Oracle some details about creating the SGA on startup. Most of the DBAs that I have worked with are either not aware or are only vaguely aware of these files. This is a good sign for you new DBAs, because it means that you do not need to worry about these files.

Log and Error Files

The final type of Oracle file that you will deal with is the log and error files. Oracle is designed to be a production, business-grade database. Therefore, when problems occur, you need to determine what is wrong quickly and get it fixed. To support this need, Oracle records all major database activities (inserts, updates, and so on.) in a set of log files. In addition, when Oracle’s monitoring processes detect a problem, they write as much relevant data about the problem as they can find to a series of error logging files (called trace files in Oracle). In this way, the DBA can look at a few files and determine what happened.

An interesting sidelight while on the subject of Oracle files is the task of finding all these files on your system. Many of the DBAs that I have come across do not know where all these files reside. This makes troubleshooting much more difficult. You will learn how to find all these files on your systems later in this book.

Interfacing with the DBMS

The Oracle DBMS software is designed to handle all of the details of storing and retrieving data for your applications. This differs from the old world of flat files where your applications had complete control over the storage and retrieval processes. You now have to learn the interface requirements that Oracle has developed to control your interaction with their data storage application.