Chapter 11
Oracle Files

by Joe Greene

In This Chapter

•   Oracle Files and Their Purposes

•   Data Files

•   Log Files

•   Control Files

•   Initialization and Configuration Files

•   Log and Trace Files

•   Automatic Resizing of Files

•   Distribution of Data for Tuning

•   Standard File Locations

Next on the list of Oracle components are the data files associated with the database. Memory areas store data only while the computer is operational. For permanent storage of information, you need to use media such as magnetic disks. Also, while memory prices have declined, the cost of disk media is still much less than that of physical computer memory. Therefore, Oracle has been designed to balance the need for speed using physical memory and the need for storage cost efficiency using files on disk drives. This chapter discusses the various files associated with an Oracle database, their contents, and how they are used.

Oracle Files and Their Purposes

For a typical PC software product installation, you place a few dozen files in one directory or a series of subdirectories under one main directory. You know where everything that you need is and the world is wonderful. This chapter deals with the files of the Oracle database world. Here there are hundreds, perhaps thousands, of files that are scattered over a number of directories. This chapter shows the overall picture of where the files are stored and then highlights those files that the DBA usually uses.

It is not as bad as it may seem. There are an enormous number of files out there in the Oracle system. Each of the Oracle add-on packages (Oracle Forms, SQL*Net, Net8, and so forth) add many more files to that collection. However, from a DBA point of view, you can divide the many files into the following categories:

•  Oracle software. These are the files that are purchased and installed to make the system work. These files make up the vast majority of the hundreds or thousands of Oracle files on your system. The good news is that, typically, you need to concern yourself with these files only at installation time. If they do not load and link correctly, your only recourse is to call Oracle support and have them help you get things working (unless you feel like rewriting major portions of Oracle in your spare time).

•  Data files. When you first create an instance, you have only a few data files. In fact, you can have a single data file under earlier versions of Oracle (6 and below). In Oracle8, you get at least four data files by default. If you have very large databases that span many disk drives, you may have dozens of data files. These are a relatively easy resource to manage because there are database views to enable you to locate the files quickly. You typically perform size maintenance on your data files more often than on any other type of Oracle file.

•  Redo log files. You have only a handful (normally about four) of redo log files on your system. There are views to help you locate these files from within the database. These files typically need maintenance only when you are changing their size or moving them for enhancing performance.

•  Archive log files. These will be your biggest concern when operating a database in archivelog mode (which is explained in more detail later). If these files fill up their destination device, it stops all updates and inserts to the database from happening and perhaps crashes the instance. It is definitely enlightened self-interest to know where your archive logs are and to monitor their size and clean them up routinely.

•  Control files. If you are lucky, you may never bother with your control files after you create an Oracle instance. These files are used to help Oracle start up by providing configuration information and status. You alter their contents when you modify the database configuration, but Oracle takes care of this behind the scenes.

•  Initialization files. These files store the information that is needed to start the system. They contain the locations for the control files and all the tuning parameters that you set to adjust the performance of your instance. You will typically bother with these files only when you get into tuning the Oracle database.

•  Log and trace files. Many books do not emphasize these files. I was a pretty experienced DBA before I found the log file used for SQL*Net (which had grown to 1.4MB because no one knew that it was there to clean out). However, these little jewels are your keys to troubleshooting major problems such as a crash of the instance. The major Oracle processes are designed to leave a last message on the system when they encounter problems and are crashing. The system actually has processes that monitor the activity of other processes and record messages when they detect that something is wrong.

•  Audit files. Oracle enables you to audit certain events that occur in the database (see Chapter 19, “Oracle8 Administrative Requirements”). You can store the record of activities either in the database or in a file that you specify in your initialization file.

From a DBA perspective, I have just narrowed that list of hundreds or thousands of files associated with Oracle down to a list of a few dozen. When you consider that once you understand one control file or log file, you understand them all, this makes it much more manageable. The key is getting a handle on where things are and what they do for you. Then when a problem arises, you know where to go for help.