Re: How to backup a data warehouse?

• "Terrian, Tom" <tterrian_at_daas.dla.mil>

> How do you guys backup your data warehouses? Our warehouse is suppose to
> top out around 3.5TB. It seems that the traditional hot/cold backup
> methods will not be able to keep up. How do you guys do it?

Solutions I've seen that worked:

    Back up the data before you load it. By the time data is     loaded there's simply too much of it to back ot all up.

    Use a database that is designed for OLAP databases and     allows restorable backups of physical storage (e.g., Red Brick).     This is probably your best bet since Oracle is an OLTP database     and simply isn't designed for warehousing.

    Split the data by instance and only down the instances that     have data that needs to be backed up [sick, but it does work].

    Mirror the data at load time, split the mirror and back that     up (requires re-naming the database and potentially bringing     the instance up under a new name, single-process mode). This     works if your data can be backed up in less than one load     cycle. And you certianly have an extra few TB of disk space     lying around somewhere, right?

Because Oracle is designed for OLTP it doesn't allow you to back up individual data segments while the database is up -- not that you can restore at least. It's also nearly impossible to isolate the updates to any one disk file (making it hard to back up limited storage and make that work). Net result is that your best backup strategy w/in Oracle is to get the data before it's put into the database and reload (vs. restore) it when the time comes.

Given that a CD storing gzipped data can frequently handle 5GB of raw space, burning a cd of today's load is often reasonable.

--
Steven Lembark                               2930 W. Palmer
Workhorse Computing                       Chicago, IL 60647
                                            +1 800 762 1582
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steven Lembark
  INET: lembark_at_wrkhors.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).