26 February 2013

Oracle Database 12c New Database release.

Oracle Database 12c is scheduled to release quite soon this year according to rumors.

At the end of this Blog the are a few expected new features amongst which a Multi-tenant Database approach, what is a multi-tenant Database, a not really totally new concept as described by Microsoft below:

Multi-tenancy introduction

Trust, or the lack thereof, is the number one factor blocking the adoption of software as a service (SaaS). A case could be made that data is the most important asset of any business—data about products, customers, employees, suppliers, and more. And data, of course, is at the heart of SaaS. SaaS applications provide customers with centralized, network-based access to data with less overhead than is possible when using a locally-installed application. But in order to take advantage of the benefits of SaaS, an organization must surrender a level of control over its own data, trusting the SaaS vendor to keep it safe and away from prying eyes.
To earn this trust, one of the highest priorities for a prospective SaaS architect is creating a SaaS data architecture that is both robust and secure enough to satisfy tenants or clients who are concerned about surrendering control of vital business data to a third party, while also being efficient and cost-effective to administer and maintain.
This is the second article in our series about designing multi-tenant applications. The first article, Architecture Strategies for Catching the Long Tail, introduced the SaaS model at a high level and discussed its challenges and benefits. It is available on MSDN. Other articles in the series will focus on topics such as workflow and user interface design, overall security, and others.
In this article, we'll look at the continuum between isolated data and shared data, and identify three distinct approaches for creating data architectures that fall at different places along the continuum. Next, we'll explore some of the technical and business factors to consider when deciding which approach to use. Finally, we'll present design patterns for ensuring security, creating an extensible data model, and scaling the data infrastructure.

Three Approaches to Managing Multi-Tenant Data
The distinction between shared data and isolated data isn't binary. Instead, it's more of a continuum, with many variations that are possible between the two extremes.

 Data architecture is an area in which the optimal degree of isolation for a SaaS application can vary significantly depending on technical and business considerations. Experienced data architects are used to considering a broad spectrum of choices when designing an architecture to meet a specific set of challenges, and SaaS is certainly no exception. We shall examine three broad approaches, each of which lies at a different location in the continuum between isolation and sharing.

Separate Databases
Storing tenant data in separate databases is the simplest approach to data isolation.


Figure 1. This approach uses a different database for each tenant
Computing resources and application code are generally shared between all the tenants on a server, but each tenant has its own set of data that remains logically isolated from data that belongs to all other tenants. Metadata associates each database with the correct tenant, and database security prevents any tenant from accidentally or maliciously accessing other tenants' data.
Giving each tenant its own database makes it easy to extend the application's data model (discussed later) to meet tenants' individual needs, and restoring a tenant's data from backups in the event of a failure is a relatively simple procedure. Unfortunately, this approach tends to lead to higher costs for maintaining equipment and backing up tenant data. Hardware costs are also higher than they are under alternative approaches, as the number of tenants that can be housed on a given database server is limited by the number of databases that the server can support. (Using autoclose to unload databases from memory when there are no active connections can make an application more scalable by increasing the number of databases each server can support.)
Separating tenant data into individual databases is the "premium" approach, and the relatively high hardware and maintenance requirements and costs make it appropriate for customers that are willing to pay extra for added security and customizability. For example, customers in fields such as banking or medical records management often have very strong data isolation requirements, and may not even consider an application that does not supply each tenant with its own individual database.

Shared Database, Separate Schemas

Another approach involves housing multiple tenants in the same database, with each tenant having its own set of tables that are grouped into a schema created specifically for the tenant.


Figure 2. In this approach each tenant has its own separate set of tables in a common database

When a customer first subscribes to the service, the provisioning subsystem creates a discrete set of tables for the tenant and associates it with the tenant's own schema. You can use the SQL CREATE command to create a schema and authorize a user account to access it. For example, in Microsoft SQL Server 2005:


The application can then create and access tables within the tenant's schema using the SchemaName.TableName convention:

CREATE TABLE ContosoSchema.Resumes (EmployeeID int identity primary key, Resume nvarchar(MAX))

After the schema is created, it is set as the default schema for the tenant account:


A tenant account can access tables within its default schema by specifying just the table name, instead of using the SchemaName.TableName convention. This way, a single set of SQL statements can be created for all tenants, which each tenant can use to access its own data:


Like the isolated approach, the separate-schema approach is relatively easy to implement, and tenants can extend the data model as easily as with the separate-database approach. (Tables are created from a standard default set, but once they are created they no longer need to conform to the default set, and tenants may add or modify columns and even tables as desired.) This approach offers a moderate degree of logical data isolation for security-conscious tenants, though not as much as a completely isolated system would, and can support a larger number of tenants per database server.
A significant drawback of the separate-schema approach is that tenant data is harder to restore in the event of a failure. If each tenant has its own database, restoring a single tenant's data means simply restoring the database from the most recent backup. With a separate-schema application, restoring the entire database would mean overwriting the data of every tenant on the same database with backup data, regardless of whether each one has experienced any loss or not. Therefore, to restore a single customer's data, the database administrator may have to restore the database to a temporary server, and then import the customer's tables into the production server—a complicated and potentially time-consuming task.
The separate schema approach is appropriate for applications that use a relatively small number of database tables, on the order of about 100 tables per tenant or fewer. This approach can typically accommodate more tenants per server than the separate-database approach can, so you can offer the application at a lower cost, as long as your customers will accept having their data co-located with that of other tenants.

Shared Database, Shared Schema

A third approach involves using the same database and the same set of tables to host multiple tenants' data. A given table can include records from multiple tenants stored in any order; a Tenant ID column associates every record with the appropriate tenant.

Figure 3. In this approach, all tenants share the same set of tables, and a Tenant ID associates each tenant with the rows that it owns

Of the three approaches explained here, the shared schema approach has the lowest hardware and backup costs, because it allows you to serve the largest number of tenants per database server. However, because multiple tenants share the same database tables, this approach may incur additional development effort in the area of security, to ensure that tenants can never access other tenants' data, even in the event of unexpected bugs or attacks.
The procedure for restoring data for a tenant is similar to that for the shared-schema approach, with the additional complication that individual rows in the production database must be deleted and then reinserted from the temporary database. If there are a very large number of rows in the affected tables, this can cause performance to suffer noticeably for all the tenants that the database serves.
The shared-schema approach is appropriate when it is important that the application be capable of serving a large number of tenants with a small number of servers, and prospective customers are willing to surrender data isolation in exchange for the lower costs that this approach makes possible.

So now we know what muli-tenancy is, is Microsoft the only one using this concept already? 

Not really also IBM whit is DB2 Database is already ahead of the multi-tenancy game:


Multi-tenancy at the data layer using DB2 can be used in various situations as discussed in the next six cases. Also keep in mind that if you are a small company and want to reduce licensing costs, you can consider using the no-cost version ofDB2: DB2 Express-C. DB2 Express-C does not have any limits for the database size. 

Case 1: Sharing tables

In this case, the following resources are shared:      

  • The database server

  • The DB2 instance

  • The database

  • One or more table spaces

  • One or more tables

Figure below shows an overview of the shared resources in this case. The inventory, customers, and orders tables have information from the clients of the various tenants.


The advantages of this case are that it provides the lowest cost, lowest storage, minimum amount of DB2 licensing, and minimum number of cloud instances needed.

The main disadvantage is that if, for example, one table becomes corrupted, it affects all customers. Also, application complexity might also be added in trying to always determine in your queries which records to retrieve for a given tenant.

Case 2: Sharing a database

In this case, the following resources are shared:

  • The database server
  • The DB2 instance 
  • The database

Figure below shows an overview of the shared resources in this case.



In this case, the benefits are that sharing a database is still relatively low in cost in terms of still using one DB2 license and one cloud instance. Data isolation is good since different set of tables is used. Customization from a data perspective is easier since every tenant gets its own set of tables.

The disadvantages are that more storage is required since you need to create one set of the same tables per tenant. So comparing to case 1, you would be using x times more of storage, where x is the number of tenants. The application complexity is also increased and not as flexible, since now you need to customize your application to handle different table names and potentially different table structure in case there is specific customization for a tenant.

Case 3: Sharing a database and using a different schema name

In this case, the following resources are shared:
  • The database server
  • The DB2 instance
  • The database

Figure below shows an overview of the shared resources in this case.



Under this case, the benefits are that it is still low in cost, almost the same as in case2. You still need one DB2 license and one cloud instance. Data isolation is good because a separate set of tables is used. Compare to case 2; there is less complexity in the application because the SQL statements used can be exactly the same. Redirecting a query to a given set of tables, is done by changing the schema name, by using the SET SCHEMA command. Customizations of a given table will obviously add complexity to your application.

The disadvantage, as in case 2, is that you must still use more storage because you would be creating one set of tables per tenant.

Case 4: Sharing an instance

In this case, the following resources are shared:
  • The database server
  • The DB2 instance
Figure below shows an overview of the shared resources in this case.


In this case, the benefits are that it is still low in cost, almost the same as in case 2.You would still need one DB2 license and one cloud instance. Data isolation is very good because each tenant gets its own database, which in DB2 is an independent unit. Each database can be configured and maintained independently, providing more flexibility. The application complexity is less than in case 1. The table structure for most tables will be the same in all databases. If customization is required for one tenant, the table definition can be altered, but this adds complexity to the application.

The disadvantage is that you will need more storage. Each database in DB2 creates its own catalog, which in other database products is known as the dictionary; therefore, more tables, views, and other database objects from the system must be created. In addition, in the case of DB2, there is a limit of 256 active databases per instance; therefore, under this scenario, only 256 tenants could work concurrently. Another disadvantage is that your memory consumption also increases, which can be troublesome on two fronts:
  • You might reach the memory limit of the DB2 edition you are using, and will have to purchase a more expensive DB2 edition.
  • You might reach the memory limit in your cloud instance, in which case, you will need to choose a more expensive cloud instance.

Case 5: Sharing a database server

In this case, only the database server resource is shared. Figure below shows an overview of the shared resources in this case.


In this scenario, each tenant gets its own DB2 instance. The first benefit is good access control. Application complexity is similar to case 4. However, the system administrator might have to configure connectivity parameters appropriately in all the instances, which can mean more work to do. The table structure for most tables is the same and, as in case 4, for a given tenant, you can customize some tables, but application changes are required. Another benefit is that each instance and database can be maintained independently. If you bring down one instance, it affects only one tenant.

With respect to disadvantages, again, you see more storage required than in other cases, and you can also encounter problems with respect to memory. Although the number of instances in DB2 is limited by the operating system limits, and although starting an instance does not consume a lot of memory, having many instances started with several databases active at the same time might still cause memory problems. As a result, you could be forced to change your DB2 edition to a more expensive one or change your cloud instance to a larger, more expensive one. In addition to these disadvantages, administration complexity will also increase, which might warrant the company hiring more resources and directly affect costs.

Case 6: Sharing a database server with multiple DB2copies

In this case, only the database server resource is shared. Figure below shows an overview of the shared resources in this case.


For the purpose of a SaaS, there are really no benefits in using this approach. In terms of disadvantages, there are several:
  • More copies of the DB2 code must be stored in your cloud instance, taking up space.
  • You have to install and configure DB2 for each copy installed; therefore, there is more administration setup time required.
  • More application complexity exists, and this type of environment can confuse developers as to which database to connect in which instance of which DB2copy.
  • It has similar issues as case 5 for memory and storage consumption. 

In summary, this case has no real benefit, but it has been added in this article for thoroughness.

, and then finally, what is was all about, the new release of Oracle 12c (or 12g, whatever the name will be), can be seen as a Cloud Ready Database with lots of new features, of which a few are mentioned here below, for more accurate list of features I refer to Oracle it self after the release date, somewhere this year the romuors have it.

Oracle 12c new features

Oracle is planning to release its latest version Oracle 12c which would be

a cloud based database management system. Please be aware that Oracle 12c has not been officially announced yet and so no decisions should be made on below features. I have consolidated it from various sources.

1. Pluggable Database 

DBAs will be able create multiple pluggable databases (PDB) that sits
within a single database container. Each PDB will have its own metadata and
private data files. It will be easy to apply patches for all databases by
patching only one container database. Backup and recovery efforts are less
since DBAs can maintain each PDB separately. PDBs are ideal for creating
development and test environments.

2. VARCHAR2 length up to 32767

This one will be one of the best feature for developers who always struggle
to manage large chunk of data. Current version of databases allows only
up to 4000 bytes in a single varchar2 cell. So developers has to either use
CLOB or XML data types which are comparatively slower that varchar2

3. Default value can reference sequences

This is also for developers who struggle to maintain unique values in
Primary Key columns. While creating a table default column can be
referenced by sequence.nextval.

4. Top-N feature

As like MySQL(limit clause), Oracle 12c will allow Top-N feature in
queries. Analytical function improvements.


5. PL/SQL Unit Security

A role can be granted to a code unit, say Procedure,Function,Package etc.

6. With Clause improvement

Functions can be created with SQL having WITH clause.

7. Duplicate Indexes  

Create duplicate indexes on the same set of columns. Like in large
applications you might want two different types of index on the same data.

8. Boolean in SQL

As of Oracle 11g Boolean is not a supported data type in SQL and 12c you can
enjoy this feature.

9. Implicit Result Sets

Ref Cursors can be directly open in side program units. No need to use
Oracle collection to handle ref cursor separately.

10. Transaction Guard

Transaction can be marked as “unknown” where transactions outcome is
not known.  This will safe guard duplicate submission of successful transactions.

Few more features as bullet points. More details yet to come
  •  Asynchronous Global Index maintenance for DROP and TRUNCATE.
  •  Cascade for TRUNCATE and EXCHANGE partition.
  •  Multiple partition operations in a single DDL.
  •  Online move of a partition (without DBMS_REDEFINTIION).
  •  Interval + Reference Partitioning.
  •  Adaptive Execution/statistics Plans.
  •  STATISTICS COLLECTOR steps in the SQL Trace.
  •  Global temporary tables can have “session private statistics”.
  •  Temporary Undo for temporary tables.

For more new features and last minute information I refer to Oracle and form Migration support and Database Maintenance please contact me at:

Drs. Albert Spijkers 
DBA Consulting 
web:           www.dbaconsulting.nl 
blog:           DBA Consulting blog 
profile:       LinkedIn Profile 



  1. If this means anything the following book about 12c is due to be published 22 May 2013, so does that mean 12c will be out about or before then? Time will tell.

    Expert Consolidation in Oracle Database 12c

  2. It is very good blog.I am also looking for this from a long time.Many people like to visit here.I have seen many things over here.It is very good.

    Oracle consultant UK

  3. thanks for share..