Re: backup and recovery in data warehouse

NOVEL CONCEPT PLEASE READ CAREFULLY BEFORE FLAMING actually this isn't bad advice in general

Don't backup the data base at all. If you are using flat files to load your warehouse just compress and save the file. Reload when needed. This works reasonably well when using SQLLDR with direct=yes and in parallel with no indexes (drop and recreate). The point is that loads should not take longer than trying to restore. Also try using RAID 5 on the read only partitions. You might even want to load into a RAID 0+1 partition to begin with and then move the data over with the dd command to a RAID 5 partition. Use symbolic links (ln -s) to fool the database. This should resolve the RAID 5 small block write problem.

It's been a while since last I played these games (Oracle 7.x) so you would definately want to try this at full volumne before commiting to it.

You may also find that you end up having to reload more because of whoops then hardware problems.

Hope this helps and feel free to contact me directly.

Mark Rosenbaum			Mendel Systems Inc.
(303) 727-7956			Consultants in High Performance 
1685 S. Colorado Blvd.		DataBase and Data Warehousing
# S-335				ftp://ftp.netcom.com/pub/mj/mjr/
Denver CO 80222			mjr_at_netcom.com

In article <87lajc$mes$1_at_nnrp1.deja.com>, <jeremycdba_at_my-deja.com> wrote:
>Thanks for the replies. To answer a few of the questions raised.
>
>This data warehouse will be at least 1.5 TB in size that's why we can't
>do nightly cold backups. Having some read-only tablespaces will reduce
>the amount to backup. But I expect that we'll never be able to backup
>the entire database in one go even excluding the read only tablespaces.
>Exports and imports will just be too slow.
>
>We're thinking of NOT running in archive log mode. Being a data
>warehouse users will only be performing reads. The only updates/inserts
>will be performed during the loading of data, which can be reloaded if
>there are any problems. Having archiving turned off will improve the
>performance of this. So I can't see the point of running in archive log
>mode. Nice in theory I know so this may change in the future.
>
>On the tablespace sizing and creation. We're going to be partitioning
>by month and each of these partitions will be in a seperate tablespace.
>So how do we know how big to make each tablespace and when should they
>be created? If we guess at the size and they're too small then we'll
>have problems, too big and we're wasting space. That's why I suggested
>using autoextend. And should I create them all for say two years before
>they're needed???
>
>Thanks again.
>
>Jeremy
>
>In article <389B14B3.93302673_at_wolfenet.com>,
> Jeremiah Wilton <jeremiah_at_wolfenet.com> wrote:
>> You can cold-backup any subset of tablespaces you want. As long as
 you
>> use archivelog mode, there is no requirement to backup the whole thing
>> associated with the type of backup being used (hot/cold).
>>
>> For instance, if you have two tablespaces, but only enough time to
 back
>> up one each night, you can back up the first one one night and the
 other
>> one the next night. When it comes time to resore/recover, you restore
>> the two nights' backups, then roll forward until they are consistent.
>>
>> The question I have is: why would the user want to use cold backups
>> instead of hot backups?
>> --
>> Jeremiah
>>
>> Brian Peasland wrote:
>> >
>> > If you are doing cold backups, then you should backup the entire
>> > database. If you want to back up different tablespaces on different
>> > nights, then use hot backups. Alternatively, you can also use
 exports.
>> > To find information, check out Oracle8 Backup and Recovery from
 Oracle
>> > Press. Or Oracle 24x7 also from Oracle Press.
>> >
>> > jeremycdba_at_my-deja.com wrote:
>> > >
>> > > I'm looking for information on a backup and recovery strategy for
 a
>> > > data warehouse. I'm thinking of using a combination of read-only
>> > > tablespaces and incremental cold backups, ie backing up different
>> > > tablespaces/datafiles each night. I can't find any case studies or
>> > > specific examples of this method and would appreciate information
 or
>> > > pointers to documents, articles or white papers etc.
>> > >
>> > > I'm using Oracle 8i on Unix Solaris.
>>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Thu Feb 17 2000 - 00:00:00 CST