Oracle 12c Multitentant – My Top Ten PDB Questions PartI

Q. What are my options for connecting to a Pluggable Database ?

A. Connect to root, then
SQL> alter session set container = nisha;

Database connection using easy connect

Ex: CONNECT username/password@host[:port][/service_name][:server][/instance_name]

$ sqlplus test1/test123@//localhost/nisha

Need to define Database connection using a net service name

Example TNSNAMES.ora:
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

CDB1 =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (SERVICE_NAME = cdb1)

nisha =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
      (SERVICE_NAME = nisha)

Q. How do I switch to main(root) container Database ?


Q. How do I determine which PDB or CDB I am currently connected to ?

SQL> show con_name


SQL> select sys_context ( ‘Userenv’, ‘Con_Name’) “Container DB” from dual;
Container DB

Q. What are my options to migrate an existing pre 12.1 database to 12c Multi-tenant database ?

Option 1.
• Upgrade an existing pre 12.1 database to 12.1
• Plug-in the database post upgrade into a CDB
Option 2.
• Create a staged PDBs based on pdb$seed
• Use datapump or golden gate replication to migrate a Database into a PDB

Q. How do I know if my database is Multitenant or not ?

A. From root container execute:
SQL> select NAME, DECODE(CDB, ‘YES’, ‘Multitenant Option enabled’, ‘Regular 12c Database: ‘) “Multitenant Option ?” , OPEN_MODE, CON_ID from V$DATABASE;
NAME                        Multitenant Option ?                  OPEN_MODE              CON_ID
———           ——————————           ——————–           ———-
CDBJIVE              Multitenant Option enabled                      MOUNTED                       0

Q. How do I know What Pluggable databases do we have in this container database ?

    CON_ID NAME                           OPEN_MODE
———- ————————        ————
         2 PDB$SEED                         READ ONLY
         3 PDB1                                 MOUNTED
         4 PDB2                                 MOUNTED
         5 PDB3                                 MOUNTED
         6 PDB4                                 MOUNTED
         7 PDB5                                 MOUNTED
         8 nisha                                MOUNTED
         9 nitin                                MOUNTED

Q. What is the different Container ID signify?

A. CON_ID “0” means data does not pertain to any particular Container but to the CDB as a whole. For example, a row returned by fetching from V$DATABASE pertains to the CDB and not to any particular Container, so CON_ID is set to “0”.  A CONTAINER_DATA object can conceivably return data pertaining to various Containers (including the Root which has CON_ID==1) as well as to the CDB as a whole, and CON_ID in the row for the CDB will be set to 0.

Following table describes  various values of CON_ID Column in Container Data Objects.
0 = The data pertains to the entire CDB
1=  The data pertains to the root
2= The data pertains to the seed
3 – 254 = The data pertains to a PDB, Each PDB has its own container ID.
Q. Do I need separate SYSTEM,SYSAUX, Temporary tablespaces, and Undo for each of my PDB ?

A. There is a separate SYSTEM and SYSAUX tablespace for the root and for each PDB. However, there is one default temporary tablespace for the entire CDB. But, you can create additional temporary tablespaces in individual PDBs. One active temporary tablespace is needed for a single-instance CDB, or one active temporary tablespace is needed for each instance of an Oracle RAC CDB. There is one active undo tablespace for a single-instance CDB. As with previous versions, for RAC [CDB], there is one active undo tablespace for each instance. Only a common user who has the appropriate privileges and whose current container is the root can create an undo tablespace.

Q. Can I specify a separate default tablespace for the CDB and for each PDB ?

A. Yes. You can specify a separate default tablespace for the root and for each PDB

Q. Does the CDB contain any user data ?

A. No. All user data is in the PDBs. The root contains no user data or minimal user data. This makes the unplug-ability very streamlined

Q. Does Pluggable database support separate database characterset ? 

A. A CDB uses a single character set. All of the PDBs in the CDB use this character set.
Q. Is there a specific Net Files in a Pluggable database environment ? 

A. There is a single listener.ora, tnsnames.ora, and sqlnet.ora file for an entire CDB. All of the PDBs in the CDB use these files.

Q. Can I change init.ora for my PDB.
For Multitenant there are two groups of parameters: – Those that can be modified within a PDB and those that can only be set at CDB level
The following query helps to determine: