RAID 5 and Oracle Databases

While researching the effect of RAID 5 disk configuration on Oracle databases I came accrost more than I thought I expected on the topic.

With disk as cheap as it is today there is no reason to ever use RAID 5 on an Oracle database. Even for a database which sees only a moderate amount of updates the performance loss incurred by using RAID 5 is too high. RAID 1 or some combination of RAID 1 and RAID 0 should be used when high availability is required. Database 10g users should also consider allowing Oracle’s new Automatic Storage Management to handle redundancy on un-mirrored disks (or even raw partitions.)

But don’t take my word for it. Here are some opinions from some notable Oracle administrators:

Oracle Database RAID 5
Mark Rittman

No RAID5. Use RAW whenever possible and consider RAID10 or 0+1. RAID 5 can severely affect performance on highly updated databases.

Oracle Database Administration: The Essential Reference
David Kreines & Brian Laskey

RAID-5 is, in fact, very powerful and inexpensive. It is also a technology to be avoided in most cases when configuring your Oracle database! This may seem a harsh statement, but the reality is that although RAID-5 provides good levels of data protection at a low monetary cost, this comes at a very high cost for disk I/O. In particular, write operations on RAID-5 arrays can be orders of magnitude slower than the same operations on a single disk.

Oracle and RAID usage
Mike Ault

Use RAID10 when possible, RAID5 if it is not. Size the array based on IO needs first, then storage capacity and you can’t go wrong.

Disk Management for Oracle
Donald Burleson

Oracle recommends using (SAME) Stripe And Mirror Everywhere (a.k.a., RAID 1+0) for all systems that experience significant updates. This is because of the update penalty with RAID 5 architectures.

Using RAID 5 for a high-update Oracle system can be disastrous to performance, yet many disk vendors continue to push RAID 5 as a viable solution for highly updated systems.

RAID 5 is not recommended for high-update Oracle systems. The performance penalty from the parity checking will clobber Oracle performance.

If you’re still not convinced check out This site is committed to the Battle Against Any Raid Five (or four or free, uh, three.) The site has links to several more articles exposing the perils of RAID 5.

oracle, rdbms, dba, systems administration, sysadmin, raid, raid 5

Database Connection Strings

ConnectionStrings.comHere’s a cool site to look up those pesky connection strings when you’re coding. has a fairly extensive list of connection strings for all sorts of data sources and programming languages.

It doesn’t have everything, but there’s an email address if you want to submit more connection strings to the site. Why didn’t I think of this!

Thanks to Tom for sending this to me.

database, dba, database administration, database development, development, oracle, mysql, sqlserver, odbc, access, sql server

We win a Sun T2000!

Sun T2000Well things weren’t quite as easy as Johnathan Schwarts, now CEO of Sun implied when he wrote “if you write a blog that fairly assesses the machine’s performance (positively or negatively), send us a pointer, we’re likely to let you keep the machine.” but after a little extra paperwork and a more formal contest we have won a Sun Fire T2000 server!

That’s right! Thanks to considerable help from Scott Maziarz, an outstanding Plymouth State University IT major who has been working with me on all things Oracle, we were able to evaluate the performance of the Sun Fire T2000 against our existing Sun Fire V440. Scott ran a series of tests based on some jobs we run daily to recreate data marts in our production system.

Scott ran the tests and compiled the results and I wrote up our findings here on Life After Coffee for others to see (and so we could enter the contest.) We ended up shipping our T2000 back as our 60 day trial was over before we were chosen for the contest, but now Sun says they’ll be sending it back to us.

So a big thanks to Scott for his help and to Sun for running this program and choosing us as a winner!

Oracle’s Dual Table

On my article about auto increment columns in Oracle Stephane asked about the use of dual in Oracle.

Dual is sort-of a dummy table. It’s a real table, but not one that should ever get updated. It exists in every Oracle database and is useful for troubleshooting and development.

If you describe dual you will see it’s definition:

SQL> desc dual;
Name Null? Type
----------------------------------------- -------- ----------------------------

Dual has only one row, but you can select the value of it.

SQL> select * from dual;


Dual can be selected by any user. Since it contains just the one row you can use it to return a single result to you, like

SQL> select sysdate from dual;


Now you could have done this against any single-row table. You could even create a single row table to select results like this, but since Oracle provides the dual table everyone tends to use it.

Thanks for the question Stephane. I think many people use Dual without knowing (or even wondering) what it is.

oracle, database administration, dba, database development

Oracle Shell Scripting/I become an author

Oracle Shell Scripting by Jon EmmonsAs of today I have gone under contract with Rampant TechPress to write the book Oracle Shell Scripting: Linux and UNIX Programming for Oracle. This book explores shell scripting as a means to automate and improve the environment in which many of us run Oracle.

Shell scripting is necessary for automating a broad range of administration tasks on Oracle servers. Many Oracle administrators and developers lack a strong system administration background and therefore manually perform these tasks at the cost of time, money and sustainability.

This book will discuss how to automate many of the common Oracle administration tasks as well as the methods necessary to automate site-specific tasks. Special attention will be given to writing industry strength scripts which free the Oracle professional from daily maintenance tasks yet provide proper tracking and feedback to ensure the best possible operation.

While this will be quite a diversion from blogging (in more than one way) I am quite excited about publishing a longer work. I have always taken a quiet pride in my shell scripts as they chug away nightly backing up databases and monitoring the system. The opportunity to give such an essential topic a full treatment should be quite rewarding.

Having been a Solaris and Linux system administrator for quite some time and now beeing a full-time DBA I don’t know how I could survive without my army of shell scripts. Even more I remember how hard it was to learn shell scripting and UNIX in general. Now my hope is that I can lend some of my experience, and some pre-fab scripts to others.

Speaking of pre-fab scripts, the book will come with over 50 working shell scripts for a broad range of purposes. Read all about the book and even check out the table of contents at Rampant’s site.

Well, the book won’t hit shelves until May of next year, but you can expect to hear more about it from me as that date gets closer.