Re: Backups in a DW Environment

Mohammed,

Comments inline...

on 1/9/04 2:24 PM, mkb at mkb125_at_yahoo.com wrote:

> Have a question on backups in a DW environment.
>
> Our DW is somewhat small at the moment but projected
> to grow. I seem to be having a hard time trying to
> convince the sys admin that I don't want archive
> logging turned on. To me, it does'nt make much sense.

On the contrary, not using archivelog mode is what makes less sense, thus justifying more careful consideration and justification. Archivelogging is the "industry standard" and makes complete sense in all but a few extreme cases.

Have you considered what archive logging actually provides for you, and what is necessary to engineer the same effects on your own? Think it through...

>
> He's proposed using EMC BCV's which I've agreed to
> (and also sounds like a good idea) but also wants to
> turn on archiving. My thinking is why turn on
> archiving if I can restore my DB from last night's
> BCV's and then bring it up to date by re-loading any
> data that was loaded after the BCV split.

The rebuild-then-reload method seems to make sense on paper, but it is the cause of extreme difficultly in actual practice. If you have not yet already implemented a very mature change-management procedure, to record all changes in the database, complete with all of the security to prevent it being bypassed, then you are in for a rough time.

Robust change-management and ironclad security always makes sense, but the extra insurance of being able to recover every change using archivelogging makes sense also.

Also, on the topic of "BCV splits", one of the problems of using "BCV splits" (or file-system snapshots or similar snapshot schemes) is that, while it makes backups very easy, it does not make recovery any easier. This type of backup-centric thinking is very seductive.

What is the purpose of the whole exercise? Taking backups? Or being recoverable?

Oracle Recovery Manager (RMAN) is not named "Backup Manager" for a reason. RMAN is "recovery-centric". It seems more complex on the backup end of things (it isn't), but it is undeniably easier on the recovery side of things. Try to work RMAN into your strategy at all times. It is worth the extra consideration.

>
> Our system is not 24x7 so we can shutdown before the
> BCV split. Also, it's not directly accessed by users
> for ad-hoc queries. Automated processes access the
> database and build cubes using Cognos tools. Users
> access these and not the DB directly.

Any data warehouse that is shutdown, even for a few minutes, just to take a backup, has been engineered to fail. People keep data warehouses busy on a 24x7 basis just like any other system.

>
> So, again I don't see the need for archive logging.
>
> Any thoughts?
>
> mohammed

Hope this helps...

-Tim

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: tim_at_sagelogix.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).