Re: How to plan a Big DB Tablespace Containers

Karanbir wrote:
>
> Hi,
>
> Well i have used Oracle only for small Databases on AIX.
>
> But now i need to Create a Database which will be around 300 to 400 GB
> on SUN.
>
> The DB has around 400 Tablespaces. Each Table has 2 Tablespaces one for
> the Data & another for the Indexes. The Tables are normal ones having
> Numbers, Varchars,etc, ie no Big Objects.
>
> I will be loading the Database from NULL to this size through my Front
> End Applications.
>
> My loading will be in Steps, so after each Step I want to take a Cold
> Backup of the Database and all Tablespaces.
>
> I dont want to create in the begining the Tablespace Container of Say
> 1GB, but i want to start from some size and as an when data is added i
> will be altering the Tablespace Datafile, or add new one. This is
> becuase when initially there is no data in Datafile and if i have
> containers that huge the Backup will take lot of time & space also.

I have three production databases between 600 and 800 GB. Some of our current problems come from when the systems were smaller and small datafiles were allocated. If you have the time and backup devices, I would create the datafiles at 1 or 2 GB each and add datafiles of that size when necessary. Don't forget to add an extra 8K to each datafile for the header. I personally use sizes of 1034248KB (1010MB + 8K) and 2068488KB (2020MB + 8K) so that a little room is left in an evenly allocated (GB) filesystem for emergencies. With those sizes, if you can use MB extent sizes and evenly fill up the datafile without free space left at the end. Also, a common size makes it easier to move/swap datasets for performance purposes. With our DLT libraries and multiple drives, we can backup about 100 GB/hr. Depending on the length of your load jobs and tolerance for recovery and rerunning the jobs, you can make multiple loads during the day and then submit the backup to run overnight so you don't lose any prime shift hours.
>
> I think there is a Max Number of Datafiles stuff in Oracle, how is that
> concerned and how much can this be, where is it specified ??

The number of datafiles is specified in the init<SID>.ora and don't forget about the UNIX parm for the max number of open datafiles.
>
> Can you please suggest how should i go about it.
>
> Thanks
>
> Karanbir.
Received on Thu May 04 2000 - 00:00:00 CDT