O'Reilly Network: Oracle DBA Checklists

Published on O'Reilly (http://www.oreilly.com/)
http://www.oreillynet.com/pub/a/oreilly/letters/2002/ordbacheck_0902.html
See this if you're having trouble printing code examples

Date: September 2002
Subject: Oracle DBA Checklists
From: Marshall J. Jones

I have a question concerning the Oracle DBA Checklists Pocket Reference, which I just purchased. I am just starting as an Oracle DBA and I have six months experience with databases, period, so it is 99.9 percent probable that I haven't a clue as to what I am saying here; if that's the case, please forgive me.

On page 16, "Table 1. Sample Disk Layout," you have Physical Disk 5 listed with the /oracle4 Directory having the Contents of 'All archive logs'. But I was taught in the Oracle Backup and Recovery class that archive log files, as well as the redo logs, should be multiplexed across multiple drives. This example in your book, as I understand it, leaves you with a single point of failure.

I'd be interested in hearing what you have to say, because now I'm wondering whether or not to have confidence in this book. (I like the book: don't get me wrong.)

Sincerely,
Marshall J. Jones

Marshall,

I wrote the "Network Management" section at the back of the book. I'm not a backup and recovery expert, so Darl Kuhn, author our Oracle RMAN Pocket Reference, will shed some light on your query.

The issue seems to be whether you believe you'll ever lose your archive log drive at the same time as you lose a drive containing one or more datafiles. The risk of that happening would be about the same as losing both mirrors of a redo log.

I'm curious, if you follow the advice on page 6, and you lose the one drive with all your archive log files, what then? Aren't you at risk at that point? Wouldn't you need to do something immediately to protect yourself?

FWIW, the last environment I worked in, we used hardware mirroring on all database-related disks. So archive logs were protected by hardware mirroring. Because I was paranoid, I used both hardware and software mirroring on my redo log files.

Jonathan Gennick

That's a good question.

Many DBAs don't multiplex their archived redo log files. Here are a couple of thoughts:

If you lose only your archived redo log files, the database can continue to function just fine. However, the issue then becomes, if you loose both your archived redo log files and your data files, then those transactions contained in the archived redo logs would be lost. That's bad. You'd only be able to recover to your last good backup of data files and archived redo logs.
Recognizing that this is a risk, in the olden days (pre-Oracle8i), sometimes DBAs would manually copy the archived redo logs to a different location, or have a job that ran several times a day that backed up the archive redo logs to tape.
If you do lose your archived redo log files, the best practice as listed in [Oracle DBA Checklists Pocket Reference] is sound advice. (See page 18.) Shutdown the database, take a good backup, and fix the problem with the disk containing the archive redo logs.
I don't know when this book was written, but the feature to enable multiplexing of archived redo logs may not have been available. Now with Oracle8i and beyond, it's fairly easy to multiplex by setting the log_archive_dest_n. Of course, if you write to multiple locations there is more overhead, and you'll need to make sure that ARCn can write out to multiple locations fast enough. And of course, you'll also need more disk space.

Note: log_archive_duplex_dest and log_archive_dest have been deprecated and you should use log_archive_dest_n to specify the destination of your archived redo logs. Also note that this parameter is only available with the Enterprise Edition, so if you're using the Standard Edition, you'll have to put something manual in place to mimic this functionality.

So should you or shouldn't you multiplex your archived redo logs? I hate this answer, but it depends. . . .

If you think there is a risk that you'll lose both archived redo logs and data files at the same time, and your business can't afford to lose those transactions, then yes, you should multiplex archived redo logs, and you should also be backing those up to tape often.
If you can live with the risk of losing both the archived redo logs and datafiles at the same time, then I wouldn't multiplex.

I don't usually multiplex the archive redo logs. Usually the business is willing to live with the risk. Even if you do multiplex, if you have a total disaster, you could still be in danger of losing data.

One place where I do use multiplexed archived redo locations is with Oracle Data Guard (Standby). The archived redo logs are written to a different server at the same time they're being generated on the primary database. This is for databases where the business has said 7x24 and no lost transactions (running Data Guard in Guaranteed No Data Loss mode).

I hope that helps.

Darl Kuhn

Got a compliment, complaint, or suggestion? Let us know!

Return to: letters.oreilly.com