A quick tip just in time for Christmas
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! 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 126.96.36.199.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 188.8.131.52.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
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.