Re: Backups in a DW Environment

I can understand the concern about ingesting large amount of data. We ingest about 200 GB a night. To get around the archiving problem we make a noarchivelog 'staging' instance, to run our loads. Then we use transportable tablespaces to move the data to production. Its alot quicker and easier to restore a backup copy transportable tablespace than it is to roll forward plus we dont have to generate massive amounts of redo.

If you can do your loads in the middle of the night and very few production users are on then, you can put your staging instance right on your production server. We don't do this, but we have them all on the same netapp.

If you do this, I recommend using 99 percent free and 1 percent used in order to 'compact' your tablespace. This keeps the tablespace as small as possible and decreases how long it takes to copy. This speeds up the load process(getting data to production), backups, and recovery. We were able to knock a 28 GB tablespace down to 12 GBs.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, January 09, 2004 10:04 PM

> 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ryan
  INET: ryan.gaffuri_at_cox.net

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).