Oracle and Disk Usage

How Many Is Enough?

Some applications will do fine with two disks or volumes. This setup is not great, mind you, but the system will function. Other applications, such as large complex systems involving numerous indexes, data tables, and tablespaces, may require dozens. To reduce disk contention and maximize database reliability, it is suggested that the DBA utilize OFA procedures to place database files. The next subsections cover file placement for some basic disk array layouts.

One Area (Surely You Jest) 

It is foolish and very dangerous to even consider using a single area to hold all Oracle files for other than DOS, OS/2, WINDOWS NT, or MAC-based single-user databases. Unless you have the latest and greatest EMC array with multiple gigs of storage, hot replacement of disks, and RAID5, a single large area can lead to problems. A single disk failure or crash in a stripe set could completely destroy your system. Since there are no file placement options with one area, let's go on to the next configuration.

Two Areas (Just Barely Adequate) 

At least with two areas you can achieve separation of data and indexes and can separate redo logs from archive log files. This gives you some redundancies in recovery options. The file placement is shown here.

• AREA1. Oracle executables, index datafiles, redo logs, export files, a copy of the control file

• AREA2. Data-datafiles, rollback segment datafile, temporary user datafiles, archive log files, a copy of the control file

As you can see, an attempt is made to spread I/O between the two areas. Indexes and data are on separate controllers, as are redo logs and rollback segments. Additional recoverability in case of disk crash is given by having exports on one drive and archive log files on the other. While infinitely better than only one area, having only two areas is still an extremely vulnerable condition and is not recommended. Again, with the most up-to-date storage areas with large caches, extreme striping, and hot replacement of disks, one or two areas is workable.

Three Areas (Nearly There) 

With three areas available we improve the chances that the database can be recovered from disk crashes. We can also reduce the disk contention caused by sharing areas between highly active files in flagrant disregard for OFA rules. Let's look at the three-area layout:

• AREA1. Executables, redo logs, rollback segments, export files, copy of the control file

• AREA2. Data-datafiles, temporary user datafiles, a copy of the control file, redo logs

• AREA3. Archive log files, indexes, a copy of the control file

Again, an attempt is made to spread I/O evenly across the platters. While this is better than one or two areas, there is still contention between redo logs and rollback segments, indexes, and archives.

Four Areas (Just about Right) 

Four areas are much better. Now we can spread the read- and write-intensive rollbacks and redo logs. In addition, we can isolate the archives away from the indexes. Let's look at the structure:

• AREA1. Executables, redo log files, export files, a copy of the control file

• AREA2. Data-datafiles, temporary user datafiles, a copy of the control file, redo logs

• AREA3. Indexes, a copy of the control file, redo logs

• AREA4. Archive logs, rollback segments

Now we have succeeded in spreading I/O even further. Redo logs and rollback segments have been separated; and because archive logs will not be as active as redo logs, there will be less contention in this configuration. In most installations, exports will be done during off hours, therefore there should be little contention between the redo logs and exports.

Five Areas (Shoehorn Required) 

Well, this may not be Nirvana, but it is a minimum configuration. Five areas allow OFA compliance and permit maximum spread of I/O load. Let's look at a five-area spread:

• AREA1. Executables, a copy of the control file, redo logs, the SYSTEM tablespace datafiles

• AREA2. Data-datafiles, temporary user datafiles, a copy of the control file, redo logs

• AREA3. Index datafiles, a copy of the control file, redo logs

• AREA4. Rollback segment datafiles, export files

• AREA5. Archive log files

Six Areas (Almost Enough) 

We are getting closer with six available areas; we can move the exports and temporary areas to their own disk now, eliminating that source of contention, but what about backups?

• AREA1. Executables, a copy of the control file, redo logs, the SYSTEM tablespace datafiles

• AREA2. Data-datafiles, a copy of the control file, redo logs

• AREA3. Index datafiles, a copy of the control file, redo logs

• AREA4. Rollback segment datafiles, export files

• AREA5. Archive log files

• AREA6. Temporary areas, export files

Seven Areas (Oracle Nirvana)

With seven areas we have minimum contention; and by moving export files to tape, we can eliminate one additional source of database lockup.

• AREA1. Executables, a copy of the control file, redo logs, the SYSTEM tablespace datafiles

• AREA2. Data-datafiles, a copy of the control file, redo logs

• AREA3. Index datafiles, a copy of the control file, redo logs

• AREA4. Rollback segment datafiles, export files

• AREA5. Archive log files

• AREA6. Temporary areas, export files

• AREA7. Backup staging area

By monitoring AREA5 and periodically removing archive log files to tape we can eliminate another. If we really wanted to push OFA, we could add a couple of areas for redo logs. Of course, if you use the partitioning options of Oracle, then you may need many more areas in order to place the partitions in separate areas.

Other authors recommend larger configurations, and in some cases they are warranted. If you have several large tables, consider placing these, and their indexes, in separate areas. Most of this type of consideration is application-dependent. With some of the giant databases we are now seeing (hundreds of gigabytes aren't uncommon anymore) and with petabyte sizes now available through the latest releases of Oracle, it seems silly to talk about a mere 5, 7 or 100 disks. If you can use RAID0/1 and stripe data across multiple platters, do it. Of course, don't do what one inexperienced system administrator did: try to do RAID5 sets with two disks each.

As previously stated, the more we can spread our tablespaces across multiple disks, the better Oracle likes it. If you have the disk resources, spread Oracle as thin as you can. While a seven-area configuration performs well, and is easy to maintain, the more disk areas,  the merrier.

Disk Striping, Shadowing, RAID, and Other Topics