Re: Backup and Recovery strategy and DB design

See below

kachkar_at_my-deja.com wrote in message <834jqq$dp0$1_at_nnrp1.deja.com>...
>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
>

I would have two concerns with this setup. First, you must understand how your physical disks are laid out. Are all disks on one controller? Are they setup as RAID5 or RAID 0+1? Are you using Hardware based RAID or Software RAID? This will help you to spread your data out so you minimize your disk contention.

Secondly, Partitioning objects in the same tablespace or physical device doesn't fully take advantage of the partitioning feature. Sure, you will get some performance boost because you don't have to search the whole table, but if you have a query that crosses partitions, you would be better off having those two partitions in different tablespaces on different devices.

>Isn't better to have as many datafiles as possible specially for the
>partitioning?

Only if they are on seperate physical devices. Otherwise, it doesn't help much.

>Isn't better to have for each month( partition ) a separate datafile ?
Depends on your data, but probably yes.

>and if so , how is it going to affect the database performance , backup
>and recovery ?

If the majority of your queries are looking at the data a month at a time, then it will probably help performance. Backup and recovery should be unaffected.

>
>
>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?

I would say this plan is a minimum. If you can backup your database in 4-5 hours and can afford for it to be down during the wee hours, it would be better to do a cold backup every night. Otherwise, you might look at doing a full online backup each evening.

If yes or no , why ?
>And how about incremental backup ,incremental levels , non-cumulative
>and cumulative

I'm fussy about this because I worry about recovery time. I prefer a full backup whenever I do a backup.

>
>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 .

You might be able to work magic with the partions here. I'll have to defer to others about the specifics about archiving with partitions.

Another thought is that 8.1 has a feature where you can add a "WHERE" clause to an export specification although I haven't played with it much yet.

>
>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
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Tue Dec 14 1999 - 08:59:38 CST