Does it make sense to consolidate Oracle databases?

Question

I am more experienced with SQL Server and Sybase than Oracle, and understand those products well. I've been asked to look for ways to reduce the server estate running Oracle. I understand that an instance in Oracle maps to a database hosting many tablespaces. I have a fairly good grasp of the fundamentals, however if I wanted to consolidate SERVER1,..,SERVER4 running Oracle database into one server what would be the best way to do it physically? I am considering Virtual as well using a DBaaS (Database as a Service) model, but am curious if it can/should be done physically.

Is it possible to have four separate instances point to four separate databases on one machine? Or would I have to merge the four databases into one database on the consolidated server and manage the schemas to ensure there are no name conflicts? If I did that would I have one instance or four?

I have read the documentation but I'm still not 100% sure about this area.

Answer 1

You have two options:

1. Run multiple Oracle instances on the same machine
2. Consolidate all of your Oracle instances into a single instance, placing the data in separate schemas

Since you're familiar with SQL Server/Sybase, I'll explain the difference between them & Oracle as far as databases and users are concerned.

• A SQL Server database is equivalent to an Oracle Schema. An Oracle schema is owned by a single user
• A SQL Server dataserver is equivalent to an Oracle Instance

Running 4 instances on one machine is trivial, so I won't explain further.

Consolidating to a single database is also easy if the separate databases don't have conflicting schema names. If they do, it may not be an issue as long as the applications/interfaces/packages don't have hard-coded schema names - it's easy to export data from one schema in a database & import it into a different schema on another database.

Answer 2

It is fairly easy to consolidate multiple databases into one real database. In Oracle, a database is the collection of files. Users connect to the database by connecting to an instance. A database can be served by multiple instances, in which case you are running RAC.

So for simply consolidating database into one single database you don't need RAC but you could, if you want/need to do this.

If you are consolidating into one database there are a few things to take into account:

1. namespaces
2. service level agreements - don't make maintentance impossible by combining conflicting slas.
3. performance isolation - use Resource Manager to handle this
4. application isolation - make sure the apps all use their own tns_alias to connect
5. services - give every application an own service name in the database, if possible.

When you have naming conflicts, there is a problem, combination not possible. You might want to take some downtime for maintenance/upgrades. If you can not get a downtime from all applications at the same time, you have a problem. Using Resource Manager you can give a certain performance guarantee for the specific services. Services are a smart thing to use, it is the easiest thing to see how resources are used, compared to one and other.

Easiest is to run multiple instances on a single server, each serving it's own database. This is the easiest but not the smartest thing to do. Smartest is to have a single instance on a single server. This is because every instance considers itself as the master of the server. You can not very easy isolate their resource usage, as you can do in a single instance. If you want to give some performance guarantee in a multiple instance server, the setup will grow in complexity because in many cases you need to start multiple projects and users to run your databases under.

A single database can easily support a few hundred applications, a lot cheaper than using a few hundred databases. This quickly saves BIG money on a yearly basis by making good use of Oracle features.