Creating a very large database ( Oracle 8 under Solaris - a3500 fc-al
arrays )

Hi

I have been given the task of creating a fairly large database about 200 gig
as data from variouse non-oracle database will be loaded into it - the
database is going to be a 24x7 OLTP type, there will be about 35 to 40
tablespaces, and the database is expected to grow rapidly.

Would like to receive your expert recommendations on the following issues :

1) What should be the size of system tablespace for a database of this size
     and storage parameters configuration for the system tablespace to avoid
     dynamic extension.

2) How the datafiles should be placed across the disks to minimize
contention

3) As the database will be growing fast and at certain point of time we
would
    like to move the old data to a data-warehouse to keep it to a
    managable size - any suggestions on that will be welcomed too.

4) What should be the backup strategy (full and incremental)

5) any other issues of importance i.e. initial parameters etc.

Tks so much for your time

Rekha

Answer:

One thing that you did not state was whether this is going to be a large
database with lots of relatively "medium size" tables, or a large database
with a couple of VLT's (Very Large Tables).

And this in my mind plays more of a critical role than the overall size of
the database. After all, whether the database is 20GB or 200GB, the same
principles apply in physical design - spreading i/o across disks, backup
strategies and so on. Just because the data volume is larger should not mean
that the basics of Oracle db admin still apply.

VLT's is a bit different than normal tables, simply because the volume makes
many "ordinary" things impossible. A quick backup of a table - no problem,
fire up exp and quickly export of that table. A new index - just give me a
few minutes.. Hey, don't even think to try this with a VLT! :-)

Imagine a VLT with 2 indexes - a primary/unique one and a normal type
(non-unique) lookup index. Let's say the VLT has a 100 million rows. Now
imagine the time it takes to insert a -single- row. First, Oracle has to
scan the unique index to determine if there are any violations. 100 million
index values - that works out to quite a few reads that are required to
ensure that the new index value is unique. Now similar, but fewer reads on
the non-unique index to update that. Finally three writes - two for the
updating the indexes and one to update the table. That insert what you
normally would associate with a single write, has in fact a -huge- number of
reads to do. Now imagine having to insert 20,000 rows into this VLT. What
are you doing for the next 24 hours? :-)

So when it comes to VLTs, the picture start to change as the overheads to
pay per row accumulate to a lot. And is usually here that you start using
things like Parallel Query and Partitioning.

A 200GB does not imply VLTs. Neither does a 50GB imply that it does not have
a VLT.

My suggestion is to stick to the basics of Oracle db admin and simple common
sense. You can not go wrong with that, irrespective of the database size. If
you do have VLTs in this new database, then you need to pay close attention
to the requirements for accessing this table - a VLT can quickly become a
giant bottle neck.  If it is simply a large database due to the number of
tables (and not the size of the tables), then treat it as you would any
other Oracle database.

regards,
Billy