Oracle 11G R2 install using separate instances or multiple schemas in a single instance

Posted on

Question :

I am in the middle of a server upgrade, and have finally come to the conclusion that I need a very important question answered. I would like to inform everyone that I am a novice to Oracle Administration but am learning quickly and still have a lot to learn.

The company I work for has many customers that use their own web portal which is connected to it’s own database schema. We have isolated 3 customers to a single database instance and if the instance goes down then generally those 3 customers go down as well.

My goal for this server upgrade is to prevent any interruption from one customers issues from another customers issues. I want complete isolation, which means installing separate database instances per customer. However, I am not sure if this is a great way of handling it.

So let me ask:

  1. Is it best to isolate the schemas to their own instances if I want to avoid having multiple customers crash, instead of a single client?

  2. Would Automatic Memory Management be beneficial or would Automatic Shared Memory Management be beneficial for each database instance installed on the same server?

    • What I understand is that ASMM would be great for having multiple schemas in a single database instance.
      • Currently we have memory issues because SGA and PGA were set manually and when one schema runs out of memory we would have to bounce the database instance, killing each customers connections. I want to avoid this no matter what route I take, ASMM or AMM.

PLease let me know which is best and how to best accomplish this task.

Answer :

One instance should be able to do the job. I’d recommend simply using AMM and ASMM. If your DB is on a server dedicated as the DB server (i.e. not running web services or other software), you might try setting:

  • memory_target = memory_max_target = sga_max_size = 50% to 60% of your server’s physical memory. If this is less than 1500M, you’re probably going to have pain. If the server is Unix or Linux, you could push this even to maybe 75%.
  • sga_target = pga_aggregate_target = db_cache_size = shared_pool_size = shared_pool_reserved_size = large_pool_size = java_pool_size = 0. Setting these to 0 allows Oracle to size them as needed. Oracle will steal from db cache when it needs more memory for cursors, for example.

If one of your problems is that one of your customers floods the database with too many connections, you would do best to find a way to limit this behavior, ideally at the app server if you can configure db connection pool max sizes. If you can’t change this on the app server, then it can be done at the DB side. For example, you could allow each schema/user to have maximum 200 connections to the DB. Beyond that limit, the offending schema gets denied additional sessions, but the other schemas remain healthy.

Leave a Reply

Your email address will not be published. Required fields are marked *