Re: Backup and Recovery strategy and DB design

In article <834jqq$dp0$1_at_nnrp1.deja.com>,   kachkar_at_my-deja.com wrote:
> Hi all,
>
> I need some help/ opinions/ suggestions for the following matter:
>
> We are expecting the size of our database to be about 200GB ,
>
> The current design is :
> 4 tablespace each table space has one datafile
>
> Tablespace no 1:
> Cmacdata contains about 160 table
> Size:5GB
>
> Tablespace no 2:
> CmacIndex contains indexes for the 160 table
> Size: 2 GB.
>
> Tablespace no 3:
> CmacMeter contains 3 Big tables each one is partitioned by month
> Size: 10GB.
>
> Tablespace no 4:
> CmacMeterIndex contains indexes for the 3 big tables and they are
> partitioned too)
> Size: 4GB
>
> Is this a good design , considering the database performance and
backup
> and recovery performance
>
> Isn't better to have as many datafiles as possible specially for the
> partitioning?
> Isn't better to have for each month( partition ) a separate
datafile ?
> and if so , how is it going to affect the database performance ,
backup
> and recovery ?
>
> Also for the backup and recovery strategy the plan is:
> The database is on archive mode
> Every day we will backup the transaction files ( logfiles )
> Every week we will back up the whole database ( I don't know how long
it
> is going to be ) , we can do the backup for tables space no 1 on
> Saturday and for table space no 2 on Sunday and so on …..
> Now, is this a good plan? If yes or no , why ?
> And how about incremental backup ,incremental levels , non-cumulative
> and cumulative
>
> One last thing :
>
> Our business requirements says that the data should be available
for 7
> years , now because I don't want the database and datafiles to
explode ,
> I think ( I might be wrong ) I need to backup the data for each year
and
> save them to tapes then remove the data for that year from the
database
> using delete from table where …. , then if the people needed the data
I
> have to restore it again .
>
> Firstly, I don't know if this logic is good or bad
> Secondly. If it is OK , how can I implement it ?
>
> I really appreciate your help,
> Thanks in advance
> Regards,
> Khaled
>

It isn't your tablespace organization that is so important as the disk layout used to support that organization. If all the tablespaces are on one raid stripe then for all practical purposes you have one logical disk and disk performance is not an issue. If you have multiple unstripped disk then tablespace file placement becomes an issue to be carefully considered.

Depending on your platform and your version of Oracle you may find that you have a 2G or 4G limit on your database datafile size. You should check your OS specific documentation before you make plans based on a specific number of files.

I have found that the indexes for a specific set of tables often consume as much space as the tables they cover. In most applications there are tables that require multiple indexes. You should ensure you have the available space to add indexes, if necessary, for tuning purposes.

These are just some thoughts for you to consider. --
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Dec 14 1999 - 09:12:02 CST