Why all pluggable database names should be unique

December 19, 2019

2–3 minutes

A quick tip just in time for Christmas Smile

I logged on to my database this morning, and things just didn’t look right. In fact, they looked down right alarming. All my objects were gone, my user account had the wrong password..It was almost as if I was connecting to a totally different database!

That’s because I was! Smile Here is how it happened:

I have a 19c database, called it DB19, and it had a single pluggable database within it called PDB1. (As you can see, I am just a wizard when it comes to picking meaningful names!). This is my “day to day” database for doing AskTOM questions, research, education etc etc.
Over the past few days, I’ve been exploring some options with cloning pluggables, application containers etc. So I needed more pluggables and more databases. So I created a second 19c database, called DB19S which also had a single pluggable database called PDB1.
You can probably see what is coming here, but let me reinforce the point by taking a look at the listener on this machine once I had created my second database.


C:\Users\hamcdc>lsnrctl status LISTENER19

LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 18-DEC-2019 16:19:03

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1519)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER19
Version                   TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production
Start Date                12-DEC-2019 17:59:32
Uptime                    5 days 22 hr. 19 min. 31 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\oracle\product\19\network\admin\listener.ora
Listener Log File         C:\oracle\diag\tnslsnr\gtx\listener19\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1519)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1519ipc)))
Services Summary...
...
...
Service "pdb1" has 2 instance(s).
  Instance "db19", status READY, has 1 handler(s) for this service...
  Instance "db19s", status READY, has 1 handler(s) for this service...
...
...
The command completed successfully

Once you have two pluggables (each from a different container) with the same name, they are going to register that name with the listener. Hence the database you get directed to when you use that access is going to be somewhat arbitrary. Probably not what you want Smile

So whilst it is not mandatory, I would strongly recommend that every pluggable database on your server have a unique name. Then you won’t get caught out like I did.

Have a safe and happy festive season everyone.

3 responses to “Why all pluggable database names should be unique”

Parag Kansara

December 20, 2019 at 12:23 am

Ah! the never ending assumption of us DBAs and Developers, “I was connected to the right database (at least that’s what I thought)”. After years of experience, I have now learned to color code my SQL*Plus screens. Black background with Green fonts (Mainframe anyone?) for DEV/TEST/QA environments AND Black background with Red font for PROD. No more excuses, S#!t I executed DELETE or TRUNCATE in the wrong environment.

Good to know and learn from your experience to name every PDB uniquely. One more thing to remember.

As always, thank you for sharing!

Reply
pjkansara

December 20, 2019 at 12:25 am

Ah! the never ending assumption of us DBAs and Developers, “I was connected to the right database (at least that’s what I thought)”. After years of experience, I have now learned to color code my SQL*Plus screens. Black background with Green fonts (Mainframe anyone?) for DEV/TEST/QA environments AND Black background with Red font for PROD. No more excuses, S#!t I executed DELETE or TRUNCATE in the wrong environment.

Good to know and learn from your experience to name every PDB uniquely. One more thing to remember.

Reply
oraclebase

December 20, 2019 at 4:58 pm

Hi.

The definition of the pdb_name says this,

“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.”

And the second note at the top of this page mentions collisions.

https://docs.oracle.com/database/121/SQLRF/statements_6010.htm#SQLRF55686

Luckily (I guess) I had this same issue quite early in 12.1 days, and wasted the best part of a day trying to figure out what was going on, so it kind-of stuck in my head, but it’s a really easy thing to miss because on the surface it feels like it should be fine, until you look at the listener services.

For nearly all my demos of clones and relocates I now build multiple VMs, because I’m sick of making stupid mistakes, trying to “keep things simple”. 🙂 And of course, if in doubt, rebuild!

It’s these silly little things that drive people nuts when they are just getting into the CDB/PDB stuff. 🙂

Cheers

Tim…

Reply

Learning is not a spectator sport