Re: distribute database files across disks

In article <1995Aug16.082941.17233_at_ucl.ac.uk>, Mr Angus Beare <ucfaabe_at_ucl.ac.uk> wrote:
>I want to experiment with using multiple disks for my
>database storage. I'm hoping this may improve performance.
>
>I have one worry. What if one of the disks fails? How will
>Oracle behave? Will it still function if a tablespace datafile
>is unavailable?
>
>What are the pros and cons?
>
When you say "multiple disks", I assume you are not talking about any type of RAID or disk mirroring. If you are, then ignore my comments.

As far as spreading your tablespaces, redo logs, and rollback segments across multiple disks, this is what you should ALWAYS do. If all of these files are on the same disk, you I/O performance is going to be terrible. At the bare minimum, you should have 7 disks: ROLLBACK, Redo Logs, SYSTEM, DATA, INDEXES, OS & Oracle S/W, and TEMP. If you want to use ARCHIVELOG mode, add 2 more disks: one for archived redo logs, and another redo log disk. For a good article on the basics of database file distribution, take a look at the Jan/Feb issue of Oracle Magazine.

As for how the database will function during disk failures, that depends on whether you use ARCHIVELOG mode, and if you do, the way you distribute your files. Someone will correct me if I'm wrong, but I beleive that in NOARCHIVE mode, if a database file becomes damaged or inaccessable, the database halts. But with ARCHIVE mode, the database will continue to function, but will not be able to access any objects in the damaged or inaccessable files unless the information is still in the buffer cache. The exception to this is the SYSTEM tablespace. If you lose it, the database halts immediately. For more information on ARCHIVELOG mode, see the Oracle Server Administrator's Guide.

Other than I/O performance, the advantages to multiple disks in conjunction with ARCHIVELOG mode are:
1. The database may continue to function during a disk failure. 2. Only the files on the damaged disk need to be restored.

The BIGGEST plus is the increased performance of you database when your data is distributed properly. Properly distributing your data across multiple disks can increase performance by more than 100% in some cases.

Vince Cross

-- 
* disclaimer - My views respresent NT/BNR in every way, NOT!
NOTE - my real email address is bartok_at_bnr.ca, NOT vcross_at_bnr.ca
* non-work related email to vlcross_at_aol.com (Having an AOL account doesn't
  make me a complete idiot. Excessive drinking makes me one.)