Oracle Database 12c Deployment Options

Introduction

Oracle Database 12c Multitenant Architecture is definitely the highlight feature of Oracle 12c. In this article we will review the Deployment Options for Oracle Database 12: Multitenant, Single Tenant, and Non-CDB.

Multitenant

Multitenant is the new paradigm in Oracle 12c which allows managing multiple Pluggable Databases in a single instance (i.e. Single Instance configuration) or multiple Pluggable Databases with multiple instances (i.e. RAC configuration). This is an extra cost option which is available only for Enterprise Edition Databases. In order to choose the Multitenant Option, check the "Create As Container Database" option in DBCA, as follows:

Single Tenant

Single Tenant is similar to the Multitenant in terms of the architecture and pluggable databases capabilities such as unplug/plug and PDB cloning options; however, it allows having only one single Pluggable database. This option does not require the extra cost license, as opposed to the Multitenant option. 

Non-CDB

Non-CDB basically means the old pre-12c architecture. In this option there is only one single Database with one single instance (i.e. Single Instance configuration), or one single Database with multiple instances (i.e. RAC configuration). In either case, Single Instance or RAC, there is only one single Database. The concept of Pluggable Databases is irrelevant and the 12c PDB options such as unplug/plug and clone are not available. In order to use the Non-CDB option, uncheck the "Create As Container Database" option in DBCA, as follows:

Single Tenant vs. Non-CDB

In case that you are interested in upgrading to Oracle Database 12c, but you are not interested in using the Multitenant option, you may ask yourself whether you should use Single Tenant or Non-CDB. Personally, I highly recommend on adopting  the Single Tenant due to the following reasons:

• Unplug/Plug - With Single Tenant you could unplug your PDB and plug it into a different CDB which could simplify upgrades and migrations in many cases.
• Fast Cloning - With Single Tenant you could fast clone your PDB to a different CDB which could simplify data migrations in many cases.
• Deprecation of Non-CDB Architecture - As per Oracle official documentation "The non-CDB architecture is deprecated in Oracle Database 12c, and may be desupported and unavailable in a release after Oracle Database 12c Release 2. Oracle recommends use of the CDB architecture.". This means that in the future we will all have to use either Single Tenant or Multitenant, so I believe it is better to get the knowledge and skills for managing Pluggable Databases as soon as possible.

Summary

I've created the following diagram which covers the 3 deployment options in Oracle Database 12c.

Additional Resources

• Deprecation of Non-CDB Architecture

• Oracle Multitenant (White Paper)

• Introduction to the Multitenant Architecture (Oracle Documentation)

Getting free space ORA errors even though tablespace has enough free space

Introduction

Recently I've seen an interesting question in the OTN discussions forums. 

The question was - Why do I get free space errors (such as ORA-01654) even though tablespace has enough free space. Here is the link to the thread: https://community.oracle.com/message/13869346#13869346

While this may sounds like a weird and complex scenario -  it is actually a pretty common issue and simple to explain.

Background

When Oracle needs to allocate more space for a segment (such as index segment, table segment, etc.) it doesn't allocate a single Oracle Block for the extra space, but rather an extent which is a logical unit of database storage space allocation made up of a number of contiguous data blocks.

When there is no more enough free space in the database blocks, and Oracle needs to allocate, it has to allocate an extent. The size of the extent depends on the tablespace storage configuration which could have either uniform extents sizes where each new extent has the same size, or system-managed extents sizes where Oracle determines the optimal size of additional extents and the extent sizes may vary (Read more about this here: http://docs.oracle.com/cd/B28359_01/server.111/b28318/logical.htm#i19599)

Solving The Mystery

The total free space in a tablespace might be large enough for allocating additional extent; however, in some cases where the tablespace is fragmented, the largest contiguous extent might be smaller than the next extent size. 

In order to obtain the next extent size, it is possible to query the NEXT column in the DBA_SEGMENTS dictionary view, as follows:

SQL> CREATE TABLE large_extent_tbl
(
   id   NUMBER (20)
)
STORAGE (NEXT 10M)
TABLESPACE TEST;  
Table created.

SQL> insert into large_extent_tbl values (1);

1 row created.

SQL> SELECT next_extent/1024/1024
  FROM dba_segments
 WHERE segment_name = 'LARGE_EXTENT_TBL';

NEXT_EXTENT/1024/1024
---------------------
                   10

As you can see I created the table with a NEXT EXTENT definition of 10 which is larger than the largest contiguous extent in the tablespace. In order to obtain the largest contiguous extent in the tablespace we can query DBA_FREE_SPACE as follows:

SQL> SELECT ROUND(SUM (bytes) / 1024 / 1024) "total_free_space (MB)",
       ROUND(MAX (bytes) / 1024 / 1024) "largest_contiguous_extent (MB)"
  FROM dba_free_space
 WHERE tablespace_name = 'TEST' ; 
total_free_space (MB) largest_contiguous_extent (MB)
--------------------- ------------------------------
                  128                              6

SQL>

As you can see, the largest contiguous extent is 6MB which is smaller than the NEXT extent for the segment (which is 10MB).

Additional Resources

• AskTom - How to Find tablespace Fragmentation
• MOS DOC ID 162994.1 - Script to Report Extents and Contiguous Free Space