One of our customers is keen to reduce the number of databases they have by merging the schemata of many varying and mostly third-party applications into one (or a few) databases.  Their goal is to reduce operating costs and to use Oracle’s resource management to greater effect for applications using the same server.  However, an architect’s point of view can be very different than that of the operations staff.  Investigating problems, addressing bugs, arranging outages, tuning performance, applying upgrades, backup and recovery, etc, all would be affected by this strategy.  Our team is very aware of the operational realities of database support, and so we warn against schema consolidation as a default practice.

A few years ago, our team won the support of a different client’s Oracle environment.  This client’s previous production DBA had squashed about thirty applications into one database, and we had to deal with all of the issues arising from his legacy.  We documented them as they arose, resulting in a list of reasons to keep applications in separate databases:

Application Separation

Each application must be stored in a dedicated database to make possible:

  1. Running applications with different versions and options (think licence cost) of Oracle.  Each application will have different requirements / certification.
  2. Independent Oracle upgrades or patching.
  3. Independent scheduled database outages (eg parameter change or Oracle patch application) – higher availability, less change request effort.
  4. Different service levels for different databases, eg H/A, RAC, DR etc add cost and complexity, so may not be suitable for all applications.
  5. Independent application recovery or database flashback to a previous point in time.  (Tablespace PITR is possible in some cases.  Restrictions apply, and extra planning and resources are required).
  6. Independent backup – quicker, smaller and more flexible.  (A backup regime for TSPITR is not standard practice, flexible, robust, efficient or a complete backup).
  7. Independent test database refreshes using RMAN, storage (eg SAN BCVs) or OS tools.  (Transportable tablespace might be an option, but not a great one).
  8. Customised and efficient configuration for each application (eg cursor_sharing, character set).
  9. Reduced inter-application contention, (eg same public synonym required, tablespace name, schema name, performance problems).
  10. System testing one application’s change without having to retest other applications (that share the same database).  Eg, if an upgrade script changes performance, or shared memory usage, or recreates a public synonym or schema name or database link or revokes public privileges… will that break another app inhabiting the same DB?
  11. Higher availability by restricting the outages caused by internal errors and corruption to one application.  (Eg what happens when an undo block is corrupted?  Or constant ORA-4031s?  Higher chance with multiple apps in one DB.  DB becomes SPOF – bug, file loss, corruption, latch deadlocks, etc).
  12. Lower data dictionary / fixed view overhead.  A higher number of extents, objects, etc make some regular queries much slower and heavier on resource use.  (Affects DBAs and monitoring tools).
  13. Clarity of which database objects and settings belong to which application, and which interdependencies exist (by way of database links and the privileges of the link users).  Helps when an application is decommissioned, upgraded or migrated separately.
  14. Better security between users of one application and another.  (Some applications may be designed to give the users high database level privileges which could be exploited to access data for other applications in the same database).
  15. Lower server requirements, (smaller databases need less resources, so less powerful servers are required).

Possible Objections / Exceptions to the List Above

  • Less of the list above would be applicable when two applications essentially share the same data set.
  • Custom / in-house applications may be designed to mitigate some of the list above.
  • Some test or development schemata might be able to co-exist in one database when resources are limited and the environments can be recreated from source code or refreshed via export/import or transportable tablespaces.
  • Trivial schemata (one or two simple tables per application with little data or change) may be considered a low risk to merge into one database.
  • There will be some administration and resource overhead to having multiple databases instead of one.  Although, some management of a merged database will be more difficult – eg troubleshooting and performance tuning/investigation.  (Lots of sessions, segments, SQL etc to filter).
  • RAC allows scaling out instead of scaling up, so multiple smaller servers can still be used for a large database.  (Although RAC incurs higher costs, overhead and complexity).
  • For RAC databases, using services to partition the workload could mitigate some of the instance level contention and configuration issues.  If an instance is guaranteed, even during failover, to be exclusive to one application, then that instance could have customised initialisation parameters.
  • Using Oracle resource manager can address some of the performance / contention issues, but not all of them.  Eg, it only kicks in when CPU is maxed out, only controls foreground processes and it doesn’t manage all resources (I/O, shared pool, latches).

Feedback Welcome

We don’t usually accept comments, but this time I’m seeking feedback.  Are there any challenges or benefits you’ve found by consolidating schemata into one or a few databases?  Do you think recent database features assisted to make this strategy more successful?  Is anything missing from the list?


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s