Doubts on number of tablespaces and allocation among disks

Dear friends,
I have got so many solutions from your site. Thanks much. We have a scenario like this, Server capacity is 216GB(12disks of 18 gb each), 1(one) disk controller and has RAID 5.The database is nearly 100GB and we have nearly 300 tables. I have sagrigated all the tables into verysmall, small, big, verybig. So for big and very big categories i have planned for 11 partitions each and keeping in seperate tablespaces, so it comes to 22 tablespaces. and for other
categories i am planning for 4-5 tablespaces. All the tablespaces will be of LMT( System exceptional). Apart from this there is next phase of the project and
the data will reside in the same database with equivalent space cosuming. Here are my list of doubts:
1) In this case of tablespaces it can come to 60-70 in number. Will there be any
problem in having these many tablespaces in cases like maintenance and backups.
2) Till now i didn't plan for a backup strategy. And also please let us know
how we should proceed for backup strategy also.
3) We are planning to have a datafile of 1GB size, for easy maintenance at
Operating System level. Please comment on this.
4) As my server has only one disk controller, can I place all my indexes and data tablespaces together in one disk. I am asking this because documents says RBS tablespace should be away from data tablespace, redo log files should be
away from data tablespaces and etc... Will this hold good when there is one disk controller.

Thanks in Advance.

Number of tablespaces doesnt matter, we have a datamart with around 200 tablespaces right now and expecting to grow to 800 or so in one year time (uses read-only tablespaces, one partition per tablespace)

Your database sounds like DWH (RAID 5), if it is then you dont need to run the db in archive log

Size is up to your own choice but IMHO a 100GB database can perfectly have 2GB datafiles or 4GB inclusive depends your OS limitations

One controller doesnt seem enough, may be it will be your bottle neck in the future (even your controleller is fiber optic one is little)

Quote:

---

Originally posted by V6163

1) In this case of tablespaces it can come to 60-70 in number. Will there be any
problem in having these many tablespaces in cases like maintenance and backups.

---

This might be a little overkill for one controller and all your disks in a RAID5 configuration. However, your plan will carry you into the future if you get more disks and controllers.

Quote:

---

2) Till now i didn't plan for a backup strategy. And also please let us know
how we should proceed for backup strategy also.

---

How you implement your backup is dependant on your Mean Time To Recovery (MTTR). If your MTTR is short, you want frequent backups. If your MTTR is long, you can get by with longer intervals between backups.

If this is production, put your database in archivelog mode.

After you have your backup working, test, test, test the recovery.

Don't rely on imp/exp as your sole method of recovery.

Quote:

---

3) We are planning to have a datafile of 1GB size, for easy maintenance at
Operating System level. Please comment on this.

---

This is fine. On a big database, I limit my datafiles to 2G, but 1G is fine.

Quote:

---

4) As my server has only one disk controller, can I place all my indexes and data tablespaces together in one disk. I am asking this because documents says RBS tablespace should be away from data tablespace, redo log files should be
away from data tablespaces and etc... Will this hold good when there is one disk controller.

---

You single point of failure will be your disk controller. If you have a good number of disks, you may want to consider splitting them up into a combination of RAID5, RAID 0 and RAID 0+1 and split your data accordingly. This way, when you get another disk controller you are already set to take advantage of it.

Jeff, Why limit the datafiles to 2G's I was recently told this by one of out senior DBA's as well, a little late though.
What is the benefit/drawback?
Thanks
MH

because some OS cant handle file bigger than 2GB

Quote:

---

Originally posted by Mr.Hanky
Jeff, Why limit the datafiles to 2G's I was recently told this by one of out senior DBA's as well, a little late though.
What is the benefit/drawback?
Thanks
MH

---

And it's a lot easier moving a 2G file than a 10G file. I also feel you can spread your data out better with many smaller files than one larger file.

Dear pando,
Thanks for ur reply. we have oracle 8i and our application is not DWH. It is an OLTP, it has both intranet and internet users. Intranet the users can be 800. I need to run my database definetely in Archive log mode only. Can u suggest me now.

Thanks
V6163.

2gb tablespace might seem small, but it is the actually size that I recommend also. This is becasue It's easier to move around/backup/cp/administer etc.

Cheers,