Friday, 9 June 2017

Multitenant : Pluggable Database (PDB) Names

The CREATE PLUGGABLE DATABASE command includes a very brief, but important statement about the naming of pluggable databases (PDBs).

"Specify the name of the PDB to be created. The first character of a PDB name must be alphanumeric and the remaining characters can be alphanumeric or the underscore character (_).

The PDB name must be unique in the CDB, and it must be unique within the scope of all the CDBs whose instances are reached through a specific listener."
The first part of the second paragraph seems pretty obvious. Why would we expect to have two PDBs with the same name in the same container? The second part of the second paragraph is less obvious though. The PDB name must be unique amongst all the PDBs serviced by a single listener.

Imagine a scenario where we have two CDBs under the same ORACLE_HOME, both using the same listener. If both have a PDB called "pdb1", this will cause problems when trying to connect. If we check the listener status, we can see a service called "pdb1", with two instances ("cdb1" and "cdb2").

$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 14-FEB-2015 13:15:50

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7-121.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                14-FEB-2015 12:29:40
Uptime                    0 days 0 hr. 46 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ol7-121/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-121.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "cdb1" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb1XDB" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb2" has 1 instance(s).
  Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "cdb2XDB" has 1 instance(s).
  Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "pdb1" has 2 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
  Instance "cdb2", status READY, has 1 handler(s) for this service...
The command completed successfully
$

In the "tnsnames.ora" file we have the following entry.

PDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1)
    )
  )

If we connect using the alias, we will round-robin between the two instances.

SQL> CONN sys@pdb1 AS SYSDBA
Enter password:
Connected.
SQL> SELECT name FROM v$database;

NAME
---------
CDB2

SQL> CONN sys@pdb1 AS SYSDBA
Enter password:
Connected.
SQL> SELECT name FROM v$database;

NAME
---------
CDB1

SQL> CONN sys@pdb1 AS SYSDBA
Enter password:
Connected.
SQL> SELECT name FROM v$database;

NAME
---------
CDB2

SQL>

Because we can move PDBs between containers using unplug/plugin, it would be easy to accidentally bring about this situation, so the safest approach is to make sure all PDBs have a unique name within your organisation.