Delphix Use Case: Oracle Instance Consolidation

Non-Production Database Server Capacity

Delphix solves the problem of provisioning as many full copy databases as required, however often the next bottleneck is having sufficient database server capacity to run multiple databases concurrently.

This problem is worse when a single application requires a set of databases to be running concurrently.   Now each user who wants to have a private copy of the entire application dataset requires to run multiple database instances.

You can squeeze more database instances on a single server by reducing the database initialization parameters to a minimal specification depending on the intended use.  However this still means that each database has its own SGA and a full set of around 50 background processes.  The Oracle solution to this problem is Oracle 12c Multitenant.  Oracle Multitenant is an effective solution but it is expensive, is quite complicated and requires 12c.

For an excellent comparison of database consolidation options see Tim Hall’s blog article Oracle Database Consolidation Comparison.

A simple and more cost effective alternative to Multitenant is schema consolidation and this is made even easier when schemas are contained in transportable tablespaces.   To create a consolidated instance using transportable tablespaces you start with a stub or empty database and import the required set of tablespaces from the source databases.   Relevant schema objects that are stored in the SYSTEM tablespaces can be copied across using a meta-data only export.  You will have to take care of namespace collisions and realign synonyms and database links but you probably have that capability already for provisioning your UAT or pre-production test environments.

The consolidated database will be tied to a specific Oracle database version however you can transport tablespaces from different platforms, those using a different endian format will require an RMAN conversion step.

Are my tablespaces transportable?

It is quite easy to check if the tablespaces you want to consolidate are transportable.

SQL> EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(

        ts_list => 'TTS1, TTS2',

        incl_constraints => TRUE,

        full_check => TRUE);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected

Often violations are caused by references to objects in other tablespaces.  These can be corrected but would require moving the dependent object. 

This is much easier if you have already ingested all your source databases into Delphix as you can make these changes safely in a Delphix Virtual Database (VDB).

How does Delphix help?

You can build your consolidated database instance using a Delphix Virtual Database (VDB).  You use the same process as you would for a physical copy however when you import into a VDB you immediately get compression.  If you repeat the process you also get de-duplication and version control.  Not only that but you can create virtual database copies from a master consolidated VDB.

As mentioned above dealing with any transportable tablespace rule violations are also easier managed if the source databases have already been virtualized.

What’s the recipe?

The first two steps below are just to allow us to create an empty VDB that we will then populate using transportable tablespaces.

1.     Create a stub or empty Oracle physical database.

2.     Ingest the stub database as a Delphix dSource (stub)

3.     Create a virtual database (vcons) from dSource (stub)

4.     Import transportable tablespace meta data directly into VDB (vcons)

5.     Copy the datafiles that make up the transportable tablespaces directly into Delphix under the appropriate VDB mount point.

For example, if my VDB mount point is /home/delphix/vdb, my vcons VDB datafiles will reside under a folder named /home/delphix/vdb/vcons/datafile.

Delphix compresses the copied datafiles on the way into the Delphix mounted storage.  If the files are being overwritten then deduplication takes place between the file versions.

6.     If you have schema related objects including stored procedures that reside in the source database SYSTEM tablespace, export and import those objects into vcons.

7.     Realign any synonyms and database links.

8.     Run a validation script to make sure everything is in place.

9.     Take a Delphix Snapshot of VDB (vcons) and bookmark it.  This enables labeled versions.

Now you are good to go.  You can configure the SGA for the vcons VDB to an appropriate setup for the intended workload.  You can also create child VDBs of vcons, sharing all the storage and optionally using different SGAs.  If not all child VDBs require access to all the tablespaces in vcons, from a storage perspective it does not matter because the unwanted tablespaces will incur no additional storage, however you can just drop them if desired from the child VDBs without impacting any other copies.

If you are already using Oracle 12c multitenant then Delphix can be used to virtualize your 12c pluggable databases.

Summary

Delphix in combination with Oracle transportable tablespaces can minimize the storage required to provision sets of databases and can also minimize the amount of database server resources required to access those datasets through schema and consequently instance consolidation.