Stock Market Analytics Zoomcamp - Module 3

Oracle 12c Multitenant Architecture

By: Juan Carlos Olamendy Turruellas

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 USER     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; User created. SQL> GRANT CREATE SESSION TO C##PDB01 CONTAINER=ALL; Grant succeeded.

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:

• System Global Area (SGA) comprises mainly of (it be tuned via SGA_MAX_SIZEparameter):
  • data buffer cache: used to cache the copies of data blocks retrieved from the database. Its size can be tuned viaDB_CACHE_SIZE parameter
  • redo log buffer: used to store redo entries before written into the redo log files. Each redo entry represents images of the changes (transactions) made to the database. The redo logs enable to recover an instance in the event of system failure. Its size can be tuned via LOG_BUFFER parameter
  • shared pool: used to store common structures shared among all users. Its size can be tuned viaSHARED_POOL_SIZEparameter. It´s remarkable to say that if the size of this memory area is insufficient, then the system can enter into performance degradation state. It comprises of
    • library cache: used to store information about commonly executed SQL and PL/SQL statements.
    • data dictionary cache: used to store information about object definitions in the database, such as columns, tables, indexes, users, privileges, etc
• large pool: is an optional memory structure to relieve the load burden on the shared pool such as backup and restore operations as well as bulk operations. Its size can be tuned via LARGE_POOL_SIZE parameter
• Java pool: is an optional memory structure and only required if the instance has JVM installed. It´s used to store information about Java data and code. Its size can be tuned via JAVA_POOL_SIZE parameter
• Program Global Area (PGA) is a memory region containing data and control information for each server process. Each one has its own private PGA created when the server process starts. A server process receives requests from the clients and services them; so when you invoke an application or Oracle tool then Oracle server creates a server process to execute the SQL statements issued by the application

 From the point of view of process, Oracle comprises of:

• User process: represents the execution environment of applications and tools (for example SQLPlus or an application program) on the client-side that sends request to the Oracle instance. The user process communicates with the instance through the server process
• Server process: represents the server-side artifact that receives the requests from the clients and serves them by communicating directly with Oracle instance. It can be configured as dedicated server and shared server:
  • Dedicated server: there is a single server process to serve each client process
  • Shared server: a server process can serve several client processes
  • Background process: represents the artifact that manages the database behind the scene. They can be classified as mandatory and optional
    • Mandatory
      • Process Monitor process (PMON): used to clean up work after failed processes by rolling back transactions, dropping connections and releasing resources
      • System Monitor process (SMON): used to clean up and recover the system after a crash by rolling forward the entries in the redo log files, rolling back uncommitted transactions and opening the database to be used
      • Database Writer process (DBWRn): used to write modified data blocks from the data buffer cache into the data files
      • Log Writer process (LGWR): used to write entries from the redo log buffer into redo log files
      • Checkpoint process (CKPT): used to send a signal to DBWR to write data as well as to update data and control files header when log file switch occurs
• Optional:
  • Recovery process (RECO): used to resolve failures of distributed transactions
  • Archiver process (ARCn): used to copy the redo log files into an archival storage when they are full or a log switch occurs
  • Other processes

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.