As an Oracle database administrator, you’re no stranger to the complexities of multitiered database architectures. One of the most significant innovations in Oracle 12c was the introduction of the multitenant architecture, which enabled the creation of pluggable databases (PDBs) within a container database (CDB). But with this added flexibility comes the challenge of identifying whether your database is a CDB or PDB. In this article, we’ll delve into the world of Oracle multitenancy, exploring the key differences between CDBs and PDBs, and providing you with practical steps to determine which type of database you’re working with.
Understanding the Oracle Multitenant Architecture
Before we dive into the specifics of identifying CDBs and PDBs, it’s essential to understand the basics of the Oracle multitenant architecture. In a traditional Oracle database, each database instance corresponds to a single database. However, with the advent of multitenancy, a single instance can now host multiple, isolated databases. This allows for better resource utilization, simplified database management, and enhanced security.
In a multitenant architecture, the container database (CDB) serves as the top-level database, hosting one or more pluggable databases (PDBs). The CDB acts as a container, providing a centralized management point for the PDBs, which are essentially self-contained databases with their own system resources, tablespaces, and schemas.
Distinguishing Characteristics of CDBs and PDBs
So, how do you know if your database is a CDB or PDB? Let’s explore the key differences between these two types of databases:
CDB Characteristics
- A CDB is the top-most database in a multitenant architecture.
- It acts as a container, hosting one or more PDBs.
- A CDB has its own system resources, such as memory and processes.
- It has a single set of system tablespaces, including the SYSTEM, SYSAUX, and UNDO tablespaces.
- A CDB has a single set of system schemas, including SYS and SYSTEM.
- It is responsible for managing the overall database instance, including startup and shutdown operations.
PDB Characteristics
- A PDB is a self-contained database that resides within a CDB.
- It has its own system resources, such as memory and processes, which are isolated from the CDB.
- A PDB has its own set of tablespaces, including the SYSTEM, SYSAUX, and UNDO tablespaces.
- A PDB has its own set of schemas, including the SYS and SYSTEM schemas, which are isolated from the CDB.
- It is responsible for managing its own database operations, such as creating and managing database objects.
Practical Steps to Identify Your Database Type
Now that we’ve explored the distinguishing characteristics of CDBs and PDBs, let’s move on to the practical steps to identify your database type.
Method 1: Using the Oracle SQL Command Line
One of the simplest ways to determine whether your database is a CDB or PDB is to use the Oracle SQL command line. Follow these steps:
- Open the Oracle SQL command line and connect to your database as a privileged user (e.g., SYS or SYSTEM).
- Execute the following SQL command:
sql
SELECT NAME, CDB FROM V$DATABASE;
The output will display the database name and a Boolean value indicating whether the database is a CDB (YES) or PDB (NO).
Method 2: Using the Oracle Enterprise Manager Cloud Control
If you have Oracle Enterprise Manager Cloud Control installed, you can use the Guided Discovery feature to identify your database type. Follow these steps:
- Log in to Oracle Enterprise Manager Cloud Control as a privileged user.
- Navigate to the Targets tab and select Databases from the drop-down menu.
- Select the database you want to identify from the list of discovered targets.
- Click on the Properties tab and scroll down to the Database section.
- Look for the Multitenant property, which indicates whether the database is a CDB or PDB.
Method 3: Examining the Database Files
Another approach to identifying your database type is to examine the database files. Follow these steps:
- Connect to the database server and navigate to the directory where the database files are stored.
- Look for the presence of the following files:
- ** control01.ctl**: This file indicates the presence of a CDB.
- ** pitching.pdb**: This file indicates the presence of a PDB.
- If you find both files, it’s likely that your database is a CDB hosting one or more PDBs.
Conclusion
In this article, we’ve explored the world of Oracle multitenancy, delving into the key differences between container databases (CDBs) and pluggable databases (PDBs). We’ve also provided you with practical steps to identify whether your database is a CDB or PDB, using the Oracle SQL command line, Oracle Enterprise Manager Cloud Control, and database file examination. By understanding the characteristics of each database type, you’ll be better equipped to manage and optimize your Oracle database environment. Remember, knowing your database type is crucial for effective database administration and optimization.
What is a CDB?
A CDB, or Container Database, is a database that contains one or more pluggable databases (PDBs). It is the top-most level in the Oracle multitenant architecture. A CDB provides a centralized management and administration of the PDBs it contains.
In a CDB, you can create, clone, unplug, and plug PDBs as needed. A CDB can contain up to 252 PDBs, making it an efficient way to manage multiple databases. The CDB also provides features such as unified auditing, patching, and backup and recovery.
What is a PDB?
A PDB, or Pluggable Database, is a portable collection of schemas, objects, and related structures that appear as a separate database to an application. A PDB is created within a CDB and can be easily moved between CDBs.
PDBs provide isolation, management, and resource allocation benefits. Each PDB has its own system identifier (SID) and database name, making it appear as a separate database to an application. PDBs can be easily cloned, unpluged, and plugged into different CDBs, making it a flexible way to manage multiple applications.
How do I identify if my database is a CDB or PDB?
To identify if your database is a CDB or PDB, you can use the V$DATABASE view. The V$DATABASE view provides information about the database, including the CDB and PDB names. You can query the V$DATABASE view using the following SQL command: SELECT CDB, NAME FROM V$DATABASE.
If the query returns a row with a non-null value for the CDB column, then your database is a PDB. If the query returns a row with a null value for the CDB column, then your database is a CDB.
What are the benefits of using a CDB?
One of the main benefits of using a CDB is the ability to manage multiple databases as a single unit. This makes it easier to manage and administer multiple applications, as well as providing a single point of management for backup and recovery, patching, and auditing.
Additionally, CDBs provide a more efficient use of system resources, as multiple PDBs can share the same system resources. This leads to improved performance and reduced costs. CDBs also provide enhanced security features, such as unified auditing and access control.
How do I create a new PDB?
To create a new PDB, you can use the CREATE PLUGGABLE DATABASE command. This command creates a new PDB within an existing CDB. You can also use the DBCA (Database Configuration Assistant) tool to create a new PDB.
When creating a new PDB, you need to specify the PDB name, administrator username and password, and other configuration options such as the storage location and size. Once the PDB is created, you can configure it further by creating schemas, objects, and related structures.
Can I convert a non-CDB to a CDB?
Yes, you can convert a non-CDB to a CDB using the ALTER DATABASE CONVERT TO PLUGGABLE DATABASE command. This command converts an existing non-CDB to a CDB, allowing you to create and manage PDBs within it.
Note that the conversion process requires the database to be in read-only mode, and it may also require additional system resources and downtime. Therefore, it is recommended to carefully plan and test the conversion process before performing it in a production environment.
What are the requirements for creating a CDB?
The main requirement for creating a CDB is that the Oracle Database software must be version 12c or later. Additionally, the system must meet the minimum system requirements for Oracle Database, including sufficient memory, CPU, and storage.
It is also recommended to have a sufficient number of system resources, such as CPUs and memory, to support the CDB and its PDBs. Furthermore, it is recommended to have a robust storage system in place to support the growth of the CDB and its PDBs.