OCP 12C – Managing CDBs and PDBs

Cyrille Modiano

OCP Oracle DBA in Montreal at Desjardins

Latest posts by Cyrille Modiano (see all)

CDB

Connecting to a CDB or PDB

  • Connecting to a CDB or PDB is not different of connecting to a standard database.
  • When you create a PDB, it is automatically assigned a service which can be used to reach this specific PDB.
  • You can either use the EZ connect syntax, the Oracle Net Service syntax or the new : ALTER SESSION SET CONTAINER syntax.
  • The CDB_SERVICES view lets you view the services for each PDB.
  • The SHOW CON_NAME command can be used to identify the container your are connected to.

Users and administrative tasks

  • Only a common user can connect to CDB$ROOT.
  • To use the ALTER SESSION SET CONTAINER command, a common user must have the SET CONTAINER privilege.
  • All management tasks except PLUG/UNPLUG and STOP/START  in a PDB are equivalent to a standard database.
  • You can use the CONTAINER=ALL clause in a DDL statement to execute it in all PDBs. This can only be done by a common user with the SET CONTAINER privilege.
  • By DEFAULT when you issue a DDL the CONTAINER clause is equal to CURRENT.
  • A common user name starts wth C##

Starting and Stopping CDBs and PDBs

  • Stopping and starting a CDB is not different from a standard database.
  • As there is only one instance (the CDB) a PDB can’t be opened if the CDB is not.
  • Starting/stoping  a PDB :
    • ALTER PLUGGABLE DATABASE MY_PDB OPEN/CLOSE;
  • Starting/stoping  all PDBs :
    • ALTER PLUGGABLE DATABASE ALL OPEN/CLOSE;
  • Starting/stoping  all PDBs except one :
    • ALTER PLUGGABLE DATABASE ALL EXCEPT ONE_PDB OPEN/CLOSE;
  • By default all PDBs are mounted when you open a CDB
  • A PDB can be :
    • OPEN READ WRITE
    • OPEN READ ONLY
    • OPEN MIGRATE
    • MOUNTED
  • the V$PDBS view can be used to see the OPEN_MODE of each PDB.

Changing parameters for a CDB/PDB

  • Only the CDB has a SPFILE
  • Some parameters can be modified at the PDB level, you can find them by querying the V$PARAMETER view : select name, value from v$parameter where ispdb_modifiable=’TRUE’;
  • All PDBs will inherit parameters from the CDB if you don’t set them at the PDB level.
  • All instance parameters are only alterable at the CDB level.

Querying management views in a multitenant architecture

  • CDB_XXX views show information about the CDB$ROOT and all the PDBS
  • DBA_XXX views show information for the current container (PDB or CDB)
  • ALL_XXX views show information about objects a user can access inside a specific container.
  • USER_XXX views show information about objects owner by a user inside a specific container.
  • V$CONTAINERS displays information about PDBs and the root associated with the current instance.
  • V$PDBS displays information about PDBs associated with the current instance.
  • CDB_PDB_HISTORY shows the history of the PDBs in a CDB. It provides information about when and how each PDB was created and other information about each PDB’s history.

Users, Privileges and Roles in a multitenant architecture

  • A common user is a user which belongs to the CDB$ROOT but is known by all the PDBs too. They can perform action in the root as well as in some PDBs if they are granted privileges to do so.
  • Common user name always start with C##
  • To create a common user, a common user must have the CREATE USER and SET CONTAINER privileges.
  • Local users are only known in a specific PDB, they can’t perform actions outside the scope of the PDB where they have been created. They can’t be created in the root.
  • Local users can’t grant common privileges to a common user.
  • Local users can grant local privileges to a common user.
  • rivileges can be LOCAL or COMMON too
  • A LOCAL user can only be granted LOCAL privileges
  • A COMMON user can have both LOCAL and COMMON privileges.
  • Roles can be LOCAL or COMMON, as for username, a common role name always start with C##
  • Unlike privileges a LOCAL user can be granted both LOCAL roles and COMMON roles.

2 thoughts on “OCP 12C – Managing CDBs and PDBs

  1. “Local users can’t grant privileges to a common user.” – that is not true
    Good summaries for OCP12c !

    1. Hi George,

      Thanks for reading my blog and pointing out my mistake. You are righ, I replaced “Local users can’t grant privileges to a common user.” by :
      – Local users can’t grant common privileges to a common user.
      – Local users can grant local privileges to a common user.

      See you.

      Cyrille

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.