By: Juan Carlos Olamendy Turruellas
In this article, I want to talk about the architecture of the last version of Oracle database (version 12c) and in particular on the biggest architectural change: multitenant support. For the examples below, I will use the following box: Oracle database 12c running inside CentOS 6.4.
This new approach of Oracle (multitenant architecture) is also present on the version name 12c where the c letter stands for cloud. Let´s remind that the pillar of cloud computing is virtualization, where several virtual machines runs concurrently all hosted in physical machines deployed on the Web (the cloud). Each virtual machine has its own resources and it´s also independent of the host physical machine. And the good news is that the new concepts around the multitenant architecture of Oracle 12c are similar to virtualization, and they are: container database (CDB) and pluggable database (PDB) or a tenant. Doing some analogy, the CDB is like the physical host machine while the PDB is like virtual machines. Each PDB is independent each other; however all PDBs share the SGA memory and the background and foreground (user processes) processes as well as the data dictionary information (metadata), common roles, common users and common privileges. A PDB is used basically to encapsulate a sub-set of tables and indexes as well as local roles, local users and local privileges. Unlike the old Oracle databases (pre-12.1c version) where only one database can be mounted by an instance, the multitenant architect enable mount multiple databases (CDB) by an instance.
When Oracle database software is installed and it´s execute the SQL statement CREATE DATABASE, then the CDB is created. You can find out if the database is a CDB or PDB using the following statement:
SQL> SELECT NAME, CDB, CON_ID FROM V$DATABASE;NAME CDB CON_ID--------- --- ----------
CONDB YES 0
SQL> SHOW PARAMETER enable_pluggable_database
NAME TYPE VALUE------------------------------------ --------enable_pluggable_database boolean TRUE
After that, the DBA can create new PDB within this CDB as shown below:
SQL> CREATE PLUGGABLE DATABASE cdb01_pdb01
admin IDENTIFIED BY password
FILE_NAME_CONVERT = ('/pdbseed/', '/cdb01pdb01/');
SQL> ALTER PLUGGABLE DATABASE cdb01_pdb01 OPEN;
As well, the DBA can drop a PDB as shown below:
SQL> ALTER PLUGGABLE DATABASE cdb01pdb01 CLOSE;
SQL> DROP PLUGGABLE DATABASE cdb01pdb01 INCLUDING DATAFILES;
As you can see in order to modify the PDB the SQL statement is ALTER PLUGGABLE DATABASE while for the CDB the SQL statement is changed for ALTER DATABASE.
Another important thing to remember is that the CDB has common users (name convention indicates to start with C## or c## and to contain ASCII characters), and a common user can login to a PDB if he has the CREATE SESSION privilege on that PDB as shown bellow:
SQL> CREATE USER C##PDB01 IDENTIFIED BY PDB01;
SQL> GRANT CREATE SESSION TO C##PDB01 CONTAINER=ALL;
Now let me show the logical view of the Oracle 12g multitenant architecture.
The architecture is basically similar as the traditional architecture in the sense that we have two main sub-system: the database (collection of data stored on physical files and disks) and the instance (memory structure + OS processes). The key difference is how the application data is separated and distributed through different PDBs and the share data and metadata resides in CDB.
When Oracle software is started on the server node, then it launches several background processes and allocates a memory area (System Global Area - SGA) shared across the processes. The combination of SGA and processes is called an instance. An instance can exist without a database although it´s no useful. The instance has a unique identifier or SID. The relationship between database and instance is basically that a database may be mounted and opened by many instances, and traditionally an instance may mount and open a single database (with multitenant architecture may mount and open multiple databases) at any point of time; so the SGA and processes inside the instance are the mechanisms to access and manage the database.
Going deeper in the elements of the architecture, we have memory and process architectural elements.
From the point of view of memory, Oracle comprises of:
From the point of view of process, Oracle comprises of:
In order to understand the concepts, let´s see some practical examples.
Using the ps command, we can see all processes running under the oracle user. There are no Oracle database processes at this point.
As well, there is no shared memory associated to the oracle user.
Then we run SQL*Plus and connect as sysdba. The connection is successful and SQL*Plus reports that there is an idle instance.
Now there is only a server process (with 4043 process id in the figure below) connected to SQL*Plus. There is no shared memory and no background processes, in short, there is no running instance yet.
Next step, let´s start the instance as show below.
In this moment, I have an instance running and the ORCL database mounted on this instance. The instance comprises the SGA and the required background processes.
If you want to see information about the SGA, let´s do it with the following SQL statements.
You can check the SID and name for the current database.
Now let´s talk how Oracle database organizes the data.
At the logical level, there is the following hierarchy: tablespace, segment, extent and data block as shown in the following logical view.
A tablespace is logical space for storing database objects such as tables, indexes, sequences, clusters, and others. A database must consist of at least two tablespaces: SYSTEM and SYSAUX. There are three type of tablespaces: permanent, undo and temporary tablespaces. A tablespace consists of one or more segments.
A segment is used to store the same type of database objects, that is, every table is stored in his own segment (named Data Segment), and every index is stored in his own segment (named Index Segment), and so on. There are eleven type of segments: table, table partition, index, index partition, cluster, rollback, deferred rollback, temporary, cache, lobsegment, lobindex. A segment consists of one or more extents.
An extent consists of one or more contiguous data blocks. Spaces for a data on physical files is allocated through extents.
A data block is the smallest unit of storage in Oracle database. Data are actually stored in blocks. A data block consists of one or more physical blocks (OS block), so we need to define the data block size as a multiple of the OS block size. You can set the data block size through the DB_BLOCK_SIZE at the moment the database is created and cannot be changed.
At the physical level (files inside the OS filesystem), the common types of files are: data files, redo log files and control files.
The data file stores data objects (segments) for a single tablespace, but a tablespace can be distributed into several data files. The redo log file store the entry for redo log (or transactions) committed by the database. And the control file stores information about the physical structure of the database. The control file is very important for the database operation.
In this article, I´ve explained the architecture of Oracle database 12c where we can see the new trends as well as analyze the principal architectural elements.
Multitenant Architecture in oracle rare to find a solution.I really appreciate this blog men.