8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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. There is a note about possible collisions towards the top of the page.
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.
For more information see:
Hope this helps. Regards Tim...