Does it make sense to consolidate Oracle databases

oracle

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.

Best Answer

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.

Related Question