Thursday, February 28, 2019

All you want to know about CDBs , PDBs and multitenant databases

Oracle Multitenant Architecture: CDBs, PDBs, and Key Features

Oracle's Multitenant Architecture, introduced in Oracle Database 12c, fundamentally changes how databases are managed by allowing multiple Pluggable Databases (PDBs) to reside within a single Container Database (CDB). This architecture provides significant benefits for consolidation, agility, and resource management.

What are CDBs and PDBs?

  • Container Database (CDB): From a Database Administrator's (DBA) point of view, the CDB is the single, overarching database that contains all the PDBs. It includes common Oracle metadata, background processes, and shared memory (SGA).

  • Pluggable Database (PDB): From an application's point of view, a PDB is an independent, fully functional database. Applications connect to a PDB as if it were a traditional non-CDB. Many PDBs can be plugged into a single Multitenant CDB.

Why Use Multitenant Databases?

The multitenant architecture offers numerous advantages:

  • Rapid Provisioning (Via Clones): New PDBs can be quickly provisioned by cloning existing PDBs or the PDB$SEED, drastically reducing deployment time.

  • Online PDB Relocate: PDBs can be relocated between CDBs (or within the same CDB) while remaining online, minimizing downtime.

  • Sharing Background Processes: All PDBs within a CDB share the same set of background processes (e.g., PMON, SMON, DBWn), reducing the overall process footprint.

  • Sharing SGA: PDBs share the same System Global Area (SGA) of the CDB, leading to more efficient memory utilization.

  • Minimize CAPEX and OPEX: By consolidating multiple databases onto a single CDB, hardware resource requirements (CAPEX) and operational overhead (OPEX) are significantly reduced.

  • Single Backup for CDB: A single backup operation for the CDB protects all contained PDBs, simplifying backup strategies.

  • Automatically Standby: All PDBs within a CDB are automatically protected by a single Data Guard standby database configured for the CDB.

  • Perform Rapid Upgrades: Upgrades are performed at the CDB level, meaning all PDBs are upgraded simultaneously, streamlining the upgrade process.

  • Reference Data in a Different CDB: PDBs can access data in other PDBs or even other CDBs using database links, facilitating data sharing.

  • Isolate Grants within PDBs: Security grants and user management can be isolated within individual PDBs, enhancing security and simplifying administration.

  • Refreshable Clone: A read-only clone of a PDB that can periodically synchronize with its source PDB, useful for reporting or testing environments.

  • Snapshot Copy PDB: A point-in-time copy of a PDB. Note that this PDB cannot be unplugged from the CDB root or application root.

Multitenant Database Structure

  • The System Container: This includes the CDB$ROOT (the root container) and all PDBs directly plugged into the CDB$ROOT.

  • CDB$ROOT: This is the mandatory root container of a CDB. It stores Oracle system metadata only and is exactly one per CDB.

  • PDB$SEED: This is a system-supplied template PDB within CDB$ROOT. It cannot be dropped and serves as the source for creating new PDBs.

  • Application Container: An application container consists of exactly one application root and the application PDBs plugged into this root. It allows for managing a set of related PDBs as a single application.

  • Application Root: The parent container for application PDBs within an application container.

  • Application PDB: If a PDB belongs to an application container, then it is an application PDB.

  • Application Seed: An optional application PDB within an application root, serving as a template for creating new application PDBs.

Administration Roles

Different administrative roles exist to manage the various containers:

  • CDB Administrator: Manages the entire CDB, including CDB$ROOT and all PDBs.

  • Application Container Administrator: Manages a specific application container, including its application root and application PDBs.

  • Application PDB Administrator: Manages PDBs within a specific application container.

  • PDB Administrator: Manages PDBs that are not part of an application container (i.e., directly plugged into CDB$ROOT).

Key Concepts

  • SYS User: SYS is a common user in the CDB. Every PDB is conceptually "owned" by SYS in that it's the superuser within each PDB.

  • Cross-PDB Access: By default, a user connected to one PDB must use database links to access objects in a different PDB.

  • PDB Lifecycle:

    • To open a PDB, you must start its respective service.

    • To stop a PDB: ALTER PLUGGABLE DATABASE PDB_NAME CLOSE IMMEDIATE;

  • PDB$SEED Contents: Contains standard Oracle schemas and objects, including SYSTEM, SYSAUX, TEMP, and UNDO tablespaces, serving as a clean base for new PDBs.

Useful Commands

  • Show current container name:

    show conn_name

  • List all containers (CDBs and PDBs):

    SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

  • View common users in the CDB:

    SELECT con_id, username, default_tablespace, common FROM cdb_users;

  • List all PDBs:

    SELECT PDB_NAME FROM DBA_PDBS;

How Hot Clones Work

When performing a hot clone of a PDB (where the source PDB remains open and available), Oracle ensures data consistency by:

  • Applying Redo: The cloning process applies redo logs from the source PDB to catch up the cloned PDB to the point-in-time when the clone operation started.

  • Applying Undo: It then applies undo information to roll back any uncommitted transactions that were active on the source PDB at the time of cloning, ensuring the cloned PDB is transactionally consistent.

PDB/CDB New Features (12.2, 18c, 19c)

Oracle continuously enhances the multitenant architecture with each release:

Oracle Database 12.2 Enhancements

  • Hot Clone a Remote PDB or Non-CDB: Allows cloning a PDB or non-CDB from a remote database over a database link while the source is online. The source must use local undo mode.

  • Relocate PDB: Enables moving a PDB from one CDB to another while keeping the PDB online during most of the operation.

  • PDB Archive Files (.pdb): Introduces self-contained .pdb archive files that bundle both the PDB's XML manifest and its datafiles, simplifying PDB transport.

  • PDB Refresh: Allows creating a refreshable PDB clone that can be periodically updated from a remote source PDB, with options for manual or auto-refresh.

  • Proxy PDB: Creates a logical pointer to a PDB in a remote CDB, allowing local access to a remote PDB without actually moving its data. Benefits include unchanged client connections, a single entry point, and sharing an application root.

  • AWR for Pluggable Database: Enables granular AWR data collection at the PDB level, providing performance insights for individual PDBs. (ALTER SYSTEM SET awr_pdb_autoflush_enabled=TRUE;)

Oracle Database 18c Enhancements

  • Snapshot Carousel (PDB Archives): A repository for periodic point-in-time copies of a PDB, enabling easy recovery or cloning to a specific point in time. PDBs can be created with SNAPSHOT MODE EVERY X HOURS or manually with ALTER PLUGGABLE DATABASE SNAPSHOT.

  • Transportable Backups: Supports using backups performed on a PDB prior to it being unplugged and plugged into a new container, facilitating agile PDB relocation without requiring immediate pre/post-move backups.

  • Switchover Refreshable Clone PDB between CDBs: Allows for planned or unplanned switchover of a refreshable clone PDB between different CDBs, enabling PDB migration with minimal downtime.

  • Transient No-Standby PDBs (Clone): Allows creating a hot clone of a PDB without it being replicated to a standby database, useful for temporary testing or development.

Oracle Database 19c Enhancements

  • Real-time Statistics (Exadata Only): Extends online statistics gathering to include conventional DML statements, collecting statistics "on-the-fly" during INSERT, UPDATE, and MERGE operations. (Requires Exadata).

  • High-Frequency Automatic Optimizer Statistics: Allows for more frequent, granular collection of optimizer statistics, improving the accuracy of execution plans.

  • Validate SPFILE Parameters: New command to validate SPFILE parameters, useful for consistency checks, especially in Data Guard environments.

  • Schema Only Accounts: While existing from 9i, their use is increasingly emphasized for security best practices in multitenant environments, restricting direct login to schema owners.

  • Automatic Replication of Restore Points: Restore points created on the primary database are automatically replicated to the standby database via the redo stream.

  • DML Operations on Active Data Guard Standby Databases: Enhances DML redirection, allowing DML to be performed on a read-only standby by transparently redirecting it to the primary (requires adg_redirect_dml=TRUE and username/password connection).

  • Inline External Table (EXTERNAL clause): Allows defining external tables directly within a SQL query, eliminating the need for separate DDL statements ("Zero DDL").

This comprehensive overview should provide a solid understanding of Oracle's Multitenant Architecture and its evolution across recent database releases.


PDB/CDB new features (12.2, 18c, 19c) 





Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)