4

I have several application which currently running on my local. I have installed oracle 11g and create a global database name as oracle11. Now should i create again different database for each application using Database configuration assistance or should i create different users for each application under that global database? What would be the best practice? If i create different users its also behave same as creating different databases right?

What if in a production environment where server house for couple of application? Do i need to do the same as my local by creating one database and different users for each application?

Improve this question
2
  • Have you tried creating multiple databases? Have you tried creating multiple users? Did you notice any difference between the two?
    – Oswald
    Jul 31, 2013 at 4:59
  • First of all , your mistaking a database and an instance. second - the answer depends on how many servers you have and what is the expected workload. consolidation is not always the worst idea ....
    – haki
    Jul 31, 2013 at 5:02

3 Answers 3

Reset to default
8

Databases use server resources: CPU, RAM, disk I/O. Unless your server is physically partitioned (e.g. Solaris Zones) or you are using VMs it is a really bad idea to host multiple databases on the same server. You are just creating an environment where contention will be rife and tuning will be even trickier than normal.

Oracle RDBMS has always been designed to host multiple schemas in the one database, and for different applications that is the way to go. This is one of the main differences between Oracle and (say) MS SQL Server or MySQL.

Improve this answer
4

I advocate one database - multiple schemas because:

  • One database version all the time
  • One backup.
  • One characterset
  • One user management
  • One toolbox installation (My own collection of useful objects)
  • One monitoring (EM, Spotlight, etc)
  • One database to patch.
  • One database to keep secure.
  • One database to upgrade.
  • One alert log
  • One dictionary
  • One sga
  • One one one...

But life is hard and software vendors fall behind and do not keep up with the later version of the database. Software vendors do not handle encoding correctly. Software vendors demand their own database based on FUD (fear, uncertainty and doubt). Software vendors require the DBA role for their application. Due to these restrictions, we end up running 8i to 11g, all kinds of encodings, backup is a full time job and the overall administration is a nightmare.

The solution!

Oracle has this new pluggable pill in their latest release 12.1.0.1 of the database which is supposed to address the "multiple database vs. multiple schema" discussion. Now you can plug a database into the multitenant container database - CDB.

DSS databases and OLTP databases can be kept separate.

Improve this answer
2
  • 2
    Well first thing is that 12c is still way too fresh to use in production. And second - pluggable databases is Enterprise Edition option with additional cost. For free or in Standard Edition you can use only one PDB.
    – Mindaugas Riauba
    Aug 1, 2013 at 3:29
  • You forgot the "One database to rule them all"... :) Additionally. the ability to create relationships between schemas. It is impossible to create relationships between databases.
    – AnthonyVO
    Jun 23, 2021 at 21:43
1

You already got two answers about one database - multiple schemas approach. I will try to add some arguments when several databases may be better.
If your applications are very different it may be better to have separate databases for them. Then you will be able:

  • to upgrade one database and leave other on older version (application requirements)
  • restart one database while other is running 24/7
  • it is not too difficult to move the whole database to another server if e.g. one of the applications starts to consume all the CPU of the server
  • from the resources standpoint additional instance will consume like 1GB of RAM more than two schemas in one database. That's not so much for current servers

Also one small note for the multiple schemas approach - put different applications data into separate tablespaces. This will add just a few minutes while creating users but may save a lot of maintenance time later. Trust me. :)

Improve this answer
1
  • 1
    I agree separate databases for different applications may be better in certain circumstances. But there are very few scenarios where having multiple databases on the same server is best practice (except through the magic of virtualization). Only your first point is genuinely pertinent. Moving a schema is hardly more difficult than moving a database. Restarting is irrelevant. And the point about RAM usage is contentious (I know systems which still exist on small amounts) and fails to address all the other resources which will experience contention.
    – APC
    Aug 1, 2013 at 7:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.