Re: Backup strategy

In article <3r13se$cd7_at_hpg30a.csc.cuhk.hk>, ernestyik_at_cuhk.hk wrote:

> I believe that we will perform hot backup weekly and cold
> backup on a monthly basis.
>
> : If you experience a drive
> : failure, you would need to recover the data files from the most recent
> : cold or hot backup, and then apply the archive logs from that point on.
> : You will be able to recover your database up to the time of the crash,
> : losing only the uncommitted transactions at that time.
>
> I believe recovering up to the time of crash is not possible if
> the archived redo logs are also damaged together with the drive failure?

Yes, this is correct. This is a CRITICAL point that can screw you up BIG TIME if you don't pay attention to this: Put your archive logs onto their own volume. Never make your archive log destination the same disk as any tablespace datafiles. If you lose that drive, you lose the data files AND the ability to recover them. If you split them up, you could lose your data files and still be able to recover. If you lose your archive logs, your database is fine (I would recommend an immediate shutdown and cold backup, since you will be in an unrecoverable mode).

Of course you could lose BOTH drives and then you would be sunk. Disk shadowing and/or mirroring can reduce the chances of this happening.

I back up my archive log destination each evening onto an optical juke box and to tape. This protects me several different ways. I am still exposed (a tiny bit) by having the only copy of the archive logs on a drive by itself. I COULD experience two drive failures, but that is highly unlikely (knock wood) in the configuration I have.

I recommend Kevin Loney's new book, The Oracle DBA Handbook for those who want a decent desk reference (or in their car in their travelling DBA laptop kit, like I do). It covers these topics and many more in a single book.

> : Yes, its a pain to run in archive log mode, but if you need your data
> : (and who doesn't), it is really the best option.
>
> What is the pain actually? (Our database is still not in
> archivelog mode now) Performance degradation? Hard Disk space
> consumption?

The pain is not hard disk consumption at my site. I have a 1 gig drive allocated for archive logs. On a normal day, I cut 40 meg of archive logs. Under heavy stress, I cut 400 meg of archive logs. Space is not currently an issue. However, if you run out of archive log space, your database will stop in its tracks and wait for you to free up more space by backing up archive logs and removing them from the destination. For some people, this is a pain. For others, its no biggie.

However, NEVER EVER EVER EVER bring your database up in anything OTHER THAN ARCHIVE LOG MODE once you are operating in archive log mode. You will immediately invalidate your recovery strategy. You will have a "break" in your archive log stream and cannot recover past that point. The only remedy is to shutdown and perform a cold backup. You could, of course start a hot backup immediately and warn people that if you have a media failure before the hot backup is complete, you will lose all work since the break in the archive log stream.

Hope this helps!

And by the way, if I ever spout something that is NOT true, please let me know. I, unfortunately, have not had any formal training as a DBA. "Heartbreak Ridge", as they say. I was left standing after the multi component failure and received a battle field promotion.

> Thanks for your valuable information.

You are quite welcome. Let me know if you wanna e-chat sometime.

> Regards,
> Ernest Yik.
>
> --
> * Origin: VAXlab : Home of Ernest * Hong Kong * Internet: ernestyik_at_cuhk.hk

Hong Kong, eh? I was BORN there back in '64. My mom says the labor was cheaper, but I don't buy it (sorry, unintentional pun the second time). Received on Tue Jun 06 1995 - 00:00:00 CEST