Disadvantages of consolidating databases?

Question

In an organization that has two applications each with its own Oracle database instance, what are the disadvantages of consolidating the two databases into one database with two schemas?

Backups and replicating the database are bigger and slower, probably. What else?

Some background:

The two databases are the "gold source" for their respective data. Each is critical to the operation of the organization and each is actually used by several appliations, tools, and reports (but each database is principally "owned" by one application). The need to join data across the databases, to relate entities in one to entities in the other, comes up frequently. For this reason there are DB links connecting the two and some cross-database materialized views to help with performance. There is an effort underway to reduce data duplication and these materialized views are under discussion. Some in the organization want to phase out DB links and materialized views and introduce more web services to make the data available across applications. My concern is that there are too many situations that require complex joins of data across the two databases so services that expose the data won't perform. Another approach for reducing DB links and materialized views is to consolidate the schemas into one database, but I want to make sure I'm not forgetting any critical disadvantages to that approach.

Answer 1

Could have something to do with licence costs - scaling up vs. scaling out.

Answer 2

The biggest concern I would have is that all your code will need to be rewritten to account for the new database and schemas. Or at least looked at. This courl introduce new bugs. I don't know how Oracle handles refernces to different databases, so I'll use an example of what I mean using SQL Server syntax. If I was joining to two tables onthe same server in different databases my select would be something like this:

SELECT a.field1, b.field2 FROM database1.dbo.table1 a JOIN database2.dbo.table2 b ON a.myid = b.myFK

To go your your new consolidated idea, you would want to write:

SELECT a.field1, b.field2 FROM schema1.table1 a JOIN schema2.table2 b ON a.myid = b.myFK

You will need to be especially careful of any tables that have the same name in both databases now, this could cause some sneaky bugs.

Note these are not difficult changes but all SQL hitting your database would have to be examined to see if it will work or adjusted if not.

I'm not sure if just putting them in the same database would do it either. You might need to consolidate some tables to avoid the duplication across applications. (In this case add fields to reference the old id numbers for things people are used to looking up by id like person_id that may appear on old paperwork, so they can be researched) This is a fairly major rewrite with all the attendant possibilites to make things worse due to new bugs.

If you go down this path, I highly recommend that you read a book on refactoring datbases before you decide how to design.

Answer 3

Hi jlpp,

In a single consolidated database, you will lose some flexibility from a DBA point of view:

• A database obviously can have only one version (10.2.0.5 for example), which means that upgrades and patches will affect all schemas -- this may be a bad thing in case of multiple vendor app requirement mismatch.
• Similarly, some administrative tasks (restore database A to point in time t) may be more complicated with a single database.

Overall, you will have less administration tasks (a single backup, single patching...) but each task will be more critical since they will have a global effect.

---

On the development side, beware of namespace collisions: some features are global over a single database, for example:

• directories,
• public synonyms,
• DB link
• Schemas

This means that you will have some work to do if you want to consolidate two databases that have public synonyms with the same name that points to two different things.

Answer 4

its hard to tell by just the information provided, big in db world would be 100gb or more, so 2 dbs would be 200GB. if both db are not bigger than 100GB then size should not be a huge factor in the decision, replication and sync can be done on changes only and backups should not be a big difference (again this depends on specifics such as when backups are done or if downtime is possible or backups are done during non-peak times) Other than that other factors are: naming collisions in dbo's such as keys, foreign key names, table names, etc. some renaming of tables, store procedures names too.