multiple databases/ same server
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: multiple databases/ same server

  1. #1
    Join Date
    Sep 2000
    Posts
    96
    Does anyone have an opinion/advice re: running two Oracle databases on the same NT server? I know it can be done, however, I have a software vendor telling me it is not a good idea. I have not ever run multiple databases on the same server and wanted to ask you all since I'm sure someone has had experience with this. Thanks!

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    if you size your box to hold 2 instances, then you can perfectly run these 2 instances. you must check that you have enough RAM, HD, and that your processor will be powerful enough ...

    PS : RAM should be about twice the sum of your SGAs

    PPS : your software vendor surely has no experience at all on Oracle

  3. #3
    Join Date
    Aug 2000
    Location
    Straham NH
    Posts
    73
    Not a problem here. I run 4 instances on one NT box. Memory 4 gig, Hard drives striped and 100 Gig. Processor is 2 Zeon 1GHz

  4. #4
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    One of our development boxes runs Redhat 7.1 and VMWare. This is used to divide the box into 4 virtual servers, each running NT 4.0 SP6. Each of these virtual NT servers runs multiple instances. One of them has seven 8i instances. Another has three 8i instances and one 9i instance etc.

    We also have a number of production boxes running multiple instances. The highest number of production instances on a single NT box being 4.

    They all run fine and don't give us any problems.

    I'm not saying this is an ideal arrangement but I think it illustrates quite well that NT can cope easily with multiple instances if the box is sized correctly. Most people run into problems with Oracle on NT/2000 because they try to run it on a posh PC rather than a server. People rarely skimp like that when using UNIX.
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  5. #5
    Join Date
    Sep 2000
    Posts
    96
    Thanks for the info.....I suspected our vendor was a little off-base here.

  6. #6
    Join Date
    May 2001
    Posts
    285
    Tim,

    Could you please provide some advise on how to size the instances correctly in order to make them run on the same NT server?

    Let's say we have a Win2K server, dual processor (P-III 1GHz), 60GB hard drive (w raid 5 array), and 1GB memory, and we are going to use Oracle 9i.

    It's a dev environment, and I know I will need to put a 13GB DB on it. So how many instances do you think we can put on that server and how to size them correctly?

    I know we usually located about 55-60% of toally memory to all instances, but don't have much idea on other thing. Waiting for your advices.

    Thanks a lot!

  7. #7
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    First off, can you put both projects into different schemas on a single instance. If so you could save lots of disk space and memory. If this is not an option, as it's not for us, you will have to consider multiple instances.

    How many instances is a difficult question because it depends on so many factors. On a similar size box I run 7 small instances. When I say small I'm talking about memory allocations, not necessarily disk space. We got to this state by constantly adding instances, tuning them with an eye on not wasting resources and asking ourselves if we could fit another on.

    Obviously, if one instance is using most of the memory and CPU adding another instance will be a disaster.

    The best I can suggest is something really general like:

    1) Check out how much free memory is available on your server, assuming some is taken by the operating system and any other products you have running.

    2) Of the remaining memory allow yourself to use approximately 80% for Oracle. This memory then has to be divided between however many instances you have. For each instance use it's share of the memory to set the following parameters:

    db_cache_size
    java_pool_size
    large_pool_size
    shared_pool_size

    Start the instances up and watch what happens. For a quick check use a script like this:

    http://www.oracle-base.com/DBA/DBASc...ing/Tuning.sql

    When things are working keep an eye on the memory use of the box. Make sure you're not maxing out and using loads of swap file. If you are reduce the memory allocations of the instances.

    If you have some low use instances give them less resources. We have some 8i instances with 4M buffer cache which sounds rubbish, but it's all they need considering the amount of use they get.

    If your server is too small you may find yourself having to make do with substandard hit ratios. Don't worry too much as long as users aren't complaining. If they are ask for more hardware.

    Hope this helps??

    PS. Be careful with the total size of the SGA in 9i. If the total SGA is less then 128M the granule size is 4M, greater than 128M and it becomes 16M. Since the db_buffer_cache is rounded to the nearest granule the values you set and the values you get may seem quite different.

    [Edited by TimHall on 01-18-2002 at 03:49 AM]
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  8. #8
    Join Date
    May 2001
    Posts
    285
    Thanks Tim! I very much appreciate your advise.

    I understand that by putting multiple instances on the same server, it will cost a lot more disk space and memory since each instance will need to have its own db and bkgd processes etc. But will I get any performance gain by seperate 2 schemas into 2 instance on the same server, compared with put them into the same instance on the same server?

    Since I have dev environment here, I don't have much problem with put all my schemas on the same instance. So if the answer to the above question is 'no', then with no doubt I will remove the other instance and just keep one on my server.

    Another question is, I am always confused when I look at the available memory on my DB server. Shall I look at available Physical memory and something else? It will also be great if you can point me to the Oracle doc or metalink file which explain this more.

    Thanks again!

  9. #9
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    The advantage of multiple instances are that they can be tuned and bounced individually. Typically, during the first few weeks of a project we are changing parameters and generally messing about quite a bit. This would affect other projects sharing the same instance. If you don't need this sort of flexibility I would say stay with multiple schemas on one instance and save yourself some disk space and memory.

    The only way you really see performance gains using multiple instances on a single machine is if each instance serves a fundamentally different purpose, one OLTP and one DSS etc. In these situations you may wish to have differing block sizes and tune each instance differently. These issues are not so much of a problem in 9i since it's designed with hybrid systems in mind (multiple block sizes and buffer caches).

    Take a look at this manual for NT/2000 tuning related to 9i:

    http://download-uk.oracle.com/otndoc...a90164/toc.htm

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  10. #10
    Join Date
    Sep 2001
    Location
    Fort Smith
    Posts
    184
    We have 9 databases with different versions of Oracle running on a single NT server and they are working normally.
    The only issue to be considered is the Memory. As the databases are test databases I have configured the server with 2 GIG memory. If you want to run production databases you need to consider the requirement of the memory basing on the sgas/ oracle processes created at a time and then consider swap file configuration mostly on the system drive with 2 GIG space at least as the memory is OS+ APPS+ ORACLE and instancial requirement of memory
    sonofsita
    http://www.ordba.net

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