"Concentrate; put all your eggs in one basket, and watch that basket..."
-- Andrew Carnegie

>>Anyone that knows me - knows what my answer will be
That sounds like Oracle saying ... :)

My choice probably also here. I think several independent Oracle databases looks less obvious in your case.
How are you going to link your COUNTRY-SPECIFIC OPERATIONAL DATA to shared operational data tables then?

Think about other following points:
- COSTS (do not see this important one in your criteria or money is not an issue)
a) HW costs and maintenance
b) SOFT maintenance (patches¿)
c) Oracle licenses per CPU probably
b) Oracle partitioning is extra-licensed
e) Once your big DB instance can not handle the application load you are probably a potential RAC customer, means about 50 % more license costs.

- Availability.
If you need high availability you would probably look for a failover cluster or RAC.
If 1st option, then you can build active-active cluster with 2 databases from the beginning. So just think about it. The second one can handle your shared data, can be a duplicate of the primary DB and do reporting, can be a RAC node to spread a load or just leave this box without a DB..

- Performance/tuning: here you should understand that in Oracle you can split a parts of you DB mostly by tablespace and schema. So mainly on that level you can distinguish a DB load. In some cases it¿s not easy to see who produces the performance problem on the singe instance.

- Recovery with online DB
Complete recovery is not an issue. Incomplete recovery might be not easy.
If say I want to recover my country after a failed DB upgrade -> the old good exp/imp can help otherwise you build an auxiliary new database -> restore needed tablespaces, do incomplete recovery,¿.. and so on.

In some cases it is not easy to see who produces the performance problem on a single instance.

You should check out DBMS_Service. Instead of logging into a different instance to segment your load, you can login to one instance and a different *service* to segment your load. Oracle tries very hard to give you the tools to make ONE 'look like' many. Services, partitioning, RLS/VPD, etc. Actually between those 3 you've got 97% of what you need. You can segment your traffic, you can segment your data, you can secure access to that data...

Once your big DB instance can not handle the application load you are probably a potential RAC
customer, means about 50 % more license costs.

Even if you split your application into two databases (to avoid RAC complexity) that's still better than 1 per country. Rac isn't a fait d'accompli.

I still see only USER & Tablespace as the main and always way of splitting an application parts. VPD is an option if you use it at all. Resource manager can help also. Additionally the application design is important. If you have web application with one BIG application schema you are in trouble. In that case you can not even trace a needed user session on the DB side.

"Service" (I thought) is a new 10g feature which is only useful for RAC. I could not find on Metalink any example of using it on a single instance. From recent Oracle 10g training I've learned there are external services introduced (new column in v$session) which is useful in RAC configurations. Could you or maybe Tom give a link or an example of using Service in a single DB instance.

Partitioning definitely a great thing but I think it can give you only the performance benefit with some manageability advantages and only make sense for big DBs for extra money. It can even make performance tuning / load analysis more complex for you.

Tom,

Thank you for your answer to my posting. I do also appreciate the contribution of the other members of "asktom" community.

>> Single database, period
OK, you're right and we do believe that a single database is the best solution in our case.

We still have 3 solutions:
1) one schema per country: set of tablespaces by schema/country
Advantages:
- backup/restore each country separately made easy (???)
- Isolate completely each country from the others (it is more about "psychology" than technology)
- Recovery is made easy (schema level recovery???)

Disavantages:
- multi-schemas to manage (application level updates, ...)
- requires replication or materialized views for "shared logistical and operational tables"


2) One unique schema for all the countries.
Advantages:
- only one schema to manage

Disavantages:
- requires partitioning per country to guaranty "isolation" per country (extra cost)
- backup/restore per country becomes more complex (switching partitions with empty tables, ...) and could require unavailability (???) of the related tables (impacting all the countries)
- only local indexes could be created (to isolate partitions)
- politically difficult to stand up for (as each country would like to be completely isolate from the others). The idea of having all the eggs in the same basket does frighten countries.
- implement VPD to isolate country's data (more complex than basic security linked to schema users)
- Data corruption (at global table level) does affect all the countries

3) Hybrid design: one schema/country for "country-specific" tables and a common schema for "shared logistic and operational" tables.

Some partition concepts are still not clear enough for me.
About partitioning:
- What happens during the exchange of partitions: does the global table remain available for DML?
- What happens if you try to access the partition being exchanged(during the exchange process)?
- Is it possible to flashback only one partition of a table?
- Does RMAN able to backup/restore only specific partitions within a partitioned-table?

About databases:
- Does it make sense to have 1 instance/server per "big" country and one unique instance/server for all the "small" countries? The goal is to separate huge resource consumers (big countries) from the small consumers (small countries). The final situation is about 5 servers/instances.

Thank you and best regards,
Emmanuel.

Tom,
would your comment "I would violently oppose a multi schema approach in all cases" still hold in a RAC enviroment?

I mean, I could have different countries as different schemas within ONE single RAC DB, and assign different countries to different nodes/sub-clusters via services. In such a scenario, your complaint about shared-sql being defeated losses relevance since each country would run against a different node/sub-cluster (separate country instance(s)).

I agree with you on "single DB period". I also agree with opposing multi schema in a single server scenario versus a partitioning, VPD approach, but I think in a RAC environment the multi-schema approach does have its merits/advantages. Please your comments.

Regards,
Andres

Tom,

I definitely agree with the approach of one database, one schema that you've mentioned above and, while this may not be an issue for Emmanuel, I've still gotta ask about data localization (physically).

For example; let's assume that one of the tables that would be partitioned by country is for some type of large data store (1GB or larger) that is only relevant to that country for whatever reason (some type of local backups or other binary data). You've spoken to the many advantages of storing files within Oracle as opposed to the filesystem before but with an architecture like this (one central DB being accessed around the world), how could you manage this while delivering an acceptable response time? Access to this data will obviously be a lot faster with some type of local storage, especially over slow WAN links that may be adequate for the majority of other SQL transactions.

In the filesystem world we have techniques like SAN replication that would allow us to keep things centrally located while still allowing for local access at remote locations. Is there a similar means to localize these partitions to the relevant countries without jeopardizing data consistency/integrity while still enjoying the benefits of DB file storage?

Hi is there any suggested approach on how many tables per schema or it doesn't matter.
I dint know in the world about max number of tables in one schema.
I have a schema with 500+ tables and I am expecting 200 more tables in coming days.
If needed new tables can easily be created in a new schema of their own if they serve a single different functionality for example (if we add a new sales module, most new tables can be created in sales schema).

is there any thought process of one huge schema with lot of tables/ and other objects per database or multiple schemas which are created on basis of the functionality they support.