Oracle 11G R2 – Separate Instances vs Multiple Schemas in a Single Instance

oracle

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:

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?
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.

Best 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.

Related Solutions

Sql-server – How to connect multiple users/schemas in Oracle 11g

You need to grant the REFERENCES privilege on the reference table to user2. (See GRANT, Table Privileges section. Note that this cannot be granted to a role, must be granted to the user directly.)

Here's a demo:

SQL> create user user1 identified by user1;
User created.
SQL> grant create session, create table, unlimited tablespace to user1;
Grant succeeded.

SQL> create user user2 identified by user2;
User created.
SQL> grant create session, create table, unlimited tablespace to user2;
Grant succeeded.

SQL> create table user1.ref_table (id number primary key);
Table created.
SQL> insert into user1.ref_table values (1);
1 row created.
SQL> insert into user1.ref_table values (2);
1 row created.
SQL> commit;
Commit complete.

SQL> grant references on user1.ref_table to user2;
Grant succeeded.

SQL> connect user2/user2;
Connected.
SQL> create table oth_table (thing number, fk number);
Table created.
SQL> alter table oth_table add(constraint fk1 foreign key (fk)
  2      references user1.ref_table on delete set null);
Table altered.

SQL> insert into oth_table values (42, 1);
1 row created.
SQL> insert into oth_table values (256, 2);
1 row created.
SQL> commit;
Commit complete.

SQL> connect user1/user1;
Connected.
SQL> delete from ref_table where id = 1;
1 row deleted.
SQL> commit;
Commit complete.

SQL> connect user2/user2;
Connected.
SQL> select * from oth_table;

     THING     FK
---------- ----------
    42
       256      2

If you want admin to do all the work, it'll need the CREATE ANY INDEX privilege, and you still need the REFERENCES grant to user2.

Here's how that could work:

SQL> create user user1 identified by user1;
User created.
SQL> create user user2 identified by user2;
User created.
SQL> create user admin identified by admin;
User created.

--- Grants
----------------
SQL> grant create session, create table, unlimited tablespace to user1;
Grant succeeded.
SQL> grant create session, create table, unlimited tablespace to user2;
Grant succeeded.
SQL> grant create session, create any table,
  2        create any index, alter any table to admin;
Grant succeeded.

--- Create reference table as admin
----------------
SQL> connect admin/admin
Connected.
SQL> create table user1.ref_table (id number primary key);
Table created.

--- Do the "references" grant
----------------
SQL> connect / as sysdba
Connected.
SQL> grant references on user1.ref_table to user2;
Grant succeeded.

--- Create the second table as admin
----------------
SQL> connect admin/admin
Connected.
SQL> create table user2.oth_table (foo number, bar number);
Table created.

--- Add the constraint
----------------
SQL> alter table user2.oth_table add(constraint fk
  2      foreign key (bar) references user1.ref_table(id)
  3      on delete set null);
Table altered.

Multiple instances in Oracle

The documentation explains it very well:

An Oracle database server consists of an Oracle database and an Oracle instance. Every time a database is started, a system global area (SGA) is allocated and Oracle background processes are started. The combination of the background processes and memory buffers is called an Oracle instance.

An oracle database is the physical files that make up the database itself (control files, data files etc) - Documentation link.

A database is associated with one or more instances, with multiple instances making a RAC setup - Documentation link. As far as your second question is concerned, multiple instances of a single database is a RAC setup.

Best Answer

Related Solutions

Sql-server – How to connect multiple users/schemas in Oracle 11g

Multiple instances in Oracle

Related Question