Oracle Database – Pluggable Database

Oracle has been developing database to be more resilient with pluggable database.

Following are few notable features that Oracle cites:

  • Plugging In to the CloudOracle Database 12c, released in 2013, was designed for the Cloud, featuring a new Multitenant architecture, In-Memory column store, and support for JSON documents. Oracle Database 12c helped DBAs make more efficient use of their IT resources, while continuing to reduce costs and improve service levels for end users.
  • Integration and memory performanceOracle Database 18c simplified integration with directory services such as Microsoft Active Directory. It also introduced functionality to exploit memory not only for columnar data models but also for high speed row access. 
  • Enhanced stabilityOracle Database 19c is the long-support version of the Oracle Database 12c (Release 12.2) family of products. A major focus of this release was stability. Oracle Database 19c also introduces several small but significant improvements to features such as JSON and Active Data Guard.

An Oracle database server consists of a database server/engine and at least one database instance, commonly referred to as simply an instance. 

Because an instance and a database engine are so closely connected, the term Oracle database is sometimes used to refer to both instance and database engine. In the strictest sense the terms have the following meanings:

  • Database: A database is a set of files, located on disk, that store data. These files can exist independently of a database instance. This drives the engine of the database instance.
  • Database instance: An instance is a set of memory structures that manage database files. The instance consists of a shared memory area, called the system global area (SGA), and a set of background processes. An instance can exist independently of database files.

A database is an engine/platform and the instance is an application on the platform. Thus, the new architecture allows to have multiple instances on the same platform. The platform allows a multitenancy. The multitenant architecture enables an Oracle database to be a multitenant container database (CDB).

A non-CDB is a traditional Oracle database that cannot contain Pluggable DBs (PDB). A CDB is a single physical database that contains zero, one, or many user-created pluggable databases. A pluggable database (PDB) is a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle Net client as a non-CDB.

The multitenant architecture solves a number of problems posed by the traditional non-CDB architecture. Large enterprises may use hundreds or thousands of databases, often running on different platforms on multiple physical servers. Modern servers are able to handle heavier workloads than before. A database may use only a fraction of the server hardware capacity. This approach wastes both hardware and human resources.

By consolidating multiple physical databases on separate computers into a single database on a single computer, the multitenant architecture provides the following benefits:

  • Cost reduction for hardware
  • Easier and more rapid movement of data and code
  • Easier management and monitoring of the physical database
  • Separation of data and code
  • Separation of duties between a PDB administrator, who manages only the PDBs to which she or he is granted privileges, and the CDB administrator, who manages the entire CDB

Benefits for manageability include:

  • Easier upgrade of data and code by unplugging and plugging in PDBs
  • Easier testing by using PDBs for development before plugging them in to the production CDB
  • Ability to flash back an individual PDB to a previous SCN
  • Ability to set performance limits for memory and I/O at the PDB level
  • Ability to install, upgrade, and manage a master application definition within an application container, which is a set of PDBs plugged in to a common application root

Sharding Architecture

Sharding is a database scaling technique based on horizontal partitioning of data across multiple databases. Applications perceive the pool of databases as a single logical database.

Key benefits of sharding for OLTP applications include linear scalability, fault containment, and geographical data distribution. Sharding is well suited to deployment in the Oracle Cloud. Unlike NoSQL data stores that implement sharding, Oracle Sharding provides the benefits of sharding without sacrificing the capabilities of an enterprise RDBMS.

In a sharding architecture, each database is hosted on a dedicated server with its own local resources – CPU, memory, flash, or disk. Each database in such configuration is called a shard. All of the shards together make up a single logical database, which is referred to as a sharded database.

Horizontal partitioning involves splitting a database table across shards so that each shard contains the table with the same columns but a different subset of rows. A table split up in this manner is also known as a sharded table.

Instance Memory Structures 

Oracle Database creates and uses memory structures for program code, data shared among users, and private data areas for each connected user.

The following memory structures are associated with a database instance:

  • System Global Area (SGA)The SGA is a group of shared memory structures that contain data and control information for one database instance. Examples of SGA components include the database buffer cache and shared SQL areas. Starting in Oracle Database 12cRelease 1 (12.1.0.2), the SGA can contain an optional In-Memory Column Store (IM column store), which enables data to be populated in memory in a columnar format.
  • Program Global Areas (PGA)A PGA is a memory region that contains data and control information for a server or background process. Access to the PGA is exclusive to the process. Each server process and background process has its own PGA.

The following figure shows a sharded table horizontally partitioned across three shards.

References