The Geek Diary

Oracle Multitenant: How to Create CDB and PDB

by

Creating a CDB

The steps required to create a new CDB, using either DBCA or SQL*Plus, are the same.

  • The first step, as for any database, non-CDB or CDB, consists of configuring an instance with an init.ora parameter file and then starting the instance.
  • The second step is the creation of the CDB using the CREATE DATABASE command with a new clause ENABLE PLUGGABLE DATABASE specifying that the database is a multitenant container database and not a non-CDB. The operation creates the controlfiles during the mount phase, the redo log files, and CDB root datafiles during the open phase. The CDB root datafiles are used for the SYSTEM tablespace containing the Oracle-supplied metadata and data dictionary, the SYSAUX tablespace for AWR, and the UNDO tablespace. It also creates the CDB seed with its own datafiles used for the SYSAUX, SYSTEM and UNDO tablespaces. You may use the new clause SEED FILE_NAME_CONVERT to define the location of the datafiles of the CDB seed pluggable database. The clause creates the CDB seed. The CDB seed datafiles can be used as templates for future PDBs creation. If you omit this clause, Oracle Managed Files determines the names and locations of the CDB seed’s files.
  • The third step is the creation of the catalog with the execution of the $ORACLE_HOME/rdbms/admin/catcdb.sql script connected to the CDB root.

Creating a CDB: Using SQL*Plus

Below are the detailed steps to create a new CDB using SQL*Plus.

1. Before starting the instance, create an init[sid].ora parameter file with the parameters: DB_NAME, CONTROL_FILES if OMF is not used, and DB_BLOCK_SIZE. The global database name of the CDB root is the global database name of the CDB. The ENABLE_PLUGGABLE_DATABASE parameter set to TRUE is required to define that the instance is ready for a CDB and not a non-CDB creation. You can also use the MAX_PDBS parameter to limit the number of PDBs in the CDB. Set the ORACLE_SID environment variable. Launch SQL*Plus, connect as an OS authenticated user belonging to the DBA OS group, and execute the STARTUP NOMOUNT command. 

SQL> CONNECT / AS SYSDBA
SQL> STARTUP NOMOUNT

If you are using Oracle ASM storage to manage your disk storage, then you must start the Oracle ASM instance and configure your disk groups before performing the next steps.

2. Use the CREATE DATABASE command with the clause ENABLE PLUGGABLE DATABASE to create a CDB and not a non-CDB. The command creates the CDB root and the CDB seed. You can use the clause SEED FILE_NAME_CONVERT to specify the location of the CDB seed’s files. If you omit the clause, OMF determines the names and locations of the CDB seed’s files. The FILE_NAME_CONVERT specifies the source directory of the CDB root datafiles and the target CDB seed directory. Omit the clause SEED FILE_NAME_CONVERT if you use the new init.ora parameter PDB_FILE_NAME_CONVERT, mapping names of the CDB root datafiles to the CDB seed datafiles. The directories must exist. The character set defined is the single one for the CDB.

SQL> CREATE DATABASE cdb1 ENABLE PLUGGABLE DATABASE ...
SEED FILE_NAME_CONVERT = ('/oracle/dbs','/oracle/seed');

3. Run the catcdb.sql SQL script to build views on the data dictionary tables and install standard PL/SQL packages in the CDB root. You can also execute the catalog.sql and catproc.sql SQL scripts and all other SQL scripts related to the options installed.

New Clause: SEED FILE_NAME_CONVERT

What is new compared to the non-CDB CREATE DATABASE statement? You can find an example of a full CREATE DATABASE statement below:

SQL> CREATE DATABASE cdb1
     USER SYS IDENTIFIED BY p1 USER SYSTEM IDENTIFIED BY p2
     LOGFILE GROUP 1 ('/u01/app/oradata/CDB1/redo1a.log',
                      '/u02/app/oradata/CDB1/redo1b.log') SIZE 100M,
             GROUP 2 ('/u01/app/oradata/CDB1/redo2a.log',
                     '/u02/app/oradata/CDB1/redo2b.log') SIZE 100M
     CHARACTER SET AL32UTF8 NATIONAL CHARACTER SET AL16UTF16
     EXTENT MANAGEMENT LOCAL DATAFILE
                      '/u01/app/oradata/CDB1/system01.dbf' SIZE 325M
     SYSAUX DATAFILE '/u01/app/oradata/CDB1/sysaux01.dbf' SIZE 325M
     DEFAULT TEMPORARY TABLESPACE tempts1
           TEMPFILE '/u01/app/oradata/CDB1/temp01.dbf' SIZE 20M
     UNDO TABLESPACE undotbs
           DATAFILE '/u01/app/oradata/CDB1/undotbs01.dbf' SIZE 200M
     ENABLE PLUGGABLE DATABASE
     SEED FILE_NAME_CONVERT =('/u01/app/oradata/CDB1','/u01/app/oradata/CDB1/seed');

The first important clause required if you want the database to be a multitenant container database is ENABLE PLUGGABLE DATABASE clause correlated with the ENABLE_PLUGGABLE_DATABASE initialization parameter set to TRUE, and one way to declare the directory for the CDB seed datafiles is to use the SEED FILE_NAME_CONVERT clause. The FILE_NAME_CONVERT specifies the source directory of the CDB root datafiles and the target CDB seed directory.

The /u01/app/oradata/CDB1 CDB root directory and the /u01/app/oradata/CDB1/seed CDB seed directory must exist.

New Clause: ENABLE PLUGGABLE DATABASE

Oracle Managed Files

If you do not use explicit datafile names, use Oracle Managed Files (OMF) – Set the DB_CREATE_FILE_DEST instance parameter with the value of the destination directory of the datafiles of the SYSTEM, SYSAUX, UNDO, and USERS tablespaces specified in the statement. Oracle chooses default sizes and properties for all datafiles, control files, and redo log files. In the first example, the /u01/app/oradata directory must exist. If you use Oracle ASM storage, you can set DB_CREATE_FILE_DEST to +data, where +data would be a preconfigured ASM disk group. For example:

SQL> CONNECT / AS SYSDBA
SQL> STARTUP NOMOUNT
SQL> CREATE DATABASE cdb2
     USER SYS IDENTIFIED BY p1 USER SYSTEM IDENTIFIED BY p2
     EXTENT MANAGEMENT LOCAL
     DEFAULT TEMPORARY TABLESPACE temp
     UNDO TABLESPACE undotbs
     DEFAULT TABLESPACE users
     ENABLE PLUGGABLE DATABASE;

PDB_FILE_NAME_CONVERT Instance Parameter

If you do not use the SEED FILE_NAME_CONVERT clause, use a new instance parameter – The PDB_FILE_NAME_CONVERT instance parameter maps names of existing files (the root datafiles in your case) to new file names (the seed datafiles in this case). In the example, both /u02/app/oradata/CDB1 and /u02/app/oradata/seed directories must exist. For example:

PDB_FILE_NAME_CONVERT = '/u02/app/oradata/CDB2','/u02/app/oradata/seed'

After CDB Creation: What’s New in CDB

After the CDB is created, there are new CDB components and objects such as:

  • Two containers: The CDB root and the CDB seed (maximum number of PDBs: 4096)
  • As many services as containers: The service name for the root container is the CDB name given at the CDB creation concatenated with the domain name. Each new PDB is assigned a service name: the service name is the PDB name given at PDB creation concatenated with the domain name. If you create or plug a PDBtest PDB, its service name would be PDBtest concatenated with the domain name. You can find all service names maintained in a CDB in the CDB_SERVICES view. To connect to the CDB, you connect to the root, using either local OS authentication or the root service name. For example, if you set the ORACLE_SID to the CDB instance name and use CONNECT / AS SYSDBA, you are connected to the root under the common SYS user granted system privileges to manage and maintain all PDBs. To connect to the desired PDB, use either easyconnect or the tnsnames.ora file. For example, CONNECT username/password@net_service_name.
  • Common users: SYS, SYSTEM, created in all containers, the root, and the seed
  • Common privileges granted to all users in all containers
  • Predefined common roles in all containers, the root, and the seed
  • Tablespaces: SYSTEM, SYSAUX associated with each container (maximum number of datafiles:65534)

Data Dictionary Views: DBA_xxx

For backward-compatibility, DBA views show the same results in a PDB as in a non-CDB. For example, the DBA_OBJECTS view shows the objects that exist in the PDB from which you run the query.

  • In the root, DBA_xxx views only show objects contained in the root.
  • In a PDB, the DBA_xxx views only show objects contained in the PDB.

Examples of DBA views:

  • While connected to the root, you query DBA_USERS. You get the list of common users created from the root (in the root, only common users exist).
  • While connected to a PDB, you query DBA_USERS. You get the list of users, common and local, of the PDB.

DBA dictionary views providing information within PDB:

SQL> SELECT table_name FROM dict WHERE table_name like 'DBA%';
  • DBA_tablespaces: All tablespaces of the PDB
  • DBA_data_files: All datafiles of the PDB
  • DBA_tables: All tables in the PDB
  • DBA_users: All common and local users of the PDB

Data Dictionary Views: CDB_xxx

In a CDB, for every DBA_* view, a CDB_* view is defined.
– In the CDB root, CDB views can be used to obtain information about tables, tablespaces, users,privileges, parameters, PDBs, and other types of objects contained in the CDB root and all open PDBs.
– In a PDB, the CDB_* views show objects visible through a corresponding DBA_* view only.

In addition to all the columns found in a given DBA_* view, the corresponding CDB_* view also contains the CON_ID column, which identifies a container whose data a given CDB_* row represents. In a non-CDB, the value of a CON_ID column is 0. In a CDB, the value can be either 1 used for rows containing data pertaining to the CDB root only or n where n is the applicable container ID.

Examples of CDB views:

  • Connected to the CDB root and querying CDB_USERS, you get the list of users, common and local, of each container.
  • Connected to a PDB and querying CDB_USERS or DBA_USERS, you get the same list of users, common and local, of the PDB.
  • Connected to the CDB root and querying the CDB_PDBS view, you get the list of all PDBs. Querying the CDB_TABLESPACES view, you get the list of all tablespaces of all PDBs.

CDB dictionary views provide information across PDBs:

 SQL> SELECT view_name FROM dba_views WHERE view_name like 'CDB%';
  • CDB_pdbs: All PDBs within the CDB
  • CDB_tablespaces: All tablespaces within the CDB
  • CDB_users: All users within the CDB (common and local)

Data Dictionary Views: Examples

Given below are some examples to make comparisons between DBA_xxx and CDB_xxx views.

SQL> CONNECT / AS SYSDBA
SQL> SELECT role, common, con_id FROM cdb_roles;
SQL> SELECT role, common FROM dba_roles;
SQL> CONNECT sys@PDB1 AS SYSDBA
SQL> SELECT role, common, con_id FROM cdb_roles;
SQL> SELECT role, common FROM dba_roles;
  • In the first example, connected to the CDB root and querying CDB_ROLES, you get the list of roles, common and local, of each container. Note that the new column CON_ID displays the container the role belongs to.
  • In the second example, querying DBA_ROLES, you get all common roles of the CDB root only (there cannot be any local roles in the CDB root).
  • In the third example, connected to the PDB1 and querying CDB_ROLES, you get the list of roles, common and local, of the container you are connected to. The CON_ID displays the same value in all rows.
  • In the fourth example, querying DBA_ROLES, you get the same list except that there is no CON_ID column. Because the CON_ID column in CDB_ROLES displays the same value in all rows, this value is not helpful.

The same backward-compatibility principle applies also to each of the familiar v$views. Access to V$views showing data from PDBs can be secured using privilege.

SQL> SELECT name,open_mode FROM v$pdbs;

NAME               OPEN_MODE
----------------   ----------
PDB$SEED           READ ONLY
PDB1               READ WRITE

Data Dictionary Views: V$xxx Views

Below are some examples of V$xxx views. The new column CON_ID in V$xxx views display how the single SGA is accessed by any PDB within the CDB.

SQL> SELECT distinct status, con_id FROM v$bh order by 2;

STATUS        CON_ID
----------    ------
 cr            1      <--- CDB root
 free          1
 xcur          1
 xcur          2      <--- CDB seed
 cr            3      <--- PDB1 PDB
SQL> select OBJECT_ID, ORACLE_USERNAME, LOCKED_MODE, CON_ID from V$LOCKED_OBJECT;

OBJECT_ID    ORACLE_USERNAME  LOCKED_MODE  CON_ID
----------   ---------------- -----------  -------
    83711     SYS               3            3       <--- PDB1 PDB
    83710     DOM               3            4       <--- PDB2 PDB

In the first example, the V$BH view provides the list of distinct status of the block buffers currently in the buffer cache. The blocks are clearly identified in the CON_ID column, each block being accessed by a specific container. 1 stands for the CDB root, 2 for the CDB seed, and 3 for the PDB1 pluggable database.

In the second example, the V$LOCKED_OBJECT view provides the list of locks currently held on objects in different PDBs. The locks are clearly identified in the CON_ID column as locked by a specific container. 3 stands for one PDB and 4 for another PDB.

After CDB Creation: To do List

After the CDB is created, the container database administrator (CDBA) has to complete administrative tasks:

  • Create the SPFILE from the PFILE.
  • Execute the $ORACLE_HOME/rdbms/admin/utlrp.sql SQL script.
  • Optionally plug non-CDBs if the initial plan was to consolidate several non-CDBs into a single one.
  • Test startup and shutdown procedures.
  • Create new event triggers to automate PDBs opening.
  • Create backup and recovery procedures.

After possible PDB creation during the CDB creation, the pluggable database administrator (PDBA) has to complete administrative tasks in its own PDB:

  • Set a default permanent tablespace.
  • Create additional temporary tablespaces if specific amount of temporary space is required in the PDB.

Automatic Diagnostic Repository

All traces, incident dumps and packages, the alert log, Health Monitor reports, core dumps, and more files are stored in the Automatic Diagnostic Repository (ADR), a file-based repository for database diagnostic data. It has a unified directory structure across multiple instances and multiple products stored outside of any database. It is, therefore, available for problem diagnosis when the database is down. Nothing is changed with the arrival of container databases. Each instance of each product stores diagnostic data underneath its own ADR home directory. Each CDB, linked to a single instance, stores trace files in the same ADR home directory.

Its location is set by the DIAGNOSTIC_DEST initialization parameter. If this parameter is omitted or left null, the database sets DIAGNOSTIC_DEST upon startup as follows: if the environment variable ORACLE_BASE is set, DIAGNOSTIC_DEST is set to $ORACLE_BASE. If the environment variable ORACLE_BASE is not set, DIAGNOSTIC_DEST is set to $ORACLE_HOME/log.

Automatic Diagnostic Repository: alert.log File

The alert_CBD1.log shows new DDL statements as shown below:

CREATE DATABASE cdb1
…
ENABLE PLUGGABLE DATABASE
SEED   FILE_NAME_CONVERT=('/u01/app/oradata/CDB1','/u01/app/oradata/seed');

CREATE PLUGGABLE DATABASE PDB$SEED AS CLONE USING …
CREATE PLUGGABLE DATABASE pdb1 … ;
ALTER  PLUGGABLE DATABASE pdb1 UNPLUG INTO … ;
ALTER  PLUGGABLE DATABASE ALL OPEN ;
ALTER  PLUGGABLE DATABASE pdb2 CLOSE IMMEDIATE ;

Some of the common DDL statements logged in alert log are listed below:

  • CREATE PLUGGABLE DATABASE
  • ALTER PLUGGABLE DATABASE
  • DROP PLUGGABLE DATABASE

Provisioning New Pluggable Databases

There are different methods to provision new PDBs in a CDB.

  • Create a new PDB from the CDB seed, the PDB$SEED, for example for a new application implementation. This type of PDB creation is nearly instantaneous.
  • Plug an unplugged PDB into another CDB or into the same CDB. For example, you have to upgrade a PDB to the latest Oracle version, but you do not want to apply it on all PDBs. Instead of upgrading a CDB from one release to another, you can unplug a PDB from one Oracle Database release and then plug it into a newly created CDB from a higher release. In case you unplugged a PDB inappropriately, you can still replug it into the same CDB.
  • Plug non-CDBs in a CDB as PDBs, as part of the migration strategy. It is also a good way to consolidate several non-CDBs into a CDB.
  • Clone a PDB from another PDB of the same CDB. For example, you want to test the application patch of your production. You first clone your production application in a cloned PDB and patch the cloned PDB to test.
  • Relocate a PDB into another CDB so as to dispatch resources.
  • Proxy a PDB. A proxy PDB provides fully functional access to another PDB in a remote CDB. This feature enables you to build location-transparent applications that can aggregate data from multiple sources that are in the same data center or distributed across data centers.

Tools

There are different tools to provision new PDBs in a CDB.

  • SQL*Plus
  • SQL Developer
  • Enterprise Manager Cloud Control
  • Enterprise Manager Database Express

To create a new PDB from the CDB seed or from an existing PDB or by plugging an unplugged PDB method, you can also use Database Configuration Assistant (DBCA).

Create New PDB from PDB$SEED

Create New PDB from PDB$SEED

The creation of a new PDB from the CDB seed is nearly instantaneous. The operation copies the datafiles from the READ ONLY seed PDB to the target directory defined in the CREATE PLUGGABLE DATABASE statement.

It creates tablespaces such as SYSTEM, to store a full catalog including metadata pointing to Oraclesupplied objects, SYSAUX for local auxiliary data, and UNDO for local undo segments.

It creates default schemas and common users that exist in the CDB seed, SYS who continues to have all superuser privileges, and SYSTEM who can administer the PDB. It creates a local user (the PDBA), granted a local PDB_DBA role. Until the PDB SYS user grants privileges to the local PDB_DBA role, the new PDBA cannot perform any other operation than connecting to the PDB.

New default service is also created for the PDB.

Summary

  • Copies the datafiles from PDB$SEED datafiles
  • Creates tablespaces SYSTEM, SYSAUX, UNDO
  • Creates a full catalog including metadata pointing to Oracle- supplied objects
  • Creates common users:
    • SYS
    • SYSTEM
  • Creates a local user (PDBA), granted local PDB_DBA role
  • Creates a new default service

Steps: With FILE_NAME_CONVERT

The steps to create a new PDB from the CDB seed are the following:

If you do not use OMF (Oracle Managed Files):

1. Connect to the CDB root as a common user with the CREATE PLUGGABLE DATABASE system privilege and execute the CREATE PLUGGABLE DATABASE statement as shown below. The ADMIN USER clause defines the PDBA user created in the new PDB with the CONNECT and PDB_DBA roles (empty role). The clause FILE_NAME_CONVERT designates first the source directory of the CDB seed datafiles and second the destination directory for the new PDB datafiles.

SQL> CREATE PLUGGABLE DATABASE pdb1
     ADMIN USER admin1 IDENTIFIED BY p1 ROLES=(CONNECT)
     FILE_NAME_CONVERT = ('PDB$SEEDdir', 'PDB1dir');

2. When the statement completes, use views to verify that the PDB is correctly created.
- The CDB_PDBS view displays the list of the PDBs and the CDB_TABLESPACES view displays the list of the tablespaces of the new PDB (SYSTEM, SYSAUX, UNDO).
- Still connected to the CDB root, open the PDB. Then try to connect to the new PDB under common user, SYS who always exists in any PDB, or the user defined in the ADMIN USER clause, admin1.

SQL> CONNECT / AS SYSDBA
SQL> SELECT * FROM cdb_pdbs;
SQL> SELECT * FROM cdb_tablespaces;
SQL> SELECT * FROM cdb_data_files;
SQL> ALTER PLUGGABLE DATABASE pdb1 OPEN RESTRICTED;
SQL> CONNECT sys@pdb1 AS SYSDBA
SQL> CONNECT admin1@pdb1

3. The CDB_PDBS view shows the STATUS of the new PDB: it is NEW. The PDB has never been opened. It must be opened in READ WRITE or RESTRICTED mode for Oracle to perform processing that is needed to complete the integration of the PDB into the CDB and mark it NORMAL. An error will be thrown if an attempt is made to open the PDB read only.

Steps: Without FILE_NAME_CONVERT

If you use OMF or PDB_FILE_NAME_CONVERT, then first connect to the CDB root as SYS.

- With OMF, set, in init.ora, the DB_CREATE_FILE_DEST instance parameter to a target directory for the datafiles of the new PDB. For example:

DB_CREATE_FILE_DEST = '/u01/app/oradata/CDB1/pdb1'

- Without OMF, set the PDB_FILE_NAME_CONVERT new instance parameter to both the source directory of the CDB seed datafiles and the target directory for the new PDB datafiles.

PDB_FILE_NAME_CONVERT = '/u01/app/oradata/CDB1/seed','/u01/app/oradata/CDB1/pdb1'

The /u01/app/oradata/CDB1/pdb1 directory must exist.

- Or you can also use the clause in the CREATE PLUGGABLE DATABASE command:

CREATE_FILE_DEST = '/u01/app/oradata/CDB1/pdb1'

Then use the cdb_pdbs view to verify that the new PDB and its tablespaces exist:

SQL> SELECT * FROM cdb_pdbs;
SQL> SELECT * FROM cdb_tablespaces;
SQL> SELECT * FROM cdb_data_files;

You May Also Like