Doubts on number of tablespaces and allocation among disks
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Doubts on number of tablespaces and allocation among disks

  1. #1
    Join Date
    Oct 2001
    Location
    India
    Posts
    36
    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.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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)

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.


    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.


    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.

    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 Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Jan 2001
    Posts
    3,131

    Question

    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
    I remember when this place was cool.

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    because some OS cant handle file bigger than 2GB

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Oct 2001
    Location
    India
    Posts
    36
    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.

  8. #8
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    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,

    OCP 8i, 9i DBA
    Brisbane Australia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width