Re: Size of an ORACLE Database over 100 Gb

In article <NEWTNews.844494718.10331.dafis_at_ppp.test.jccbi.gov>, Martin Douglas <dafis_at_ppp.test.jccbi.gov> wrote:
>
>In article <5334mp$hii_at_paris.magic.fr>, <mti-sales_at_magic.fr> writes:
>>

  <ad deleted>
>>
>> Alain BORGO <Alain.Borgo_at_ratp.fr> wrote:
>>
>> >We have a new application that use ORACLE 7. The total size of the
>> >database is about 250 Gb. We certainly put all the files on 60 disks
>> >of 4 Gb each (perhaps we will use the RAID 5 technology).
 

>> >The problem is to manage this very big database : particulary we fear
>> >for the administration of the data. Another diffuclty is the time to
>> >save these 250 Gb.
 

>> >We're wondering if someone had already manage such a big database or a so
 big volume of data.  

>> >Anyone with some experience with that sort of problem will be welcome.
 

>> >Alain BORGO.
>>
>>
>>
>Hi,
>
> I am working on a database that has 21 gig of data and 26 gig of
>indexes. Some of my indexes are 2 gig. and some tables are 4 gig. I will add
>some new functionality soon which means that my 50 gig database per year might
>be as large as 75 or 100 gig per year. We have a one year old database and
>are just learning what problems you will encounter. Temp tablespaces must be
>large, exports will run days. imports will run weeks. Cold backups are a
>blessing but I would say for you to use and oracle backup at least once a
>month or periodicaly as time permits. my most recent problem is running a
>full export that is larger than one tape. The database is fast but
>administation is a nightmare.
>
> I would like to see other peoples comments.
>

If the data can be partitioned into historical (non volatile or unchanging) and current (volatile or changing) then that is a big win. Typcially in VLDB the unchanging data is the majority of information. The unchanging data is also a very good match for RAID 5 since it will be read only. This data should be backed up twice and then not worried about. The RAID 5 should protect the data and if that failes you would have 2 backups to tape (probably DLTs). The changeable data should be on RAID 0+1. After nightly loads this data can be backed up as read only. If you are forced to insert during the day you can create a seperate table to insert to and then back it up at the end of the day.

In the back of the Oracle 7.3 System Tuning Guide there is an excellent appendix on how Partition views work. That is a great starting point.

BTW the check constraint example is wrong for default date settings. Use dd-mon-yy instead of the mon-dd-yy that is shown.

Hope this helps. If you have more questions feel free to contact me.

Mark Rosenbaum			Otey-Rosenbaum & Frazier, Inc.
mjr_at_netcom.com			Consultants in High Performance and
(303) 727-7956			Scalable Computing and Applications
POB 1397			ftp://ftp.netcom.com/pub/mj/mjr/resume/