1

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.

migrated from stackoverflow.com Jul 1 '14 at 19:10

This question came from our site for professional and enthusiast programmers.

  • The term "client" here is ambiguous and clouds the discussion: client you're doing business with vs. database client (eg. JDBC). You might want to edit your question use the term "customer" when you mean the former. – Joshua Huber Jul 1 '14 at 19:19
0

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.

  • How would I be able to isolate instance crashes from affecting all schemas (which affects all customers) if I use a single instance with multiple schemas? – Krptodr Jul 1 '14 at 20:46
  • 1
    The idea is not to isolate, but to avoid instance crashes altogether. There is no reason an Oracle instance should regularly crash. Hopefully if you try with adequate memory settings like above, you would not have the same issues you were having. – Joshua Huber Jul 1 '14 at 21:01
  • What about database instances to separate customer data, due to privacy policy and intellectual property? In other words I believe I have no choice but to separate according to database instances. What should I watch out for? – Krptodr Jul 2 '14 at 16:40
  • Separate schemas provides privacy. As long as your db users don't have wide privileges like SELECT ANY TABLE, then they can't see outside their own schema. You can still choose to have separate instances if you like, but you don't have to. In my environment, I have several Oracle databases that have multiple different applications running using different schemas, and their data is isolated and private. The main drawbacks of multiple instances is that more memory is required, memory is not as efficiently used, and more instances for you to manage/patch/upgrade. – Joshua Huber Jul 2 '14 at 18:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.